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.

Key Buffer:
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:
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

Table Cache:
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.

Binary Logging:
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


You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *