Difference between revisions of "MySQL Optimization"

From wiki.mikejung.biz
Jump to navigation Jump to search
(my.cnf template)
Line 453: Line 453:

Latest revision as of 02:25, 21 February 2018

Introduction to MySQL Optimization

Almost all of the info in this wiki comes from this AMAZING book, you should seriously read it

Also relevant

Optimizing MySQL is very important to get right the first time. A well tuned MySQL server can perform 2 - 3 x better than a poorly tuned MySQL server. The good news is that there are really only a few settings that ever need to be tuned / changed. This wiki will walk you through the steps for determining if MySQL needs to be tuned, and if it does, how to tune the settings.

Before we even begin to talk about MySQL configuration settings we are going to cover hardware and how it has an impact on overall performance.

Quick Note on the definition of Optimization, according to me.

Optimization is about UTILIZING as many resources as possible, not REDUCING resource utilization. No one buys a Ferrari and modifies it to go slower, or conserve gas do they? Servers are meant for getting work done, so making sure they have the resources available is half the battle, the other half is to allow them to efficiently allocate their resources to get work done faster. I will provide two examples below to hopefully illustrate my point.

  • Server A: Lots of CPU power, good storage system, what else could I want?
E5-2650 with 16 cores
SATA RAID 10 with 4 disks
10GB of MySQL data
  • Server B: Decent CPU, not a ton of cores, lots of RAM, basic storage system.
SATA RAID 1 with 2 disks
10GB of MySQL data

The question is, what server will perform better? You might think that because the E5 box has a lot of cores, costs a lot of money and has RAID 10 it's better. However, if you were to run some Sysbench OLTP read tests on these boxes the E3 will more than likely crush the more expensive server. Why? RAM, that's why.

Here is what happens if I send a SELECT query to the E5 server:

Step 1) Server gets my request, goes to find my data, there's a 4/10 chance that it's in RAM. Just so happens my data is not in RAM. 
Step 2) Server sends my disk a request for my data, CPU sends the request really quickly, then waits for the array to return my data. 
Step 3) My disks took their time getting my data, lets say they took 20ms (really long). 
Step 4) My CPU fiddles it's thumbs waiting on my data to be returned from the disks (into RAM). Finally gets the results and sends to me. 

Here is what happens if I send a SELECT query to the E3 server:

Step 1) Server gets my request, goes to find my data, 100% chance it's in RAM. It's in RAM, awesome
Step 2) It takes less than 1ms for the RAM to find my data and return it to the CPU
Step 3) CPU sends the data to me.

While I realize that I am just throwing arbitrary numbers at this example, it's still a safe bet to assume a disk seek will take say 20ms and a RAM seek will be under 1ms. Even if the E5 server gets some luck with queries asking for data that is cached in RAM, we are still looking at a 10x performance difference between the E5 and E3 server.

Basically the E3 server could theoretically serve 10 requests in the amount of time the E5 server takes to return one request.

Optimal MySQL Server Hardware Configuration

There are 4 main areas to focus on in terms of server performance, RAM, CPU, DISK and NETWORK. I'm going to cover each of these and explain how they affect MySQL's performance.


I'm mentioning RAM first because it is the most important factor for MySQL performance. The fastest CPU on Earth will not help to speed up MySQL if it only has 2GB of RAM. There has always been a severe bottleneck between the CPU and Harddrive, the solution to that is RAM. Reading data out of RAM is significantly faster than reading data from a SATA harddrive, or even an SSD. Because of this we want to try and fit as much data into RAM as possible to speed up response times.

How much RAM do I need?

  • Let's say you have 1 database that totals 10GB in size. You would want at least 10GB of RAM for this database. You also need RAM for the OS and things like that so 16GB of RAM should be sufficent.
  • If you have 50GB of databases then you will want at least 50GB of RAM. Obviously this can sometimes be cost prohibitive, but the fact is that you will want to put as much of your data into RAM as possible. This is THE MOST IMPORTANT way to improve MySQL performance. If you don't have sufficient RAM on your server MySQL will perform poorly regardless of the my.cnf settings you try to change.

