I've just finished reading this old Q&A which had some really good detailed information on a similar setup to ours, though unfortunately our problem (now) is not with replication.
We have a new master database server (Server version: 5.5.27-log MySQL Community Server) that is on a bare metal server with the following specs:
- 2 x Intel E5-2620-v2
- 64Gb Memory
- 2 x Fusion-io 600Gb card (Raid 1) for MySql
- 1 x SSD for Centos 6.5
- 1Gbps Network
HyperThreading has not been disabled on the server as there is mixed opinions about whether this helps on large memory systems, but we're not against trying it.
We currently replicate to 3 slaves that are virtualised on a SSD cluster. We were replicating to 4 but this seemed too much for the SSD cluster and we had periods of lag.
All tables are InnoDB and master DB and slave Writes are between 3.5K - 2.5K qps, whilst Reads on the master are about 7.5k - 10k qps.
The settings for the master DB are as follows:
long-query-time=10 slow-query-log max_connections=500 max_tmp_tables=1024 key_buffer = 1024M max_allowed_packet = 32M net_read_timeout=180 net_write_timeout=180 table_cache = 512 thread_cache = 32 thread_concurrency = 4 query_cache_type = 0 query_cache_size = 0M innodb_file_per_table innodb_file_format=barracuda innodb_buffer_pool_size=49152M innodb_buffer_pool_instances=2 innodb_read_io_threads=16 innodb_write_io_threads=16 innodb_io_capacity = 500 innodb_additional_mem_pool_size=20M innodb_log_file_size=1024M innodb_log_files_in_group = 2 innodb_doublewrite=0 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT
Our problem is with CPU load, especially Sys Cpu. As you can see from mpstat we have 0% iowait and very high %sys.
Linux 2.6.32-431.29.2.el6.x86_64 13/11/14 _x86_64_ (24 CPU) 13:57:18 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 13:57:19 all 23.35 0.00 74.65 0.00 0.00 0.88 0.00 0.00 1.13 13:57:20 all 21.95 0.00 75.50 0.00 0.00 0.96 0.00 0.00 1.59 13:57:21 all 23.74 0.00 72.63 0.00 0.00 1.00 0.00 0.00 2.63 13:57:22 all 23.88 0.00 71.64 0.00 0.00 1.17 0.00 0.00 3.31 13:57:23 all 23.26 0.00 73.89 0.00 0.00 0.92 0.00 0.00 1.92 13:57:24 all 22.87 0.00 74.87 0.00 0.00 1.00 0.00 0.00 1.25 13:57:25 all 23.41 0.00 74.51 0.00 0.00 0.96 0.00 0.00 1.12
Previously the master database was running on a virtualised server (same SSD cluster as the slaves). It had a host to itself within the vSphere cluster which had:
- 2 x Intel X5570
- 32Gb of memory
- SSD shared from cluster
There was never any problem before, the server ran without fault for many years, albeit with lower SQL throughput.
Queries are all simple and indexes have been optimized for inserts & updates as complex client queries are carried out on the slaves. There are no deletes, only inserts & updates. Most tables (all big ones) have primary keys.
The CPU usage seems to grow once the memory buffer is full, and MySql is the only application being run on the server.
Connections range from about 200-400 with around 100-200 of those running. Innodb Buffer Pool Hit Ratio is 100%. There's no swap memory ever being created so I cannot see this being the issue:
We have a ton of history with New Relic but unfortunately I cannot paste in screenshots here.
I've gone through countless blogs and Q&As like this but cannot find any cause nor solution so am putting it out there.. Any ideas?
It seems I can now post screenshots. These two captures from New relic show the system load and query load on the server over a 6 hour window with a restart of mysql in the middle.