Q: Why MySQL InnoDB log files creates such big HDD load? I have dedicated to MySQL InnoDB log files (ib_logfile0, ib_logfile0) HDD - sda. And atopsar shows big load to this HDD
atopsar -d 60:

13:02:10  disk           busy read/s KB/read  writ/s KB/writ avque avserv _dsk
13:03:10  sda             59%    4.4     4.0    45.3     6.2   1.0  11.88 ms
13:04:10  sda             60%    4.5     4.0    45.6     6.1   1.0  11.98 ms
13:05:10  sda             58%    4.2     4.0    44.7     6.0   1.0  11.94 ms

dstat -tdD total,sda 60:

----system---- -dsk/total----dsk/sda--
     time     | read  writ: read  writ
24-09 13:11:24|  23k  912k:9689B  391k
24-09 13:12:24|  33k  971k:  16k  270k
24-09 13:13:24|  16k  893k:  14k  235k
24-09 13:14:24|  18k  963k:  16k  254k

pt-ioprofile -cell sizes:

     total      pread       read     pwrite      write      fsync       open      close      lseek      fcntl filename
    905728          0          0     905728          0          0          0          0          0          0 /var/mysqllog/mysql/ib_logfile0

200-400Kb per second does not seems to be much. Specially considering that the only files on HDD are MySQL InnoDB log files and (from the InnoDB blog).:

The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.

The question is why MySQL InnoDB log files creates such big HDD load?
Fragmentation:

filefrag /var/mysqllog/mysql/ib_logfile0
/var/mysqllog/mysql/ib_logfile0: 14 extents found, perfection would be -1 extent
filefrag /var/mysqllog/mysql/ib_logfile1
/var/mysqllog/mysql/ib_logfile1: 17 extents found, perfection would be -1 extent

Does not seems to be much fragmentation for files 1Gb, so it should be almost sequential writes.
I tried to use other HDD (different maker, different size) and situation with load is same. So the reason should not be in the HDD.
Is there a chance to tune mysql to heighten performance?
Could it be that mysql writes to InnoDB logs in several threads and therefore HDD's head have to change position each time? If yes is it possible to force mysqld to write to InnoDB log files in 1 thread or at least not simultaneous?

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         | 3221225472             |
| 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          | 33554432               |
| innodb_log_file_size            | 1073741824             |
| 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            | 1                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 16                     |
| 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         | 16                     |
+---------------------------------+------------------------+

InnoDB status:

+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| Innodb_buffer_pool_pages_data         | 189538       |
| Innodb_buffer_pool_bytes_data         | 3105390592   |
| Innodb_buffer_pool_pages_dirty        | 0            |
| Innodb_buffer_pool_bytes_dirty        | 0            |
| Innodb_buffer_pool_pages_flushed      | 15444830     |
| Innodb_buffer_pool_pages_free         | 3            |
| Innodb_buffer_pool_pages_misc         | 7066         |
| Innodb_buffer_pool_pages_total        | 196607       |
| Innodb_buffer_pool_read_ahead_rnd     | 0            |
| Innodb_buffer_pool_read_ahead         | 44261        |
| Innodb_buffer_pool_read_ahead_evicted | 2063         |
| Innodb_buffer_pool_read_requests      | 7430686238   |
| Innodb_buffer_pool_reads              | 144665       |
| Innodb_buffer_pool_wait_free          | 0            |
| Innodb_buffer_pool_write_requests     | 276646158    |
| Innodb_data_fsyncs                    | 9659307      |
| Innodb_data_pending_fsyncs            | 0            |
| Innodb_data_pending_reads             | 0            |
| Innodb_data_pending_writes            | 0            |
| Innodb_data_read                      | 3131691008   |
| Innodb_data_reads                     | 191406       |
| Innodb_data_writes                    | 23908920     |
| Innodb_data_written                   | 523765341184 |
| Innodb_dblwr_pages_written            | 15444830     |
| Innodb_dblwr_writes                   | 209711       |
| Innodb_have_atomic_builtins           | ON           |
| Innodb_log_waits                      | 0            |
| Innodb_log_write_requests             | 29583143     |
| Innodb_log_writes                     | 8042225      |
| Innodb_os_log_fsyncs                  | 8136493      |
| Innodb_os_log_pending_fsyncs          | 0            |
| Innodb_os_log_pending_writes          | 0            |
| Innodb_os_log_written                 | 17633602560  |
| Innodb_page_size                      | 16384        |
| Innodb_pages_created                  | 144810       |
| Innodb_pages_read                     | 191009       |
| Innodb_pages_written                  | 15444830     |
| Innodb_row_lock_current_waits         | 0            |
| Innodb_row_lock_time                  | 3857         |
| Innodb_row_lock_time_avg              | 0            |
| Innodb_row_lock_time_max              | 218          |
| Innodb_row_lock_waits                 | 8922         |
| Innodb_rows_deleted                   | 1964207      |
| Innodb_rows_inserted                  | 7013442      |
| Innodb_rows_read                      | 16861336570  |
| Innodb_rows_updated                   | 17193829     |
| Innodb_truncated_status_writes        | 0            |
+---------------------------------------+--------------+


A: You're using innodb_flush_log_at_trx_commit=1 which means every transaction is written to the disk. In that case every commit will result in a fsync which (unless you have battery backed raid controller) goes to the disk no matter what. It is specifically there to prevent data loss in case of a system failure (HDD cache is volatile to power loss).
You didn't mention disk setup or filesystem. A single spinning disk cannot do more than 150-200 IOPS (high end server disks) while a normal user grade HDD is around 60-80 IOPS. So 45 is not totally off. http://en.wikipedia.org/wiki/IOPS#Examples
You can try different things.
a) Setup a raid which will multiply you're IOPS capacity. Striping type RAID setups help the most.
b) If you can live with possibly loosing a seconds of data then you can change innodb_flush_log_at_trx_commit to 2 which will only flush to disks once every second. Significant write performance improvement.
c) If you can group writes in your application to a single transaction that will result in less fsync / second.
d) You can also upgrade your setup by getting SSD or some battery backed up cache raid card. But both of them are quite expensive solution.

A: innodb_log_buffer_size would play a role here as well. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. However the current value of the buffer seems to be fine. But, you can play with it and see.
