MySQL Optimization
January 20, 2009 Posted by KP
I only optimized MySQl on a VPS several years ago, it seems that my server never had to. I was just aware of those my.cnf examples coming along with the installation.
There are 5 example files under /usr/local/share/mysql/:
my-huge.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
my-innodb-heavy-4G.cnf
Each file is for servers with different configurations, mainly memory. My server has 2GB memory, my-huge.cnf is for me:
This is for a large system with memory of 1G-2G where the system runs mainly MySQL.
Although it’s named ‘huge’, not every configuration is huge enough, I changed max_allowed_packet to 4 MB for bigger file upload, also removed many things I don’t need or quite understand, then copy it to /usr/local/etc/my.cnf.
Any improvement? I have no idea. It does make me feel better, though. 
Here is my my.cnf, not for reference, but please let me know if you find that something should be changed.
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 4M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU’s*2 for thread_concurrency
#thread_concurrency = 8
skip-networking
skip-federated
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
Related Posts:
- Optimizing MySQL
- MySQL Backup and my.cnf
- MySQL Start Script
- Monitor MySQL Log File
- Using Perl and MySQL
- MySQL Log File
- Create MySQL User
Filed Under: MySQL