page_cleaner issues after upgrading to Percona XtraDB 57 from 56

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi There,
After upgrading from 56 to 57 I’m facing a lot of issues with page_cleaner, for example:
InnoDB: page_cleaner: 1000ms intended loop took 6834ms. The settings might not be optimal. (flushed=201, during the time.)
which after some time causes flow control, and this in turn crashes my application.

I remembered that there was a number of new options for controlling page_cleaners introduced in PXCD 56 ([url]https://www.percona.com/doc/percona-server/5.6/performance/xtradb_performance_improvements_for_io-bound_highly-concurrent_workloads.html#xtradb-performance-improvements-for-io-bound-highly-concurrent-workloads[/url]), to my surprise those options are gone from PXCD 57.

Could you assist me with finding those or a way how i can modify some options ?

2

Hi,

What is your current my.cnf ? This might indicate your innodb_io_capacity might be too high. Also what kind of storage do you have ? The fact system could not flush 200 pages in 7 seconds points to either very slow or overloaded storage. Even single spinning disk should be able to do more than 100 pages per second

3

HI Peter,

Thank for your reply. You are right our backend storage is not the fastest unfortunately and very often is under a lot of pressure.
Please have a look below at innodb variables:
*************************** 1. row ***************************
Variable_name: innodb_adaptive_flushing
Value: ON
*************************** 2. row ***************************
Variable_name: innodb_adaptive_flushing_lwm
Value: 10
*************************** 3. row ***************************
Variable_name: innodb_adaptive_hash_index
Value: ON
*************************** 4. row ***************************
Variable_name: innodb_adaptive_hash_index_parts
Value: 8
*************************** 5. row ***************************
Variable_name: innodb_adaptive_max_sleep_delay
Value: 150000
*************************** 6. row ***************************
Variable_name: innodb_api_bk_commit_interval
Value: 5
*************************** 7. row ***************************
Variable_name: innodb_api_disable_rowlock
Value: OFF
*************************** 8. row ***************************
Variable_name: innodb_api_enable_binlog
Value: OFF
*************************** 9. row ***************************
Variable_name: innodb_api_enable_mdl
Value: OFF
*************************** 10. row ***************************
Variable_name: innodb_api_trx_level
Value: 0
*************************** 11. row ***************************
Variable_name: innodb_autoextend_increment
Value: 64
*************************** 12. row ***************************
Variable_name: innodb_autoinc_lock_mode
Value: 2
*************************** 13. row ***************************
Variable_name: innodb_buffer_pool_chunk_size
Value: 134217728
*************************** 14. row ***************************
Variable_name: innodb_buffer_pool_dump_at_shutdown
Value: ON
*************************** 15. row ***************************
Variable_name: innodb_buffer_pool_dump_now
Value: OFF
*************************** 16. row ***************************
Variable_name: innodb_buffer_pool_dump_pct
Value: 25
*************************** 17. row ***************************
Variable_name: innodb_buffer_pool_filename
Value: ib_buffer_pool
*************************** 18. row ***************************
Variable_name: innodb_buffer_pool_instances
Value: 4
*************************** 19. row ***************************
Variable_name: innodb_buffer_pool_load_abort
Value: OFF
*************************** 20. row ***************************
Variable_name: innodb_buffer_pool_load_at_startup
Value: ON
*************************** 21. row ***************************
Variable_name: innodb_buffer_pool_load_now
Value: OFF
*************************** 22. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 11811160064
*************************** 23. row ***************************
Variable_name: innodb_change_buffer_max_size
Value: 25
*************************** 24. row ***************************
Variable_name: innodb_change_buffering
Value: all
*************************** 25. row ***************************
Variable_name: innodb_checksum_algorithm
Value: crc32
*************************** 26. row ***************************
Variable_name: innodb_checksums
Value: ON
*************************** 27. row ***************************
Variable_name: innodb_cleaner_lsn_age_factor
Value: high_checkpoint
*************************** 28. row ***************************
Variable_name: innodb_cmp_per_index_enabled
Value: OFF
*************************** 29. row ***************************
Variable_name: innodb_commit_concurrency
Value: 0
*************************** 30. row ***************************
Variable_name: innodb_compression_failure_threshold_pct
Value: 5
*************************** 31. row ***************************
Variable_name: innodb_compression_level
Value: 6
*************************** 32. row ***************************
Variable_name: innodb_compression_pad_pct_max
Value: 50
*************************** 33. row ***************************
Variable_name: innodb_concurrency_tickets
Value: 5000
*************************** 34. row ***************************
Variable_name: innodb_corrupt_table_action
Value: assert
*************************** 35. row ***************************
Variable_name: innodb_data_file_path
Value: ibdata1:2000M;ibdata2:10M:autoextend
*************************** 36. row ***************************
Variable_name: innodb_data_home_dir
Value: /var/lib/mysql
*************************** 37. row ***************************
Variable_name: innodb_default_row_format
Value: dynamic
*************************** 38. row ***************************
Variable_name: innodb_disable_sort_file_cache
Value: OFF
*************************** 39. row ***************************
Variable_name: innodb_disallow_writes
Value: OFF
*************************** 40. row ***************************
Variable_name: innodb_doublewrite
Value: ON
*************************** 41. row ***************************
Variable_name: innodb_empty_free_list_algorithm
Value: backoff
*************************** 42. row ***************************
Variable_name: innodb_fast_shutdown
Value: 1
*************************** 43. row ***************************
Variable_name: innodb_file_format
Value: Barracuda
*************************** 44. row ***************************
Variable_name: innodb_file_format_check
Value: ON
*************************** 45. row ***************************
Variable_name: innodb_file_format_max
Value: Barracuda
*************************** 46. row ***************************
Variable_name: innodb_file_per_table
Value: ON
*************************** 47. row ***************************
Variable_name: innodb_fill_factor
Value: 100
*************************** 48. row ***************************
Variable_name: innodb_flush_log_at_timeout
Value: 1
*************************** 49. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 0
*************************** 50. row ***************************
Variable_name: innodb_flush_method
Value:
*************************** 51. row ***************************
Variable_name: innodb_flush_neighbors
Value: 1
*************************** 52. row ***************************
Variable_name: innodb_flush_sync
Value: ON
*************************** 53. row ***************************
Variable_name: innodb_flushing_avg_loops
Value: 30
*************************** 54. row ***************************
Variable_name: innodb_force_load_corrupted
Value: OFF
*************************** 55. row ***************************
Variable_name: innodb_force_recovery
Value: 0
*************************** 56. row ***************************
Variable_name: innodb_ft_aux_table
Value:
*************************** 57. row ***************************
Variable_name: innodb_ft_cache_size
Value: 8000000
*************************** 58. row ***************************
Variable_name: innodb_ft_enable_diag_print
Value: OFF
*************************** 59. row ***************************
Variable_name: innodb_ft_enable_stopword
Value: ON
*************************** 60. row ***************************
Variable_name: innodb_ft_max_token_size
Value: 84
*************************** 61. row ***************************
Variable_name: innodb_ft_min_token_size
Value: 3
*************************** 62. row ***************************
Variable_name: innodb_ft_num_word_optimize
Value: 2000
*************************** 63. row ***************************
Variable_name: innodb_ft_result_cache_limit
Value: 2000000000
*************************** 64. row ***************************
Variable_name: innodb_ft_server_stopword_table
Value:
*************************** 65. row ***************************
Variable_name: innodb_ft_sort_pll_degree
Value: 2
*************************** 66. row ***************************
Variable_name: innodb_ft_total_cache_size
Value: 640000000
*************************** 67. row ***************************
Variable_name: innodb_ft_user_stopword_table
Value:
*************************** 68. row ***************************
Variable_name: innodb_io_capacity
Value: 200
*************************** 69. row ***************************
Variable_name: innodb_io_capacity_max
Value: 2000

4

and 2nd part:
*************************** 70. row ***************************
Variable_name: innodb_kill_idle_transaction
Value: 0
*************************** 71. row ***************************
Variable_name: innodb_large_prefix
Value: ON
*************************** 72. row ***************************
Variable_name: innodb_lock_wait_timeout
Value: 90
*************************** 73. row ***************************
Variable_name: innodb_locks_unsafe_for_binlog
Value: ON
*************************** 74. row ***************************
Variable_name: innodb_log_buffer_size
Value: 33554432
*************************** 75. row ***************************
Variable_name: innodb_log_checksums
Value: ON
*************************** 76. row ***************************
Variable_name: innodb_log_compressed_pages
Value: ON
*************************** 77. row ***************************
Variable_name: innodb_log_file_size
Value: 2831155200
*************************** 78. row ***************************
Variable_name: innodb_log_files_in_group
Value: 2
*************************** 79. row ***************************
Variable_name: innodb_log_group_home_dir
Value: ./
*************************** 80. row ***************************
Variable_name: innodb_log_write_ahead_size
Value: 8192
*************************** 81. row ***************************
Variable_name: innodb_lru_scan_depth
Value: 1024
*************************** 82. row ***************************
Variable_name: innodb_max_bitmap_file_size
Value: 104857600
*************************** 83. row ***************************
Variable_name: innodb_max_changed_pages
Value: 1000000
*************************** 84. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 75.000000
*************************** 85. row ***************************
Variable_name: innodb_max_dirty_pages_pct_lwm
Value: 0.000000
*************************** 86. row ***************************
Variable_name: innodb_max_purge_lag
Value: 0
*************************** 87. row ***************************
Variable_name: innodb_max_purge_lag_delay
Value: 0
*************************** 88. row ***************************
Variable_name: innodb_max_undo_log_size
Value: 1073741824
*************************** 89. row ***************************
Variable_name: innodb_monitor_disable
Value:
*************************** 90. row ***************************
Variable_name: innodb_monitor_enable
Value:
*************************** 91. row ***************************
Variable_name: innodb_monitor_reset
Value:
*************************** 92. row ***************************
Variable_name: innodb_monitor_reset_all
Value:
*************************** 93. row ***************************
Variable_name: innodb_numa_interleave
Value: OFF
*************************** 94. row ***************************
Variable_name: innodb_old_blocks_pct
Value: 37
*************************** 95. row ***************************
Variable_name: innodb_old_blocks_time
Value: 1000
*************************** 96. row ***************************
Variable_name: innodb_online_alter_log_max_size
Value: 134217728
*************************** 97. row ***************************
Variable_name: innodb_open_files
Value: 512
*************************** 98. row ***************************
Variable_name: innodb_optimize_fulltext_only
Value: OFF
*************************** 99. row ***************************
Variable_name: innodb_page_cleaners
Value: 4
*************************** 100. row ***************************
Variable_name: innodb_page_size
Value: 16384
*************************** 101. row ***************************
Variable_name: innodb_parallel_doublewrite_path
Value: xb_doublewrite
*************************** 102. row ***************************
Variable_name: innodb_print_all_deadlocks
Value: OFF
*************************** 103. row ***************************
Variable_name: innodb_purge_batch_size
Value: 300
*************************** 104. row ***************************
Variable_name: innodb_purge_rseg_truncate_frequency
Value: 128
*************************** 105. row ***************************
Variable_name: innodb_purge_threads
Value: 4
*************************** 106. row ***************************
Variable_name: innodb_random_read_ahead
Value: OFF
*************************** 107. row ***************************
Variable_name: innodb_read_ahead_threshold
Value: 56
*************************** 108. row ***************************
Variable_name: innodb_read_io_threads
Value: 4
*************************** 109. row ***************************
Variable_name: innodb_read_only
Value: OFF
*************************** 110. row ***************************
Variable_name: innodb_replication_delay
Value: 0
*************************** 111. row ***************************
Variable_name: innodb_rollback_on_timeout
Value: OFF
*************************** 112. row ***************************
Variable_name: innodb_rollback_segments
Value: 128
*************************** 113. row ***************************
Variable_name: innodb_show_locks_held
Value: 10
*************************** 114. row ***************************
Variable_name: innodb_show_verbose_locks
Value: 0
*************************** 115. row ***************************
Variable_name: innodb_sort_buffer_size
Value: 1048576
*************************** 116. row ***************************
Variable_name: innodb_spin_wait_delay
Value: 6
*************************** 117. row ***************************
Variable_name: innodb_stats_auto_recalc
Value: ON
*************************** 118. row ***************************
Variable_name: innodb_stats_method
Value: nulls_equal
*************************** 119. row ***************************
Variable_name: innodb_stats_on_metadata
Value: OFF
*************************** 120. row ***************************
Variable_name: innodb_stats_persistent
Value: ON
*************************** 121. row ***************************
Variable_name: innodb_stats_persistent_sample_pages
Value: 20
*************************** 122. row ***************************
Variable_name: innodb_stats_sample_pages
Value: 8
*************************** 123. row ***************************
Variable_name: innodb_stats_transient_sample_pages
Value: 8
*************************** 124. row ***************************
Variable_name: innodb_status_output
Value: OFF
*************************** 125. row ***************************
Variable_name: innodb_status_output_locks
Value: OFF
*************************** 126. row ***************************
Variable_name: innodb_strict_mode
Value: ON
*************************** 127. row ***************************
Variable_name: innodb_support_xa
Value: ON
*************************** 128. row ***************************
Variable_name: innodb_sync_array_size
Value: 1
*************************** 129. row ***************************
Variable_name: innodb_sync_spin_loops
Value: 30
*************************** 130. row ***************************
Variable_name: innodb_table_locks
Value: ON
*************************** 131. row ***************************
Variable_name: innodb_temp_data_file_path
Value: ibtmp1:12M:autoextend
*************************** 132. row ***************************
Variable_name: innodb_thread_concurrency
Value: 0
*************************** 133. row ***************************
Variable_name: innodb_thread_sleep_delay
Value: 10000
*************************** 134. row ***************************
Variable_name: innodb_tmpdir
Value:
*************************** 135. row ***************************
Variable_name: innodb_track_changed_pages
Value: OFF
*************************** 136. row ***************************
Variable_name: innodb_undo_directory
Value: ./
*************************** 137. row ***************************
Variable_name: innodb_undo_log_truncate
Value: OFF
*************************** 138. row ***************************
Variable_name: innodb_undo_logs
Value: 128
*************************** 139. row ***************************
Variable_name: innodb_undo_tablespaces
Value: 0
*************************** 140. row ***************************
Variable_name: innodb_use_global_flush_log_at_trx_commit
Value: ON
*************************** 141. row ***************************
Variable_name: innodb_use_native_aio
Value: ON
*************************** 142. row ***************************
Variable_name: innodb_version
Value: 5.7.12-5
*************************** 143. row ***************************
Variable_name: innodb_write_io_threads
Value: 4

The nodes are 8 CPUs 16GB RAM. Nodes are located in the same DC

5

What is the storage system you’re using ?

This is just the warning which is not the big deal by itself while your overloaded storage is. Your innodb_io_capacity is not excessive and you’re using reasonably optimal buffer pool size and innodb log file size. I would consider innodb_flush_method=O_DIRECT though.

6

Hi Peter,

Thank you for your suggestion I will implement the change.
Currently I’m using Synology storage with drives spinning up to 7000rpms which is connected to esx over NFS protocol.
I understand that this is not an optimal setup, but this is only used for our QA environment.
However any suggestion you could provide on optimization would be welcome.