How To Optimizing MySQL Server
By dieq41 | May 6, 2009
Source : dhika.cikul.or.id
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..
Topics: Programming, Software, Tutorial - Tips & Trik | No Comments »
![[Valid RSS]](valid-rss.png "Validate my RSS feed")
