From HostThyself

Tuning MySQL to get better performance.

[edit] Tuning MySQL for VPS

In a VPS scenario, CPU and memory are restricted, especially on a budget VPS. For a low traffic it is sufficient. Tuning MySQL could squeeze a little more out of a VPS.

What I use on a 256Mb, 100Hz VPS

[mysqld]
set-variable = max_allowed_packet=32M
set-variable = max_user_connections=650
set-variable = max_connections=650
set-variable = wait_timeout=60
set-variable = interactive_timeout=5
log_slow_queries=/var/log/mysql_slow-queries.log
long_query_time=5

max_connections = 660
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1024
thread_cache_size = 128
wait_timeout = 60
interactive_timeout = 5
connect_timeout = 10
max_allowed_packet = 32M
max_connect_errors = 10
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
skip-innodb
safe-show-database

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
open_files_limit = 8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[edit] Finding slow queries

Slow queries can drag down the performance of the server. Often it is caused by no or improper index. By finding out which query is slow, you can add index to speed up the query.

In the configuration notice the line

log_slow_queries=/var/log/mysql_slow-queries.log

In the server create the file and set the ownership to the mysqls user with the following

touch /var/log/mysql_slow-queries.log
chown mysqld:mysqld /var/log/mysql_slow-queries.log

Check the log file from time to time to see if there is any slow queries.

[edit] References

• MySQL Tuning and Optimisation: Some Consulting Tips
• Tuning MySQL
• What to tune in MySQL Server after installation
• Optimizing for Query Speed