Millions of
user_newtalk requests, conspicuously absent from the typical
dump. Many of them are doing "statistics", whatever that means, and the
rest are locked. One side of me is curious as to what "statistics" means
and why it produces this behaviour. The other side of me says
Could these hanging selects be just a part of the phenomenon?
I mean, assume, the mysql has some reason not to respond, but
still accepts new requests. Then all anonymous users reading
a page will add a new process looking for a newtalk flag. They
don't cause the slowdown, they are its victims.
So the question is: Why does it slow down every 15 minutes?
Do we have some cron jobs running every quarter of an hour?
I think you might be right that it's a symptom not a cause. I tried
disabling user_newtalk requests for anonymous users, but the problem still
occurred. But instead of large numbers of user_newtalk requests blocking, it
was article text requests. Common to both was that the blocked queries were
all in the "statistics" state.
Is it a cron job? Well if so the period should be perfectly regular. In the
captured output previously attached, there are 3 events, with the
separations between the events being 23'20" and 19'25". Another file I
have
here shows a period of 22'05" between two events. Performance tends to slow
down before it actually stops, so I'd put error bars on those times of about
20 seconds.
Currently I'm blaming the statistics state. Conventional wisdom is that a
long statistics state is caused by a complex join with no specified index.
-- Tim Starling.