By utilizing as much RAM as possible you not only speed up query response time, you also free up the server's DISK so that it can attend to other things, like writing data. This also makes your CPU much more efficient since it does not have to wait for the slow disk to process it's request for data.

Bottom Line

If the server is performing poorly you should ensure that

  • A) MySQL is configured to use as much RAM as possible to store MySQL data.
  • B) Make sure it is not using SWAP space, if that is the case you might want to suggest a RAM upgrade before doing any other MySQL tuning.


After RAM, the second most important thing to look at is what the server is using for it's storage. If the server is correctly configured with a decent amount of RAM the underlying DISK speed won't matter too much, however if you do not have sufficient RAM, having a fast storage system can help to keep performance at a decent level.

Before moving forward with this section, please answer the following questions:

  • How large is my active MySQL data set?
  • How much RAM does my server have?
  • Is MySQL configured to store all my MySQL data in RAM?

If you are correctly utilizing RAM but still noticing slow DISK performance then you might want to look into possibly upgrading your storage system. However, we might want to try and tune some MySQL settings that can resolve that. We will cover that later, but the main point here is that if you utilize RAM correctly, your storage concerns should be minimal.

Here is a quick list of things to consider if you want to have good DISK performance:

Are you using RAID? If you are using RAID, is it RAID 1, RAID 10?

  • RAID 1 will not improve Random Write performance, so if you are noticing that writes are slow, you might want to consider moving to a RAID 10 configuration, or installing a BBU and enabling Write Caching to help with performance.
  • RAID 10 is a good start, but again, if Random Write performance is lacking, look into using a BBU to enable safe write caching.

What type of Disks are you using?

  • Do you have a single drive that is storing MySQL data along with everything else on the system? If you are then you need to change this. Ideally using a seperate drive for MySQL data is the way to go. Even better would be to use and SSD for the data. If you are using a single SSD, please make sure you are taking regular backups
  • If you are not using flash storage, that is fine, SATA and SAS can still perform well, but we need to tune MySQL for this. I will cover how to do this later.

Bottom Line

If you utilize RAM correctly, your disks should not have to worry too much about READING MySQL data since that is pulled from RAM. This will allow them to focus on WRITE performance, specifically Random Write performance. Using a RAID Card with a BBU and write caching can help, using SSDs and Flash storage are probably the best bet here, but again, RAM is the most important factor in terms of minimizing disk usage.


Last, and pretty much least is the CPU. The fastest CPU in the world will not help to improve performance if it has to wait for a slow SATA drive to randomly write and read data. This is inefficent and one of the main causes of high server load. There is obviously some need for a decent CPU, an Intel i5-750 is not going to perform as well as an E5-1650, but without plenty of RAM and decent DISK IO, the system will perform poorly as a whole.

Bottom Line

CPU performance is ONLY a factor if you have very low IO WAIT and you are correctly utilizing RAM. If you notice that your CPU is constantly around 90% - 100% then you may want to look into a CPU upgrade. In general, MySQL prefers faster cores instead of more cores. Anything with 8 - 32 cores should be sufficent. If you are already using something like an E3-1270v2 and you are CPU bound the next logical step would be something like an E5-2650 or above.

Again, a fast CPU is not always an efficient CPU. If you notice a high amount of IO WAIT, first make sure you are not SWAPPING and have enough RAM, if you do then look into upgrading to faster disks before upgrading a CPU.


Network speed and latency is a factor if you are using a "remote" MySQL server. This type of configuration would include 1 web node or more that talk to the database server via private network. If you are pushing a lot of traffic to the database server you may want to consider using 10Gb networking, or at least making sure the NICs are using a 1Gb link speed and are not being used for other traffic.

