Posted by gil on January 29, 2010
I have my aviation site Planenews on a FreeBSD server. As traffic increased, I was getting more database errors. Looking around the web for clues, I discovered that FreeBSD did not have a default my.cnf file in /usr/local/etc. You can find sample files in /usr/local/share/mysql. I used my-huge.cnf, renamed it to my.cnf, put it in /usr/local/etc, et voila (don’t forget to restart MySQL)!
Problem solved? Nope.. I was still getting errors at peak traffic. I then found mysqltuner, a Perl diagnosis tool for MySQL. I was missing a few variables in my.cnf. See the file below, and notice the additions under “Added by Gil.”
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
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 = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
# Added b Gil:
# max_connections 250 crashes my server, use with caution..
#max_connections = 250
wait_timeout = 180
interactive_timeout = 45
tmp_table_size = 64M
max_heap_table_size = 32M
# Disable Federated by default
skip-federated
skip-innodb
skip-bdb
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
The site seems to be running fine now, with no errors. I guess I will have to wait for a story to make it to a major social networking site to see if it really can take a heavy load. Please tell me about your optimization tips, and how you prepared for traffic spikes…
Related posts
Filed under: Computing, HOWTO
Leave a Reply