While stress-testing a project hosted on Amazon, we've found an issue with the MySQL Server (2 CPU, 4G RAM): slow and unresponsive !
[cc lang='bash']
root@nms:\~# ab -c 10 -n 100 "http://mysql/stress.php" 2>/dev/null | grep "Time taken for tests"
Time taken for tests: 11.150 seconds
[/cc]

Let's dive into it:
- a quick "top" check revealed 100% CPU usage with 97% wa, so the issue was disk io
[cc lang='bash']
top - 12:10:43 up 3 days, 27 min, 1 user, load average: 21.75, 7.37, 3.23
Tasks: 155 total, 1 running, 154 sleeping, 0 stopped, 0 zombie
%Cpu0 : 2.4 us, 0.3 sy, 0.0 ni, 0.0 id, 97.3 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 1.7 us, 0.7 sy, 0.0 ni, 0.0 id, 97.7 wa, 0.0 hi, 0.0 si, 0.0 st
[/cc]
- since a graph tells more than 1000 words, we've also checked the collectd graphs and spotted a curious thing: the disk causing the issues was not the MySQL disk (/var/lib/mysql is mounted on a high performance ssd), but the root disk !
- using the block_dump option, we nailed it: MySQL was creating some temporary tables under /tmp (default config) and this was causing the slowness

The fix (for the tests bellow, we increased 10 times the concurrency and total requests number):
- the first attempt was to move the mysql "tmpdir" to "/mnt/mysql-tmp/" (the ephemeral / instance storage)
[cc lang='bash']
root@nms:\~# ab -c 100 -n 1000 "http://mysql/stress.php" 2>/dev/null | grep "Time taken for tests"
Time taken for tests: 21.024 seconds
[/cc]
- but the final option was to move it to a "tmpfs" folder (in RAM)
[cc lang='bash']
root@nms:\~# ab -c 100 -n 1000 "http://mysql/stress.php" 2>/dev/null | grep "Time taken for tests"
Time taken for tests: 8.937 seconds
[/cc]