[PLUG] Tuning MySQL

[Kyle Hayes]

On Wednesday 12 November 2003 08:31, Paul Heinlein wrote:
> We recently deployed a new set of (contractor-built) web apps at work
> that use MySQL as the backing store. According to top, mysqld is using
> about 75% of the cpu on our web server, a uniprocessor Sun V120
> running Solaris 9.
>
> That seems high to me, and vmstat/iostat indicate that most of the
> work is cpu-bound, not i/o stuff.
>
> If I were to start looking for mysqld options to tweak, what SHOW
> STATUS variables are likely to provide good clues? I haven't done any
> performance tuning with mysql, so don't feel bashful about making
> suggestions that would seem obvious to anyone with a clue. :-)

Normally, a DB is fairly IO-bound.  

Questions:

- how big are the tables?  Please give both tables and indexes (if MyISAM 
format) as % of RAM.

- how much RAM?

- what kind of disk subsystem?   RAID?  I'm assuming SCSI...

- how many simultaneous queries?  SHOW STATUS will tell you this.
Look for max_used_connections.

- what is the ratio between SELECT and mutation operations (DELETE, UPDATE, 
INSERT, REPLACE INTO etc.)?

- what version of MySQL.  4.x will do better caching of some kinds of query.

- are you pulling a lot of data back and sorting it by fields that don't have 
indexes?

- do you have some nasty cartesian joins happening?

- what table types are you using?  MyISAM?  InnoDB?  

- You do have Paul DuBois' "MySQL", right?

My gut feeling is that you have insufficient indexing and your tables are all 
small and sit in the RAM disk cache.  However, you're still doing a lot of 
table scans.  That's my guess.  If handler_read_rnd is pretty high compared 
to the other hander_read_X counters then you may be table scanning.  This is 
in the output of SHOW STATUS.

MySQL performance comes down to just a few parameters most of the time:

- if you have about 50% or more of your indexes in key cache, you'll be OK.  
It can be less if most of your queries hit the part that is in the key cache.  
When the index stops fitting in the key cache, you start to hit the knee in 
the MySQL performance curve (at least with MyISAM tables).

- if you have carefully made sure that the per-thread caches and buffers are 
fairly small, you can handle a lot of simultaneous connections.  If not, you 
will find yourself swapping.  Then you die.  I've had desktop PCs that handle 
over 500 simultaneous connections without a problem, but that was after a 
little (not much) tuning.  

These are extremely rough rules of thumb.  YMMV.  Use with caution.  Do not 
operate heavy machinery while using this product.  Etc. Etc.

Best,
Kyle