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.
--
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 [email protected].
To post to this group, send email to [email protected].
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.
