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 (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), 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.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.