Not the answer you need?
Register and ask your own question!

page_cleaner issues after upgrading to Percona XtraDB 57 from 56

dariusz_matejadariusz_mateja EntrantInactive User Role Beginner
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 ?

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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
  • dariusz_matejadariusz_mateja Entrant Inactive User Role Beginner
    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
  • dariusz_matejadariusz_mateja Entrant Inactive User Role Beginner
    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
  • PeterPeter Percona CEO Percona Moderator Role
    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.
  • dariusz_matejadariusz_mateja Entrant Inactive User Role Beginner
    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.
Sign In or Register to comment.

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