max_binlog_size - too small?
Hi,
i have a question about max_binlog_size. We have a normal MASTER-SLAVE-Setup on MySQL 5.6 with 500GB of data, 3600 qps average (peaks to 10.000-12.000 qps) and a max_binlog_size of 500MB.
If i check the performance_schema-tables on how much time mysql spends doing something i see that it often waits for the binlog "wait/io/file/sql/binlog":
root@MASTER [performance_schema]> select EVENT_NAME, COUNT_STAR, ps_helper.format_time(SUM_TIMER_WAIT) as SUM_TIMER_WAIT from events_waits_summary_global_by_event_name s ORDER BY s.SUM_TIMER_WAIT DESC limit 10;
+--------------------------------------+----------------+----------------+
| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT |
+--------------------------------------+----------------+----------------+
| wait/io/table/sql/handler | 10607436645580 | 2592.56h |
| idle | 25728560858 | 1199.77h |
| wait/io/file/sql/binlog | 27505749546 | 218.79h |
| wait/io/file/innodb/innodb_data_file | 1639749290 | 39.43h |
| wait/io/file/innodb/innodb_log_file | 2588885123 | 21.69h |
| wait/lock/table/sql/handler | 14399632131 | 18.45h |
| wait/io/file/myisam/kfile | 3204900462 | 15.52h |
| wait/io/file/myisam/dfile | 58690320108 | 13.01h |
| wait/io/file/sql/FRM | 22556370 | 00:15:11.20 |
| wait/io/file/sql/slow_log | 1101538 | 00:01:21.66 |
+--------------------------------------+----------------+----------------+
Does that indicate that the binlog_size is too small (as well as the innodb_log_file_size)? Would it help to use a bigger binlog_size?
The binlog is rotated every 15 minutes during peak times. Could that be performance penalty?
Or would a smaller size be better?
Thanks for your advice!
Kind regards,
Moritz
Subject
Written By
Posted
max_binlog_size - too small?
October 08, 2014 05:21AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.