You are here
my.cnf Optimization for Fun and Savings
A well-tuned my.cnf file can be the difference between a nicely purring database server and one that is crawling like frozen molasses. I present an annotated version of my own my.cnf file here for those interested in specific tuning suggestions for that file.
Do not blindly copy this code block into your my.cnf file - you need to set the path names appropriately for your needs, and in many cases, the default is just fine. On the other hand, I have divided my I/O load between two disk arrays, so I've changed the defaults some.
# Obviously you will want to preserve whatever connection settings you
# have for your distribution. port, socket, user, pid-file, basedir,
# and other directory settings should only be changed from what your
# distribution gives you if you know why they should change. In order
# to prevent silliness I've commented most paths out. Again:
# I HAVE COMMENTED OUT ALMOST ALL PATH NAMES. BE SURE TO SET YOUR OWN
# AND SET THEM PROPERLY WHERE YOU NEED TO.
# YOU SHOULD TAKE YOUR PATH DATA FROM YOUR DEFAULT my.cnf FILE UNLESS
# YOU KNOW YOU NEED TO CHANGE IT.
# Ahem.
# I don't want people blindly copying and pasting then watching things
# blow up because they are running CentOS or something.
# Ideally, you should go from section to section and just pull in the
# optimizations as you need them.
[client]
port = 3306
#socket = /var/run/mysqld/mysqld.sock
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
# socket = /var/run/mysqld/mysqld.sock
nice = 0
# This open-files-limit is excessive. It's only needed if you insist on
# staying with MyISAM and then only to crazy levels. I just don't have
# any reason to remove it.
open-files-limit = 32768
# I use log-error rather than syslog.
#syslog
#log-error = /var/log/mysql/mysql-error.log
[mysqld]
# * Basic Settings
# user = mysql
# pid-file = /var/run/mysqld/mysqld.pid
# socket = /var/run/mysqld/mysqld.sock
# port = 3306
# basedir = /usr
# If you move your database to another directory, you will also have
# to set it here.
# datadir = /data/mysql
# If you make another temp directory just for MySQL, be sure to mount
# it tmpfs as with any other temporary directory if this is not a
# replication slave.
# If this is a replication slave, you should set this to a directory
# that survives reboot, so that temporary tables can be successfully
# replicated. See
# http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html
tmpdir = /tmp
# language = /usr/share/mysql/english
skip-external-locking
# Security
# No community software I am aware of actually needs LOAD DATA LOCAL,
# so I just disable it. See:
# http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html
local-infile = 0
# This prevents granting access to stored procedures automagically just
# for creating them.
automatic_sp_privileges = 0
# safe-user-create prevents autocreating users with the GRANT statement
# unless the user has the insert privilege on mysql.user
safe-user-create = 1
# secure-auth is probably not relevant if your server was built in the
# past several years. I just like it on.
secure-auth = 1
# Remove skip-show-database if you use phpMyAdmin or a similar tool to
# manage your databases, it will just frustrate you or your users.
skip-show-database
# This limits where the LOAD_FILE, LOAD_DATA and SELECT INTO OUTFILE
# statements can read from or write to. This is a good option to set.
secure-file-priv = /tmp
# Networking
# I have replication setup, on a separate interface, so I bind MySQL to
# a socket and the address for that interface. If you're just using a
# single machine, use
# skip-networking
# instead. Sockets are faster than TCP connections.
bind-address = 192.168.0.1
# There's no reason to waste time resolving domain names. If the ip
# changes, we'll know.
skip-name-resolve
# Tuning
# See my first guide at
# http://vekseid.com/blogs/vekseid/optimizing_a_server_for_mysql
# For how to enable HugePages so you can use the large-pages option
large-pages
# The client default is 16M, while the server default is 1M. Setting
# the server to 16M can make some large operations easier.
max_allowed_packet = 16M
# There's no serious reason to have a long interactive timeout. If you
# are low on connections, you shouldn't set this higher than
# wait_timeout
interactive_timeout = 3600
# I am of the opinion that the default value is far too high. IF you
# use persistent connections, even a timeout of 300 may be too high.
wait_timeout = 300
# The following two are best set to the same size, because the size
# of temporary tables is limited by the lower o the two.
# I have not found any benefit in increasing the value past my
# tmp_table_size default.
tmp_table_size = 32M
max_heap_table_size = 32M
# The next two lines replace the basic table_cache value as of MySQL
# 5.1. table_definition_cache should be big enough for every table
# in your database, plus temporary tables, and table_open_cache
# should be a reflection of how many of these will be open in a live
# connection at once - it will likely exceed your definition cache.
# It doesn't hurt to set these to large values. They don't take a lot
# of RAM and it's better than hitting the limit.
table_definition_cache = 4096
table_open_cache = 16384
# The default optimizer_search_depth is 62. This causes MySQL to take
# an obscene amount of time planning a query, though when it finally
# executes, it is pretty close to optimal. Since the vast majority of
# queries my software runs involve four or fewer relations, I set it
# to four.
optimizer_search_depth = 4
# I'm not personally a fan of UTF8, multi-byte strings are less
# efficient and annoying, while websites perform just fine by using
# ampersand codes to represent nonstandard characters.
character-set-server = latin1
# Buffers
# Past allocations of 256K, Linux switches from malloc () to the less
# time-efficient mmap (). Making buffers larger than 256k, then, is
# not necessarily a good idea. You will have fewer 'bad' queries,
# individually, but you lose out on the vast majority of other queries.
# read_rnd_buffer_size is an exception - especially on forums with
# verbose posters, a lot of times, this really is reading in a megabyte
# or so at a time, so a higher value is beneficial.
join_buffer_size = 256K
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 2M
# Threads
# 256K is now the default for 64-bit systems, this line is just a
# legacy from when I had it configured on 32-bits and was following
# the above advice.
thread_stack = 256K
# max_connections is how many connections your server will tolerate at
# once, while thread_cache_size is how many of these your server will
# cache. There is no reason not to set these to be an equal number - I
# have seen no evidence that the trivial amount of RAM a low
# thread_cache_size is worth the performance hit of opening up a new
# thread under load.
# In realistic terms, you should 'tune to failure' - you don't want
# to support more active connections than your system can feasibly handle.
# 128 is a good number for most low-end servers produced these days.
# Increase if you have multiple drive arrays or faster disks.
thread_cache_size = 256
max_connections = 256
# Query cache
# I laid out reasons and limitations of the query cache in the previous
# article:
# http://vekseid.com/blogs/vekseid/mysql_query_cache_and_innodb_considerations
# The only additional point I would make here is that changing
# query_cache_min_res_unit from the default is effectively useless,
# especially if you flush regularly like the above article describes.
query_cache_type = 1
query_cache_limit = 256K
query_cache_size = 256M
query_cache_min_res_unit = 4K
# MyISAM
# 16M is the most MySQL will store entirely in large-pages. Past that,
# it will start shunting some of it off to normal memory. Since I only
# use about 7 megs normally, this isn't a problem. Otherwise, however,
# it can make calculating how much space you need difficult.
key_buffer = 16M
# Set this to the size of a filesystem block - e.g. 4k
key_cache_block_size = 4K
# These two should both be the default values. If you are bulk-loading
# data from a script, you may want to increase bulk_insert_buffer_size
# to speed up operation.
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
# Auto-creates a backup when running the recover operation.
myisam-recover = BACKUP
# By default, MyISAM tables will INSERT into deleted row space before
# appending to the end of the disk. In exchange for saving this trivial
# amount of space, once a row gets deleted, only one insert operation
# may occur at a time until holes are filled. Setting concurrent_insert
# to 2 stops this silly behavior, at the cost of wasting a bit of disk
# space, for a significant performance improvement in MyISAM tables.
concurrent_insert = 2
# InnoDB
# The following three line are only necessary in MySQL 5.1, for loading
# the plugin which supports the new InnoDB file format.
# If you have 5.5 or later, skip these lines.
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
default-storage-engine = InnoDB
# In theory, you want this to encompass your entire database. In
# practice, you only have so much RAM available. Right now I 'need'
# 20 gigabytes in the buffer pool I only have 12.
# Fortunately, as long as you can store a big enough chunk that it
# gets a 99% hit rate, you will be fine.
innodb_buffer_pool_size = 8192M
# As with everything, only move this if you know you need to.
# innodb_log_group_home_dir = /data/mysql
# innodb_data_home_dir = /var/lib/mysql
# I set 128M as the size of ibdata1 because that's how big individual
# extent entries are in the ext4 filesystem.
innodb_autoextend_increment = 128
innodb_data_file_path = ibdata1:128M:autoextend
# Supposedly, smaller is better because it makes recovery faster, even
# if larger means slightly better performance. I have no idea what the
# logic of this is - if I have a crash, downtime is expected. But I
# have not had InnoDB crash on me in three years now.
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
# No reason to make the buffers too large, however.
innodb_log_buffer_size = 2M
innodb_additional_mem_pool_size = 2M
# Setting innodb_flush_log_at_trx_commit to 0 causes InnoDB to only
# flush to disk once per second, improving performance considerably.
# In a community environment, you are more likely to lost a topic
# read entry or something equally trivial than a post, so the data
# is very rarely going to be critical, and users often have backups
# of their own posts.
innodb_flush_log_at_trx_commit = 0
# file_per_table makes checking out which tables are doing the heavy
# lifting a lot easier, for sure. It is also required for the
# barracuda file format.
innodb_file_per_table
innodb_file_format = barracuda
# Enabling strict mode helps prevent messing up creating or altering
# a table to support the new row formats in barracuda.
innodb_strict_mode = 1
# I haven't played too much with innodb_thread_concurrency. In my
# experience, for web purposes at least, Intel architectures work best
# up to four times the number of cores. Your mileage may vary.
innodb_thread_concurrency = 8
# I set this to sixty because I have obsessive compulsive disorder.
# Don't fret over it.
innodb_lock_wait_timeout = 60
# O_DIRECT bypasses the operating system's handling of data. This
# can drastically improve how well your system handles large amounts
# of RAM by removing double buffers (once in InnoDB's cache, again
# in the filesystem's cache), at a slight cost to reliability.
# This appears to be more dramatic the more RAM you have.
innodb_flush_method = O_DIRECT
# thread_concurrency is for Solaris only. It does not apply to your
# Linux box.
#thread_concurrency = 10
# * Logging and Replication
#
# As the default configuration file says, as of 5.1 you can
# enable the general log at runtime.
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging is defined in the mysqld_safe entry.
#
# Here you can see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# You can set this to non-integer numbers now. One second is rare
# enough for me that I consider it 'long'.
long_query_time = 1
# In order to keep your sanity, you should only use this when
# developing software. It would be nice if developers of community
# software did track this more often.
#log-queries-not-using-indexes
#
# The binlog is for replication, so I've commented it out here.
# Setting sync_binlog to an extraordinarily high value (256 in my case)
# significantly reduces the load the binlog puts on the server.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
#binlog_cache_size = 256K
#sync_binlog = 256
#expire_logs_days = 14
#max_binlog_size = 1G
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
# The following items are largely defaults as setup by Debian Squeeze
# If you are largely using InnoDB, only the mysqldump section is of
# any concern.
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
# Tab completion is a freaking sanity saver.
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 16M
write_buffer = 16M- Vekseid's blog
- 1121 reads
Add new comment