Tweaking mySQL in Debian
This guide is not complete to tweak you mySQL on Debian systems, because a good tweak is depending on what you want to use mySQL to.
The key buffer holds the indexes of tables in memory, and of cause a bigger key buffer result in faster row lookups. The bigger this value is the better is it, but preventing swaping. good rule of thumb seems to be to use 1/4 of system memory. key_buffer = 256M
query_cache_size, as the name say it is the total memory available to query caching. query_cache_limit is the maximum number of kilobytes one query may be in order to be cached. If you set this value to high it will prevent a lot of small query to be cached. Setting it to low will prevent bigger query to be cached.
query_cache_size = 128MB
query_cache_limit = 4MB
If you application need to open a lot of tables, a important variable is table_cache, it is the number of tables a thread can keep open at the same time.
table_cache = 512
The InnoDB Engine:
Allmost everybody do not use InnoDB engine I mySQL, there are using MyISAM istead. Mysql reserved memory for InnoDB, so you could easy skip this Add skip-innodb to you my.cnf file.
If you don’t want to replicate data changes to a second server, and don’t use the binary logging as incredimental backup, you can disable this feature.
Comment out this line:
log_bin = /var/log/mysql/mysql-bin.log