MySQL Performance Tuning - Percona

Transcription

MySQL PerformanceTuningVol. 1By Stephane Combaudon,Alexander Rubinand Aurimas MikalauskasCopyright 2006-2014 Percona LLC

MySQL Performance TuningTable of Contents361012Chapter 1: 10 MySQL settings to tune after installationChapter 2: Advanced MySQL query tuningChapter 3: Linux performance tuning tips for MySQLChapter 4: Optimizing MySQL for ZabbixAbout PerconaPercona was founded in August 2006 and now employs a globalnetwork of experts with a staff of more than 120 people. Our customerlist is large and diverse, including Fortune 50 corporations, popularwebsites, and small startups. We have over 2,000 customers and,although we do not reveal all of their names, chances are we'reworking with nearly every large MySQL user you've heard about. Toput Percona's MySQL expertise to work for you, please contact us.Is this an emergency? Get immediate assistancefrom Percona Support 24/7. Click hereSkype: oncall.perconaGTalk: oncall@percona.comAIM (AOL Instant Messenger): oncallperconaTelephone direct-to-engineer: 1-877-862-4316 orUK Toll Free: 44-800-088-5561Telephone to live operator: 1-888-488-8556Customer portal: https://customers.percona.com/Copyright 2006-2014 Percona LLC

