MySQL: Five More Dials To Turn

Thursday Jul 9th 2009 by Sean Hull

Last month we talked about some things to set and tune in a new MySQL database installation. We touched on connections, query cache, temp tables, session memory, and the slow query log. This second part in the series hits on some other very important things to tune in a new MySQL database.

Last month we talked about some things to set and tune in a new MySQL database installation.  We touched on connections, query cache, temp tables, session memory, and the slow query log.

This second part in the series hits on some other very important things to tune in a new MySQL database.  

6. Innodb

InnoDB is likely your storage engine of choice for MySQL as it provides transactional support, as well as crash protection.  


Typically the InnoDB buffer cache should get the bulk of memory on the system.  So for example if you are just running MySQL on your server (we hope so), then perhaps 20% of the memory can go to the OS, 20-30% to sessions, and the remaining 50% to static buffers in MySQL.  If you're not using MyISAM most of that 50% will be for this InnoDB buffer pool.

calculate innodb buffer pool hit ratio
1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)

A hit ratio is not a bulletproof way to tune I/O, but it can give you a good indication and starting point from which to tune.  Since InnoDB caches both data and index in this buffer pool, the hit ratio here will tell you overall the % of hits that are satisfied by going to memory versus the ones which require disk I/O.  You want to do as little disk I/O as possible, so a higher hit rate here means you're caching better and more relevant blocks to your applications needs.


Hopefully MyISAM tables will be used less in your application.  If you're using FULLTEXT indexes, or BLOB datatype, you'll be using these types of tables.  Keep in mind that MySQL leaves caching of data blocks to the OS, so only index blocks are stored in the key buffer.  


Set this buffer and then keep an eye on the hit ratios to tune dynamically.  A good starting point would be 64M or 128M.  Also remember if you change the variable dynamically, be sure to also update your my.cnf file to the same value so the database will keep that value after restart.

calculate key buffer hit ratio
1 - ( key_buffer_reads / key_buffer_read_requests )

As with the innodb buffer cache hit ratio, this hit ratio tells you what percentage of requests were satisfied from memory (a hit) versus going to disk (a miss).  Keeping this hit ratio high indicates that your cache is full of blocks relevant to the SQL queries your application is running.

8. Tune Index Usage, Reduce Full Table Scans

We always wish that databases had a setting index_usage, and you could just turn that dial up to increase index usage.  If only database tuning were so simple!  Reducing overall full table scans will go a long way towards improving your MySQL server's performance and throughput.  

Luckily, we can calculate the percentage of table scans that are FULL TABLE SCANS.

perc_full_table_scans = 
   1 - ( handler_read_rnd_next + handler_read_rnd ) /
   ( handler_read_rnd_next + handler_read_rnd + handler_read_first +
   handler_read_next + handler_read_key + handler_read_prev )

You should also enable the slow query log (mentioned in part one of this article), and enable log_queries_not_using_indexes.

9. Security

There are a lot of different areas to be concerned about when considering security.  A few of the more important ones are as follows:

Issue the query below to view the users on your system.  Be sure they all have a password, especially the root account.  Also, be sure there is a host specified, not a wildcard, as this allows that user to connect from any host on the internet!  Most likely, the host's column should contain localhost, known webservers that connect to this database, and perhaps one or two remote client machines.  

mysql> use mysql;
Database changed
mysql> select user, host, password from user;
| user             | host      | password         |
| admin            | localhost | 441f54c126b8f67f | 
| pma_IiabUsiU1n6q | localhost | 6c4572a01bdfb70d | 
| horde            | localhost | 31e43e9079b4ad01 | 
| root             | localhost | 164bbc8331b849bd | 
| drupal           | localhost | 4bddbbde31e8ecb3 | 
| phplist          | localhost | 4bddbbde31e8ecb3 | 
| backup           | localhost | 164bbc8331b849bd | 

Also check secure_auth and be sure it is set to ON.  If you don't specifically need it, disable have_symlink, which allows files to be moved around and symlinked, but can also be used for nefarious purposes as well.  We also suggest you set skip_show_database to ON as well, which prevents users from seeing other "databases" or schemas inside your MySQL server.  Also unless you specifically need it, set local_infile as well to OFF.

10. Logs

There are many useful and relevant logfiles in MySQL, so you should look specifically at those and make sure they are enabled.  They can help immeasurably in problem resolution.  We talked about the slow query log previously; here are the rest.

binary log

You enable the binary log with the log_bin parameter.  By itself, it turns on binary logging.  You can also specify a filename.  Enabling the binary log is required for doing point in time recovery.  You can also use the mysqlbinlog tool to view exactly what queries are in the binary log, timestamps and everything.  This is powerful for problem resolution as well.  Also, if you are using replication, you'll need to enable the binary log.  Also set expire_logs_days to the number of days worth of old binary logs you want to keep around.  If you do backups every seven days, then you'll need at *least* seven days worth of binary logs to do point in time recovery.

general query log

Like the slow query log, this facility logs queries, but in this case *all* queries that funnel through your database.  This can be a useful feature to have, but can also grow large very quickly, so be sure to trim and/or rotate the logfile regularly.


MySQL has a lot of system variables, the proverbial dials on a complex piece of machinery.  However if you focus on a few important areas, you'll get your database in good running order. 

» See All Articles by Columnist Sean Hull

Mobile Site | Full Site