For the web server that using mysql as a database engine. Sometimes the process in mysql need a large resource, especially if server has a high traffic. According to my experience, to the high traffic server, the MySQL process itself can spend more than 30% of server resources. So, I will give you tips for optimizing mysql on the server that has high traffic.
Mysql configuration located in the my.cnf file, usually this file is in the /etc directory, but can also in other folders. For more safe, you can search my.cnf file to make sure the location of the file.
locate my.cnf
Once you found location of my.cnf file, edit the file with the editor that you like, and enter the configuration as below :
Note: Before you edit the my.cnf file, do the backup of the file first. so if new configuration you create fails, you can rollback to the old configuration. Some configuration have notes, you have to adjust the specifications of your servers .
[mysqld] set-variable = max_connections=400 safe-show-database local-infile=0 datadir=/var/lib/mysql skip-locking skip-networking safe-show-database query_cache_limit=1M query_cache_size=64M ## 32MB for every 1GB of RAM query_cache_type=1 max_user_connections=200 max_connections=500 interactive_timeout=10 wait_timeout=20 connect_timeout=20 thread_cache_size=128 key_buffer=256M ## 128MB for every 1GB of RAM join_buffer=1M max_connect_errors=20 max_allowed_packet=16M table_cache=1200 record_buffer=1M sort_buffer_size=2M ## 1MB for every 1GB of RAM read_buffer_size=2M ## 1MB for every 1GB of RAM read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM thread_concurrency=4 ## Number of CPUs x 2 myisam_sort_buffer_size=64M server-id=1 [mysql.server] user=mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout
After you save the configuration, please restart your mysql, try and taste the difference :) You can also use the free tool from http://www.day32.com/MySQL/ to obtain additional recommendations for setting up your mysql.
Have a nice try.. :)
Thanks for the tip. We’ve optimized all our servers in the datacentre using your tips.
Robert Punani,
Google Inc.