MySQL Performance TuningChapter 1: 10 MySQL settings to tune after installation10 MySQL settings to tune after installationBy Stephane CombaudonWhen Percona is hired for a MySQL performance audit, we are expected to review the MySQLconfiguration and to suggest improvements. Many people are surprised because in most cases, weonly suggest changing a few settings even though hundreds of options are available. The goal ofthis 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 beforeblindly 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 allowsyou to quickly revert the change if it creates any problem. But in the end, you want thesetting to be adjusted permanently in the configuration file.A change in the configuration is not visible even after a MySQL restart? Did you use thecorrect configuration file? Did you put the setting in the right section? (all settings in thispost 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 thechanges, use version control.Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x theprevious ones”.Basic settingsHere are 3 settings that you should always look at. If you do not, you are very likely to run intoproblems very quickly.innodb buffer pool size: this is the #1 setting to look at for any installation using InnoDB. Thebuffer pool is where data and indexes are cached: having it as large as possible will ensure youuse 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 writesare fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, asyou wanted both large redo logs for good performance and small redo logs for fast crash recovery.3

MySQL Performance TuningChapter 1: 10 MySQL settings to tune after installationFortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now havegood write performance and fast crash recovery. Until MySQL 5.5 the total redo log size waslimited 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 roomfor writes. If you know your application is write-intensive and you are using MySQL 5.6, you canstart with innodb log file size 4G.max connections: if you are often facing the ‘Too many connections’ error, max connections istoo low. It is very frequent that because the application does not close connections to the databasecorrectly, you need much more than the default 151 connections. The main drawback of highvalues for max connections (like 1000 or more) is that the server will become unresponsive if forany reason it has to run 1000 or more active transactions. Using a connection pool at theapplication level or a thread pool at the MySQL level can help here.InnoDB settingsInnoDB has been the default storage engine since MySQL 5.5 and it is much more frequently usedthan 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 sharedtablespace (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 wantfile 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 previousversions, you should set it to ON prior to loading data as it has an effect on newly created tablesonly.innodb flush log at trx commit: the default setting of 1 means that InnoDB is fully ACIDcompliant. 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 fsyncsthat are needed to flush each change to the redo logs. Setting it to 2 is a bit less reliable becausecommitted transactions will be flushed to the redo logs only once a second, but that can beacceptable on some situations for a master and that is definitely a good value for a replica. 0 iseven faster but you are more likely to lose some data in case of a crash: it is only a good value fora replica.innodb flush method: this setting controls how data and logs are flushed to disk. Popular valuesare O DIRECT when you have a hardware RAID controller with a battery-protected write-back4

MySQL Performance TuningChapter 1: 10 MySQL settings to tune after installationcache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help youchoose between the 2 values.innodb log buffer size: this is the size of the buffer for transactions that have not beencommitted yet. The default value (1MB) is usually fine but as soon as you have transactions withlarge blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at theInnodb log waits status variable and if it is not 0, increase innodb log buffer size.Other settingsquery cache size: the query cache is a well known bottleneck that can be seen even whenconcurrency 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 forinstance). If you have already built your MySQL application with the query cache enabled and ifyou have never noticed any problem, the query cache may be beneficial for you. So you should becautious 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 whenyou want to be able to do point-in-time recovery: restore your latest backup and apply the binarylogs. 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 thelogs will be automatically purged.Binary logging however is not free, so if you do not need for instance on a replica that is not amaster, it is recommended to keep it disabled.skip name resolve: when a client connects, the server will perform hostname resolution, andwhen DNS is slow, establishing the connection will become slow as well. It is thereforerecommended to start the server with skip-name-resolve to disable all DNS lookups. The onlylimitation is that the GRANT statements must then use IP addresses only, so be careful whenadding this setting to an existing system.ConclusionThere are of course other settings that can make a difference depending on your workload or yourhardware: low memory and fast disks, high concurrency, write-intensive workloads for instance arecases when you will need specific tuning. However the goal here is to allow you to quickly get asane MySQL configuration without spending too much time on changing non-essential MySQLsettings or on reading documentation to understand which settings do matter to you.5

MySQL Performance Tuning Chapter2: Advanced MySQL query tuningAdvanced MySQL query tuningBy Alexander RubinTuning MySQL queries and indexes can significantly increase the performance of your applicationand decrease response times. I discussed advanced techniques for optimizing MySQL queries in aPercona webinar titled “Advanced MySQL Query Tuning,” and I invite you to watch the recordingand download the slides (both free as always) rs/advanced-mysql-query-tuningThe topics I discuss include:1. GROUP BY and ORDER BY optimization2. MySQL temporary tables and filesort3. Using covered indexes to optimize your queries4. Loose and tight index scans in MySQLFollowing the webinar there were several questions from attendees. I am also sharing the answersin this chapter and I hope you find them valuable.Q: Did you reset the query cache before doing your benchmark on your query? 0.00seconds sounds too goodA: (This is in response to a couple of slides where the time showed as 0.00). Yes, MySQL wasrunning with query cache disabled. The 0.00 just means that the query was executed in less than0.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” w-profile.htmlMySQL 5.1 : Enable the slow query log with microsecond presision and log the query. Tolog all queries in the slow query log you can temporary set: long query time 0MySQL 5.6: Use the new performance schema countersTurn the page for the profile for an example query, the query shows 0.00 seconds:6

MySQL Performance Tuning Chapter2: Advanced MySQL query tuningAs 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 orCUDA) with databases and the performance differences?A: MySQL does not support it right now. Usually openCL / CUDA does not help with thedisk-bounded applications like databases. However, some projects in OLAP space can actuallyutilize 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 wewant to use where on table A and sort it by column from table BA: Unfortunately, MySQL does not support that with the covered index. MySQL will only use thefilter on the where condition (to limit the number of rows) filesort. However, if we have a limitclause, MySQL may be able to use the index for order by and stop after finding N rows, matchingthe condition. It may not be faster thou (as I showed during the webinar) and you may have to useindex hints to tell mysql to use the exact index (may not be the best approach as in some cases theuse of this index may not be the best for this case).See the next page for an example.7

MySQL Performance Tuning Chapter2: Advanced MySQL query tuningAs 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 Hashindexes.A: InnoDB use Hash Indexes for so called “” feature. InnoDB does not support hash indexes as anormal 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 onInnoDB 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 to1. Check the foreign key constraint table2. Place a shared lock on the row it will b-locks-set.html)8

