Because most MySQL production systems probably run on Linux, I’ve decided to place the most important Linux tuning tips that will help improve MySQL performance. There is nothing new here, most of them are well known, however, I’ve decided to collect those Linux configuration tips into 1 blog post.
Filesystem
- ext4 (or xfs), mount with noatime
- Scheduler – use deadline or noop
1 2 | # echo deadline >/sys/block/sda/queue/scheduler add "elevator=deadline" to grub.conf |
(For more info see Linux Schedulers in TPCC like benchmark)
Memory
- Swappiness and NUMA:
1 2 | # echo 0 > /proc/sys/vm/swappiness add "vm.swappiness = 0" to /etc/sysctl.conf |
- Set numa interleave all
1 | numactl --interleave=all |
If using Percona Server we can place it into mysqld_safe script, as Percona Server supports NUMA control.
Jeremy Cole blog contains excellent overview of NUMA as well as additional NUMA tools
(and do not forget about innodb_flush_method=O_DIRECT)
CPU
Make sure there is no powersave mode enabled:
Check /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
and make sure it is not ondemand
Check /proc/cpuinfo and compare cpu MHz number to what is listed under the “model name”
Disable the “ondemand” if it is running
Example: “ondemand” is running on all the servers
1 2 3 4 | $ ps ax| grep kondemand|wc -l 65 $ cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor ondemand |
and we have this:
1 2 3 4 | /proc/cpuinfo: model name : Intel(R) Xeon(R) CPU E5-4640 0 @ 2.40GHz .. cpu MHz : 1200.000 |
In this case we will need to disable “ondemand”.
These simple Linux tuning tips will increase MySQL performance and make it more stable (and avoid swapping). I’m am also interested in hearing about your experience with different Linux configurations and MySQL. Please share those in the comments.
Memory allocator = jemalloc (http://www.canonware.com/jemalloc/). It is used in Percona installation
I think it’s worth noting the NUMA configuration –interleave=all makes sense for a single large instance of mysql, however, from my understanding in a scenario with multiple instances, it does not have the same problem to solve, as each instance can now fit on one preferred node and –interleave=all may end up not being the best choice.
We have more 2x TPS increasing in sysbench OLTP tests with tcmalloc.
Thanks for your post, we will use it
Since most Linux use MariaDB now not MySQL any idea whether these tips all apply equally there?
For CPU, I find turbostat, cpupower (part of cpupowerutils on RHEL6.4) and i7z useful in measuring actual CPU frequencies and states.
I also found the information contained in the answer here http://stackoverflow.com/questions/12111954/context-switches-much-slower-in-new-linux-kernels very useful. Hardware vendors (HP and Dell anyway) provide tuning documentation for “Low-Latency” environments, these guides and up to date BIOS can also help tune CPU operating frequencies.
Also consider disabling transparent huge pages, especially if using jemalloc and memory footprint is important.
noatime: not a big deal today, especially with XFS:
http://xfs.org/index.php/XFS_FAQ#Q:_Is_using_noatime_or.2Fand_nodiratime_at_mount_time_giving_any_performance_benefits_in_xfs_.28or_not_using_them_performance_decrease.29.3F
looks like numa conf still makes sense for even couple of instances when some of them grouped on one node use more than (100 / n) % of memory (n — number of cpus)
as for the mariadb… guess one should check on any linux server. pretty good chance to see mariadb because “most linux use” it 🙂 don’t be mistaken by mysqld process — it’s ps bug :>
pretending not to see the advocacy there… yes these tips should apply equally there. it should apply to many more different types of sql servers
1. xfs — sometimes (depending on environment, drbd will be rather seriously harmed) it may help:
http://www.mysqlperformanceblog.com/2011/12/16/setting-up-xfs-the-simple-edition/
also barriers — off in case of healthy hardware raid
2. innodb_flush_log_at_trx_commit — if you can afford to loose some data or you believe in your hardware and software just set it to 0 or 2 🙂
3. avoid sync-binlog or if you still don’t care about the data turn them off completely, saving even more io work
4. ram big enough to fit all tables in buffer pool (innodb) or in key_buffer (myisam)
5. dirty ratio and friends in /proc
good roundup of basic linux tips, however…
setting the swappiness to zero means that if something fails the mysqld might crash rather than going into swap, so its probably safer to keep a bit of swapiness, not as high as the default, but some, just in case…
also on demand governor might help you to keep the box on low load, especially with small databases, but if you have a somewhat reasonable size database, it might make the performance on the database better if you keep it running without ondemand.
for the grub parameter, deadline is really good, but reading into kernel options, ive found the following kernel bootline more effective:
elevator=noop intel_iommu=on transparent_hugepage=always acpi_irq_nobalance
irq_nobalance here is the parameter that gets things really rolling on an ubuntu server, it reduces the total load of the system by around 60%, as irq polling does not happen every second, i like to manage the irqs from bios anyways.
Setting numa_interleave = 1 in the [mysqld_safe] sections does not work for me, using Percona cluster. I’ve to edit the mysqld_safe script, and set numa-interleave=1 on line 21. Is this a bug, should this work like this or is this the expected behaviour for Percona cluster?
Thank you for this great post!
I have to translate this post to Chinese and give some more details about NUMA and cpufreq performace.
针对MySQL的Linux性能调优技巧:http://mdba.cn/?p=244
@Dirk Bonenkamp, I had exactly the same experience: setting numa_interleave=1 in [mysqld_safe] has no effect, and you do need to edit the mysqld_safe script. (I’m using Percona Server 5.5.33.)
The details in https://www.percona.com/doc/percona-server/5.5/performance/innodb_numa_support.html about this are backwards. The innodb_buffer_pool_populate variable should be set in the config file (mysqld section), not the command line, and the numa_interleave and flush_caches options must be set on the command line, not in the config file.
There ought to be a way to set them without editing mysqld_safe, since that is part of the Percona Server RPM – but there is no way to do so that I have found.
So this is a bug in parse_arguments, and with a simple two-line change you can put numa_interleave=1 and flush_caches=1 into your my.cnf [mysqld_safe] section and it will take effect. Here’s how I modified /usr/bin/mysqld_safe from Percona server 5.5.35 (not .33, I misremembered):
Index: mysqld_safe.5.5
===================================================================
— mysqld_safe.5.5 (revision 9078)
+++ mysqld_safe.5.5 (working copy)
@@ -233,7 +233,9 @@
–syslog-tag=*) syslog_tag=”$val” ;;
–timezone=*) TZ=”$val”; export TZ; ;;
–flush-caches) flush_caches=1 ;;
+ –flush-caches=*) flush_caches=”$val”;;
–numa-interleave) numa_interleave=1 ;;
+ –numa-interleave=*) numa_interleave=”$val” ;;
–help) usage ;;
thank you very much.