I won't cover this too much since it's not the main focus of this page, but I still wanted to mention that connecting to a database server via private network will add latency to each request, which is something to keep in mind if you are troubleshooting performance issues.

Server Hardware Checklist

The four main areas of hardware performance have been covered above, now that we know how RAM, DISK, CPU and NETWORK affect MySQL perfomance, let's make sure our server is up to par. If the server has all the resources necessary, feel free to move on, however if the server is lacking or does not pass the checklist you might want to keep this in mind if you decide ot start to tune MySQL settings.

RAM Checklist

  • What is the size of the active database(s) on the server? Do they have 1GB of data, or 500GB? You must know this before you begin to tune settings.

This command should give you a decent idea of the MySQL data. Keep in mind that not all of the databases listed here are active, some could be dead weight or not in use. Identify the main databases in use and focus on them.

cd /var/lib/mysql
du -hx --max-depth 1
  • Does the server have an adequate amount of RAM? If so, are there other services running that utilize a lot of the server's RAM? Is MySQL utilizing as much RAM as possible?

How much RAM do we have, and how much is actually in use?

free -m
  • If you do not understand how to read free -m, please consult with someone before making any judgements. Linux likes to utilize any free RAM for caching the filesystem, so almost every server will show that all the RAM is utilized, this does not mean that you are out of RAM, check the buffers/cache line for a more accurate view of RAM usage

How much RAM is MySQL using? Is this close to the size of your databases, or is it barely using any RAM?

ps faux | grep -i mysql

You can also use this script to get mysql memory usage, or memory usage for any process really. Create a file called mem.sh, paste in the contents below. Run chmod +x then run ./mem.sh mysql to get an idea on memory usage. You can also use this for php or apache or whatever else is running to get an idea of what is using RAM.

ps -C $1 -O rss | gawk '{ count ++; sum += $2 }; END {count --; print "Number of processes =",count; print "Memory usage per process =",sum/1024/count, "MB"; print "Total memory usage =", sum/1024, "MB" ;};'

Here is some example output from the script above.

./mem.sh mysqld
Number of processes = 1
Memory usage per process = 99.8828 MB
Total memory usage = 99.8828 MB

Disk Checklist

  • What is the average IO WAIT for the server? Is it consistent, or just brief spikes of IO WAIT?

vmstat is a great utility to start with. Look at the swap columns and make sure the server is not actively swapping in and out all the time. Small amounts of swap in and out are normal however if you see tons of activity for si (swap in) or so (swap out) then you should either kill off some unneeded services or get more RAM because heavy amounts of swapping is going to hurt performance. In the example below I am not swapping at all which is good.

vmstat -S M 1

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0    733     79     34    417    0    0    93    22   11    4  1  0 99  0  0	
 0  0    733     79     34    417    0    0     0     0  123  211  0  0 100  0  0	
 0  0    733     79     34    417    0    0     0    20  135  219  0  0 100  0  0	
 0  0    733     79     34    417    0    0     0    88  122  212  0  0 100  0  0	
 0  0    733     79     34    417    0    0     0     0  134  201  0  0 100  0  0	

You can also use sar -s and look at the %iowait column. There is always going to be some IOwait, anything under 1 or 2% is probably nothing to worry about but still you want to get iowait as low as possible.

sar -s

08:00:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
08:10:01 AM     all      1.16      0.05      0.39      0.21      0.35     97.84
08:20:01 AM     all      1.12      0.02      0.25      0.02      0.06     98.53
08:30:01 AM     all      1.85      0.01      0.32      0.02      0.04     97.76
08:40:01 AM     all      1.68      0.01      0.32      0.03      0.14     97.81
08:50:01 AM     all      0.80      0.01      0.23      0.10      0.02     98.84

