Not the answer you need?
Register and ask your own question!

MYSQL performance issue?

manantsoamanantsoa EntrantCurrent User Role Participant
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,

Comments

  • januzijanuzi Advisor Inactive User Role Mentor
    Could You post info about mysql server status (show status / show global status - second one should be better) ?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.