MySQL and high jdb2 io/wait

Question

I am running MySQL 5.5 with InnoDB tables. There are about 200 queries per second. There are also tables with 500 000 or more rows. But I have big issues with server load and io/wait especially with the jdb2.

jdb2/md2-8 is taking 99% IO/wait see iotop output image: Iotop output

Box Specifications: Xeon 1246 v3, 32 GB RAM, 2x 240 Intel SSD RAID 1

I dont know if I have something wrong in my config or it is problem related to RAID. Any tips ?

My mysql my.cfg:

innodb_file_per_table   = 1
join_buffer_size    = 1M
open_files_limit    = 10000
myisam_use_mmap     = 1
query_cache_type    = 1
table_open_cache    = 2000
concurrent_insert   = 2
max_connections     = 3000

query_cache_size    = 16M
key_buffer_size     = 16M
read_buffer_size        = 8M
query_cache_limit   = 4M
query_cache_min_res_unit = 1K
tmp_table_size      = 64M
thread_cache_size   = 1500

sort_buffer_size    = 2M
max_heap_table_size     = 64M
innodb_buffer_pool_size = 5000M
read_rnd_buffer_size    = 128M
thread_concurrency      = 8
thread_stack        = 1M
innodb_log_buffer_size  = 2M

Thanks.

Solution

jbd2 is a kernel process used to synchronize filesystem journal to disk. This means that your MySQL setup is write-bound at the moment.

Such as high jdb2 load for such a low query count (200 per seconds) is quite strange, especially with fast SSD. Are you using a cacheless RAID card? It may disable your SSD internal caches, giving abysmal performance. If so, you can try to:

1. re-enable your disk's private cache
2. use the my.cnf option innodb_flush_log_at_trx_commit=0
3. use a BBU-enabled RAID card with 512+ MB of protected DRAM cache

Please note that option n.1 and n.2 have a small, but nonzero, risks to lose some transaction in case of power loss. By far, the safest option is the third one - to buy a proper RAID card.

Comments

[Welite]

Well, it is a dedicated server in datacenter so I have no access to hardware. But I have other machines similar as this one but only on this machine with mysql I got these problems.

[Welite]

How exactly or where to add more RAM ? buffer_size or key_size or which value ?

[EEAA]

You physically open the server and insert additional RAM sticks. If this is a server managed by a provider, the. Request that they do the same.

[symcbean]

If this is a dedicated server why have you allocated less than5 of its 32Gb to the innodb buffer pool???? That should probably be at least 15, maybe 20. Go get a copy of mysqltuner.pl and run it on your server. You can get a lot more performance out of this system without adding hardware.

[shodanshok]

Give a try to option n.2, tuning innodb transaction flushes, and report back if it helps.

[Welite]

Yesterday I found out that maybe the problem is caused by defective SSD because when I try to access certain files it completely hangs and it takes up to 20 seconds to delete one single file. But not all files are acting like that so it may be corrupted part of the SSD ?