Quebec Web Design Company

MySQL optimization

Shawn Purdy On January 14, 2013

Bookmark and Share

At some point you'll most likely start messing around with mysql's my.cnf and try to optimize your mysql setup. This guide is made to help you understand some of the options available in mysql, and the most common situations and how to deal with them. This guide will focus on mysqltuner.pl script which you can get online. This is a pretty useful script for testing mysql and finding out what may need to be adjusted. However understanding completely mysqltuner also requires some understanding of mysql, and this is usually quite a deep and complicated subject. This article is intended for beginners. So that you can come away with some understanding of mysql and how you can get more performance out of your mysql server.
 

Mysqltuner

mysqltuner is a perl script that you can get on github.
After you run mysql tuner you will end up with a report. We will focus on the performance metrics report which will look something like the below. Keep in mind it's important that MySQL is running for at least 24 hours before running this script. The Reason for this is mysql needs to run long enough to build caches and so the stats could be completely wrong if you try tuning in this situation. So you may be making things worse.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 24d 14h 56m 47s (94M q [44.387 qps], 3M conn, TX: 919B, RX: 14B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 182.0M global + 1.2M per thread (80 max threads)
[OK] Maximum possible memory usage: 282.0M (16% of installed RAM)
[OK] Slow queries: 2% (1M/94M)
[OK] Highest usage of available connections: 23% (19/80)
[OK] Key buffer size / total MyISAM indexes: 65.0M/261.0M
[OK] Key buffer hit rate: 99.6% (820M cached / 3M reads)
[OK] Query cache efficiency: 53.5% (32M cached / 60M selects)
[!!] Query cache prunes per day: 8918
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 13% (184K on disk / 1M total)
[OK] Thread cache hit rate: 99% (19 created / 3M connections)
[!!] Table cache hit rate: 0% (64 open / 326K opened)
[OK] Open file limit used: 1% (116/6K)
[OK] Table locks acquired immediately: 99% (59M immediate / 59M locks)
[OK] InnoDB data size / buffer pool: 224.0K/5.0M

Another line you may see is this
[!!] Joins performed without indexes: 2199

Now below we will explain each of these lines and what to look for. Anything marked with !! will appear in red in mysqltuner which usually it makes a suggestion to increase that value, but understanding when to change a value and when not too is where having some mysql understanding is important.

[--] Up for: 24d 14h 56m 47s (94M q [44.387 qps], 3M conn, TX: 919B, RX: 14B)
This line shows us how long mysql has been running and how many queries are run per second (44.387 queries per second) Which is a decent amount of traffic.This will depend on how many queries your application is performing, but also how much traffic your getting. So it will vary greatly from website to website. or server to server.

[--] Reads / Writes: 62% / 38%
This is the ratio of mysql reads to mysql writes. It's important to know what your data is doing more often either reads or writes.as this can help you decide in some cases on what storage engine to use. For most people this ratio will be more reads than writes. Which is also the case for this example. For example usually most websites will use myisam, but in some cases you will want to use innodb. innodb is useful if you are doing a lot of writes as innodb has row locking where myisam locks an entire table. What row or table locking means is while that table is locked it can't be written too. So it's much more handy to have row locking only in some cases. So for performance reasons innodb may help in cases where you doing a lot of writes. However innodb also has a larger memory footprint and is still some what slower than myisam. So you really need a heavy loaded database before innodb is really a legit option.

[--] Total buffers: 182.0M global + 1.2M per thread (80 max threads)
This shows you how much memory mysql will use. Which is important to know based off how much ram you do have available.

[OK] Maximum possible memory usage: 282.0M (16% of installed RAM)
This is how much memory mysql will use if it was using all available connections. Also very important for the scalability of your server.

[OK] Slow queries: 2% (1M/94M)
This shows you how many slow queries there has been since mysql was last started. Usually if you enable the slow query log you can view which queries these are so you can either re-write the query if needed. But this all depends on how long you set slow queries too. A Query can take 2 seconds but that doesn't mean it's a bad query. It just depends on how heavy you data set is. So the best practice is to investigate your data and your query to decide what the best course of action should be.

