MySQL Performance Tuning - Percona

MySQL Performance Tuning Vol. 1

By Stephane Combaudon, Alexander Rubin and Aurimas Mikalauskas

Copyright © 2006-2014 Percona LLC

MySQL Performance Tuning

Table of Contents 3 6 10 12

Chapter 1: 10 MySQL settings to tune after installation Chapter 2: Advanced MySQL query tuning Chapter 3: Linux performance tuning tips for MySQL Chapter 4: Optimizing MySQL for Zabbix

About Percona Percona was founded in August 2006 and now employs a global network of experts with a staff of more than 120 people. Our customer list is large and diverse, including Fortune 50 corporations, popular websites, and small startups. We have over 2,000 customers and, although we do not reveal all of their names, chances are we're working with nearly every large MySQL user you've heard about. To put Percona's MySQL expertise to work for you, please contact us.

Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here Skype: oncall.percona GTalk: [email protected] AIM (AOL Instant Messenger): oncallpercona Telephone direct-to-engineer: +1-877-862-4316 or UK Toll Free: +44-800-088-5561 Telephone to live operator: +1-888-488-8556 Customer portal: https://customers.percona.com/

Copyright © 2006-2014 Percona LLC

MySQL Performance Tuning Chapter 1: 10 MySQL settings to tune after installation

10 MySQL settings to tune after installation By Stephane Combaudon When Percona is hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest changing a few settings even though hundreds of options are available. The goal of this chapter is to give you a list of some of the most critical settings.

Before we start… Even the most experienced DBAs can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items: Change one setting at a time! This is the only way to estimate if a change is beneficial. Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file. A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section) The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in bytes while max_connection is dimensionless. Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control. Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic settings Here are 3 settings that you should always look at. If you do not, you are very likely to run into problems very quickly. innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM). innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery.

3


Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6. Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G. max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

InnoDB settings InnoDB has been the default storage engine since MySQL 5.5 and it is much more frequently used than any other storage engine. That’s why it should be configured carefully. innodb_file_per_table: this setting will tell InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+). With MySQL 5.6, the default value is ON so you have nothing to do in most cases. For previous versions, you should set it to ON prior to loading data as it has an effect on newly created tables only. innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your primary concern is data safety, for instance on a master. However it can have a significant overhead on systems with slow disks because of the extra fsyncs that are needed to flush each change to the redo logs. Setting it to 2 is a bit less reliable because committed transactions will be flushed to the redo logs only once a second, but that can be acceptable on some situations for a master and that is definitely a good value for a replica. 0 is even faster but you are more likely to lose some data in case of a crash: it is only a good value for a replica. innodb_flush_method: this setting controls how data and logs are flushed to disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back

4


cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the 2 values. innodb_log_buffer_size: this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase innodb_log_buffer_size.

Other settings query_cache_size: the query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your MySQL application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it. log_bin: enabling binary logging is mandatory if you want the server to act as a replication master. If so, don’t forget to also set server_id to a unique value. It is also useful for a single server when you want to be able to do point-in-time recovery: restore your latest backup and apply the binary logs. Once created, binary log files are kept forever. So if you do not want to run out of disk space, you should either purge old files with or set expire_logs_days to specify after how many days the logs will be automatically purged. Binary logging however is not free, so if you do not need for instance on a replica that is not a master, it is recommended to keep it disabled. skip_name_resolve: when a client connects, the server will perform hostname resolution, and when DNS is slow, establishing the connection will become slow as well. It is therefore recommended to start the server with skip-name-resolve to disable all DNS lookups. The only limitation is that the GRANT statements must then use IP addresses only, so be careful when adding this setting to an existing system.

Conclusion There are of course other settings that can make a difference depending on your workload or your hardware: low memory and fast disks, high concurrency, write-intensive workloads for instance are cases when you will need specific tuning. However the goal here is to allow you to quickly get a sane MySQL configuration without spending too much time on changing non-essential MySQL settings or on reading documentation to understand which settings do matter to you.

5

MySQL Performance Tuning Chapter 2: Advanced MySQL query tuning

