Hi, I tried to solve this issue for few weeks, searched everywhere and cannot find the cause of this issue.
We have two nearly equal mysql/innodb boxes in master-master replication. The setup used to be:
32GB RAM , innodb_buffer_pool=20G
Then we upgraded the memory:
64GB RAM , innodb_buffer_pool=40G
The upgrade became a problem.
After the buffer pool fills up to about 25GB, we see random freeze for 1-2 seconds. It appears about 20-100 times per hour, or so, I don’t see any regular fashion in this. The frequency of freezing gets worse while the workload becomes more write-intensive (batch data importing). I think it definitely must be some locking issue, because the box load (4CPU cores, AMD) is ususally lower than 1.0 and iowait something between 0 and 5 percent, we don’t se any spikes in the cpu usage.
I don’t think it’s an hardware issue, both machines are affected in the same way. I suspect some backround job like innodb checkpointing, but I’m not sure.
What we tried and didn’t help:
- disable query cache
- set another innodb_thread_concurrency (0, 8, 12)
- upgrade from 5.0.87 to 5.1.34
- increase innodb_log_size from 200M to 600M
- change innodb_max_dirty_pages_pct to 20 or 30
- change thread_cache_size between 0 and 20
- change binlog_format between statement and mixed
- optimize table, analyze table
- decreasing innodb_buffer_pool to 15G (of course, it decreases overall performance and raises disk usage)
30GB buffer pool seems to produce less freezing than 40GB - I assume it might be connected with random innodb index dives in the optimizer?? There has been a bug concerning this when SHOW STATUS was issued, but It has been already fixed.
slow_query_log = /var/log/mysql/slow-query.log
long_query_time = 1
max_connections = 1000
max_connect_errors = 10000
max_user_connections = 500
myisam_sort_buffer_size = 5M
thread_cache_size = 20
read_rnd_buffer_size = 5M
read_buffer_size = 2M
join_buffer_size = 2M
key_buffer_size = 100M
table_cache = 1000
sort_buffer_size = 2M
innodb_buffer_pool_size = 40G
innodb_log_file_size = 600M
tmp_table_size = 100M
max_heap_table_size = 100M
query_cache_size = 20M
query_cache_limit = 100K
60GB InnoDB + 5GB MyISAM for logging (INSERT DELAYED)
HW and SW setup
2CPU AMD 2800MHz, 2 cores each, 4 cores in total
64GB RAM 667 MHz
RAID controller with write back cache and BBU
4 HDDs 15K RPM
Ext3 filesystem, LVM
mysql dir (/var/lib/mysql) is a soft link to /data/mysql
Examples of the processlist and innodb status are enclosed
Thanks a lot for any ideas!