we have run into a issue with MariaDB and AMD Epyc 7402. We also have much less potent Intel servers that do not suffer from this problem, using exact same configuration (we are using ansible). Under more than about 15% total cpu load in %usr, there is an unusually high load in %sys.. If anything you need is missing, i can provide whatever is neccessary in form of logs and or configurations.
For starters:
- OS: Centos 7 (5.15.2-1.el7.elrepo.x86)
- MariaDB: 10.4.22-MariaDB
- This server is running as mysql master for total of 8 slaves but only 4 slaves are directly attached to it on second network interface
HW:
- AMD EPYC 7402 24-Core Processor 2x
- Memory 528161544 kB divided evenly among sockets
- HDD: INTEL SSDPE2KX010T8 2x (mdraid, lvm and ext4)
What we tried: (single and combinations (where it makes sense))
- jemalloc 3.6.0-1.el7
- jemalloc 5.2.1-1.el7 (compiled ourselves with default and new c++ compilers)
- kernel-lt 5.4.160-1
- kernel-lt 5.4.161-1
- kernel-ml 5.15.2-1.el7.elrepo.x86
- kernel-ml 5.12.1-1.el7.elrepo.x86
- kernel 3.10.0-1160.45.1.el7
- numactl --interleave=all
Configs:
[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-Summary
# 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 is on
cat /proc/`pidof mysqld`/numa* | grep interleave | wc -l
1430
