Revisiting MySQL performance

If you've added your indexes and you've redesigned your queries and you're still struggling with performance, you may need to delve a little deeper into MySQL's configuration and better understand your OS. This talk is not about tackling individual queries. Instead, it contains much broader system-level approaches to resolving performance issues.

Transcript

1. Revisiting MySQL performance an investigation into server performance
2. Abstract If you've added your indexes and you've redesigned your queries and you're still struggling with performance, you may need to delve a little deeper into MySQL's configuration and better understand your OS. This talk is not about tackling individual queries. Instead, it contains much broader system-level approaches to resolving performance issues.
3. Consider the cheaper alternatives: • Predict volume / distribution of data • Switch to InnoDB to avoid table locks • If you know the current solution is a bit poor then ask: o How long will the current solution be good for? o How much effort will it be to get it right? o Can I hack in performance later? (Probably, yes) • CPU/RAM is cheaper than a programmer • Process heavy queries asynchronously o Report is PDF'd and emailed to person who requested it • Use replication to create a slave o Push heavy reporting queries to the slave
4. What are the in-MySQL solutions? • Everything discussed in my previous MySQL talk o Indexing strategy o Query and dataset reorgnanisation o Denormalisation / Summarised data o Slides: http://slidesha.re/1BaSyN o Video: http://blip.tv/file/1791781 • Rationalise your use of SELECT * o Keep your temporary tables in memory o Keep in mind that filesort will still work in memory where the result set < sort_buffer_size • See if sub-selects can solve your problem o Change a long join to a sub-select o ORDER BY + WHERE indexed (http://bit.ly/bmkFLr)
5. I've tried all that, and it's still slow • We'll now turn our attention to my.cnf and OS • There are three very hard walls for an OS to hit: o CPU (Referred to as run queue or load average) o Memory (Quite a cheap one to fix) o Disk (Minimise the amount of travelling it has to do) • MySQL is usually either memory or disk-bound o "Memory bound" can lead to "disk-bound" • Generally speaking, your PHP application is CPU-bound o Most of the time it's waiting on disk or DB o Profile - see what % of time PHP is waiting for DB • It's very rare that MySQL is a CPU-bound problem o a quick glance at "top" can prove/disprove this
6. Is my MySQL app memory-bound? • Yes • The stock my.cnf config that comes with MySQL will most likely not be appropriate for your setup • Use SHOW GLOBAL STATUS to identify potential my.cnf tweaks o Created_tmp_tables vs Created_tmp_disk_tables o Threads_created o Sort_merge_passes * o http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html • Remember, if MySQL cannot do what it needs to do in memory, it goes to disk. If you're doing web + db on the same disk, you can't predict performance.
7. First stop - my.cnf Familiarise yourself with some of the key configuration values related to memory allocation • http://bit.ly/VKmcQ + http://bit.ly/3pqQc o Quick guides on modifying my.cnf by Peter Zaitsev • max_heap_size and max_temp_table_size o max_heap_size should be >= max_temp_table_size o SHOW GLOBAL STATUS LIKE 'Created_%' o Before and after : check ratio of tmp tables created on disk to those left in memory
8. READ ALL THE MY.CNF RESOURCES!
9. Tools to identify IO-bound problems • Apache HTTPd, swap and MySQL all contend for IO • "top" shows what percentage of current load (or "run queue") can be attributed to waiting for IO • Most simple: are we swapping? o However, swap != lots of disk activity
10. iostat - (sysstat on redhat distros) • Shows disk activity (bytes read/written, seek times etc) • Very quickly identifies IO weighting and load • iostat -x 1 10 • only works per partition o but if plan your partitions (sda1 = /var/lib/mysql, sda2 = / var/www, sda3 = /tmp) the problem is immediately obvious
11. atop - top on steroids • a screen shows us disk activity per process • similar output from iostat
12. /proc/sys/vm/block_dump • echo a "1" to this file and then the dmesg buffer will contain very low level details of disk activity o For ext3 shows both page changes and flushes separately, and by which user - therefore could explain continual "pflushd" activity o Very noisy
13. Summary so far: • Try to configure MySQL to stop going to disk so much o increase buffer pools o increase max temp table size o understand the reason behind poor performance of large tmp tables/filesorts • If we've done all we can and we're still sluggish: o Use tools to identify the source of disk access o Check for high swap, take note if pflushd is causing you headaches o iostat / atop / block_dump • Now for the fixes ...
14. The PHP fixes • Op-code cache (apc, xcache, eaccellerator) o reduces the load on the disk o apc.stat = 0 (don't check mtime) • memcache o push configuration files/arrays to memcache o faster than mysql query cache What's this got to do with MySQL? • MySQL needs fast IO. If we can't stop MySQL going to disk all the time, let's do whatever we can to make sure MySQL has virtually exclusive access to the disk.
15. Say no to swap As soon as free -m or top show you're in swap, you run the risk of the disk being run off its feet • Get more memory (duh) • Reduce footprint of services o Trim buffers in my.cnf o Reduce number of apache min spares or switch to a more memory efficient HTTP server o Disable unused php modules (mbstring, gd, etc) • Reduce OS'd tendancy to swap o /proc/sys/vm/swapiness • swapoff/swapon - move everything from swap to memory!
16. mount partitions with "-noatime" • Whenever a file is written or read, the atime attribute is updated to denote the last "access time" • MySQL : A small saving, but perhaps still worth it • Apache HTTPd : Jackpot! o Every file system read generates a tiny write o htaccess files, static resources (js, css, images) • Down side - you won't know when a file was last read o Acceptable collateral damage?
17. Tweak Apache HTTPd config • We noticed a lot of activity on /usr partition using iostat • Combined with examining mod_status, we concluded that continually creating HTTPd threads was a disk-expensive process • The fix is to stop HTTPd culling servers so aggressively so it can deal with spikes • Make sure (where possible) content is served with long cache expiry headers o JS libraries, 960.css, etc
18. Contact Information • @magmadigital / @adrianhardy • magmadigital.co.uk • #phpnw on irc.freenode.net