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
Leave a Reply