Advanced MySQL query tuning By Alexander Rubin Tuning MySQL queries and indexes can significantly increase the performance of your application and decrease response times. I discussed advanced techniques for optimizing MySQL queries in a Percona webinar titled “Advanced MySQL Query Tuning,” and I invite you to watch the recording and download the slides (both free as always) here: http://www.percona.com/resources/mysql-webinars/advanced-mysql-query-tuning The topics I discuss include: 1. GROUP BY and ORDER BY optimization 2. MySQL temporary tables and filesort 3. Using covered indexes to optimize your queries 4. Loose and tight index scans in MySQL Following the webinar there were several questions from attendees. I am also sharing the answers in this chapter and I hope you find them valuable. Q: Did you reset the query cache before doing your benchmark on your query? 0.00 seconds sounds too good A: (This is in response to a couple of slides where the time showed as 0.00). Yes, MySQL was running with query cache disabled. The 0.00 just means that the query was executed in less than 0.004 sec. MySQL does not show the higher precision if you run the query from mysql monitor. There are a couple of ways to get the exact query times: MySQL 5.0 +: Use “profiling” feature: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html MySQL 5.1 +: Enable the slow query log with microsecond presision and log the query. To log all queries in the slow query log you can temporary set: long_query_time = 0 MySQL 5.6: Use the new performance_schema counters Turn the page for the profile for an example query, the query shows 0.00 seconds:

6


As we can see, sending data is actually 0.002 seconds. Q: Do you ever see doing a seminar that shows how to leverage parallelization (openCL or CUDA) with databases and the performance differences? A: MySQL does not support it right now. Usually openCL / CUDA does not help with the disk-bounded applications like databases. However, some projects in OLAP space can actually utilize openCL/CUDA, for example, , is a column store that is massively parallel. Scanning, aggregation, sorting, etc are done in a data flow manner via the CUDA processing. Q: Is this possible to use this /covered index for order by – A.R/ with join? For example if we want to use where on table A and sort it by column from table B A: Unfortunately, MySQL does not support that with the covered index. MySQL will only use the filter on the where condition (to limit the number of rows) + filesort. However, if we have a limit clause, MySQL may be able to use the index for order by and stop after finding N rows, matching the condition. It may not be faster thou (as I showed during the webinar) and you may have to use index hints to tell mysql to use the exact index (may not be the best approach as in some cases the use of this index may not be the best for this case). See the next page for an example.

7


As we can see, MySQL will use index and avoid “order by”. Q: Why are Hash Indexes not available for InnoDB engine ? Any plans to bring Hash indexes. A: InnoDB use Hash Indexes for so called “” feature. InnoDB does not support hash indexes as a normal table index. We are not aware of the Oracle’s InnoDB team plans to bring this feature in. Please note: MySQL will allow you to use “using hash” keyword when creating an index on InnoDB table. However, it will create a b-tree index instead. Q: Will foreign key constraints slow down my queries? A: It may slow down the queries, as InnoDB will have to 1. Check the foreign key constraint table 2. Place a shared lock on the row it will read:

(http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html)

8


Q: How does use of index vary with the number of columns selected in a select query? If we are talking about the covered index: if we select a column which is not a part of covered index, mysql will not be able to satisfy the query with index only (“using index” in the explain plan). It may be slower, especially if MySQL will have to select large columns and the data is not cached. In addition, if we select a text or blob column and MySQL will need to create a temporary table, this temporary table will be created ondisk. I’ve described this scenario during the webinar. Again, it's available (free) along with my slides at http://www.percona.com/resources/mysql-webinars/ advanced-mysql-query-tuning.

9

MySQL Performance Tuning Chapter 3: Linux performance tuning tips for MySQL

Linux performance tuning tips for MySQL By Alexander Rubin 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 and include them in this chapter. Filesystem ext4 (or xfs), mount with noatime Scheduler – use deadline or noop

(For more info see Linux Schedulers in TPCC like benchmark)

Memory Swappiness and NUMA:

Set numa 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)

10

MySQL Performance Tuning Chapter 3: Linux performance tuning tips for MySQL

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

and we have this:

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).

11

MySQL Performance Tuning Chapter 4: Tuning MySQL for Zabbix

Tuning MySQL for Zabbix By Aurimas Mikalauskas

This chapter was inspired by my visit to the annual in Riga, Latvia in September 2014, where I gave a couple of talks on . It was a two day single-track event with some 200 participants, a number of interesting talks on Zabbix (and related technologies) and really well-organized evening activities. I was amazed how well organized the event was and hope to be invited to speak there next year as well. (Just in case you’re not sure what Zabbix is, it is an enterprise-class open source distributed monitoring solution for networks and applications) I must secretly confess, it was also the first conference where I honestly enjoyed being on stage and connecting with the audience – I was even looking forward to it rather than being scared as hell (which is what typically happens to me)! I guess it was all about the positive atmosphere, so big thanks to all the speakers and attendees for that. It meant a lot to me. If I had to mention one negative vibe I heard from attendees, it would be that there was not enough deeply technical content, however, I think this is slightly biased, because people I talked to most, were ones who enjoyed my technical talks and so they were craving for more. And now, without further ado, let me get to the essence of this chapter.

