怎样合理的设置redo日志？
可以通过sys.metrics视图进行监控，首先要开启要开启要监控的模块
root@mysqldb 22:58: [(none)]> show variables like '%monitor%'; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | innodb_monitor_disable | | | innodb_monitor_enable | module_log | | innodb_monitor_reset | | | innodb_monitor_reset_all | | +--------------------------+------------+
这里可以通过set global innodb_monitor_enable='module_log'开启日志模块监控。这里看到module_log的全部开了
| log_next_file | 0 | InnoDB Metrics - log | YES | | log_on_buffer_space_no_waits | 0 | InnoDB Metrics - log | YES | | log_on_buffer_space_wait_loops | 0 | InnoDB Metrics - log | YES | | log_on_buffer_space_waits | 0 | InnoDB Metrics - log | YES | | log_on_file_space_no_waits | 0 | InnoDB Metrics - log | YES | | log_on_file_space_wait_loops | 0 | InnoDB Metrics - log | YES | | log_on_file_space_waits | 0 | InnoDB Metrics - log | YES | | log_on_flush_no_waits | 0 | InnoDB Metrics - log | YES | | log_on_flush_wait_loops | 0 | InnoDB Metrics - log | YES | | log_on_flush_waits | 0 | InnoDB Metrics - log | YES | | log_on_recent_closed_wait_loops | 0 | InnoDB Metrics - log | YES | | log_on_recent_written_wait_loops | 0 | InnoDB Metrics - log | YES | | log_on_write_no_waits | 0 | InnoDB Metrics - log | YES | | log_on_write_wait_loops | 0 | InnoDB Metrics - log | YES | | log_on_write_waits | 0 | InnoDB Metrics - log | YES |
这里关于redo日志生成的变化参数主要是这两个：
root@mysqldb 22:58: [sys]> select * from metrics where variable_name in ('log_lsn_current','log_lsn_last_checkpoint'); +-------------------------+----------------+----------------------+---------+ | Variable_name | Variable_value | Type | Enabled | +-------------------------+----------------+----------------------+---------+ | log_lsn_current | 20638133 | InnoDB Metrics - log | YES | | log_lsn_last_checkpoint | 20638133 | InnoDB Metrics - log | YES | +-------------------------+----------------+----------------------+---------+ 2 rows in set (0.01 sec)
used log = log_lsn_current - log_lsn_last_checkpoint
Total log = innodb_log_file_size * innodb_log_files_in_group
Used % = (used log / Total log) * 100 --> 这个就是redo日志目前使用率
root@mysqldb 23:14: [information_schema]> show variables where variable_name in('innodb_log_file_size','innodb_log_files_in_group'); +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | innodb_log_file_size | 1073741824 | | innodb_log_files_in_group | 3 | +---------------------------+------------+ 2 rows in set (0.00 sec)
从上面看出redo file大小是3G，然后 use log = 0(因为测试数据无压力，没有跑什么数据)
怎样查看每分钟 redolog 的增长？
show engine innodb status\G sleep(60); show engine innodb status\G; Log sequence number 20638133 Log sequence number 20638137
这里计算出每分钟产生redo日志量后，与一个redo日志文件大小(innodb_log_file_size)进行对比，即可得出多久会写满一个redo日志，从而评估出每个redo日志文件的大小，以及设置几个日志组比较合适。
监控参数配置：
#innodb monitor settings innodb_monitor_enable = "module_innodb" innodb_monitor_enable = "module_server" innodb_monitor_enable = "module_dml" innodb_monitor_enable = "module_ddl" innodb_monitor_enable = "module_trx" innodb_monitor_enable = "module_os" innodb_monitor_enable = "module_purge" innodb_monitor_enable = "module_log" innodb_monitor_enable = "module_lock" innodb_monitor_enable = "module_buffer" innodb_monitor_enable = "module_index" innodb_monitor_enable = "module_ibuf_system" innodb_monitor_enable = "module_buffer_page" #innodb_monitor_enable = "module_adaptive_hash"