MySQL Performance Tuning Chapter2: Advanced MySQL query tuningQ: 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 coveredindex, 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, thistemporary table will be created ondisk. I’ve described this scenario during the webinar. Again, it'savailable (free) along with my slides at vanced-mysql-query-tuning.9

MySQL Performance TuningChapter 3: Linux performance tuning tips for MySQLLinux performance tuning tips for MySQLBy Alexander RubinBecause most MySQL production systems probably run on Linux, I’ve decided to place the mostimportant Linux tuning tips that will help improve MySQL performance and include them in thischapter.Filesystemext4 (or xfs), mount with noatimeScheduler – use deadline or noop(For more info see Linux Schedulers in TPCC like benchmark)MemorySwappiness and NUMA:Set numa interleave allIf using Percona Server we can place it into mysqld safe script, as Percona Server supportsNUMA 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 TuningChapter 3: Linux performance tuning tips for MySQLCPUMake sure there is no powersave mode enabled:Check /sys/devices/system/cpu/cpu0/cpufreq/scaling governorand make sure it is not ondemandCheck /proc/cpuinfo and compare cpu MHz number to what is listed under the “model name”Disable the “ondemand” if it is runningExample: “ondemand” is running on all the serversand 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 (andavoid swapping).11

MySQL Performance TuningChapter 4: Tuning MySQL for ZabbixTuning MySQL for ZabbixBy Aurimas MikalauskasThis chapter was inspired by my visit to the annual in Riga, Latvia in September 2014, where Igave a couple of talks on .It was a two day single-track event with some 200 participants, a number of interesting talks onZabbix (and related technologies) and really well-organized evening activities. I was amazed howwell organized the event was and hope to be invited to speak there next year as well.(Just incase you’re not sure what Zabbix is, it is an enterprise-class open source distributed monitoringsolution for networks and applications)I must secretly confess, it was also the first conference where I honestly enjoyed being on stageand connecting with the audience – I was even looking forward to it rather than being scared ashell (which is what typically happens to me)! I guess it was all about the positive atmosphere, sobig 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 enoughdeeply 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

