Reputation: 11
Percona Community Edition and MySql Community Edition gives “InnoDB: page_cleaner: 1000ms intended loop took 0ms”?
2017-09-12T19:04:53.549339Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 23911ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)
I get this error on Percona Community Edition and MySql Community Edition. Am wondering if this is an issue on the Percona and MySql Community Edition only ... Will upgrading to the Enterprise Edition of MySQL help resolve this issue?
There are a couple of posts out there with suggestions on lowering the innodb_lru_scan_depth from the default 1024 to 256. That doesn't really resolve this issue. Wonder if anyone has encountered this issue on the Enterprise Edition?
[Updating post to include SHOW VARIABLES like 'innodb%']
sql> show variables like 'innodb%';
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 53687091200 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_deadlock_detect | ON |
| innodb_default_row_format | dynamic |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 15000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_lru_scan_depth | 256 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 400 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 4 |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_thread_concurrency | 16 |
| innodb_thread_sleep_delay | 0 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_native_aio | ON |
| innodb_version | 5.7.18 |
| innodb_write_io_threads | 16 |
+------------------------------------------+------------------------+
Upvotes: 0
Views: 899
Answers (1)
Reputation: 142433
(Not an Answer, but some info and links from changelogs) (Links are relative to dev.mysql.com)
----- 2015-08-03 5.7.8 Release Candidate -- Functionality Added or Changed -- InnoDB -----
The default value for innodb_purge_threads ( /doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_purge_threads ) and innodb_page_cleaners ( /doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners ) was changed from 1 to 4. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners ( /doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners ) is automatically set to the same value as innodb_buffer_pool_instances ( /doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances ).
----- 2014-09-25 5.7.5 Milestone 15 -- Functionality Added or Changed -- InnoDB -----
For optimal shutdown and recovery performance, shutdown and recovery phases are now supported by the multi-threaded page cleaner feature (innodb_page_cleaners ( http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners )) that was introduced in MySQL 5.7.4. (Bug #18805275)
----- 2014-03-31 5.7.4 Milestone 14 -- Functionality Added or Changed -- InnoDB,Performance -----
InnoDB now supports multiple page_cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page_cleaner threads. The default value of 1 maintains the pre-MySQL 5.7.4 configuration in which there is only a single page_cleaner thread. This enhancement builds on work completed in MySQL 5.6.2, which introduced a single page cleaner thread to offload buffer pool flushing work from the InnoDB master thread.
----- 2011-10-03 5.6.3 Milestone 6 -- Functionality Added or Changed -- InnoDB,Performance -----
Work continues to offload flush ( http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_flush ) operations from the InnoDB main thread, doing them in the page_cleaner thread instead. The latest changes to the buffer pool flushing algorithms can improve performance for some I/O-bound workloads, particularly in configurations with multiple buffer pool instances. You control this feature by adjusting the settings for the innodb_lru_scan_depth ( http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth ) and innodb_flush_neighbors ( http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_neighbors ) configuration options. To find the optimal settings, test each combination of the above settings with both the Adaptive Hash Index ( http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_adaptive_hash_index ) and the Doublewrite Buffer ( http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_doublewrite_buffer ) turned on and off. See Tuning InnoDB Buffer Pool Flushing ( http://dev.mysql.com/doc/refman/5.6/en/innodb-lru-background-flushing.html ) for more details.
=======
Recommend reading the article on background-flushing and clicking Affects me on any relevant bugs in bugs.mysql.com, such as
81899 and/or
76661
Also,... innodb_io_capacity = 15000 seems high, even for SSD.
Upvotes: 0
Related Questions
- How to solve mysql warning: "InnoDB: page_cleaner: 1000ms intended loop took XXX ms. The settings might not be optimal "?
- MySQL - MyISAM tables Perforamce issue
- InnoDB: page_cleaner: 1000ms intended loop took xxx ms. The settings might not be optimal
- MySQL: Very slow update/insert/delete queries hanging on "query end" step
- MySQL can take more than an hour to start
- What to do with "InnoDB: page_cleaner: 1000ms intended loop took 0ms"?
- Why is Percona pt-online-schema-change performing so badly?
- Percona 5.6 InnoDB issue not using indexes correctly
- percona mysql 5.5.25a server very slow in replication
- MySQL / InnoDB occasionally some updates run very slow, in 'Updating' status