Optimum MYSQL settings

[A.L.M.Buxey at lboro.ac.uk]

hi,

gosh. its such a wide question (well, the answer
can be very open...). there are many many ways to
optimise the DB - you can chuck more memory
at the server settings - increase the buffers etc.
you can add more index keys to the tables...
you can change the DB engine - eg InnoDB instead
or MyISAM (default) - heck, even switch to
postgres insteaa (some will claim its orders
faster! ;-) ). in essence I'd start with the usual
basic optmisations - move to a my.cnf file thats
inline with the large or huge .cnf examples

but then I'd look at your queries and find why
they take so long - use the 'explain' command
to emulate the select and inserts...find out
what causes the locks etc. you can also turn on
the slow debug for mysql and see what and where
things are lurking.  theres also useful tools
to explain what and where your mysql settings
are failing - that give pointers to better
performance.  the real optimisations will
come from analysis of the data going into
the DB and how its being used...I've achieved
huge gains with such DB table tweaking for minimal
memory loss - searches orders of magnitude faster.

alan