Bin logs with high i/o

Question

I'm DBA (IBM Informix) and already use MySQL with Drupal , where have low workload. So, my knowledge of MySQL is minimal.

Few days ago I start use MySQL with a basic configuration as back-end for a Zabbix server and start to get into a issue what is bugging me.

After add close of 10 hosts to be monitored by Zabbix , the machine (openssue 12.2) where run Zabbix Server (2.0) and MySql (5.5) become with constant "high" write I/O , what I deduce is because of the bin-logs

Bellow I copy a dstat command output (column, dsk/total - writ). Have a constantly write , close of 1 MB/seg and I working only with 10 host. I have at least more 40 to add... with this I/O I will get a lot of overhead.

   

     ----system---- ----total-cpu-usage---- ------memory-usage----- -net/total- -dsk/total- ---load-avg---
         time     |usr sys idl wai hiq siq| used  buff  cach  free| recv  send| read  writ| 1m   5m  15m
    12-12 15:00:01|  1   0  99   0   0   0| 501M  340k 1428M 76.8M|4207B 7166B|3277B 1190k|0.01 0.10 0.11
    12-12 15:00:31|  1   0  96   2   0   0| 489M  340k 1413M  104M|  99k   33k| 294k 1195k|0.01 0.09 0.10
    12-12 15:01:01|  1   0  98   1   0   0| 478M  340k 1413M  115M|5306B   46k|3140B 1537k|0.11 0.11 0.11
    12-12 15:01:31|  1   0  98   1   0   0| 477M  340k 1415M  114M|4783B   36k|6144B  871k|0.28 0.16 0.13
    12-12 15:02:01|  1   0  99   0   0   0| 508M  340k 1417M 82.0M|4674B   19k|  48k  675k|0.17 0.15 0.13
    12-12 15:02:31|  1   0  99   0   0   0| 481M  340k 1417M  108M|4594B 7006B|1775B  802k|0.10 0.13 0.12
    12-12 15:03:01|  1   0  99   0   0   0| 495M  340k 1419M 92.3M|4518B 6740B|5734B  701k|0.06 0.12 0.12
    12-12 15:03:31|  1   0  99   0   0   0| 485M  340k 1420M  102M|4122B 6408B| 410B  762k|0.04 0.11 0.12
    12-12 15:04:01|  1   0  98   1   0   0| 490M  340k 1421M 94.8M|4286B 6736B|9011B  810k|0.18 0.13 0.13
    12-12 15:04:31|  1   0  99   0   0   0| 483M  340k 1421M  102M|4575B 6896B| 273B  630k|0.11 0.12 0.12
    12-12 15:05:01|  1   0  99   0   0   0| 484M  340k 1421M  101M|5028B 7435B| 683B  701k|0.07 0.11 0.12
    12-12 15:05:31|  1   0  99   0   0   0| 481M  340k 1422M  103M|6781B 9190B|  21k  434k|0.04 0.10 0.12
    12-12 15:06:01|  1   0  98   1   0   0| 485M  340k 1424M 97.9M|4204B 6668B|9830B  693k|0.18 0.13 0.13
    12-12 15:06:31|  1   0  98   1   0   0| 482M  340k 1425M 99.0M|4480B 6892B|  23k  718k|0.11 0.12 0.13
    12-12 15:07:01|  1   0  99   0   0   0| 509M  340k 1429M 67.9M|4608B 7056B|  16k  694k|0.07 0.11 0.13
    12-12 15:07:31|  1   0  98   1   0   0| 490M  340k 1420M 95.8M|3978B 6396B|  28k  776k|0.04 0.10 0.12
    12-12 15:08:01|  1   0  99   0   0   0| 488M  340k 1420M 97.4M|4287B 6654B|1638B  618k|0.02 0.09 0.12
    12-12 15:08:31|  1   0  99   0   0   0| 486M  340k 1421M  100M|4514B 6966B| 410B  614k|0.02 0.08 0.12
    12-12 15:09:01|  1   0  99   0   0   0| 487M  340k 1421M 98.7M|4263B 6763B| 410B  643k|0.01 0.07 0.11
    12-12 15:09:31|  1   0  99   1   0   0| 484M  340k 1422M  101M|4167B 6409B|4506B  736k|0.01 0.07 0.11

This should be because of bin logs ? My desire is enable some buffer/cache to get flush less constant with more MB per seg.

root@vm04:/etc# egrep -v "^#|^ *$" my.cnf
[client]
port            = 3306
socket          = /var/run/mysql/mysql.sock
[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
datadir                 = /vu/mysql/dados
skip-external-locking
key_buffer_size         = 256M
max_allowed_packet      = 1M
table_open_cache        = 256
sort_buffer_size        = 1M
net_buffer_length       = 8K
read_buffer_size        = 1M
read_rnd_buffer_size    = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
log-bin=mysql-bin
binlog_format=mixed
max_binlog_size=10485760
expire_logs_days=3
innodb_flush_log_at_trx_commit=0
server-id       = 1
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 2M
[safe_mysqld]
log-error       = /var/log/mysql/mysqld.log
socket          = /var/run/mysql/mysql.sock
[mysqldump]
socket          = /var/run/mysql/mysql.sock
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size     = 128M
sort_buffer_size    = 128M
read_buffer         = 2M
write_buffer        = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_multi]
mysqld              = /usr/bin/mysqld_safe
mysqladmin          = /usr/bin/mysqladmin
log                 = /var/log/mysqld_multi.log

Answer 1

There are two suggestions I have for you

SUGGESTION #1

Your binary logs are being written to /vu/mysql/dados, your datadir.

If you have a folder on a separate disk, perhaps you should map your binary logs there. That will keep data and logging on separate disks.

[mysqld]
log-bin=/someothermountedfolder/mysql-bin

SUGGESTION #2

You are using MySQL 5.5 but I do not see any options to make InnoDB access multiple cores.

Please add the following to my.cnf to give InnoDB some Enhanced I/O Performance

[mysqld]
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=2000

Give it a Try !!!

Answer 2

First, to address your question regarding binlog. If you are not setting up replication, or using the binary logs for point in time recovery, then you can disable binary logging to ease up on the IO.

However, from looking at your configuration, my first suggestion would be to increase the innodb_log_file_size variable from the default of 5MB (see how to do that here) to something like 64MB for starters. This will delay MySQL flushing dirty pages to the tablespace, the process which is described better in the documentation.