Optimise MySQL Configuration for WordPress

Re-configure default MySQL server to run more efficiently.

Below is the content of my.cnf file that we use on a Debian Wheezy server with MySQL 5.5. Server has 2GB of RAM.

MySQL dataroot /var/lib/mysql is mounted on a separate drive.

# The MySQL 5.5 database server configuration file
# Server with 2GB of RAM + 1GB swap
# By: www.lisenet.com
#
# Global total buffers (mysqltuner) include:
#   key_buffer_size
#   innodb_buffer_pool_size
#   innodb_additional_mem_pool_size
#   innodb_log_buffer_size
#   tmp_table_size
#   query_cache_size
#
# Per-thread buffers (mysqltuner) include:
#   read_buffer_size
#   read_rnd_buffer_size
#   sort_buffer_size
#   join_buffer_size
#   thread_stack
#   binlog_cache_size

[mysqld]
# *** Basic Settings ***
user                    = mysql
pid_file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc_messages_dir         = /usr/share/mysql
skip_external_locking
symbolic_links          = 0
bind_address            = 127.0.0.1

# *** Tuning ***
myisam_recover          = BACKUP
max_connections         = 100
wait_timeout            = 3600
interactive_timeout     = 3600
# nofile is set to 2048 for mysql user in limits.conf 
open_files_limit	= 2048
table_open_cache	= 1024
max_allowed_packet	= 16M
# Max size to which user-created MEMORY tables are permitted to grow
# Make these equal
max_heap_table_size     = 256M
tmp_table_size          = 256M

# *** Fine Tuning ***
key_buffer_size	 	= 128M
join_buffer_size 	= 2M
sort_buffer_size	= 2M
read_buffer_size 	= 2M
read_rnd_buffer_size	= 256K
thread_stack		= 256K
thread_cache_size       = 8

# *** Query Cache Configuration ***
query_cache_type	= 1
query_cache_limit	= 4M
query_cache_size        = 64M

# *** Logging ***
# Be aware that this log type is a performance killer
general_log_file        = /var/log/mysql/mysql.log
general_log             = 0

#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time        = 2
#log_queries_not_using_indexes

# *** Replication and Binlogs ***
# Binlogs are not enabled
server_id		= 1
#log_bin		= /var/log/mysql/mysql-bin.log
expire_logs_days	= 7
max_binlog_size         = 1G

# *** InnoDB ***
innodb_file_per_table   	= 1
innodb_flush_method      	= fsync
innodb_flush_log_at_trx_commit 	= 1
innodb_log_file_size     	= 32M
innodb_buffer_pool_size 	= 128M
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_instances 	= 1
innodb_log_buffer_size		= 10M
innodb_lock_wait_timeout 	= 100
innodb_data_file_path    	= ibdata1:16M:autoextend:max:2048M

[mysqldump]
quick
quote_names
max_allowed_packet = 16M

[isamchk]
key_buffer	= 16M

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
!includedir /etc/mysql/conf.d/

Mysqltuner:

$ mysqltuner
...
[--] Total buffers: 586.0M global + 6.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.2G (60% of installed RAM)
...