MariaDB + Galera 노드 99% RAM 사용

MariaDB + Galera 노드 99% RAM 사용

데이터베이스 노드는 RAM의 99%와 스왑의 100%를 사용합니다.서버에는 48개의 코어와 126GB의 RAM이 있습니다.MariaDB 전용 서버입니다.

              total        used        free      shared  buff/cache   available
Mem:           125G        125G        385M        3.8M        364M         46M
Swap:          8.0G        8.0G          4K

버퍼 풀 크기로 테스트합니다.실제로 80GB가 있습니다.

나의 .cnf는

max_connections        = 2000
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_rate_limit     = 1000
log_slow_verbosity      = query_plan
log-queries-not-using-indexes

log_slave_updates       = 1
server-id               = 2

slave_parallel_threads  = 20
slave_parallel_mode     = aggressive

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
myisam_recover_options  = BACKUP
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 3000
thread_cache_size              = 100
open_files_limit               = 1000000
table_definition_cache         = 4096
table_open_cache               = 10240

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 80G

expire_logs_days        = 10
max_binlog_size   = 100M
binlog_checksum = NONE

엔진 INNODB 상태 표시

FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
836875115 OS file reads, 1192435981 OS file writes, 178546937 OS fsyncs
332.35 reads/s, 16384 avg bytes/read, 1814.16 writes/s, 227.53 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 139625, free list len 2739601, seg size 2879227, 50355508 merges
merged operations:
 insert 324530876, delete mark 138997690, delete 37797258
discarded operations:
 insert 4927776, delete mark 25723, delete 25316
Hash table size 21247271, node heap has 35507 buffer(s)
Hash table size 21247271, node heap has 38379 buffer(s)
Hash table size 21247271, node heap has 31384 buffer(s)
Hash table size 21247271, node heap has 192464 buffer(s)
Hash table size 21247271, node heap has 11439 buffer(s)
Hash table size 21247271, node heap has 7315 buffer(s)
Hash table size 21247271, node heap has 8776 buffer(s)
Hash table size 21247271, node heap has 11195 buffer(s)
617988.20 hash searches/s, 14809.22 non-hash searches/s
---
LOG
---
Log sequence number 129037015002594
Log flushed up to   129037015002594
Pages flushed up to 129036919726312
Last checkpoint at  129036915246463
0 pending log flushes, 0 pending chkp writes
2624367 log i/o's done, 2.71 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 87946166272
Dictionary memory allocated 108679083
Buffer pool size   5242240
Free buffers       8167
Database pages     4897624
Old database pages 1807753
Modified db pages  192680
Percent of dirty pages(LRU & free pages): 3.928
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 80, single page 0
Pages made young 1154903301, not young 43368008533
393.89 youngs/s, 1349.45 non-youngs/s
Pages read 836846327, created 10508131, written 1153543951
330.66 reads/s, 7.19 creates/s, 1759.41 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 5.81/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4897624, unzip_LRU len: 0
I/O sum[826696]:cur[10624], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   655280
Free buffers       1018
Database pages     612178
Old database pages 225963
Modified db pages  25033
Percent of dirty pages(LRU & free pages): 4.082
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 148414929, not young 5535667615
53.28 youngs/s, 240.19 non-youngs/s
Pages read 110280643, created 1879304, written 151902544
44.96 reads/s, 0.23 creates/s, 225.77 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 2.18/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612178, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   655280
Free buffers       1025
Database pages     612251
Old database pages 225986
Modified db pages  23729
Percent of dirty pages(LRU & free pages): 3.869
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 144048081, not young 5416839566
46.93 youngs/s, 168.31 non-youngs/s
Pages read 105018752, created 1234415, written 142490330
40.39 reads/s, 0.96 creates/s, 220.77 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.32/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612251, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   655280
Free buffers       1024
Database pages     612122
Old database pages 225940
Modified db pages  23820
Percent of dirty pages(LRU & free pages): 3.885
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 145084999, not young 5382783071
48.10 youngs/s, 150.82 non-youngs/s
Pages read 104679389, created 1234930, written 143032402
40.98 reads/s, 1.60 creates/s, 218.29 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.09/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612122, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   655280
Free buffers       1023
Database pages     612193
Old database pages 225965
Modified db pages  24200
Percent of dirty pages(LRU & free pages): 3.946
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 27, single page 0
Pages made young 144564799, not young 5358994152
51.46 youngs/s, 133.61 non-youngs/s
Pages read 102446321, created 1223513, written 144225873
41.77 reads/s, 0.77 creates/s, 224.42 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.70/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612193, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   655280
Free buffers       1024
Database pages     612206
Old database pages 225970
Modified db pages  24000
Percent of dirty pages(LRU & free pages): 3.914
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 26, single page 0
Pages made young 141563225, not young 5258479757
47.79 youngs/s, 139.80 non-youngs/s
Pages read 101318339, created 1224233, written 141897061
39.77 reads/s, 0.96 creates/s, 215.14 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 1.12/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612206, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   655280
Free buffers       1024
Database pages     612299
Old database pages 226004
Modified db pages  24028
Percent of dirty pages(LRU & free pages): 3.918
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 27, single page 0
Pages made young 144579506, not young 5518771524
48.56 youngs/s, 143.56 non-youngs/s
Pages read 103731207, created 1240136, written 143049715
39.33 reads/s, 0.70 creates/s, 218.54 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612299, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   655280
Free buffers       1024
Database pages     612148
Old database pages 225948
Modified db pages  24020
Percent of dirty pages(LRU & free pages): 3.917
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 142515269, not young 5362566037
50.82 youngs/s, 189.73 non-youngs/s
Pages read 102372358, created 1234589, written 144176736
40.79 reads/s, 0.98 creates/s, 221.45 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 1.40/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612148, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   655280
Free buffers       1005
Database pages     612227
Old database pages 225977
Modified db pages  23850
Percent of dirty pages(LRU & free pages): 3.889
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 144132493, not young 5533906811
46.95 youngs/s, 183.42 non-youngs/s
Pages read 106999318, created 1237011, written 142769290
42.67 reads/s, 0.98 creates/s, 215.03 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 612227, unzip_LRU len: 0
I/O sum[103337]:cur[1328], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Process ID=9599, Main thread ID=139872644048640, state: sleeping
Number of rows inserted 568667343, updated 531925669, deleted 47986174, read 816941685947
578.99 inserts/s, 837.00 updates/s, 0.07 deletes/s, 774076.80 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

실제로 노드에는 Threads_connected 620 Connections 334083이 있습니다.

노드는 며칠(14일) 동안 RAM 사용을 증가시켜 왔으며 단기간에 RAM을 획득하지 못한 것으로 보입니다.

어떻게 하면 메모리 사용량을 줄일 수 있습니까?

정보가 부족하면 추가할 수 있습니다.그라파나 모니터를 가지고 있습니다.

감사해요.

my.cnf [mysqld] 섹션에 대해 고려해야 할 제안입니다.

innodb_flushing_avg_loops=5  # from 30 to reduce loop delay

각 버퍼 풀에서 수정된 페이지의 평균을 5%까지 줄입니다.

이것은 루트 로그인을 사용할 경우 발생할 수 있는 동적 글로벌 변수입니다.

SET 글로벌 innodb_nodb_age_session=5;

인스턴스의 중지/시작 또는 재시작을 기다리지 않고 이 오버헤드를 줄이기 시작합니다.

언급URL : https://stackoverflow.com/questions/55926391/mariadb-galera-node-99-ram-usage