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.