mysql disk I/O 100%

Question

MySQL database is showing disk i/o utilization around 100% continuously. A database server is having 24 GB ram.

We tried to optimize queries but nothing worked much.

Please check the current configuration parameters shown below:

• Parameter Current Value
• key_buffer_size 536.88 MB
• query_cache_size 268.43 MB
• tmp_table_size 40368.70 MB
• innodb_buffer_pool_size 12884.90 MB
• innodb_additional_mem_pool_size 8.4MB
• innodb_log_buffer_size 134.22 MB
• max_connections 6000

• sort_buffer_size 8.38

• read_buffer_size 8.38 MB

• read_rnd_buffer_size 8.38 MB
• join_buffer_size 0.26 MB
• thread_stack 0.26 MB
• binlog_cache_size 0.03 MB

Please suggest if you find anything wrong in the current configuration.

Answer 1

Swapping like mad!!

Do not blindly increase tunables. Go back to the defaults except for one:

innodb_buffer_pool_size

Set that to about 70% of available RAM.

Answer 2

Rate Per Second=RPS - Suggestions to consider for your my.cnf (or my.ini) [mysqld] section

query_cache_size=50M  # from 268M to reduce CPU cycles used in management of QC
tmp_table_size=256M  # from 40G for ~ 1% of RAM a reasonable limit
max_heap_table_size=256M  # to match size of tmp_table_size, using 1% of RAM
sort_buffer_size=1M  # from 8M allocated per connection - next 2 are per connect also
read_buffer_size=256K  # from 8M to reduce volume of data retrieved by 95%
read_rnd_buffer_size=256K  # from 8M to reduce volume of data retrieved by 95%

Apply all on 1 stop/start of your instance. Please post comments on positive/negative results after 24 hours of normal workday uptime.