12


Zabbix and MySQL The very first thing I did when I arrived at the conference was to approach people who I knew use Zabbix on a large scale and tried to figure out what were the biggest challenges they face. Apparently, in all of the cases, it was MySQL and more specifically, MySQL disk IO. With that in mind, I would like to suggest a few optimizations that will help your MySQL get the best out of your disks (and consequentially will help your Zabbix get the best out of MySQL) and the available hardware resources in general. SSD is a game changer “Will MySQL run better on SSDs?” I’ve been hearing this question over and over again, both publicly and privately. I can tell you without a shadow of doubt, if IO is currently your bottle-neck – either because some queries take a long time to run and you see diskstats reporting 100-250 reads per second until the query completes (latency), or because you are overloading the disks with requests and wait time suffers (throughput), SSDs will definitely help and not just by little, by much! Consider this: the fastest spinning disk (15k rpm) can do 250 random IO operations per second tops (at this point it is limited by physics) and single query will only ever read from one disk even if you have RAID10 made of 16 disks, so if you need to read 15,000 data points to display a graph, reading those data points from disk will take 60s. Enterprise-class SSD disk, on the other hand, can do 15,000 or even more 16k random reads per second with a single-thread (16k is the size of an InnoDB block). And as you increase the throughput, it only gets better! So that means that the query in the previous example would take 1s instead of 60s, which is a significant difference. Plus you can run more requests on the same SSD at the same time and the total number of IO operations will only increase, while a single spinning disk would have to share the available 250 IO operations between multiple requests. The only area where SSDs don’t beat spinning disks (yet) is sequential operation, especially single-threaded sequential writes. If that is your typical workload (which might be the case if you’re mostly collecting data and rarely if ever reading it), then you may want to consider other strategies. MySQL configuration Besides improving your disk IO subsystem, there’s ways to reduce the pressure on IO and I’m going to cover a few my.cnf variables that will help you with that (and with other things such as internal contention).

13


Note, most of the tunables are common for any typical high-performance MySQL setup, though some are explicitly suited for Zabbix because you can relax a few parameters for great effect at the price of, in the worst case, loosing up to 1s worth of collected data which, from discussions during the conference, didn’t seem like a big deal to anyone. - innodb_buffer_pool_size – if you have a dedicated MySQL server, set it as high as you can (ceiling would be 75% of total available memory). Otherwise, you should balance it with other processes on the server, but if it’s only zabbix server, I would still leave it very high, close to 75% of total RAM. - innodb_buffer_pool_instances – on MySQL 5.5, set it to 4, on MySQL 5.6 – 8 or even 16. - innodb_flush_log_at_trx_commit = 0 – this is where you compromise durability for significantly improved write throughput, especially if you don’t own a disk subsystem with non-volatile cache. Basically the loss you may incur is up to 1s worth of writes during MySQL or server crash. A lot of websites actually run with that (a lot of websites still run on MyISAM!!!), I’m quite sure it’s not an issue for Zabbix setup. - innodb_flush_method = O_DIRECT – if you are running Linux, just leave it set to that. - innodb_log_file_size – you want these transaction logs (there’s two of them by default) to hold 1 to 2 hours worth of writes. To determinte that, you can probably have a look at the Zabbix graphs for your MySQL server, but also you can run the following from the mysql command line:

