The Five Minute DBA: Fix your slow database – my.cnf parameters

I thought I would write a few blog posts on what I am calling the 5 minute DBA. The content of these is  interesting or frequently asked questions that I get when out on a gig, you know those ones were a developer or a dba runs up and says: “Hey how do I do this?” I figure, If nothing else maybe it will save me some time in future.

There are two audiences here. The first is the true 5 minute dba. Their seems to be a lot of folks out their who end up responsible for fixing or maintaining a MySQL database who are not really DBA’s. They maybe developers, sysadmins, or even network guys who know just a  little about databases. These guys and gals become DBA’s five minutes at time during the day generally when something goes wrong.  They tend to be  looking for the quick fix, something that can be done in five minutes or less so they can get back to their other important tasks.

The second group are comprised of those who are employed to do database work fulltime. For these folks maybe these posts will serve as a reminder or even act as a reference of sorts. Some of them however maybe enlightening,  as I will also throw out some of the oddball questions I I get from time to time. You know… if you were on a deserted island and could only change 1 database parameter what would it be? Hopefully I am not the only one who thinks about that.
So Here goes.

So your database is crawling and you got called into try and make is run faster.  If you only have 5 minutes to make an impact on the system what 3 or 4 config parms  do you look at and change?

#1.  Key_buffer_size or innodb_buffer_pool_size:
Everyone should know these parameters, but I still run into clients who have these woefully underset ( as in default ).  Please try bumping these parms first.  In the simplest terms, these are the main memory pools for the database.  You want more data to be read out of memory as opposed to being read from disk.  The key buffer is only used for MyISAM tables, while the innodb buffer is obviously only for innodb.  The key buffer can only be set to go to 4GB ( unless you create different pools which is a whole nother topic ), while the innodb buffer pool does not have that restriction.  The innodb buffer stores both data and indexes pages in memory while the key buffer only stores indexes.  Rule of thumb is generally set your innodb buffer pool to 70-80% of your total physical memory, but don’t set it high enough for you to start swapping.

If you want to see what these are set to, you can do this:
mysql> show variables like ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name           | Value     |
+————————-+———–+
| innodb_buffer_pool_size | 805306368 |
+————————-+———–+
1 row in set (0.00 sec)
and
mysql> show variables like ‘key_buffer_size’;
+—————–+———-+
| Variable_name   | Value    |
+—————–+———-+
| key_buffer_size | 67108864 |
+—————–+———-+
1 row in set (0.00 sec)

Its also a good practice to check you database size to make sure you have not over allocated. You can do this a couple of different ways. My favorite is executing the following:

Select sum(data_length)/1024/1024 as data_size, sum(index_length)/1024/1024 as index_size, sum(data_length+index_length)/1024/1024 as total, engine from information_schema.tables group by engine

+—————+————–+—————+——–+
| data_size     | index_size   | total         | engine |
+—————+————–+—————+——–+
|    0.00000000 |   0.00000000 |    0.00000000 | CSV    |
| 3658.23437500 | 136.43750000 | 3794.67187500 | InnoDB |
|    0.00000000 |   0.00000000 |    0.00000000 | MEMORY |
|  331.06847763 |   0.99218750 |  332.06066513 | MyISAM |
+—————+————–+—————+——–+
4 rows in set (0.19 sec)

#2  innodb_flush_log_at_trx_commit

I like to harp on this all the time, disk access is crappy.  Whatever you can do to help limit or reduce the amount of work the disk has to do will generally help speed up performance.  The Parmeter innodb_flush_log_at_trx_commit can help you reduce some disk activity, at a cost however.  You can read about what it does officially here:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

Bascially when you have this set to 1 ( which is the default ) each commit will flush the log buffer to disk.  However setting this to 0 or 2 will delay this write to disk.   Setting this to 0 will delay the flush & write and only do this every second.  While this may seem small it can have a huge impact on write heavy database performance.  The question is, can you risk losing 1 seconds worth of “commited”.  In most cases people are ok with this, in fact lots of companies still rely on backups for restores ( so they figure they can lose hours or more of data ).

#3 query_cache_size & query_cache_type

There are a lot of people who are query cache haters out their.  Many of them believe that the query cache is a kludge, a krutch, or just a bad feature…   many people have pointed to scalability and other issues as reasons to just leave it off. I have told some clients to turn off their query cache because it was doing them more harm then good (check hits vs inserts and prunes).   But for a large population of users the query cache can dramatically improve your performance ( multiple times in some cases ).  Basically the query cache stores queries and result sets in memory to be used by the exact same query ( same spaces, cases etc ) if it comes back into the system.    Their have been some documented bugs with larger query caches ( some of which I believe are fixed now ) so I typically stay small and start with 128MB ( I generally go no more then 256MB ).  These parameters are dynamic, so they make it easy to test on your system ( set @@global.query_cache_size=128*1024*1024 ).  Typically I will set it, then watch the Qcache status variables and watch hits -vs- inserts.  If you have 1 insert and 100 hits that means for every query executed and inserted 100 queries did not have to be fully executed. Also watch out for low memory prunes.

