On Thu, Feb 5, 2009 at 12:36 PM, dan0 <dan.murie...@gmail.com> wrote:
> > Hello, > > I am having an issue with my msyqld process responding to a query > after a moderate period of inactivity. I'll issue a simple query such > as "Person.objects.filter(name__icontains='Dan')", which would map to > about 5000 entries in a table of roughly 200,000 rows, and see the > mysqld process stall at 100% cpu load for upwards of 10 seconds. > > I understand that this is not a tiny query, and it's not moving a tiny > amount of information around. Also, I gather that to properly evaluate > a query like name__icontains, the database server will likely need to > touch each row in the table. HOWEVER: the same query, issued a short > time after the first, will take roughly .2 seconds. I suspected that > this may be because the information is cached, so instead I ran a > smiliar query: "Person.objects.filter(name__icontains='James')", which > also ran in ~.2 seconds. (PS, this time includes the evaluation of the > QuerySet) > > The "puzzling" part of this is that in an attempt to profile the > function which is performing this action, I added two calls to > time.time(), one as the very first line of the function, and one as > the very last, just before a render_to_response call. One would assume > that since time.time() is a very straightforward and low-overhead > method of retrieving the system time, that this would be very > accurate, but nay. > > The initial, very slow, query, in reality taking 10+ seconds, was > profiled at roughly 2 seconds. ?????!?!??!?!??! > Are you sure your queryset was actually evaluated before the render_to_response call? Oftentimes when a queryset is included in a template context, the view does nothing to trigger evaluation, so it is not until the template is rendered that the expense of contacting the database is incurred. Thus it is quite possible the 10+ seconds was spent somewhere in the render_to_response, after your timing call. > Please, help. I have scoured the documentation and this forum, but > haven't found a solution. > > I think the effect you are seeing is more of MySQL thing than a Django thing, so that is where I'd be looking for a better idea of what the server is doing to speed up subsequent queries like this (in fact I think I recall reading about it at some point in reading up on MySQL, though I don't recall where). I see a similar effect on my own MySQL DB, independent of Django. First query after a lull is slow (though not quite as slow as yours). Subsequent identical query is practically instantaneous. Subsequent similar query is faster than the first (though not as fast as yours). In this test I didn't retrieve any of the data, just counts, which may be affecting the times of course: mysql> select count(*) from Clues; +----------+ | count(*) | +----------+ | 1110564 | +----------+ 1 row in set (0.03 sec) mysql> select count(*) from Clues where Clue like '%mystery%'; +----------+ | count(*) | +----------+ | 348 | +----------+ 1 row in set (6.02 sec) mysql> select count(*) from Clues where Clue like '%mystery%'; +----------+ | count(*) | +----------+ | 348 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from Clues where Clue like '%fish%'; +----------+ | count(*) | +----------+ | 2407 | +----------+ 1 row in set (1.81 sec) mysql> select count(*) from Clues where Clue like '%kittens%'; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (1.68 sec) mysql> select count(*) from Clues where Clue like '%perfect%'; +----------+ | count(*) | +----------+ | 733 | +----------+ 1 row in set (1.68 sec) mysql> select count(*) from Clues where Clue like '%mystery%'; +----------+ | count(*) | +----------+ | 348 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from Clues where Clue like '%perfect%'; +----------+ | count(*) | +----------+ | 733 | +----------+ 1 row in set (0.00 sec) Karen --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
