Forum Moderators: open
what's the best solution to mod. my.cnf mysql
this is my conf. of my.cnf at the moment
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = key_buffer_size=16M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = max_allowed_packet=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
set-variable = max_connections=600
set-variable = thread_concurrency=8
log-bin
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
thanks
key_buffer = 1G
max_allowed_packet = 8M
thread_stack = 128K
sort_buffer_size = 16M
thread_cache = 200
thread_cache_size = 256
table_cache = 768
Setting your key_buffer size big enough is a very important issue. Here's a real good tip to find out if you have enough in your key_buffer_size:
For example on my server, I found that Key_read_requests=2546754640 and Key_reads=1221270. Now I compute the ratio, and I find that the ratio of requests to reads is 2085.33. In other words, out of every 2085.33 requests it does one "real" read.
Anything over 1000 is excellent. So I am happy with the amount of RAM I threw at my key_buffer_size. (I threw a whole gigabyte at it. I hope that would be enough!)
Unfortunately this is only one issue out of a hundred issues. A real good question to ask yourself is, do you have enough RAM to do all the things that you are telling mysql to do? I used to have load problems and I kept upping the values, higher, and higher, and the server got slower, and slower. Then I discovered that if I just go in the OTHER direction... ;-)
?
FDR
