Diagnosing and fixing MySQL I/O Performance.

Slow Inserts/Selects/Joins? High Load? High I/O wait?  


Hopefully this post will provide a few ideas for diagnosing and fixing some MySQL I/O issues.
All examples are based on InnoDB but most of the principals are the same/similar for MySAM.


Disks and storage can be a huge bottleneck for MySQL databases. Due to the nature of random I/O, disks with low seek times or poorly configured SAN infrastructure can really affect the performance of your MySQL server.


The obvious answer is get faster disks or SSD drives. This might not always be cost effective or practical. Who wants to walk down the corridor to visit purchasing and ask for a few thousand pounds when you can make some improvements from terminal?


Diagnosis:


So, let's look at a couple of tools to diagnose I/O problems:


iostat -x 2


This will give you a sample of your I/O wait (percentage of time that your CPUs are idle while waiting for a disk I/O request) every 2 seconds. Depending on how many cores you have this number will vary. 
With more modern CPUs that have multiple cores a higher I/O wait can be tolerated with out this being a problem. 


iostat -xcknd 1 120 > ioout.txt 


This will give you a 2 minute snapshop of your I/O wait stats and redirect it into a file for you check over.


A high I/O wait time coupled with high load can be a bad sign. On our 48 core systems I see a 7% wait time but a load of 0.2 so it's not much of an issue.


iotop -o -b 


This will give you a list of the processes that are writing to disk and the -b switch will format it nicely for logging over time.


ps -axu


Using the command ps -axu  and looking for commands with the state D will show you processes waiting for disk. Or you can get some help from watch and awk -


watch -n 1 "(ps aux | awk '\$8 ~ /D/  { print \$0 }')"


There are many more tools you could use: vnstat, top, nmon, systat, etc..


These examples will point you in the right direction when looking for I/O issues. The important thing is to remember is that all systems are different and you will need to look into the system and analyse what tolerance levels you can accept.
I am not going to be reckless and tell you what results you should be expecting to see. Performance is subjective right?




Possible Tweaks


Indexes/Queries: 


I will only touch on this subject in this post but a very important part of making sure you are getting the best MySQL performance is to make sure you are indexing on the correct fields for your application.
It is also important to make sure your queries are as optimized and application aware as possible. Make sure you are always using explain extended to make sure you are hitting your indexes.


explain extended SELECT foo FROM bar;


I am going to do another post where I will cover Indexes and queries in much more detail.


Memory:


Adding RAM to your server is a very easy and often cost effective way of reducing the disk I/O dependency of MySQL.
InnoDB uses it's buffer pool to buffer reads and writes, the more you can cache the less strain you put to disk.
If your server is dedicated to MySQL it's normally safe to allocate a large chunk of your memory to your innodb_buffer_pool. You can change this value by editing your my.cnf file.


In the past I have seen people recommending that you should set your  innodb_buffer_pool limit to around 10% larger than your data. This is a good figure but can be difficult if you store a large amount of data, so experiment with the amount of memory you can afford to allocate.
It's very important to make sure you never let your system start swapping, this will cause a huge drop in performance and will just act against the goal you have. If this happens reduce the buffer pool size until you reach the sweet spot.


Splitting I/O:


This will only really work for systems that are storing MySQL data on remote storage like a SAN.
InnoDB creates two log files, (ib_logfile0 , ib_logfile1) InnoDB writes to these log files as well as writing data to it's data files. If both the data and log files are stored on the SAN then you are doing twice as many writes to one set of disks than you need to.
You can move these log files to the local storage on the server by shutting down MySQL, editing my.cnf (innodb_data_file_path) and moving the files. You can also just rename the files and symlink them back to local disk and let MySQL recreate them.


If you are using a SAN you should read my post on Pushing your SAN with Open-iSCSI http://www.samlambert.com/2011/07/how-to-push-your-san-with-open-iscsi_13.html


Swappiness:


If you want to help Innodb keep tables and indexes in memory and further reduce I/O you can change the Linux swappiness value.


Swappiness is a value between 0 and 100. 100 means that Linux will be very aggressive at reclaiming memory from applications and will preemptively swap to disk. 0 will mean that memory won't be reclaimed as often allowing you cache better.


Set your swappiness to 0 by running the command


echo 0 > cat /proc/sys/vm/swappiness




RAMdisk:


For those who cannot Split I/O or just want to utilize more RAM you can force mysql to create temporary files on a RAMdisk.


You can create a 2 gb RAMdisk using the following commands:


1.mkdir -p /db/ramfs && chown mysql:mysql /db/ramfs
2.mount ramfs /db/ramfs -t ramfs -o size=2G
3.vi my.cnf file and add: tmpdir = /db/ramfs
4.service mysql restart


I hope that some of you will of found this post helpful and it will have at least provoked some thoughts about MySQL and I/O.


I will carry on updating this blog offering advice and articles on MySQL, Message queuing, Telemetry, Linux, general bits and bobs.


Thanks for reading! Don't forget to bookmark and share. Any questions please comment.