MySQL: Five More Dials To Turn

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.  

innodb_buffer_pool_size

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.

7. MyISAM

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.  

key_buffer_size

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.

Conclusion

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

Sean Hull
Sean Hull
Sean Hull is a business and technology consultant, author and speaker. He is the founder and senior consultant at Heavyweight Internet Group in New York and has worked as a database consultant and expert for ten years. He is the author of "Oracle and Open Source" on O'Reilly & Associates and his articles have appeared in Oracle's own OTN website, DBA Zine, ChangeThis.com, and many others. Using techniques, technologies, and perspectives discussed in the book, articles, and seminars, he has taken his career into exciting areas. He has served as Technology Consultant, Project Manager, CEO, and mentor with a background in programming, systems integration & administration, project development and management. He can be reached at [email protected] or visit http://www.iheavy.com for more info about consulting services.

Latest Articles