在组复制上调优MySQL
在组复制上调优MySQL
提问于 2020-02-23 19:27:17
在组复制中，我即将从旧版本的MySQL 5.6 (主/从配置)切换到MySQL 8(多主配置上的3个节点)。
表为99% InnoDB。
webfarm刚刚将前面的InnoDB配置变量复制到这些新机器上。我想知道如何提高性能，每台机器都有以下配置：
1) Cpu:
12 core (Intel Xeon Processor (Skylake, IBRS))
2) RAM:
total used free shared buff/cache available
Mem: 49456252 5164100 11487392 18972 32804760 43676276
Swap: 1998844 6924 1991920
3) Disk:
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 54G 1.2G 50G 3% /
/dev/mapper/vg0-mysql 1004G 414G 590G 42% /var/lib/mysql我所有的数据库都是250 of的数据。
InnnoDB变量：
*************************** 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_in_core_file
Value: ON
*************************** 19. row ***************************
Variable_name: innodb_buffer_pool_instances
Value: 8
*************************** 20. row ***************************
Variable_name: innodb_buffer_pool_load_abort
Value: OFF
*************************** 21. row ***************************
Variable_name: innodb_buffer_pool_load_at_startup
Value: ON
*************************** 22. row ***************************
Variable_name: innodb_buffer_pool_load_now
Value: OFF
*************************** 23. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 34359738368
*************************** 24. row ***************************
Variable_name: innodb_change_buffer_max_size
Value: 25
*************************** 25. row ***************************
Variable_name: innodb_change_buffering
Value: all
*************************** 26. row ***************************
Variable_name: innodb_checksum_algorithm
Value: crc32
*************************** 27. row ***************************
Variable_name: innodb_cmp_per_index_enabled
Value: OFF
*************************** 28. row ***************************
Variable_name: innodb_commit_concurrency
Value: 0
*************************** 29. row ***************************
Variable_name: innodb_compression_failure_threshold_pct
Value: 5
*************************** 30. row ***************************
Variable_name: innodb_compression_level
Value: 6
*************************** 31. row ***************************
Variable_name: innodb_compression_pad_pct_max
Value: 50
*************************** 32. row ***************************
Variable_name: innodb_concurrency_tickets
Value: 5000
*************************** 33. row ***************************
Variable_name: innodb_data_file_path
Value: ibdata1:12M:autoextend
*************************** 34. row ***************************
Variable_name: innodb_data_home_dir
Value:
*************************** 35. row ***************************
Variable_name: innodb_deadlock_detect
Value: ON
*************************** 36. row ***************************
Variable_name: innodb_dedicated_server
Value: OFF
*************************** 37. row ***************************
Variable_name: innodb_default_row_format
Value: dynamic
*************************** 38. row ***************************
Variable_name: innodb_directories
Value:
*************************** 39. row ***************************
Variable_name: innodb_disable_sort_file_cache
Value: OFF
*************************** 40. row ***************************
Variable_name: innodb_doublewrite
Value: ON
*************************** 41. row ***************************
Variable_name: innodb_fast_shutdown
Value: 1
*************************** 42. row ***************************
Variable_name: innodb_file_per_table
Value: ON
*************************** 43. row ***************************
Variable_name: innodb_fill_factor
Value: 100
*************************** 44. row ***************************
Variable_name: innodb_flush_log_at_timeout
Value: 1
*************************** 45. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 1
*************************** 46. row ***************************
Variable_name: innodb_flush_method
Value: O_DIRECT
*************************** 47. row ***************************
Variable_name: innodb_flush_neighbors
Value: 0
*************************** 48. row ***************************
Variable_name: innodb_flush_sync
Value: ON
*************************** 49. row ***************************
Variable_name: innodb_flushing_avg_loops
Value: 30
*************************** 50. row ***************************
Variable_name: innodb_force_load_corrupted
Value: OFF
*************************** 51. row ***************************
Variable_name: innodb_force_recovery
Value: 0
*************************** 52. row ***************************
Variable_name: innodb_fsync_threshold
Value: 0
*************************** 53. row ***************************
Variable_name: innodb_ft_aux_table
Value:
*************************** 54. row ***************************
Variable_name: innodb_ft_cache_size
Value: 8000000
*************************** 55. row ***************************
Variable_name: innodb_ft_enable_diag_print
Value: OFF
*************************** 56. row ***************************
Variable_name: innodb_ft_enable_stopword
Value: ON
*************************** 57. row ***************************
Variable_name: innodb_ft_max_token_size
Value: 84
*************************** 58. row ***************************
Variable_name: innodb_ft_min_token_size
Value: 3
*************************** 59. row ***************************
Variable_name: innodb_ft_num_word_optimize
Value: 2000
*************************** 60. row ***************************
Variable_name: innodb_ft_result_cache_limit
Value: 2000000000
*************************** 61. row ***************************
Variable_name: innodb_ft_server_stopword_table
Value:
*************************** 62. row ***************************
Variable_name: innodb_ft_sort_pll_degree
Value: 2
*************************** 63. row ***************************
Variable_name: innodb_ft_total_cache_size
Value: 640000000
*************************** 64. row ***************************
Variable_name: innodb_ft_user_stopword_table
Value:
*************************** 65. row ***************************
Variable_name: innodb_io_capacity
Value: 200
*************************** 66. row ***************************
Variable_name: innodb_io_capacity_max
Value: 2000
*************************** 67. row ***************************
Variable_name: innodb_lock_wait_timeout
Value: 120
*************************** 68. row ***************************
Variable_name: innodb_log_buffer_size
Value: 134217728
*************************** 69. row ***************************
Variable_name: innodb_log_checksums
Value: ON
*************************** 70. row ***************************
Variable_name: innodb_log_compressed_pages
Value: ON
*************************** 71. row ***************************
Variable_name: innodb_log_file_size
Value: 268435456
*************************** 72. row ***************************
Variable_name: innodb_log_files_in_group
Value: 2
*************************** 73. row ***************************
Variable_name: innodb_log_group_home_dir
Value: ./
*************************** 74. row ***************************
Variable_name: innodb_log_spin_cpu_abs_lwm
Value: 80
*************************** 75. row ***************************
Variable_name: innodb_log_spin_cpu_pct_hwm
Value: 50
*************************** 76. row ***************************
Variable_name: innodb_log_wait_for_flush_spin_hwm
Value: 400
*************************** 77. row ***************************
Variable_name: innodb_log_write_ahead_size
Value: 8192
*************************** 78. row ***************************
Variable_name: innodb_lru_scan_depth
Value: 1024
*************************** 79. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 90.000000
*************************** 80. row ***************************
Variable_name: innodb_max_dirty_pages_pct_lwm
Value: 10.000000
*************************** 81. row ***************************
Variable_name: innodb_max_purge_lag
Value: 0
*************************** 82. row ***************************
Variable_name: innodb_max_purge_lag_delay
Value: 0
*************************** 83. row ***************************
Variable_name: innodb_max_undo_log_size
Value: 1073741824
*************************** 84. row ***************************
Variable_name: innodb_monitor_disable
Value:
*************************** 85. row ***************************
Variable_name: innodb_monitor_enable
Value:
*************************** 86. row ***************************
Variable_name: innodb_monitor_reset
Value:
*************************** 87. row ***************************
Variable_name: innodb_monitor_reset_all
Value:
*************************** 88. row ***************************
Variable_name: innodb_numa_interleave
Value: OFF
*************************** 89. row ***************************
Variable_name: innodb_old_blocks_pct
Value: 37
*************************** 90. row ***************************
Variable_name: innodb_old_blocks_time
Value: 1000
*************************** 91. row ***************************
Variable_name: innodb_online_alter_log_max_size
Value: 134217728
*************************** 92. row ***************************
Variable_name: innodb_open_files
Value: 3459
*************************** 93. row ***************************
Variable_name: innodb_optimize_fulltext_only
Value: OFF
*************************** 94. row ***************************
Variable_name: innodb_page_cleaners
Value: 4
*************************** 95. row ***************************
Variable_name: innodb_page_size
Value: 16384
*************************** 96. row ***************************
Variable_name: innodb_parallel_read_threads
Value: 4
*************************** 97. row ***************************
Variable_name: innodb_print_all_deadlocks
Value: OFF
*************************** 98. row ***************************
Variable_name: innodb_print_ddl_logs
Value: OFF
*************************** 99. row ***************************
Variable_name: innodb_purge_batch_size
Value: 300
*************************** 100. row ***************************
Variable_name: innodb_purge_rseg_truncate_frequency
Value: 128
*************************** 101. row ***************************
Variable_name: innodb_purge_threads
Value: 4
*************************** 102. row ***************************
Variable_name: innodb_random_read_ahead
Value: OFF
*************************** 103. row ***************************
Variable_name: innodb_read_ahead_threshold
Value: 56
*************************** 104. row ***************************
Variable_name: innodb_read_io_threads
Value: 4
*************************** 105. row ***************************
Variable_name: innodb_read_only
Value: OFF
*************************** 106. row ***************************
Variable_name: innodb_redo_log_archive_dirs
Value:
*************************** 107. row ***************************
Variable_name: innodb_redo_log_encrypt
Value: OFF
*************************** 108. row ***************************
Variable_name: innodb_replication_delay
Value: 0
*************************** 109. row ***************************
Variable_name: innodb_rollback_on_timeout
Value: OFF
*************************** 110. row ***************************
Variable_name: innodb_rollback_segments
Value: 128
*************************** 111. row ***************************
Variable_name: innodb_sort_buffer_size
Value: 1048576
*************************** 112. row ***************************
Variable_name: innodb_spin_wait_delay
Value: 6
*************************** 113. row ***************************
Variable_name: innodb_spin_wait_pause_multiplier
Value: 50
*************************** 114. row ***************************
Variable_name: innodb_stats_auto_recalc
Value: ON
*************************** 115. row ***************************
Variable_name: innodb_stats_include_delete_marked
Value: OFF
*************************** 116. row ***************************
Variable_name: innodb_stats_method
Value: nulls_equal
*************************** 117. row ***************************
Variable_name: innodb_stats_on_metadata
Value: OFF
*************************** 118. row ***************************
Variable_name: innodb_stats_persistent
Value: ON
*************************** 119. row ***************************
Variable_name: innodb_stats_persistent_sample_pages
Value: 20
*************************** 120. row ***************************
Variable_name: innodb_stats_transient_sample_pages
Value: 8
*************************** 121. row ***************************
Variable_name: innodb_status_output
Value: OFF
*************************** 122. row ***************************
Variable_name: innodb_status_output_locks
Value: OFF
*************************** 123. row ***************************
Variable_name: innodb_strict_mode
Value: ON
*************************** 124. row ***************************
Variable_name: innodb_sync_array_size
Value: 1
*************************** 125. row ***************************
Variable_name: innodb_sync_spin_loops
Value: 30
*************************** 126. row ***************************
Variable_name: innodb_table_locks
Value: ON
*************************** 127. row ***************************
Variable_name: innodb_temp_data_file_path
Value: ibtmp1:12M:autoextend
*************************** 128. row ***************************
Variable_name: innodb_temp_tablespaces_dir
Value: ./#innodb_temp/
*************************** 129. row ***************************
Variable_name: innodb_thread_concurrency
Value: 12
*************************** 130. row ***************************
Variable_name: innodb_thread_sleep_delay
Value: 0
*************************** 131. row ***************************
Variable_name: innodb_tmpdir
Value:
*************************** 132. row ***************************
Variable_name: innodb_undo_directory
Value: ./
*************************** 133. row ***************************
Variable_name: innodb_undo_log_encrypt
Value: OFF
*************************** 134. row ***************************
Variable_name: innodb_undo_log_truncate
Value: ON
*************************** 135. row ***************************
Variable_name: innodb_undo_tablespaces
Value: 2
*************************** 136. row ***************************
Variable_name: innodb_use_native_aio
Value: ON
*************************** 137. row ***************************
Variable_name: innodb_version
Value: 8.0.17
*************************** 138. row ***************************
Variable_name: innodb_write_io_threads
Value: 4
*************************** 139. row **************************
Variable_name: max_connections
Value: 3072
*************************** 140. row ***************************
Variable_name: max_user_connections
Value: 3072我读了很多关于缓冲池大小的文章( 80%的规则)，但我仍然在考虑是否还有其他变量需要增加。
在实际的主服务器中，我有以下统计数据：
此外，我尝试运行mysqltuner脚本，以下是建议：
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysql/error.log file
Control error line(s) into /var/log/mysql/error.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
table_definition_cache(2000) > 464470 or -1 (autosizing if supported)
innodb_buffer_pool_size (>= 241.7G) if possible.
innodb_log_file_size should be (=3G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=24)行"innodb_buffer_pool_size (>= 241.7G)“(如果可能的话)。有点震惊，怎么可能有这么大的内存和缓冲池呢？
我知道这与我之前提到的数据大小(250 of )有关，但这么大的RAM容量不值得暗示吗？请随时询问其他细节，并感谢您的帮助！
@丹布莱克
在其中一台组复制机的mysql配置文件下面，我的客户现在不使用这些机器，当所有的机器都准备好并正确配置时，我将切换到它们。
这是GR机器上完整的mysqltuner报告：https://pastebin.com/XEFbpGUV
@Wilson Hauck我不认为SSD或NVMEE是一种设备：
pvdisplay
--- Physical volume ---
PV Name /dev/sdc
VG Name vg0
PV Size <651.93 GiB / not usable 4.00 MiB
Allocatable yes
PE Size 4.00 MiB
Total PE 166893
Free PE 493
Allocated PE 166400
PV UUID 4Xco4e-Es5d-LcZ2-FHxt-8PYw-B1E6-h9nwP8
cat /sys/block/sdc/queue/rotational
1这些是我将要切换的一台机器的输出，我重复一遍，这些机器仍然没有被应用程序使用：
正常运行时间25小时：
( B) https://pastebin.com/Uqtre6BS
( C) https://pastebin.com/3HfWdCTR
( D) https://pastebin.com/EazB2XWZ
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 193064
max locked memory (kbytes, -l) 65536
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 193064
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited除了这些信息之外，我还提供了我的客户
现在使用的从机的输出。
正常运行时间: 26天
MySQL配置文件：
Mysql调优脚本+显示全局状态+显示全局变量+显示完整的PROCESSLIST (我在这里详细介绍了可供声誉使用的链接)
磁盘类型：
pvdisplay
--- Physical volume ---
PV Name /dev/vdb1
VG Name vg0
PV Size 912.54 GiB / not usable 2.00 MiB
Allocatable yes
PE Size 4.00 MiB
Total PE 233611
Free PE 5259
Allocated PE 228352
PV UUID G8aSfO-Ktbg-UcfP-4yoL-jjH9-qjxe-OUN9ni
cat /sys/block/vdb/queue/rotational
1
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 459880
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 459880
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
iostat -xm 5 3
Linux 3.2.0-4-amd64 02/24/20 _x86_64_ (12 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
6.95 0.00 0.65 1.20 0.01 91.19
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.16 0.09 0.15 0.00 0.00 22.30 0.00 1.02 1.94 0.46 0.46 0.01
vdb 5.84 89.65 156.08 208.73 7.59 3.05 59.71 0.92 2.53 1.93 2.97 0.68 24.89
dm-0 0.00 0.00 161.92 269.31 7.59 3.05 50.51 1.21 2.81 2.52 2.98 0.58 24.96
avg-cpu: %user %nice %system %iowait %steal %idle
3.35 0.00 0.54 4.43 0.00 91.69
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.60 4.40 1.40 0.02 0.01 9.10 0.01 1.93 2.55 0.00 0.14 0.08
vdb 3.40 35.60 830.40 51.20 17.90 0.30 42.26 1.16 1.31 1.18 3.53 0.85 75.04
dm-0 0.00 0.00 833.80 74.60 17.90 0.30 41.01 1.54 1.69 1.53 3.47 0.83 75.20
avg-cpu: %user %nice %system %iowait %steal %idle
3.40 0.00 0.43 4.64 0.00 91.52
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
vda 0.00 0.40 0.00 0.40 0.00 0.00 16.00 0.00 0.00 0.00 0.00 0.00 0.00
vdb 5.80 30.80 810.60 46.80 14.65 0.27 35.63 1.39 1.59 1.61 1.32 1.04 89.36
dm-0 0.00 0.00 816.20 67.20 14.67 0.27 34.63 2.02 2.24 2.34 1.04 1.01 89.36更新第二编辑
- 从服务器
(显示全局状态；+显示全局变量；+显示完整PROCESSLIST；)
- 阶段服务器
显示全局变量；
(显示全局状态；+显示完整PROCESSLIST；)
回答 1
Database Administration用户
发布于 2020-02-26 08:54:35
每秒速率= RPS
关于从my.cnf 米舍尔德部分考虑的建议
max_connections=750 # from 3072 to conserve RAM - max_used_connections was 507 in 27 days
read_rnd_buffer_size=192K # from 2M to reduce handler_read_rnd_next RPS 133,629
innodb_io_capacity=900 # from 200 to enable higher IOPS to your HDD
join_buffer_size=16M # from ~ 32M will still support 2.6M rows to be joined
connect_timeout=20 # from 8 seconds for tolerance and to reduce aborted_connects RPHr 593
table_definition_cache=20000 # from 1400 to reduce opened_table_definitions RPS 104
key_cache_age_threshold=7200 # from 300 (seconds) to reduce key_reads RPS 3
log_queries_not_using_indexes=OFF # from ON to conserve CPU cycles - since log is OFF.这只是个开端而已。查看我的配置文件，网络配置文件的联系信息和免费实用程序脚本，以协助性能调优。
观察: 48G RAM对负载是不够的，19%的查询需要超过10秒才能完成，需要在需要索引以避免表扫描的情况下进行投资，com_analyze和com_optimize分别表示27天内没有活动，并且有更多提高性能的机会。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/260377
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号