maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #04428
MariaDB memory usage
Hi,
Hi, I have MariaDB 10.1.19 with 1 GB of RAM on (AWS RDS) and another one
with 4 GB of RAM (production instance). Memory usage keeps on increasing on
both. Free memory goes below 50MB. InnoDB status shows "History list length
2671". My total table count is 225 and shows around 10 GB of data for them.
I am giving the InnoDB engine status for your reference below, I would be
happy to get any pointers on this issue.
=====================================
2017-03-31 10:53:08 2b25f6ec2700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6695 srv_active, 0 srv_shutdown, 187084 srv_idle
srv_master_thread log flush and writes: 193768
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 77210
OS WAIT ARRAY INFO: signal count 212544
Mutex spin waits 201779, rounds 289254, OS waits 5769
RW-shared spins 71644, rounds 1292843, OS waits 28918
RW-excl spins 27933, rounds 1582253, OS waits 41414
Spin rounds per wait: 1.43 mutex, 18.05 RW-shared, 56.64 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 106794202
Purge done for trx's n:o < 106794191 undo n:o < 0 state: running but idle
History list length 3618
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 106794062, not started
MySQL thread id 110715, OS thread handle 0x2b25f6ec2700, query id 5020955
182.71.229.138 ebuser init
SHOW ENGINE INNODB STATUS
---TRANSACTION 106794201, not started
MySQL thread id 8, OS thread handle 0x2b25a2703700, query id 5020954
localhost 127.0.0.1 rdsadmin cleaning up
---TRANSACTION 106028547, not started
MySQL thread id 1, OS thread handle 0x2b25a2681700, query id 0 Waiting for
background binlog tasks
--------
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
748253 OS file reads, 1159722 OS file writes, 187669 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 101, seg size 103, 63021 merges
merged operations:
insert 49746, delete mark 141267, delete 44296
discarded operations:
insert 0, delete mark 0, delete 0
29.05 hash searches/s, 1.85 non-hash searches/s
---
LOG
---
Log sequence number 150636062402
Log flushed up to 150636062402
Pages flushed up to 150636062402
Last checkpoint at 150636062402
Max checkpoint age 216721613
Checkpoint age target 209949063
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
138085 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3021996032; in additional pool allocated 0
Total memory allocated by read views 416
Internal hash tables (constant factor + variable factor)
Adaptive hash index 262562512 (46748264 + 215814248)
Page hash 366056 (buffer pool 0 only)
Dictionary cache 13611078 (11688592 + 1922486)
File system 960472 (812272 + 148200)
Lock system 7306496 (7305416 + 1080)
Recovery system 0 (0 + 0)
Dictionary memory allocated 1922486
Buffer pool size 180216
Buffer pool size, bytes 2952658944
Free buffers 8193
Database pages 158851
Old database pages 58475
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1247738, not young 25033050
0.00 youngs/s, 0.00 non-youngs/s
Pages read 748000, created 43142, written 994180
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 158851, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1025
Database pages 19861
Old database pages 7311
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 98238, not young 2723636
0.00 youngs/s, 0.00 non-youngs/s
Pages read 92864, created 5425, written 135077
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19861, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19853
Old database pages 7308
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 95625, not young 3098260
0.00 youngs/s, 0.00 non-youngs/s
Pages read 89244, created 5544, written 116844
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19853, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19851
Old database pages 7307
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 128795, not young 3109159
0.00 youngs/s, 0.00 non-youngs/s
Pages read 96654, created 5166, written 121945
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19851, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19847
Old database pages 7306
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 305308, not young 4040408
0.00 youngs/s, 0.00 non-youngs/s
Pages read 96897, created 5125, written 135851
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19847, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19852
Old database pages 7308
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 128721, not young 3047134
0.00 youngs/s, 0.00 non-youngs/s
Pages read 94024, created 5765, written 126032
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19852, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19860
Old database pages 7311
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 146912, not young 2641167
0.00 youngs/s, 0.00 non-youngs/s
Pages read 94359, created 5540, written 120390
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19860, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19872
Old database pages 7315
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 96242, not young 3087412
0.00 youngs/s, 0.00 non-youngs/s
Pages read 88868, created 4950, written 119917
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19872, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 22527
Buffer pool size, bytes 369082368
Free buffers 1024
Database pages 19855
Old database pages 7309
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 247897, not young 3285874
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95090, created 5627, written 118124
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 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: 19855, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 9357, id 47444582741760, state: sleeping
Number of rows inserted 1455191, updated 16167028, deleted 1399680, read
1844055639
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 30.83 reads/s
Number of system rows inserted 3, updated 641, deleted 3, read 20758
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.12 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
--
Regards,
Selvam
KnackForge <http://knackforge.com>
