[Savannah-hackers-public] Slow SQL Queries

[Bob Proulx]

FSF Nagios sent an alert on internal0 high load average earlier this
week.  Ruben notified me.  I jumped in but had already missed the high
load and things were in the after-event settle down return to normal
behavior.  But it had load spiked to 53+ within the previous few minutes.

I looked through the logs everywhere and determined that external
influences were hitting the web frontend on frontend0.  This was
flowing through to hitting internal0 for database queries.  I missed
being able to figure out exactly what was hitting the web page.  But
if it was hitting there once it will hit there again.  All we need to
do is wait.  The event did not cause a high load on the web frontend0
which handled it okay.  The slow queries caused the MySQL server to
have a high load as it forked to handle the task.

Afterward I turned on the slow query log on internal0.  This is
available moving forward.  Initially I had the time limit set at 2
seconds and it was streaming out logs of 2-second slow queries.
(Which is not a good sign.)  I reduced it to 4 seconds and then
finally back to the 10 second default value until the stream slowed to
a trickle.  At 10 seconds there are always at least one per day but at
least not streaming.  At the point that we have time to look and
improve things it needs to be reduced in steps lower and the
problematic queries found and fixed.

There appear to be many queries that search every row of the
database.  This may be unavoidable due to the web bug tracker query
possibilities.  But if a human is running the web interface the rate
of them will be slow enough that this isn't a problem.  It is only a
robot that would strike the web interface fast enough for this to be a
problem.

* Improve the robots.txt file to keep them out of wherever they were
hitting that was trouble.  But I think this was a probe attack and not
a well behaved robot.

* In other cases it appears that some columns should have an index but
do not.  Another opportunity for improvement.

* And of course many tables (most?) are of the old type and should be
migrated to an ACID3 compliant engine that also has better locking
behavior.

Time is what keeps everything from happening all at once. :-)

Bob