Hi,
We are running percona-xtradb-cluster 5.6, and we are experiencing some memory problems from time to time. So far a restart was the only solution I found.
I have 3G allocated to memory buffer:
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 2
The innodb engine status looks like this:
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
2017-12-07 10:06:23 7fb3690e1700 INNODB MONITOR OUTPUT
Per second averages calculated from the last 3 seconds
BACKGROUND THREAD
srv_master_thread loops: 1655915 srv_active, 0 srv_shutdown, 5637776 srv_idle
srv_master_thread log flush and writes: 7293510
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 53195134
OS WAIT ARRAY INFO: signal count 56687384
Mutex spin waits 72499203, rounds 1361471924, OS waits 11228972
RW-shared spins 14622231, rounds 390995632, OS waits 10490135
RW-excl spins 14109990, rounds 840417647, OS waits 12102118
Spin rounds per wait: 18.78 mutex, 26.74 RW-shared, 59.56 RW-excl
TRANSACTIONS
Trx id counter 3839960741
Purge done for trx’s n:o < 3839960741 undo n:o < 0 state: running but idle
History list length 2852
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started
MySQL thread id 1941619, OS thread handle 0x7fb3690e1700, query id 1211959458 localhost root init
show engine innodb status
—TRANSACTION 3835508202, not started
MySQL thread id 1886179, OS thread handle 0x7fb273ce8700, query id 1211547418 10.2.203.68 valil cleaning up
—TRANSACTION 0, not started
MySQL thread id 1886178, OS thread handle 0x7fb369163700, query id 1211547417 10.2.203.68 valil cleaning up
—TRANSACTION 3839960735, not started
MySQL thread id 1, OS thread handle 0x7fb3691e5700, query id 1211959450 Slave has read all relay log; waiting for the slave I/O thread to update it
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
110595766 OS file reads, 1333856377 OS file writes, 36003312 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 65858, seg size 65860, 3485666 merges
merged operations:
insert 14044836, delete mark 300930432, delete 37437707
discarded operations:
insert 17371, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
LOG
Log sequence number 7973454295079
Log flushed up to 7973454295079
Pages flushed up to 7973454295079
Last checkpoint at 7973454295079
Max checkpoint age 80826164
Checkpoint age target 78300347
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
602308662 log i/o’s done, 0.00 log i/o’s/second
BUFFER POOL AND MEMORY
Total memory allocated 3296722944; in additional pool allocated 0
Total memory allocated by read views 424
Internal hash tables (constant factor + variable factor)
Adaptive hash index 140001856 (50999768 + 89002088)
Page hash 1594568 (buffer pool 0 only)
Dictionary cache 27896518 (12751472 + 15145046)
File system 1977808 (812272 + 1165536)
Lock system 7970936 (7969496 + 1440)
Recovery system 0 (0 + 0)
Dictionary memory allocated 15145046
Buffer pool size 196606
Buffer pool size, bytes 3221192704
Free buffers 2048
Database pages 189126
Old database pages 69773
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 47645180, not young 2494521231
0.00 youngs/s, 0.00 non-youngs/s
Pages read 110588897, created 48491789, written 711001857
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 189126, unzip_LRU len: 0
I/O sum[10404]:cur[0], unzip sum[0]:cur[0]
INDIVIDUAL BUFFER POOL INFO
—BUFFER POOL 0
Buffer pool size 98303
Buffer pool size, bytes 1610596352
Free buffers 1024
Database pages 94562
Old database pages 34886
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 23758299, not young 1217773446
0.00 youngs/s, 0.00 non-youngs/s
Pages read 55098024, created 24291393, written 368564268
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 94562, unzip_LRU len: 0
I/O sum[5202]:cur[0], unzip sum[0]:cur[0]
—BUFFER POOL 1
Buffer pool size 98303
Buffer pool size, bytes 1610596352
Free buffers 1024
Database pages 94564
Old database pages 34887
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 23886881, not young 1276747785
0.00 youngs/s, 0.00 non-youngs/s
Pages read 55490873, created 24200396, written 342437589
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 94564, unzip_LRU len: 0
I/O sum[5202]: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. 3488, id 140404475774720, state: sleeping
Number of rows inserted 929252828, updated 307342247, deleted 193739747, read 18770419699
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
END OF INNODB MONITOR OUTPUT
The problem is that the mysql process is using a lot more RAM than this:
ps -aux | grep mysql
root 2876 0.0 0.0 4180 580 ? S Sep13 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 3488 3.6 70.8 6478772 5786208 ? Sl Sep13 4413:09 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root 3685 0.0 0.0 40220 4160 pts/1 S+ 10:05 0:00 mysql -uroot -px xxxxxx osbp -A
Any idea why are there extra more than 2G used?
Thank you,
Alex