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 On 23 November 2013 07:50, Jeff Tchang <[email protected]> wrote: > 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. > -- 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/CAG_XiSD-h0cHv%3D_v%3DrMNNDd3R2cxE15ia4hsj0HLBbCJBDW2KA%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