[OK] Highest usage of available connections: 23% (19/80)
Max amount of connections that were needed which is 19 of 80 total in this example. If you have a really high amount of connections make sure your application is not using mysql's pconnect as this usually uses up extra connections for no real benefit. pconnect stands for persistent connection. This is even more important if your memory is restricted as it will effect the overall scalability of your server.

[OK] Key buffer size / total MyISAM indexes: 65.0M/261.0M
[OK] Key buffer hit rate: 99.6% (820M cached / 3M reads)
This is the total amount of MyiSAm indexes. Basically indexes consistent of primary keys, unique and general indexes. These are used in relational databases with tables that are related to other tables. So you should always make sure that this number is higher than the total. In This example we only have a 65M buffer and there is 261M in indexes. Thankfully in this case there is extra databases that are not actually being used at all, but if you did have data like this really key_buffer_size should be probably at least 270M instead of 65M.

[OK] Query cache efficiency: 53.5% (32M cached / 60M selects)
[!!] Query cache prunes per day: 8918
Here we have the query_cache, and there is more than just query_cache_size that will effect this. But as you can clearly see here we have almost 9000 daily prunes. This suggests I need a larger query cache. Right now it's set to 64M and it would probably be okay at 128M. However query_cache_min_res_unit = 2K Depending on your data and your queries it may make sense to either raise or lower this number. If your doing a lot of really small queries with small amounts of data that are under 2K. These will not get cached. So you may want to cache these and making it 1K for example will allow this.

[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 1M sorts)
For best performance sort_buffer_size should always be 256K, this may vary in extreme cases, but from my experience everything higher is slower.

[!!] Joins performed without indexes: 2199
This means you have relational tables in your database(s) that are not using indexes. It's highly suggested you leave join_buffer_size at 256K and not increase this value as it uses a lot more memory and is not as fast as adding indexes to your tables. Although if your application is poorly designed it may be more work to add these indexes. But it will be worth the effort in the long run.

[OK] Temporary tables created on disk: 13% (184K on disk / 1M total)
This depends mostly on 3 variables. The first two are in mysql and are called
tmp_table_size
max_heap_table_size

Both of these values should be the same. 32M is usually a good starting point, and really 64M after that. However if you don't see any improvement after increasing these values you can safely ignore this check. BLOB and TEXT fields do not get cached in this cache and if you have a lot of these it skews the results of this check. No amount of memory will make any difference here.

[OK] Thread cache hit rate: 99% (19 created / 3M connections)
I have this set to 16K and have found this to work quite well. You don't want to starve mysql of threads. So setting this value high lets mysql use threads when it needs to.

[!!] Table cache hit rate: 0% (64 open / 326K opened)
This is a big debate and depending on who you ask you might get a different answer. But basically if it's possible to get this above 80% you could try to keep table_open_cache as high as you need, but if you look at this example we have 326K opened, Making table_open_cache that large is just crazy, and is not faster. It's actually faster to just make table_open_cache 64 and ignore this check.

[OK] Open file limit used: 1% (116/6K)
This goes hand in hand with the table cache above. So it's unlikely it will go any higher here 4096 is a good and standard setting to start out with.

[OK] Table locks acquired immediately: 99% (59M immediate / 59M locks)
This is locks that myisam puts on tables. If you have a really low % here you may be in need of innodb tables for certain tables.

[OK] InnoDB data size / buffer pool: 224.0K/5.0M
Finally this is the innodb index pool. It's similar to myisam's key_buffer_size it stores the indexed information for innodb tables for tables that have relationships.
 

Final Thoughts

Hopefully this gives you some better understanding of what you need to do to optimize mysql. Keep in mind it can sometimes take weeks to get a really solid configuration, and as data changes on your server it's always a good idea to run mysqltuner every so often to see if anything needs further tweaking.





Comments

Subscribe to our mailing list

Live Help