To find out if we are swapping or not you can run the following command. Having a commit% that is over 100 is fine and normal. Linux likes to over commit RAM because many times services don't actually use tons of RAM and if they are not frequently run the data gets swapped from RAM to the disk because the data is not used often enough to deserve to be in RAM. %memused will almost always be above 80%. This is normal and it means that Linux is using the spare RAM as a cache until the RAM is needed, at that point Linux releases some of the RAM to programs that need it.

sar -r

08:00:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
08:10:01 AM    152812    842792     84.65     30668    403272   3217252    105.71
08:20:01 AM     95264    900340     90.43     33488    431564   3307188    108.66
08:30:01 AM     74380    921224     92.53     36660    436680   3330424    109.42
08:40:01 AM     86184    909420     91.34     39224    418056   3324144    109.22
08:50:01 AM     79356    916248     92.03     57612    388616   3336272    109.62

You can also just run the following to get a quick, current idea of ram use

free -m

If you are not seeing high IO WAIT or swap usage, then DISK IO is more than likely not an issue.

You can use iostat to get an idea of what the current IO looks like. Use the -y flag to only show current IO, if you do not use -y then the first line of output will always be the "since boot time summary" which isn't all that helpful.

iostat -y -x -m -d 1

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
vda               0.00     0.00    0.00    1.00     0.00     0.00     8.00     0.00    3.00   3.00   0.30

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
vda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

  • If you are seeing DISK IO issues, identify the storage devices and their configuration.

Check mounts and fdisk to see how many disks we have, and where they are mounted. Are we using a single disk, or do we have multiple disks in use? Do we have a dedicated MySQL drive, or not?

fdisk -l
mount -l

Is the server using RAID? If so, what does the configuration look like? Things to look for are RAID type, and if read or write caching is in use. You might want to make a note of this for future reference as well.

## If using LSI card
/opt/MegaRAID/MegaCli/MegaCli64 -LDInfo -Lall -aAll

## If using Adaptec card
/usr/StorMan/arcconf getconfig 1

At this point you should know if IO is an issue and the amount of disks in use by the server as well as whether or not RAID is in use, it's configuration and the type of RAID card. If you are not familiar with DISK performance or RAID, note the configuration and ask around to see if this is a decent configuration, or if the server is using old hardware.

It's hard to really suggest the correct disk configuration. RAM is easy, just add more, but with storage there are many different options, what is important for now is that we at least know what kind of storage is in use.

CPU Checklist

  • How many CPU cores does the server have and what is the model of CPU?
cat /proc/cpuinfo

If they are using an old CPU like a Core2Duo, or something like that, you might want to keep this in mind. As of 2013, anything in the range of an E3-12xx through E5-26xx should be sufficient. MySQL is usually happy with 8 - 32 cores, and if CPU% is low when you look at top, or the MySQL process you probably don't need to worry about the CPU.

MySQL Configuration

Now that we have an understanding of our server and the hardware that it is using we can finally start to configure MySQL (or maybe we fixed some hardware bottlenecks and MySQL is now running like a boss).

There is a common misconception that MySQL settings are complicated, and difficult to tune. This is incorrect. 90% of the time there are TWO settings to change. Just TWO settings. If you read this wiki from the beginning you might assume that these settings involve utilizing the MOST IMPORTANT piece of hardware, known as RAM. If you assumed this was the case then good job!

MySQL goes a very good job at setting good default options for most server settings. And for the most part you should never have to change any of these default settings. To keep things nice and simple I will cover the two most important settings below. First though, it's important to find out what version of MySQL you are using!

MySQL Version

Before you start tuning away, it's a good idea to find out what version of MySQL you are using! Believe it or not, newer versions of MySQL actually perform better than older versions, crazy right?

Much like newer Nvidia drivers, updated software generally comes along with performance boosts. This is certainly the case with MySQL.

MySQL 5.1 This is not terribly old, but if you are using 5.1 then you will want to update to 5.5 before you do anything else. Be sure to backup data before and upgrade AND PLEASE MAKE SURE THE APPLICATION / DATA is compatible with 5.5. normally not an issue, but I'm just giving you fair warning.

