我们遇到了 MariaDB 和 AMD Epyc 7402 的问题。我们也有性能低得多的 Intel 服务器，它们不会遇到这个问题，使用完全相同的配置（我们使用的是 ansible）。在 %usr 的总 CPU 负载超过 15% 的情况下，%sys 中的负载异常高。如果您需要的任何东西都缺失了，我可以以日志和/或配置的形式提供所需的一切。
对于初学者：
- 操作系统：Centos 7（5.15.2-1.el7.elrepo.x86）
- MariaDB：10.4.22-MariaDB
- 该服务器作为 mysql 主服务器运行，总共有 8 个从服务器，但第二个网络接口上只有 4 个从服务器直接连接到它
硬件：
- AMD EPYC 7402 24 核处理器 2x
- 内存 528161544 kB 均匀分布在各个插槽中
- 硬盘：英特尔 SSDPE2KX010T8 2x（mdraid、lvm 和 ext4）
我们尝试过: （单一和组合（有意义的地方））
- jemalloc 3.6.0-1.el7
- jemalloc 5.2.1-1.el7（我们自己用默认和新的 c++ 编译器编译的）
- 内核-lt 5.4.160-1
- 内核-lt 5.4.161-1
- 内核-ml 5.15.2-1.el7.elrepo.x86
- 内核-ml 5.12.1-1.el7.elrepo.x86
- 内核 3.10.0-1160.45.1.el7
- numactl --interleave=all
配置：
- 我的cnf
[client]
port = 3306
[mysql]
no_auto_rehash
max_allowed_packet = 1024M
prompt = '\u@\h [\d]> '
default_character_set = utf8
[mysqld_safe]
open_files_limit = 1024
user = mysql
log-error = /var/log/mysql/error.log
[mysqld]
# NETWORKING
bind-address = 0.0.0.0
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
max_connections = 3000
max_user_connections = 3000
datadir = /var/lib/mysql
# BUFFERS
key_buffer_size = 256M
max_allowed_packet = 1024M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
# QUERY CACHE
query_cache_type = 1
query_alloc_block_size = 8192
query_cache_limit = 1024
query_cache_min_res_unit = 512
query_cache_size = 8388608
# STORAGE
default_storage_engine = InnoDB
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# MEMORY LIMITS
innodb_buffer_pool_size = 425G
innodb_log_file_size = 60G
innodb_buffer_pool_instances = 64
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
# COLLATION
character_set_server = utf8
collation_server = utf8_unicode_ci
# MyISAM variables
key_buffer_size = 1024M
# INNODB TWEAKING
innodb_file_format = Barracuda
innodb_strict_mode = ON
innodb_file_per_table = 1
innodb_thread_concurrency = 0
innodb-flush-method = O_DIRECT
# General Tweaks
tmp_table_size = 2048M
max_heap_table_size = 2048M
# Replication
server-id = 24
binlog-format = row
log_bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
expire_logs_days = 3
log_bin_trust_function_creators = 1
# LOGGING - ERROR
log_error = /var/log/mysql/error.log
log_warnings = 2
innodb_print_all_deadlocks = 1
# LOGGING - SLOW LOG
slow_query_log_file = /var/log/mysql/slow.log
slow_query_log = 1
log_queries_not_using_indexes = 0
long_query_time = 10
min_examined_row_limit = 100
# LOGGING - GENERAL LOG
general_log_file = /var/log/mysql/general.log
general_log = 0
# REPLICATION
slave_compressed_protocol = 1
# THREADING
thread_handling = pool-of-threads
thread-pool-max-threads = 6000
innodb_flush_log_at_trx_commit = 1
replicate_ignore_db = norp_*
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqldump]
quick
max_allowed_packet = 1024M
/etc/sysctl.conf
kernel.panic=30
vm.swappiness=10
net.ipv4.ip_local_port_range=10240 65000
net.core.rmem_default=524288
net.core.wmem_default=262144
net.core.rmem_max=134217728
net.core.wmem_max=134217728
net.ipv4.tcp_rmem=4096 87380 134217728
net.ipv4.tcp_wmem=4096 65536 134217728
net.ipv4.tcp_moderate_rcvbuf=1
net.ipv4.tcp_fin_timeout=10
net.core.netdev_max_backlog=300000
net.ipv4.tcp_max_syn_backlog=8192
net.ipv4.tcp_synack_retries=2
net.ipv4.tcp_max_orphans=800000
net.ipv4.tcp_keepalive_intvl=20
net.ipv4.tcp_keepalive_probes=5
net.core.somaxconn=2048
net.core.dev_weight=256
net.core.netdev_tstamp_prequeue=0
net.ipv4.ipfrag_time=5
net.ipv4.tcp_ecn=1
net.ipv4.tcp_max_tw_buckets=20000
net.ipv4.tcp_retries2=7
net.ipv4.tcp_limit_output_bytes=262144
fs.file-max=500000
net.ipv4.ip_forward=1
net.ipv4.ip_nonlocal_bind=1
net.ipv4.conf.eth0.arp_ignore=1
net.ipv4.conf.eth0.arp_announce=2
vm.overcommit_memory=1
net.ipv4.conf.default.rp_filter=0
vm.overcommit_memory = 1
PT-Mysql-概要
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 13 secs
Aborted_clients 6
Acl_database_grants 1500
Acl_proxy_users 15
Acl_users 1500
Aria_pagecache_blocks_not_flushed 70000 -16
Aria_pagecache_blocks_unused 2500 -29
Aria_pagecache_blocks_used 100000 1
Aria_pagecache_read_requests 17500000000 225000 200000
Aria_pagecache_reads 100000000 1250 3500
Aria_pagecache_write_requests 1500000000 17500 15000
Aria_pagecache_writes 600000000 7000 7000
Aria_transaction_log_syncs 3000
Binlog_commits 5000000 60 60
Binlog_group_commits 5000000 60 60
Binlog_snapshot_position 5000000000 60000 1750000
Binlog_bytes_written 35000000000 400000 1750000
Binlog_cache_disk_use 45000 1
Binlog_cache_use 5000000 60 60
Binlog_stmt_cache_use 20
Bytes_received 225000000000 2500000 2250000
Bytes_sent 1000000000000 12500000 10000000
Com_admin_commands 9000
Com_begin 70000 1
Com_call_procedure 1250
Com_commit 70000 1
Com_delete 1250000 15
Com_insert 10000000 125 450
Com_insert_select 2500
Com_rollback 600
Com_select 350000000 4000 3500
Com_set_option 60000000 600 700
Com_show_slave_status 9000
Com_show_status 12500
Com_show_tables 600
Com_show_variables 45
Com_update 20000000 225 900
Com_update_multi 70
Connections 30000000 350 350
Created_tmp_disk_tables 7000000 80 70
Created_tmp_files 80000 1
Created_tmp_tables 9000000 100 100
Delete_scan 250
Empty_queries 90000000 1000 1000
Executed_triggers 50000000 600 1000
Feature_check_constraint 6
Feature_json 6 7
Feature_subquery 1500000 20 20
Feature_trigger 6000000 70 125
Flush_commands 6
Handler_commit 400000000 5000 6000
Handler_delete 9000000 100
Handler_discover 250
Handler_icp_attempts 12500000000 150000 60000
Handler_icp_match 12500000000 150000 60000
Handler_prepare 60000000 700 2500
Handler_read_first 200000 2 1
Handler_read_key 30000000000 400000 400000
Handler_read_last 1250000 15 15
Handler_read_next 300000000000 3500000 2250000
Handler_read_prev 17500000000 200000 150000
Handler_read_rnd 12500000000 150000 125000
Handler_read_rnd_deleted 250
Handler_read_rnd_next 30000000000 300000 150000
Handler_rollback 150000 1 2
Handler_tmp_update 125000000 1500
Handler_tmp_write 17500000000 200000 200000
Handler_update 22500000 250 1250
Handler_write 12500000 150 600
Innodb_buffer_pool_bytes_data 500000000000 6000000 4500000
Innodb_buffer_pool_bytes_dirty 125000000000 1250000 1000000
Innodb_buffer_pool_pages_flushed 70000
Innodb_buffer_pool_read_ahead 125000 1
Innodb_buffer_pool_read_requests 300000000000 3500000 3000000
Innodb_buffer_pool_reads 30000000 350 250
Innodb_buffer_pool_write_requests 350000000 4000 7000
Innodb_data_fsyncs 5000000 60 70
Innodb_data_read 500000000000 6000000 4500000
Innodb_data_reads 35000000 400 250
Innodb_data_writes 5000000 60 70
Innodb_data_written 25000000000 300000 600000
Innodb_dblwr_pages_written 70000
Innodb_dblwr_writes 50000
Innodb_log_write_requests 35000000 400 900
Innodb_log_writes 5000000 60 70
Innodb_os_log_fsyncs 5000000 60 70
Innodb_os_log_written 22500000000 250000 600000
Innodb_pages_created 100000 1 3
Innodb_pages_read 35000000 400 250
Innodb_pages_written 70000
Innodb_row_lock_time 175000 1
Innodb_row_lock_waits 2250
Innodb_rows_deleted 5000000 60
Innodb_rows_inserted 4500000 50 100
Innodb_rows_read 400000000000 4500000 3000000
Innodb_rows_updated 22500000 250 1250
Innodb_system_rows_read 6
Innodb_num_open_files 2000
Innodb_available_undo_logs 800
Innodb_secondary_index_triggered_cluster_reads 60000000000 700000 600000
Key_read_requests 1250
Key_reads 400
Memory_used 10000000000 125000 1250000
Memory_used_initial 8000000000 90000
Open_table_definitions 2500
Opened_files 35000000 400 450
Opened_table_definitions 17500 1
Opened_tables 15000000 175 300
Opened_views 250
Qcache_hits 3500000 40 100
Qcache_inserts 250
Qcache_not_cached 600000000 7000 6000
Queries 500000000 6000 7000
Questions 450000000 5000 6000
Rows_read 125000000000 1500000 1250000
Rows_sent 2250000000 25000 15000
Rows_tmp_read 17500000000 200000 175000
Select_full_join 175000 2 2
Select_full_range_join 125000 1 2
Select_range 25000000 300 250
Select_range_check 125
Select_scan 2500000 30 20
Slave_connections 25
Slaves_connected 25
Slow_queries 30000
Sort_merge_passes 40000
Sort_priority_queue_sorts 20000000 225 200
Sort_range 30000000 350 300
Sort_rows 9000000000 100000 90000
Sort_scan 5000000 60 35
Subquery_cache_hit 20000
Subquery_cache_miss 2500000 30 20
Syncs 9000
Table_locks_immediate 50000 2
Table_open_cache_active_instances 6
Table_open_cache_hits 800000000 9000 17500
Table_open_cache_misses 15000000 175 300
Table_open_cache_overflows 12500000 125 300
Threadpool_idle_threads 2500 -3
Threadpool_threads 3000
Threads_created 70000
Update_scan 20
Uptime 90000 1 1
wsrep 1250000000000 15000000 1000000000
# Table cache ################################################
Size | 256
Usage | 100%
# InnoDB #####################################################
Version | 10.4.22
Buffer Pool Size | 432.0G
Buffer Pool Fill | 20%
Buffer Pool Dirty | 3%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 60.0G = 120.0G
Log Buffer Size | 8M
Flush Method | O_DIRECT
Flush Log At Commit | 1
XA Support |
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 2G
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 49981
Read Views | 41
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 41xACTIVE, 148xnot started
# MyISAM #####################################################
vmstat -1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
66 0 0 382036288 258372 23430352 0 0 3872 99095 201601 554506 16 49 35 0 0
60 0 0 382076832 258372 23444996 0 0 3472 4880 193726 557287 17 50 33 0 0
60 0 0 382070048 258372 23430188 0 0 3568 4496 186601 533398 16 51 33 0 0
61 0 0 382029760 258372 23424988 0 0 3472 4220 190556 537991 14 54 32 0 0
75 0 0 382012704 258372 23445704 0 0 3648 4360 179687 527781 11 62 27 0 0
71 0 0 382003648 258372 23469492 0 0 3536 75513 196280 531335 16 64 20 0 0
78 0 0 382000704 258372 23495520 0 0 3552 2384 189606 543385 13 67 20 0 0
85 0 0 381998528 258372 23511724 0 0 3392 3113 232940 531062 14 74 12 0 0
80 0 0 382065728 258372 23474704 0 0 3744 3192 199559 541724 13 70 17 0 0
78 0 0 382070784 258372 23445872 0 0 3692 4314 196798 552624 15 69 17 0 0
75 0 0 382059680 258372 23464400 0 0 3536 2924 197860 511828 13 66 21 0 0
84 0 0 382105184 258372 23489224 0 0 3616 3724 206273 535082 13 73 14 0 0
84 0 0 382134016 258372 23475792 0 0 3472 2293 215286 520198 12 77 11 0 0
84 0 0 382123136 258372 23472120 0 0 3568 3097 205254 525508 13 72 15 0 0
82 0 0 382153728 258372 23494132 0 0 6912 3952 233188 538006 14 72 14 0 0
81 0 0 382128064 258372 23502312 0 0 3616 3284 184822 521364 13 70 17 0 0
75 0 0 382123776 258372 23541176 0 0 3776 2872 207498 536109 12 72 16 0 0
83 0 0 382160096 258372 23540552 0 0 3536 3306 202817 551826 15 68 18 0 0
65 0 0 382205664 258372 23504828 0 0 3648 2692 190065 551570 14 61 25 0 0
62 0 0 382176576 258372 23508872 0 0 4816 4964 186138 560683 12 58 30 0 0
55 0 0 382215264 258372 23448712 0 0 3616 4165 166252 558690 11 46 43 0 0
52 0 0 382219872 258372 23430824 0 0 3584 167357 178906 592052 15 42 41 2 0
47 0 0 382217472 258372 23379732 0 0 6752 2976 178652 547447 15 40 45 0 0
46 0 0 382210624 258372 23358184 0 0 3744 2800 186172 543690 15 38 46 0 0
47 0 0 382180192 258372 23351812 0 0 8896 3976 166141 530729 15 34 51 0 0
51 0 0 382159808 258372 23362736 0 0 3568 2772 171856 559503 18 34 47 0 0
47 0 0 382181024 258372 23364880 0 0 4112 3232 165595 516269 12 39 49 0 0
46 0 0 382229728 258372 23371168 0 0 3616 3447 151896 515363 14 36 49 0 0
52 0 0 382223136 258372 23332020 0 0 4672 2674 180962 556582 14 39 47 0 0
60 0 0 382214336 258372 23347904 0 0 3568 2960 169390 522171 12 46 42 0 0
62 0 0 382228512 258372 23384148 0 0 3696 3686 191964 532110 15 49 36 0 0
65 0 0 382184832 258372 23405920 0 0 3584 4528 183894 541259 15 54 31 0 0
72 0 0 382199488 258372 23394256 0 0 3584 2345 194048 538938 15 60 25 0 0
80 0 0 382209600 258372 23405216 0 0 12128 4376 192327 513727 13 66 21 0 0
70 0 0 382176608 258372 23437752 0 0 3776 2748 216748 533426 15 67 18 0 0
69 0 0 382180928 258372 23458208 0 0 6816 4772 192280 549678 14 62 24 0 0
61 0 0 382196640 258372 23427652 0 0 4016 57509 193263 542982 12 64 24 0 0
70 0 0 382233216 258372 23434848 0 0 3648 89949 191647 541364 12 57 30 0 0
74 0 0 382189856 258372 23442084 0 0 3520 2632 186482 573915 14 60 26 0 0
62 0 0 382155744 258372 23445880 0 0 3888 5436 178857 559573 14 55 30 0 0
73 0 0 382158304 258372 23420096 0 0 3712 3800 180457 551695 15 55 30 0 0
77 0 0 382161760 258372 23402016 0 0 3488 3768 208621 542433 16 62 22 0 0
68 1 0 382149664 258372 23412868 0 0 6704 3777 184064 534328 14 60 26 0 0
69 1 0 382120896 258372 23409916 0 0 3728 4153 178721 518152 13 59 28 0 0
83 0 0 382115968 258372 23472716 0 0 3376 4576 190548 522075 15 69 17 0 0
23 1 0 381906176 258372 23575532 0 0 3792 4595 220719 560955 20 71 10 0 0
86 1 0 382011392 258372 23485620 0 0 3680 5765 217555 560589 20 71 9 0 0
72 1 0 382006240 258372 23484148 0 0 4544 5158 195189 540771 18 67 14 0 0
75 1 0 381983744 258372 23507208 0 0 3520 4404 192868 535709 15 63 22 0 0
72 1 0 381939328 258372 23526672 0 0 3632 4000 179945 545377 15 62 23 0 0
70 1 0 381931392 258372 23511188 0 0 7952 4904 181942 528306 15 60 26 0 0
78 1 0 381927520 258372 23541316 0 0 4032 5773 202670 530372 13 63 23 0 0
79 1 0 381914752 258372 23548560 0 0 3200 3520 202867 536863 14 68 18 0 0
Numa Interleave 已启用
cat /proc/`pidof mysqld`/numa* | grep interleave | wc -l
1430