Query against MySQL database much slower than Postgres using ORM

[Jeff Tchang]

Hi,

  Can anyone help with figuring out why basic select queries using the Django ORM against a MySQL database vs a postgres database are 40x as slow? More info here: https://dpaste.de/04xz. I am using Django debug toolbar to measure as well as a management script to just do simple timings.

Using Django Debug Toolbar this query clocks in at 43.02ms:

SELECT `wp_posts`.`ID`, `wp_posts`.`guid`, `wp_posts`.`post_type`, `wp_posts`.`post_status`, `wp_posts`.`post_title`, `wp_posts`.`post_name`, `wp_posts`.`post_author`, `wp_posts`.`post_excerpt`, `wp_posts`.`post_content`, `wp_posts`.`post_content_filtered`, `wp_posts`.`post_date`, `wp_posts`.`post_modified`, `wp_posts`.`comment_status`, `wp_posts`.`comment_count`, `wp_posts`.`ping_status`, `wp_posts`.`to_ping`, `wp_posts`.`pinged`, `wp_posts`.`post_password`, `wp_posts`.`post_parent`, `wp_posts`.`menu_order`, `wp_posts`.`post_mime_type` FROM `wp_posts` WHERE `wp_posts`.`ID` = 9310

Queries against a postgres database are around 1-5ms.

- Both databases are sitting in the same datacenter.
- Ping times are comparable.
- I have tried using both MySQL drivers and get similar results.
- Using Django 1.4.2

- 

The ORM statement that generates the above:

    post = get_object_or_404(Post, id=blogid)

    tstart = datetime.now()
    post_raw = Post.objects.raw('select * from wp_posts where id = %s' % blogid)
    tend = datetime.now()

    print (tend - tstart).microseconds

The raw statement is 53 microseconds or .053ms. So the ORM is adding over 40ms worth of time. However what I am trying to figure out is why the ORM is adding this much time to the MySQL query vs postgres.

I also have some test code for retrieving a post:

    cursor = connections['wordpress'].cursor()
    blogids = [9310]

    tstart = datetime.now()
    for blogid in blogids:
      cursor.execute("SELECT * FROM wp_posts WHERE id = %s", [blogid])
      row = cursor.fetchone()
    tend = datetime.now()

    print "Time (microseconds) to retrieve a single post via raw sql: %d" % ((tend - tstart).microseconds / len(blogids))


    tstart = datetime.now()
    for blogid in blogids:
      post = wordpress.models.Post.objects.get(id = blogid)
      post.guid
    tend = datetime.now()
    print "Time (microseconds) to retrieve a single post via ORM: %d" % ((tend - tstart).microseconds / len(blogids))

The results:

Time (microseconds) to retrieve a single post via raw sql: 46894
Time (microseconds) to retrieve a single post via ORM: 91875
Time (microseconds) to retrieve an object off postgres via ORM: 4228

Any help would be appreciated.

[Curtis Maloney]

First, I would recommend you try this with Django 1.6 - the ORM performance improvements are dramatic!

Secondly, your own tests show that raw SQL to MySQL takes 10 times as long as Postgres _through_ the ORM... from which it would be reasonable to conclude that the problem is not the ORM... but simply that MySQL is slow. (Or is there a digit missing, and PG is merely 4mS faster? which is still over 8%)

The best way to test that would be to issue the raw sql direct to the DBMSs and see how long they take.

I know MySQL is not the fastest tool around, but I wouldn't expect it to be so glaring for such a simple query.  It usually takes a join or three before the query planner shows its limits.

--

Curtis Maloney

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/6035c9f7-a409-4f6d-bef8-7b25302b8e7c%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.