1

The database node is consuming 99% of RAM and 100% of the swap. The server has CPU with 48 cores and 126GB of RAM. It is a dedicated server to MariaDB.

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

I test with buffer pool size. Actually has 80GB.

My .cnf is

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

SHOW ENGINE INNODB STATUS

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

Actually the node has Threads_connected 620 Connections 334083

it seems that the node has been increasing the use of ram over the days (14 days), has not acquired it in a short period of time.

How can I reduce memory usage?

If I lack some information I can add it. I have Grafana Monitor.

Thanks.

ancar31
  • 172
  • 2
  • 8
  • 1
    What about `PROCESSLIST`? Are there still running queries? – Raymond Nijland May 01 '19 at 10:27
  • 1
    Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis. – Wilson Hauck May 05 '19 at 22:12
  • What version of MySQL? – Rick James May 19 '19 at 01:51
  • Sorry for delay 10.3.9-MariaDB-1:10.3.9+maria~bionic mariadb.org binary distribution. I restart the server but after a few days it returns to the same state. I cannot execute mysql tunner because the server ha not enough memory – ancar31 May 30 '19 at 11:19
  • It really is a galera cluster – ancar31 May 30 '19 at 11:28

1 Answers1

0

Suggestion to consider for your my.cnf [mysqld] section,

innodb_flushing_avg_loops=5  # from 30 to reduce loop delay

to reduce the avg of ~ 5% modified pages in each of the buffer pools.

This is a Dynamic Global Variable that with root login could be

SET GLOBAL innodb_flushing_age_loops=5;

to start getting this overhead reduced rather than waiting on a stop/start or restart of the instance.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19
  • Another suggestion that will help conserve CPU cycles, innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for this function. – Wilson Hauck May 06 '19 at 16:43
  • Memory, not CPU seems to be the problem? – Rick James May 19 '19 at 01:53
  • 1
    @ancar31 After the additional information requested May 5 is posted, we should have some clues on what is consuming 99% of RAM. Specifically SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; complete TEXT reports. – Wilson Hauck May 20 '19 at 13:13
  • @ancar31 If this is still an issue, let us know and post the additional information requested, please. If no longer a problem. we would like to be aware to conserve our time. – Wilson Hauck Jun 15 '19 at 17:02