MySQL 5.5 This is an acceptable place to be. Performance for 5.5 is pretty close to that of 5.6, still though if you can upgrade to the latest stable release of MySQL you should.

MySQL 5.6 Current stable release. Should use MySQL 5.6 if possible. MySQL 5.7 is on it's way but as of Dec 2014 5.6 is the place to be.

MySQL 5.7 Is almost on it's way, maybe.

my.cnf changes you SHOULD NOT make

This section will cover some of the settings that you should not change unless you really know why you should change the setting. For the most part, the settings covered in this section are settings that might sound like a good idea to raise, however most of the time they end up wasting a lot of RAM and barely improving performance.


  • MySQL Default Setting: 128KB
  • What does this setting do?

Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans.

  • Why shouldn't I change this?

This is a SESSION based setting, meaning that you can specify this when you use the MySQL client to connect to the database. You would use this if you plan on running a query, or multiple queries that will be scanning a large amount of data.

This is not what most web applications will do, and even if they do full scans, it's much more efficient to raise the key_buffer value to make sure that all of the MyISAM indexes are being stored in RAM, which will significantly help performance.

Also, if you set this value to say, 32M, this means that EVERY query that performs a MyISAM scan will ALLOCATED 32M regardless of whether or not that amount is needed. So if you have 10 queries going on at the same time that want to perform a scan you are allocating 320MB of RAM. This is a massive waste of RAM and can actually help the box become OOM, which is not what we want to do!

Please leave this setting at it's default and forget it even exists.


  • MySQL Default Setting: 2MB
  • What does this setting do?

Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.

  • Why shouldn't I change this?

The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size.

Again, this is a setting that can waste a massive amount of RAM if you set this too high. The default size should be ok. If you start to raise this to 32M or higher you can start to waste significant amounts of RAM and not improve performance. Again, don't change this setting unless you know why you would want to change this. Otherwise please forget about this setting!


  • MySQL Default Setting: 256KB
  • What does this setting do?

When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.

  • Why shouldn't I change this?

This is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

Again, setting this too high (above 1M) can seriously waste a lot of RAM and will not improve performance significantly. Please do not change this setting.


source http://bugs.mysql.com/bug.php?id=73117

For MySQL 5.5 thread_concurrency defaulted to 0, which meant there was no limit to the amount of threads running under MySQL. The thing is, this doesn't do anything if you are using a Debian or RHEL based distro. Even if you tune this it won't matter because it's not a setting that was meant for most Linux systems. You can read more about the specifics by viewing the links in this section.


my.cnf changes you SHOULD make



"InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL tuning."

This is only applicable to InnoDB data, this setting has NO effect if you are using all MyISAM tables. Use the command below to determine what kind of tables are in use:

## How many InnoDB tables do we have?
mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'InnoDB'"

## How many MyISAM tables do we have?
mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MyISAM'"

If the server is roughly 50/50 in terms of MyISAM and InnoDB types then the next step would be to determine the amount of space that these tables use. If there is 20GB of InnoDB tables but only 50MB of MyISAM tables then you should focus on tunning InnoDB, not MyISAM.

The next step is to make sure that this setting is raised as high as possible, but low enough to allow other processes on the server to use RAM without swapping. If MySQL is the only service running, and the server is to be used purely for MySQL, then go ahead and raise this to about 70% - 80% of the server's total RAM.

Once you have tuned InnoDB to use RAM you are basically done tuning

What? Really? That's it? What about all those other settings in my.cnf? Well, those other settings don't really matter!. Don't get me wrong, you may need to change some of these settings from time to time, but for the most part you only need to change these if you are getting errors in the mysql log, or if there is a very specific reason to change them, but the other settings have little to no affect on overall performance.



