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.