Improve MySQL Performance

Below is an example configuration file to tune MySQL a bit which you can use as a starting point. This will tell MySQL to use a lot more RAM than the default configuration but allows to keep a lot of the data in memory, which makes it much faster. On Debian/Ubuntu, I usually place this in /etc/mysql/conf.d/tuning.conf. On other systems, you can append it to the my.conf.

[mysqld]
# Amount of simultaneous connections
max_connections=100
# Total query cache size
query_cache_size=128M
# Size limit for a single query to be cached
query_cache_limit=1M
# Number of tables kept in cache
table_open_cache=3000
# Number of table defiinitions in cache
table_definition_cache=2500
# Size of temporary tables kept in memory
tmp_table_size=150M
# Max size of HEAP tables kept in memory
max_heap_table_size=150M
# Size of key/index caches
key_buffer_size=96M
# Size of Join caches
join_buffer_size=128K

# Log slow queries
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 5

# Size of InnoDB buffer. If you have a dedicated DB server with a lot of
# memory, you can make this as big as you can.. preferable big enough to fit your complete data.
innodb_buffer_pool_size=512M
# If you do not need full ACID compliance, this can be set 0. It means that you will loose data for
# the last 1-2 seconds but makes the system much faster.
innodb_flush_log_at_trx_commit=0
# Log file size. Careful when changing this, stop mysql first.
innodb_log_file_size=512M

# Various performance settings. See documentation for more information.
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

# Skips the slow DNS resolves.
skip-name-resolve

Important: When changing innodb_log_time_size, see http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely

 Note that this is just an example. You will need to fine-tune the exact settings based on the amount of data and amount of spare memory you have. A very helpful tool for this is https://github.com/rackerhacker/MySQLTuner-perl. Add the above example script, let your server run for a few days and then run the script which will tell you how much of the assigned resources are in use and will also give you a few tips on how to improve it.