key buffer
At peak traffic times the server load can reach as high as even 20 whereas it is usually below 1. Today I tried increasing the key_buffer variable from 16M to 300M and in minutes the server load went down from 20 to less than 1! I thought that my problems were solved but after a few hours the load went up again so I increased the Key-buffer again. The same thing happened and now my Key_buffer stands at 1000M
I currently have a dedicated P4 2.4 ghz server with 1gig RAM and have one main database driven site with 10,000 unique visits per day. I always have around 150M of RAM available in my buffer and 250MB of free memory.
What can the problem be? Will typing '\c' in mysql clear the key buffer and improve performance? If so can this be automated to happen every half hour?
thanks for any help
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=400
set-variable = wait_timeout=40
set-variable = key_buffer=100M
set-variable = join_buffer=2M
set-variable = record_buffer=2M
set-variable = sort_buffer=3M
set-variable = table_cache=18
set-variable = thread_cache_size=4
