3

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          |
+---------------------------------------+------------+
1

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.
  • Did not get any noticeable improvements setting all those parameters. – Index Sep 16 '14 at 10:12
1

With regards to writes, you might want to change innodb_flush_log_at_trx_commit to either 0 or 2, see http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

There are other variables affecting performance, most notably innodb_buffer_pool_size that might be too low in your case thus IO reads might be more common than needed.

I found this blog post useful: http://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/

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.