[plug] MySQL performance tuning

[Craig Ringer]

On Sat, 2004-09-04 at 09:53, Jon Miller wrote:

> We have about 25 users accessing the database and plan to have about
> twice that in the near future.
> This is our config file, are there settings that can be tweaked to use
> more memory and less CPU?

To the best of my knowledge, the memory tradeoff with an RDBMS is
against disk I/O, rather than CPU usage. As disk I/O should have
relatively little effect on CPU usage on a modern x86 system, I suspect
your CPU usage may be unrelated.

The important measures that you've omitted are what your disks are
doing, how they're set up, and what your network activity is like. ATA
or SCSI disks, software or hardware RAID (or no RAID), RAID 1, 5, or 10,
etc. When it comes to what they're doing, vmstat may be useful and
gkrellm gives an excellent visual impression that I often find
invaluable, and it's also good for a quick view of network load.

It does seem odd that mysqld would only be consuming 1-2% of the
server's 2GB of RAM when operating on 300MB tables, but it may not
actually need most of the table contents for the reports you are
running, and so may not be loading them. The indexes and a couple of
fields may well be sufficient.

Also remember that in most cases even if mysqld doesn't have the table
loaded, the kernel will have it in the disk cache - so it's still living
in memory and ready for rapid access.

At this point, it sounds like you need to look at your queries, rather
than your database, but that's only my inexpert opinion. Does mysql
support EXPLAIN ANALYZE ? It can be mighty useful:


craig=# EXPLAIN ANALYSE SELECT count(id) FROM ledger;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=22.50..22.50 rows=1 width=4) (actual time=903.217..903.218 rows=1 loops=1)
   ->  Seq Scan on ledger  (cost=0.00..20.00 rows=1000 width=4) (actual time=10.560..774.113 rows=99432 loops=1)
 Total runtime: 930.534 ms
(3 rows)

--
Craig Ringer