The difference between the two numbers is how many bytes InnoDB has written during last hour. So on this server above, I would set innodb_log_file_size=128M and would end up with 256M of log file space allowing me to store more than 1h worth of writes in transaction logs (See this on changing the log file size if you run MySQL 5.5 or earlier) - innodb_read_io_threads, innodb_write_io_threads – don’t overthink these, they are not as important as they may seem, especially if you are using Async IO (you can check that by running “show global variables like ‘innodb_use_native_aio'” in mysql cli). On MySQL 5.5 and 5.6 you generally want to be using Async IO (AIO), so check mysql log to understand why, if you are not. That said, if you are not using AIO and you are not going to, just set these values to 8 and leave them there.

14


- innodb_old_blocks_time = 1000 – this will help you prevent buffer pool pollution due to occasional scans. This is now default in MySQL 5.6 (On 5.5, it needs to be set explicitly). - innodb_io_capacity – set this to as many write iops as your disk subsystem can handle. For SSDs this should be at least few thousand (2000 could be a good start) while for rotating disks somewhat lower values – 500-800, depending on number of bearing disks, will do. Best to benchmark disks or do the math for actual numbers, but default of 200 is definitely too low for most systems nowadays. - sync_binlog=0 – this is the default setting, but just in case it’s above 0, turn it off, unless you run something else besides Zabbix. The price of not synchronising binary logs is that in case of a master crash, replicas can get out of sync, but if you are constantly hitting IO bottle-neck due to binary log synchronisation just because you want to avoid the hassle of synchronising the slave once every five years when master crashes, you should reconsider this option. - query_cache_size=0, query_cache_type=0 – that will disable the query cache. Most of the time you don’t want query cache. And if it’s not disabled in the kernel by these settings, queries (especially small ones) will likely suffer due to query cache mutex contention. - sort_buffer_size, join_buffer_size, read_rnd_buffer_size – if you ever configured these variables, cancel those changes (just remove them or comment them out). I find these are the top three mistuned variables on most customer servers, while in many cases it’s best if they are not touched at all. Just leave them at their defaults and you’re set. - tmpdir – sometimes it’s a good idea to point tmpdir to /dev/shm so that on-disk temporary tables are actually written to memory, but there’s one important caveat starting with MySQL 5.5: if you do that, it disables AIO acorss the board, because tmpfs does not support AIO. So I would monitor the activity on current tmpdir (/tmp usually) and only switch it to /dev/shm if I see it being an issue. MySQL Partitioning I know that with a purpose of easier data pruining, however I think there are some extra benefits you could get from partitions. Well actually subpartitions if you are already using partitions by date. The KPI for Zabbix, that you could hear over and over again, is the “new values per second” number that you can find in the status of Zabbix. Basically the higher the value (given you have enough values to monitor), the better is the throughput of your Zabbix. And this is where a lot of people are hitting the Zabbix limits – MySQL can’t insert enough new values per second.

(Please turn the page)

15


Besides the optimizations I have already mentioned above (they should greatly increase your write throughput!), I would encourage you to try out partitions (if you’re not using partitions already) or subpartitions (if you are) BY HASH as we found that partitioning in some cases can increase the throughput of InnoDB. I did not test it with Zabbix specifically and as it’s not supported by Zabbix out of the box, you would have to hack it to make it work, but if you’ve done all the changes above and you still can’t get enough new values per second (AND it is not the hardware that is limiting you), try partitioning or subpartitioning the key tables by hash. If this sounds interesting but you’re not sure where to start, feel free to contact us and we’ll work with you to make it work.

On MySQL High Availability There are options to make MySQL highly available, even though many believed it’s not the case. We’ve been writing a lot on it on our blog so I will not paraphrase or repeat, instead I would like to point you to a few valuable resources on that topic: Yves post on High Availability Options for MySQL Jay’s recent webinar on Percona XtraDB Cluster Fernando’s and Martin’s webinar on MySQL Fabric

Percona Server, Percona XtraDB Cluster, Percona Toolkit – it’s all FREE! I’m not really sure why, but many people I talked to at the conference thought that all of the Percona software either needs to be bought or that it has some enterprise features that are not available unless you buy a license. The truth is that neither of it is true. All Percona software is completely free of charge. Feel free to download it from our website or through repositories and use it as you please. See you at the Zabbix conference next year!

16 Powered by TCPDF (www.tcpdf.org)

About the authors Percona architect Stéphane Combaudon joined Percona in July 2012 after working as a MySQL DBA for leading French companies. In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.

Alexander Rubin joined Percona in 2013. He's worked with MySQL since 2000 as a DBA and application developer. He was a MySQL consultant for 7 years prior to Percona (starting with MySQL AB in 2006, then Sun Microsystems and then Oracle). Alexander has helped many customers design large, scalable and highly available MySQL systems and optimize MySQL performance. He has also helped customers design Big Data stores with Apache Hadoop and related technologies.. During his career, both as a system administrator and a performance engineer, Percona architect Aurimas Mikalauskas has become familiar with many different technologies and how to apply them. He always knows the right tools for the task. In addition to MySQL performance optimization, his key areas of expertise include: MySQL High Availability, full text search, web and content cache servers, and MySQL data recovery.

Visit Percona's ever-growing MySQL eBook library for more great content at http://www.percona.com/resources/mysql-ebooks Copyright © 2006-2014 Percona LLC