[GLLUG] mysql slow

[Sean O'Malley]

Check the indexes and make sure you are indexing the right fields and the
right type of data.

Your new queries might be doing a select on FIELD_B where FIELD_B isn't
indexed. It may also be improperly indexed. Like you are doing a 'select *
from FIELD_B where NAME like 'lsd%';' and you aren't indexing record
subsets(or parts of the field), you are just indexing the whole field.
Also on the same note, if you are indexing TOO much ie you are querying
whole strings in a record, and indexing partials, you will see a
performance impact. You most likely have to revisit your queries as well.

Make sure you are using the proper database libraries. They have several
different database libraries they can tie into various libraries like
mysql, innodb, berkeley(?), etc. IIRC innodb is/was the fastest but it
might not be the default for your version of mysql. I had to switch a
bunch of them before i migrated another database because the mysql
libraries were no longer supported.

Since you are running on ancient hardware.. :)
when you rebuild the indexes, it rebuilds the entire file and replaces it,
if you run OUT of space on the partition it is running on or logging to,
your indexes can get hosed and you will see performance issues as it
truncates the index file. :) If this is an issue you can use an alternate
partition to build the new index file on. And along the same lines, there
might be either disk corruption (bad sectors) or severe fragmentation
causing speed issues. IIRC that drive is raided, and maybe one of the
drives failed.

There is also a way to view mysql statistics. You can see if you are
exceding the number of connections, etc. Along the same lines, since you
probably rewrote some code, make sure that code actually closes the
connection when it is done executing to free up the resources on the db.
You should also be able to see if you need to up the in memory cache. I
want to say the command is mysql_analyze, but I would have to look it up
for sure.

It may be time to cull some of the data from it. Duplicate the
main table schema, and create a new table called archive with that schema
or just dump and save it if you need to keep the data. I don't know 40k
records and 21MB isn't that huge, but it is a good time to look at it and
see if you actually need some of that data. If you do drop a bunch of
records, you probably want to reindex it (again).

My guess is you will figure it out. :)




On Tue, 13 Nov 2007, Mike Rambo wrote:

> 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
> _______________________________________________
> linux-user mailing list
> linux-user at egr.msu.edu
> http://mailman.egr.msu.edu/mailman/listinfo/linux-user
>