"To minimize disk I/O, the MyISAM storage engine exploits a strategy that is used by many database management systems. It employs a cache mechanism to keep the most frequently accessed table blocks in memory"

You might have noticed mention of reducing disk IO and keep the most frequently accessed table blocks in memory. By now I hope the general theme is catching on. Use as much RAM as possible to reduce DISK IO which in turn improves CPU perfomance by allowing it to do more things. When this happens, the server is happy and life is good.

Anyway, MyISAM key_buffer_size is very similar to innodb_buffer_pool, they use RAM to store data. This improves performance. If you don't have a lot of MyISAM tables, just keep this setting at 64MB (MySQL internal tables still use some MyISAM, so you don't want to set this to 0). If you have a ton of MyISAM tables, raise this value to fit in the data set.

The key buffer only stores keys from MyISAM tables. So you don't want to make this large enough to fit all data, and rather leave memory free for the OS file cache to handle the non-key columns. A good rule of thumb would be to keep this below 30% of total RAM.

You can use the same set of commands mentioned above to determine what you are working with.

##Find all INNODB tables:
mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'InnoDB'"

##Find all MyISAM tables:

mysql -e "SELECT concat(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.tables WHERE engine = 'MyISAM'"

I belive that the max value for this at least for MySQL 5.1 is 4GB, so even if your dataset is larger than 4GB, don't set the buffer size higher than it's max limit. For most of the time, a key_buffer_size between 32M and 128M is good enough. Honestly using MyISAM for your database in 2013 is like buying a new HD tv so you can watch VHS tapes..

Now that you set key_buffer_size to a sane value you are all done tuning.



The default value for innodb_log_file_size in MySQL 5.6 is 48MB. There are two log files, so keep this in mind, as 2 log files each take up 48MB of space. For servers with SSDs you can raise this to 128MB, 256MB, or even 2GB. Keep in mind that the larger the logs are the longer a recovery might take. For spinning HDDs this is a problem since they are already slow, so recovery could take a long long time. If you have SSDs which are 100s of times faster recovery may take a few more seconds or minutes if you set this really high.

"The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O"

I typically set this to 128M and leave it. It's worth testing out performance differences on your own system, so don't take my word for it, test it out yourself.

You must stop MySQL, move old logs out of the way and then start MySQL to change this setting.

innodb_log_file_size = 128M

Other Performance Tweaks

There are none.

To be fair, there are no other settings that have as much of an impact as the two buffer settings. While you could tune a number of settings, they may only give you a few % boost whereas the two settings mentioned above can give you 200% + improvements.

This wiki is meant to get your configuration and settings to the 90% mark easily. It does not take a lot of time or effort to make sure that InnoDB and MyISAM data is in RAM, and applying new settings is very simple.

There is another 10% to go still, however this last 10% starts to get complicated and there are not really any "one size fits all" changes here, so unless you really know what you are doing, it's best to leave these alone.

If you want more juicy info on other tweaks, I encourage you to go here:


my.cnf template

DO NOT COPY PASTA THIS INTO A SERVER, EVER Seriously, don't. This template is meant to be a blanket template with safe defaults on a new server with no previous MySQL data.

Behold, the copy pasta chunk of optimization that you probably were expecting much earlier in this wiki:

port                                    = 3306
socket                                  = /var/lib/mysql/mysql.sock
default-character-set                   = utf8


port                                    = 3306
tmpdir                                  = /tmp

max_connections                         = 151

## Raise to 128M for 2GB RAM, 256M for 4GB RAM and 512M for 8GB RAM
key_buffer                              = 64M

## Raise to 128M for 2GB RAM, 256M for 4GB RAM and 512M for 8GB RAM
innodb_buffer_pool_size                 = 64M

## Misc Tunables (Don't touch these unless you know why you would want to touch these)##
max_allowed_packet                      = 16M

## Changing this setting requires you to stop MySQL, move the current logs out of the way, and then starting MySQL ##
innodb_log_file_size                    = 128M