When too much RAM hurts- Centos/Redhat 5 and writes

Had an odd troubleshoot on linux.  DB server, over 200G of RAM, having problems every 30 seconds with DB queries.

The symptom was looking like blocking on I/O but not much actual I/O happening.  DB was on NFS without an async mount, so all writes being acknowledged.  Problem not really apparent on local disk that had write cache on the raid.  1G NFS mount made more problems than 10G.  So it kinda looked like write acknowledgement was a problem.  DB on a beefy NetApp so performance should be awesome.  Checked all the best practice stuff (NFS window size, netapp options, mysql .conf options. etc. etc.).  Had not gone to async on the mount yet.

Finally looking at what mysql was waiting for and how much BW was going to NFS mount on the netapp (nmon, iptraf, vmstat, iostat, top, etc), we found it waiting on a write but not really any iowait showing.

Then found this doc:

http://westnet.com/~gsmith/content/linux-pdflush.htm

Centos 5/Redhat 5 sets aside cache as a % of memory- good.  It flushes it after it gets a certain amount of memory or after 30 seconds.  Also good.  Unless you have like 28G of cache because it is taking 10% of physical memory- then  flushing 28G can be a problem.

So with a write instensive load turning that down to something the interface can keep up with makes a huge difference.  We went down to 1% of ram and flush basically every 3 seconds, although I'm considering tighter tuning on the flush interval.  Problem gone.  Writes stream to disk no problem.

How to tell if you have a MySQL performance problem

The top three things for mysql performance:

1) run 64bit OS on 64bit hardware with 64bit version of mysql (newer versions of mysql do better with large amounts of RAM if you aren't using 4.1+ you may not be able to use enough RAM).
2) make sure your indexes fit in RAM
3) Make sure you log slow queries and check the logs for problems. This is your number

Number 1 is easy.

Number 2 is a little harder. You can eyeball this if you are running ISAM databases by summing up all your IDX files and then seeing if MySQL is using more RAM than that.

But a really good way is to run a report: http://hackmysql.com/mysqlreport It has really good documentation and can make suggestions. You can use it to tune other options.

Number 3 requires a change in logging. You can also change the threshold- what is slow for you may not be for someone else. The problem with logs is you have to look at them, understand them and then act on them. Get going.

MySQL stupidity

If you want to be able to troubleshoot mysql:

1) mysqlreport is very cool: http://hackmysql.com. The documentation is awesome and for a quick rush it is fabulous. After you get it installed try having it mail you a tab delimited report: mysqlreport --email user@domain.com --pass --all -tab

2) mytop is also very cool:

3) making sure your indexes fit in RAM is very cool, ls *MYI and sum them up. That amount should be less than physical ram (and less than the key_buffer_size variable from the my.cnf--- you did already check the my.cnf).

4) you can make some changes on the fly, check your variables with show variables; then you can set them with the mysql client

5) make sure your mysql install is logging :roll eyes: