服务器版本:5.6.23-72.1-log Percona
我一直在试图找出为什么我们的 mysql 服务器是尖锐的检查点,它似乎与 ib_logfile 的轮换有关。
我继承了这个 Percona 5.6 mysql 服务器,它每天暂停一次尖锐检查点/完全脏刷新,innodb_log_file_size 为 1GB。我认为可能是日志文件大小不足,并将其增加到 25GB。现在我们只是减少了相同的行为。在我能够验证它发生在日志文件切换时,这对我来说是一种谜。
Mysql 5.5 docs: https://dev.mysql.com/doc/refman/5.5/en/innodb-checkpoints.html : "...当 InnoDB 开始重用日志文件时,它必须确保数据库页面“
Mysql 5.6 文档省略了这一点,并说它应该做的只是在正常情况下进行模糊检查点。https://dev.mysql.com/doc/refman/5.6/en/innodb-checkpoints.html:“InnoDB 实现了一种称为模糊检查点的检查点机制。InnoDB 小批量从缓冲池中刷新修改的数据库页面。有无需一次性刷新缓冲池,这会在检查点过程中中断用户 SQL 语句的处理。”
如果这种行为是意外的,有什么方法可以最小化或摆脱这种行为?
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_buffer_pool_size | 46170898432 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | inserts |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_doublewrite | ON |
| innodb_empty_free_list_algorithm | backoff |
| innodb_file_format | Barracuda |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 0 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponential_backoff |
| innodb_io_capacity | 3000 |
| innodb_io_capacity_max | 6000 |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 26843545600 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_lru_scan_depth | 2048 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 50 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 200 |
| innodb_max_purge_lag_delay | 0 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 384 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sched_priority_cleaner | 19 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.23-72.1 |
| innodb_write_io_threads | 8 |