1

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.

1

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.

  • 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 Oct 24 '15 at 20:27
  • 1
    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. – symcbean Oct 24 '15 at 22:11
  • Give a try to option n.2, tuning innodb transaction flushes, and report back if it helps. – shodanshok Oct 24 '15 at 22:32
  • 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 ? – Welite Oct 25 '15 at 8:58
0

Two things to consider:

1) Do you have appropriate indexes for your queries?

2) Can you add add RAM to your server?

By addressing #1, you will significantly reduce the number of full table scans require to fulfill your queries. You'll need to spend a good amount of time, though, understanding what type of queries are causing the most IO load.

By addressing #2, you will allow more of your database to be cached in RAM, which will simultaneously speed up queries as well as reduce IO.

  • How exactly or where to add more RAM ? buffer_size or key_size or which value ? – Welite Oct 24 '15 at 20:28
  • 1
    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. – EEAA Oct 24 '15 at 20:31
0

Your my.cnf includes 4 lines that should be removed, they are

read_buffer_size
read_rnd_buffer_size
join_buffer_size
thread_stack

these are all per connection RAM requirements and are driving your RAM footprint much higher than necessary. Let the DEFAULTS work for you to improve response time with room to breathe in RAM.

For more detailed analysis, add to OriginalPost the following,

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;

for up to five specific cfg recommendations, one per day to be applied, monitor.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.