# 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

#interactive_timeout=180interactive_timeout=100#wait_timeout=180#wait_timeout=100# Reduced wait_timeout to prevent idle clients holding connections.#wait_timeout=30wait_timeout=15connect_timeout=10# max_connect_errors is set to 10 by default#max_connect_errors=10

#table_cache=256#table_cache=1024# Checked opened tables and adjusted accordingly after running for a while.table_cache=512#tmp_table_size=32M by default#thread_cache=128# Reduced it to 32 to prevent memory hogging. Also, see notes below.thread_cache=32# key_buffer=258M# Reduced it by checking current size of *.MYI files, see notes below.key_buffer=128M# Commented out the buffer sizes and keeping the default.# sort_buffer_size=2M by default.#sort_buffer_size=1M# read_buffer_size=128K by default.#read_buffer_size=1M# 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below.# read_rnd_buffer_size=256K by default.#read_rnd_buffer_size=1M# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.# myisam_sort_buffer_size=8M by default.#myisam_sort_buffer_size=64M# thread_concurrency = 2 * (no. of CPU)thread_concurrency=2

# log slow queries is a must. Many queries that take more than 2 seconds. # If so, then your tables need enhancement.log_slow_queries=/var/log/mysqld.slow.loglong_query_time = 2slow_query_log = 1slow_query_log_file= /var/log/mysqld-slow-querys.log