Mysql – thesql memory usage keeps growing without visible reason

mariadbmemoryMySQL

We have LXC container running MariaDB.
It's dedicated to mysql use.

But for some reason it keeps crashing mysqld regularly when the memory peaks even though mysql does not seem to be doing anything much. Memory usage keeps growing slowly until mysql crashes.

I'm thinking it's a remnant of forge script somewhere. We removed the nginx and postgres that forge used to create the cluster. No need for those on dedicated DB server.

SHOW ENGINE INNODB STATUS;

| InnoDB |      | 
=====================================
2017-05-19 09:12:27 7fe4f56bc700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 3000 srv_active, 0 srv_shutdown, 20 srv_idle
srv_master_thread log flush and writes: 3020
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11623
OS WAIT ARRAY INFO: signal count 174000
Mutex spin waits 195205, rounds 178858, OS waits 3736
RW-shared spins 57572, rounds 308469, OS waits 4515
RW-excl spins 22013, rounds 427244, OS waits 2373
Spin rounds per wait: 0.92 mutex, 5.36 RW-shared, 19.41 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 299252821
Purge done for trx's n:o < 299252819 undo n:o < 0 state: running but idle
History list length 48
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 602, OS thread handle 0x7fe4f56bc700, query id 1388395 localhost forge init
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 578, OS thread handle 0x7fe4f56fe700, query id 885293 192.168.14.12 forge cleaning up
---TRANSACTION 299251342, not started
MySQL thread id 501, OS thread handle 0x7fe4f5740700, query id 1382283 192.168.14.12 forge cleaning up

...

MySQL thread id 8, OS thread handle 0x7fe8794cb700, query id 1384304 192.168.14.12 forge cleaning up
---TRANSACTION 299251627, not started
MySQL thread id 2, OS thread handle 0x7fe879591700, query id 1383374 192.168.14.12 forge cleaning up
---TRANSACTION 299251397, not started
MySQL thread id 6, OS thread handle 0x7fe87950d700, query id 1382507 192.168.14.12 forge cleaning up
---TRANSACTION 299251798, not started
MySQL thread id 4, OS thread handle 0x7fe87954f700, query id 1384008 192.168.14.12 forge cleaning up
---TRANSACTION 298962435, not started
MySQL thread id 1, OS thread handle 0x7fe8795b2700, query id 0 Waiting for requests
---TRANSACTION 299252820, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 0
MySQL thread id 579, OS thread handle 0x7fe4f571f700, query id 1388394 192.168.14.12 forge Sending data
select * from `rebates` where `user_id` = 'aced8584-38c4-47ce-8822-e59ba73b0c7f' and `rebatestatus` = '0' and `rebates`.`deleted_at` is null
Trx read view will not see trx with id >= 299252821, sees < 299252821
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 627, free list len 3411, seg size 4039, 39712 merges
merged operations:
 insert 51976, delete mark 25, delete 5
discarded operations:
 insert 0, delete mark 0, delete 0
66892.34 hash searches/s, 3647.78 non-hash searches/s

---
LOG
---
Log sequence number 97482534696
Log flushed up to   97482534696
Pages flushed up to 97426813684
Last checkpoint at  97426779284
Max checkpoint age    848635454
Checkpoint age target 822115597
Modified age          55721012
Checkpoint age        55755412
0 pending log writes, 0 pending chkp writes
121101 log i/o's done, 90.58 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 12370575360; in additional pool allocated 0
Total memory allocated by read views 59176
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 1041590304  (186996664 + 854593640)
    Page hash           1461976 (buffer pool 0 only)
    Dictionary cache    50011652    (46750576 + 3261076)
    File system         1114952     (812272 + 302680)
    Lock system         29400808    (29219368 + 181440)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 3261076
Buffer pool size        720888
Buffer pool size, bytes 11811028992
Free buffers            18393
Database pages          650335
Old database pages      240144
Modified db pages       84859
Percent of dirty pages(LRU & free pages): 12.690
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 39535, not young 153840
7.17 youngs/s, 12.25 non-youngs/s
Pages read 652480, created 1775, written 26596
14.83 reads/s, 0.92 creates/s, 13.25 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: 650335, unzip_LRU len: 0
I/O sum[8288]:cur[32], unzip sum[0]:cur[0]

Best Answer

How much RAM do you have? What is the value of innodb_buffer_pool_size? The latter should be about 70% of the former.

Did you change any other settings in my.cnf? If so, beware -- you could be causing the crash.

Memory will increase until the buffer_pool is full size. Other 'caches' will also grow until full size. At that point, various smaller allocations will come and go. You should never run out of memory. If you do, then some setting(s) is too large.

I mentioned only one particular setting because that is the most useful for a dedicated mysql server. Leaving the rest alone is best practice until some particular need shows that something else needs tuning. This is rare.

Related Question