74.怎样合理的设置redo日志的大小

怎样合理的设置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"