A (small) MySQL configuration gotcha

Posted by Brandon Burton on February 3rd, 2010 filed in mysql, sysadmin

Yesterday I ran into a small, but DUH moment with MySQL.

First, some background.  Your typical /etc/my.cnf file looks like the one below


# Default MySQL startup options

[mysqld]
innodb_buffer_pool_size=256M
innodb_additional_mem_pool=20M
innodb_log_files_in_group = 3
innodb_log_file_size = 10485760
innodb_autoextend_increment = 8

innodb_flush_log_at_trx_commit=1
sync_binlog=1

default_table_type=innodb

server-id=1

expire_logs_days=7

[mysqld_safe]
log-error=/var/log/mysqld.log

Well, I had intended to turn on slow query logging to assist with some performance issue analysis, so I added the following to the bottom of /etc/my.cnf


#slow query log
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-slow-admin-statements

I added it, then didn’t come back the server for a few weeks.

When I came back I found 0 byte file. I found this odd and started doing some investigating.

After many SQL queries and much head pounding, I finally noticed I had added the lines to the of the file. This put it below the [mysqld_safe] block.

Of course that means that while the normal MySQL daemon is running, those config options will never apply.

Some quick testing on a test system confirmed this to be true and so the fix was to move those lines above the [mysqld_safe] and a scheduled restart later, we have slow query logging working.

Leave a Comment