mysql> show status like ‘Qc%’;
+————————-+——-+
| Variable_name           | Value |
+————————-+——-+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+————————-+——-+
8 rows in set (0.00 sec)

#4.  slow_query_log & long_query_time

These parameters will not speed up your database directly, but they will provide you with details on which queries can be analyzed and tuned, and that detail will improve your performance.  The very basic thing here is to turn on the slow log and run mysqldumpslow ( or mysqlsla ) after several hours with it on.  I will talk more on what sort of sql tuning you can do in 5 minutes in a post later on.

Don’t always increase parameters either.  Setting your buffer pools too high can cause as many issues as setting it too low.  While you maybe able to quickly set these parameters and forget them, i urge you to take more then 5 minutes to test them out before leaving the world of databases:)

**** Added ****  as always its better to test, these are very generic rules of thumb.

This entry was posted in 5 minute dba, mysql, performance. Bookmark the permalink.

9 Responses to The Five Minute DBA: Fix your slow database – my.cnf parameters

  1. If they are using default-ish parameters, I think you’ve nailed the big three to look for in terms of my.cnf settings. Obviously 90% of the problems will be due to schema design problems: rationalizing indexes and clearing up bad queries will usually go a lot further.

    Depending on their load profile, I might add innodb_thread_concurrency but that only makes a big difference in a minority of cases. If they have poorly designed/optimized queries the sort/read/join buffers and tmp/heap size could also make a big short term impact.

  2. matt says:

    Of course:) I am going to do another 5 minute dba post on analyzing the slow query log, indexing, and explains… just trying to get the basics.

  3. Scott says:

    Thanks for the quick tips! It reminded me that I have not looked at our production stats in awhile. What do you think of this? Better to turn it off?

    | Qcache_free_blocks | 79093 |
    | Qcache_free_memory | 229540200 |
    | Qcache_hits | 1702627210 |
    | Qcache_inserts | 667477746 |
    | Qcache_lowmem_prunes | 58519019 |
    | Qcache_not_cached | 209042197 |
    | Qcache_queries_in_cache | 119829 |
    | Qcache_total_blocks | 328998 |

  4. Krishna Ch. Prajapati says:

    Hi,

    As documneted above, set your innodb buffer pool to 70-80% of your total physical memory.

    I have server with 16GB of physical memory with 12GB of data(innodb). Is it feasible to set innodb buffer pool to 70-80%

    Currently, innodb buffer pool is 2GB. To what extent i can increase the innodb buffer pool in this senario.

    Thanks
    Prajapati

  5. A good intro for a 5 minute DBA.

    A few qualifying points.

    1. The key_buffer is used for MyISAM but only holds Index data (e.g. .MYI), Data is maintained by the File System Cache. I’ve heard they fixed the 4GB limit, but can’t confirm that.

    2. In theory setting the innodb_buffer_pool_size to 70-80% for an Innodb only DB is a guide, it fails as you indicate if your system swaps. max_connections and the type of application connection pooling is key here to know how big the buffer can be. Monitoring the Memory usage of MySQL in System & Process space is essential, and MySQL doesn’t provide the instrumentation or the limits (e.g. on Process space) that would make the memory management more ideal.

    3. From 5.0 it is always important to do SHOW GLOBAL VARIABLES when looking at system variables by default. In this case your examples are global, but you can get caught out looking at others, when the default scope is indeed SESSION. Again, the lack of MySQL instrumentation to differentiate global only, session only, and both is on my Wish List.

    4. When analyzing the benefit of the Query Cache, looking at the additional GLOBAL settings of Com_insert,_update_,_replace,_delete and Com_select help to gauge the overall Read/Write ratio of your system. The query cache may also benefit your system more at different times during the day, depending on how your data is maintained. Gauging the R/W ratio for each hour over a 24 hour period can provide valuable insite.

    5. With 5.1 the my.cnf settings [for configuring the slow log] have changed, these listed are now deprecated. In 5.1 you can also gain from logging to File & Table, however be warned that performance of accessing mysql.slow_log for large volumes of data is poor.

  6. Alex Sadykov says:

    Good article :)

    Matt/Ronald
    Last week i was able to build my first HA cluster from scratch.

  7. Pingback: Log Buffer #134: A Carnival of the Vanities for DBAs

  8. Ryan Lowe says:

    @scott: Much more useful than the all-time stats that you posted are current stats. Try checking the following:

    %> mysqladmin ext -ri 10

  9. J. Akerson says:

    I love your 5-minute approach and would love to see your 5-minute response to other things. Here’s a few:
    5-minute “how and what to monitor first”
    5-minute “scalability enablers”
    5-minute “security steps”
    5-minute “availability enablers”
    5-minute “things to do when starting/architecting a db”

    thanks
    -J.