mysql optimization drill-down checklist

gerardg

Can anybody recommend a list of things to optimize in mysql in order of importance - such as max connections, buffers, etc. It seems to be lagging on a site with 80K daily uniques.

[deleted]

Make sure you have enough memory in your server. Swap = evil.
Make sure your database has plenty of cache defined, every record in memory is one less read-action on the disk. The more cache the better, us the all the memory (leaving some for the OS etc ofcourse)
Make sure you have indexes on the columns your queries use. Pitfall: mysql is picky about which indexes it can use for certain queries. Read the MySQL manual for details.
EXPLAIN your queries, make sure your queries are handled the way you think they are handled.
On MyISAM tables, run ANALYZE regularly (daily, just after the backup)

Anon

Run mysql with it's data files on a logical volume system (i.e. mysql's data files on stripes across multiple disks).

Anon

I am sure this can help you improuving performances:

http://www.wdvl.com/Authoring/DB/SQL/Optimize/optimize2_3.html

Vin

[deleted]

Do you want that? I mean, doesn't that slow down the write process?

Anon

It depends upon the type of disk array unit that you are writing to.

A raid system running anything other straight mirroring using IDE will always suffer write penalities.

However connecting via scsi reduces the penalty considerably, even on cheap PC disk (so yep, still having a little bit of a write impact, gain on read).

Once you move into the world of "proper" :o) servers, e.g. small Sun /HP with a small disk array, the cache on the array should negate the delay done by using raid writes and you gain on tbe raid reads.

IMHO all depends on the servers and disks.