[GLLUG] mysql slow

[Mike Rambo]

Hey all,

Does anyone have any ideas on what to look for when mysql queries are a 
bit slow? We are using a home grown work order tracking system 
(php/mysql) and have noticed latencies increasing of late. Actually, I 
think some of it may have coincided with a change made to one of the 
main tables a short while back when I added some additional function. 
I've ran table checks and re-indexed them but it hasn't helped. The 
largest table is about 21MB in size with a little under 40k records.

Some background...

The db runs on an old Proliant 3000 running an ancient RH Linux (7.3 I 
think) but it's worked well for years and the box isn't exposed 
externally. There is no trouble signs in the logs (that I've found 
anyway). The hardware is 300MHz PII with 384MB. Lots of HDD and we're 
not swapping.

Most of my selects are still simple selects from a single table though 
there are are increasing numbers that join two or three tables as this 
thing increases in complexity. I'm not a sql guru so I try to keep 
things simple. The specific function that has caused this thing to come 
to a head is one where I update about six of the 26 fields in the 
primary table in a single sql udpate. It is slow returning and there 
isn't much php code behind it either (about two dozen lines of code 
which include six conditionals (if statements in this case)).

I think we're going to be getting a new box to run this on. I'm sure 
that will help since more cycles processed each second can only improve 
things but since I'm not sure that is really the root cause of the 
problem I'd prefer to sort out what the problem really is first.

Perhaps adding fields to an existing table adds overhead that a re-index 
will not resolve??? I don't know.


-- 
Mike Rambo


To mess up a Linux box, you need to work at it; to mess up
your Windows box, you just have to work on it.
     -Scott Granneman