MySQL Performance TuningChapter 4: Tuning MySQL for ZabbixZabbix and MySQLThe very first thing I did when I arrived at the conference was to approach people who I knew useZabbix 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 bestout of your disks (and consequentially will help your Zabbix get the best out of MySQL) and theavailable 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, bothpublicly and privately.I can tell you without a shadow of doubt, if IO is currently your bottle-neck – either because somequeries take a long time to run and you see diskstats reporting 100-250 reads per second until thequery completes (latency), or because you are overloading the disks with requests and wait timesuffers (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 secondtops (at this point it is limited by physics) and single query will only ever read from one disk even ifyou 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 persecond with a single-thread (16k is the size of an InnoDB block). And as you increase thethroughput, it only gets better! So that means that the query in the previous example would take 1sinstead of 60s, which is a significant difference. Plus you can run more requests on the same SSDat the same time and the total number of IO operations will only increase, while a single spinningdisk 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, especiallysingle-threaded sequential writes. If that is your typical workload (which might be the case if you’remostly collecting data and rarely if ever reading it), then you may want to consider other strategies.MySQL configurationBesides improving your disk IO subsystem, there’s ways to reduce the pressure on IO and I’mgoing to cover a few my.cnf variables that will help you with that (and with other things such asinternal contention).13

MySQL Performance TuningChapter 4: Tuning MySQL for ZabbixNote, most of the tunables are common for any typical high-performance MySQL setup, thoughsome are explicitly suited for Zabbix because you can relax a few parameters for great effect at theprice of, in the worst case, loosing up to 1s worth of collected data which, from discussions duringthe 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 otherprocesses 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 significantlyimproved 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 ofwebsites actually run with that (a lot of websites still run on MyISAM!!!), I’m quite sure it’s not anissue 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 hold1 to 2 hours worth of writes. To determinte that, you can probably have a look at the Zabbix graphsfor 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 oflog file space allowing me to store more than 1h worth of writes in transaction logs (See this onchanging 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 asimportant 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 yougenerally 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 leavethem there.14

MySQL Performance TuningChapter 4: Tuning MySQL for Zabbix- innodb old blocks time 1000 – this will help you prevent buffer pool pollution due tooccasional 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. ForSSDs this should be at least few thousand (2000 could be a good start) while for rotating diskssomewhat lower values – 500-800, depending on number of bearing disks, will do. Best tobenchmark disks or do the math for actual numbers, but default of 200 is definitely too low for mostsystems nowadays.- sync binlog 0 – this is the default setting, but just in case it’s above 0, turn it off, unless you runsomething else besides Zabbix. The price of not synchronising binary logs is that in case of amaster crash, replicas can get out of sync, but if you are constantly hitting IO bottle-neck due tobinary log synchronisation just because you want to avoid the hassle of synchronising the slaveonce 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 timeyou 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 thesevariables, cancel those changes (just remove them or comment them out). I find these are the topthree mistuned variables on most customer servers, while in many cases it’s best if they are nottouched 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 tablesare actually written to memory, but there’s one important caveat starting with MySQL 5.5: if you dothat, it disables AIO acorss the board, because tmpfs does not support AIO. So I would monitor theactivity on current tmpdir (/tmp usually) and only switch it to /dev/shm if I see it being an issue.MySQL PartitioningI know that with a purpose of easier data pruining, however I think there are some extra benefitsyou 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 haveenough values to monitor), the better is the throughput of your Zabbix. And this is where a lot ofpeople are hitting the Zabbix limits – MySQL can’t insert enough new values per second.(Please turn the page)15

MySQL Performance TuningChapter 4: Tuning MySQL for ZabbixBesides the optimizations I have already mentioned above (they should greatly increase your writethroughput!), I would encourage you to try out partitions (if you’re not using partitions already) orsubpartitions (if you are) BY HASH as we found that partitioning in some cases can increase thethroughput of InnoDB.I did not test it with Zabbix specifically and as it’s not supported by Zabbix out of the box, youwould have to hack it to make it work, but if you’ve done all the changes above and you still can’tget enough new values per second (AND it is not the hardware that is limiting you), try partitioningor 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 workwith you to make it work.On MySQL High AvailabilityThere 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 topoint you to a few valuable resources on that topic:Yves post on High Availability Options for MySQLJay’s recent webinar on Percona XtraDB ClusterFernando’s and Martin’s webinar on MySQL FabricPercona 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 thePercona software either needs to be bought or that it has some enterprise features that are notavailable unless you buy a license.The truth is that neither of it is true. All Percona software is completely free of charge. Feel free todownload it from our website or through repositories and use it as you please.See you at the Zabbix conference next year!16Powered by TCPDF (www.tcpdf.org)

About the authorsPercona architect Stéphane Combaudon joined Percona in July 2012 after working asa MySQL DBA for leading French companies. In real life, he lives in Paris with his wifeand their twin daughters. When not in front of a computer or not spending time with hisfamily, he likes playing chess and hiking.Alexander Rubin joined Percona in 2013. He's worked with MySQL since 2000 as aDBA and application developer. He was a MySQL consultant for 7 years prior toPercona (starting with MySQL AB in 2006, then Sun Microsystems and then Oracle).Alexander has helped many customers design large, scalable and highly availableMySQL systems and optimize MySQL performance. He has also helped customersdesign 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 differenttechnologies and how to apply them. He always knows the right tools for the task. Inaddition to MySQL performance optimization, his key areas of expertise include:MySQL High Availability, full text search, web and content cache servers, and MySQLdata recovery.Visit Percona's ever-growing MySQL eBook library for more great content yright 2006-2014 Percona LLC

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 profi