Mysqld increases the load on the CPU and drops after flush-tables

Posted by mirage on Server Fault See other posts from Server Fault or by mirage
Published on 2011-11-11T19:36:04Z Indexed on 2011/11/13 17:54 UTC
Read the original article Hit count: 310

Filed under:
|

Help please advice on the issue.

Normal load on the cpu 20-30% us + sy. After restoring the database files from the slave server (same version) began a periodic problem. mysql starts to load the cpu at 100% (us + sy grows proportionally). The queue is growing, everything slows down. But with mysqladmin flush-tables are normalized for a few hours.

Dedicated linux server running mysql 2 x E5506 24Gb RAM, database size 50Gb.

[OK] Currently running supported MySQL version 5.0.51a-24 + lenny4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics --------------------------------------- ----
[-] Status: + Archive-BDB-Federated + InnoDB-ISAM-NDBCluster
[-] Data in MyISAM tables: 33G (Tables: 1474)
[-] Data in InnoDB tables: 1G (Tables: 4)
[-] Data in MEMORY tables: 120K (Tables: 3)

[-] Reads / Writes: 91% / 9%
[-] Total buffers: 12.8M per thread and 7.1G global
[OK] Maximum possible memory usage: 15.8G (66% of installed RAM)

4000 - 5500 rps

key_buffer              = 1536M
max_allowed_packet      = 2M
table_cache             = 4096
sort_buffer_size        = 409584
read_buffer_size        = 128K
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 64M
thread_cache_size       = 500
query_cache_size        = 100M
thread_concurrency      = 24

max_connections         = 700
tmp_table_size          = 4096M
join_buffer_size        = 4M
max_heap_table_size     = 4096M
query_cache_limit       = 1M

low_priority_updates    = 1
concurrent_insert       = 2

wait_timeout            = 30

server-id               = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = 1536M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2

How to solve the problem?

© Server Fault or respective owner

Related posts about linux

Related posts about mysql