[plug] MySQL performance tuning

[James Devenish]

In message <s13990b3.050 at mmtnetworks.com.au>
on Sat, Sep 04, 2004 at 09:53:51AM +0800, Jon  Miller wrote:
> We are having a problem with our database in that when several users
> are accessing it and a report is being run it slows the network down
> immensely. 

(a) From your description, I doubt it is the network that is slowing
down, but rather the server that is slowing down.

(b) SQL performance depends on a huge number of factors, and the
influence of these factors is dependent upon the specific nature of
the applications and data structures you are using!

(c) It is possible that someone has simply constructed a SQL query that
is very inefficient, or involves inefficient locking mechanisms, and
this is not necessarily MySQL-specific. It should probably be corrected
within the application rather than MySQL.

(d) MySQL supports many different 'table types', and different tables
types involve different tuning considerations (this is documented for
each table type). The problem may therefore depend on what table types
are being used on the server.

To a first approximation, you  basically have to ask whether the
performance problem is being caused by:

(a) A long-running SQL query that is blocking other queries,

(b) Vast numbers of very short queries which interact poorly
with each other,

(c) A disk failure.

If (a), you should consider whether the applications / queries / tables
have been designed inefficiently (or maybe a design bug has recently
crept in). If (b), ask your users to take longer coffee breaks or serve
fewer customers ;-) You can usually look at the MySQL process list or
logs to find out this sort of information.