Рейтинг:2

MariaDB high %sys load on AMD compared to Intel

флаг bd

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.

HTOP Load Mysql CPU-AVG

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:

  • my.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-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
флаг ua
Пожалуйста, поместите изображения в Вопрос.

Ответить или комментировать

Большинство людей не понимают, что склонность к познанию нового открывает путь к обучению и улучшает межличностные связи. В исследованиях Элисон, например, хотя люди могли точно вспомнить, сколько вопросов было задано в их разговорах, они не чувствовали интуитивно связи между вопросами и симпатиями. В четырех исследованиях, в которых участники сами участвовали в разговорах или читали стенограммы чужих разговоров, люди, как правило, не осознавали, что задаваемый вопрос повлияет — или повлиял — на уровень дружбы между собеседниками.