Hi,
I am using a production server with apache and mysql 5.0.22. Configuration is: 2 x Xeon 3Ghz and 4GB ram. Disks are SAS 15000rpm. OS is RHEL 5.
My problem is during peak time mysql is taking around 790% of total cpu’s and load average is around 40 (sometimes load average is 90+ and mysql crashes).
Below is my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
back_log = 50
long_query_time = 5
max_connections = 300
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 4M
sort_buffer_size = 2M
table_cache = 4096
thread_cache_size = 384
wait_timeout = 15
connect_timeout = 10
max_tmp_table = 256
tmp_table_size = 384M
max_heap_table_size = 64M
max_tmp_table = 256
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
concurrent_insert = 2
low_priority_update = 1
default-storage-engine = MyISAM
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=4M
innodb_buffer_pool_size=256M
innodb_thread_concurrency=4
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
I am using MyIsam tables and InnoDB for transaction and the server is hosting many websites (120 customers sites).
If you can help to find if there is a problem in my configuration or there is some wrong parameters.
Thanks,