Mysql – Too much IO to MySQL InnoDB log files

innodbMySQLmysql-5.5transaction-log

I am using MySQL version: 5.5.38

I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option.

atopsar -d 30 shows big load (>50%) to this HDD

The thing is that I have only 75 queries per second that does not seems to be much.

What can be done to increase performance?

sudo /etc/init.d/mysql status
Threads: 23
Questions: 11860661
Slow queries: 1
Opens: 2426
Flush tables: 1
Open tables: 835
Queries per second avg: 74.015

InnoDB variables:

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 1073741824             |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 268435456              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | /var/mysqllog/mysql    |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_print_all_deadlocks      | OFF                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 5.5.38                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+

InnoDB status:

+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_pages_data         | 24489      |
| Innodb_buffer_pool_bytes_data         | 401227776  |
| Innodb_buffer_pool_pages_dirty        | 930        |
| Innodb_buffer_pool_bytes_dirty        | 15237120   |
| Innodb_buffer_pool_pages_flushed      | 60571      |
| Innodb_buffer_pool_pages_free         | 40568      |
| Innodb_buffer_pool_pages_misc         | 479        |
| Innodb_buffer_pool_pages_total        | 65536      |
| Innodb_buffer_pool_read_ahead_rnd     | 0          |
| Innodb_buffer_pool_read_ahead         | 3744       |
| Innodb_buffer_pool_read_ahead_evicted | 0          |
| Innodb_buffer_pool_read_requests      | 16329316   |
| Innodb_buffer_pool_reads              | 19402      |
| Innodb_buffer_pool_wait_free          | 0          |
| Innodb_buffer_pool_write_requests     | 1167670    |
| Innodb_data_fsyncs                    | 41711      |
| Innodb_data_pending_fsyncs            | 2          |
| Innodb_data_pending_reads             | 0          |
| Innodb_data_pending_writes            | 0          |
| Innodb_data_read                      | 388255744  |
| Innodb_data_reads                     | 23960      |
| Innodb_data_writes                    | 98312      |
| Innodb_data_written                   | 2067411968 |
| Innodb_dblwr_pages_written            | 60699      |
| Innodb_dblwr_writes                   | 936        |
| Innodb_have_atomic_builtins           | ON         |
| Innodb_log_waits                      | 0          |
| Innodb_log_write_requests             | 133740     |
| Innodb_log_writes                     | 35770      |
| Innodb_os_log_fsyncs                  | 36171      |
| Innodb_os_log_pending_fsyncs          | 1          |
| Innodb_os_log_pending_writes          | 0          |
| Innodb_os_log_written                 | 78242816   |
| Innodb_page_size                      | 16384      |
| Innodb_pages_created                  | 926        |
| Innodb_pages_read                     | 23563      |
| Innodb_pages_written                  | 60699      |
| Innodb_row_lock_current_waits         | 0          |
| Innodb_row_lock_time                  | 30         |
| Innodb_row_lock_time_avg              | 0          |
| Innodb_row_lock_time_max              | 16         |
| Innodb_row_lock_waits                 | 65         |
| Innodb_rows_deleted                   | 9862       |
| Innodb_rows_inserted                  | 33925      |
| Innodb_rows_read                      | 45198746   |
| Innodb_rows_updated                   | 64550      |
| Innodb_truncated_status_writes        | 0          |
+---------------------------------------+------------+

Best Answer

Based on MySQL Documentation and other options

[mysqld]
innodb_log_file_size    = 1G
innodb_log_buffer_size  = 256M
innodb_read_io_threads  = 16
innodb_write_io_threads = 16
innodb_purge_threads    = 1
  • Make your log files 1G: Small log files cause many unnecessary disk writes. The documentation says make the log file the same size as the buffer pool.
  • Make the log buffer 256M: The larger the log buffer, the less frequently flush logs are. You have it at 8M, which is the default.
  • Increase read and write I/O threads
  • make the purge thread independent of the master thread.
Related Question