Slave memory leak and trigger oom-killer
BugDescription
Wehavethisproblem:
wehavesetinnodb_buffer_pool=80GBonbothmasterandslave,masterofferusuallyworkload,butslavewithnothingworkloadexcepttheseslavethreads,Butwiththememoryconsumptionisincreasing,afterafewdays,itusesabout120GBofmemory(RES)andsometimethemachinestartsswappingout.andinthe/var/log/message
(loged:
Outofmemory:Killprocess43817(mysqld)score847orsacrificechild
Killedprocess43817,UID500,(mysqld)total-vm:126723628kB,anon-rss:125497616kB,file-rss:3388kB)
WehavetheexactversionofPerconaServeronthemaster(withproductionworkload)anditdoesn'thavethisproblem
ThisproblemisthesameforafewrecentversionsofPerconaserver,notjustthelastone.
wedonotuserMyISAMtablesandjustuseInnodbTables;
TokuDBisnotinstalled.
OtherInformation:
thisonejustOOMlastnightT-T:
xiean@(none)11:02>\s
--------------
mysqlVer14.14Distrib5.6.23-72.1,forLinux(x86_64)usingEditLinewrapper
Connectionid: 252482
Currentdatabase:
Currentuser: xiean@localhost
SSL: Notinuse
Currentpager: stdout
Usingoutfile: ''
Usingdelimiter: ;
Serverversion: 5.6.23-72.1-logSourcedistribution
Protocolversion: 10
Connection: LocalhostviaUNIXsocket
Servercharacterset: utf8
Dbcharacterset: utf8
Clientcharacterset: utf8
Conn.characterset: utf8
UNIXsocket: /data/mysql/3307/var/run/mysql.sock
Uptime: 10hours32min9sec
Threads:6Questions:5888709Slowqueries:312Opens:200Flushtables:1Opentables:193Queriespersecondavg:155.256
--------------
CentOSrelease6.5(Final)
Linuxxxxxxxx2.6.32-431.el6.x86_64#1SMPFriNov2203:15:09UTC2013x86_64x86_64x86_64GNU/Linux
my.cnf
-----------------------------------------------------
[mysqld]
skip-external-locking
skip-name-resolve
default-storage-engine=InnoDB
character-set-client-handshake=FALSE
character-set-server=utf8
collation-server=utf8_unicode_ci
init_connect='setnamesutf8'
connect_timeout=20
back_log=600
log_bin_trust_function_creators=1
binlog_format=row
max_binlog_size=512M
binlog_cache_size=2M
expire-logs-days=7
slave-net-timeout=20
log_slave_updates=1
relay_log_recovery=1
sync_master_info=1
sync_relay_log_info=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
slow_query_log=1
long_query_time=1
max_connections=2000
max_user_connections=1000
max_connect_errors=100000
key_buffer_size=64M
max_allowed_packet=512M
table_open_cache=6144
table_definition_cache=4096
sort_buffer_size=512K
read_buffer_size=512K
read_rnd_buffer_size=512k
join_buffer_size=512K
tmp_table_size=64M
max_heap_table_size=128M
query_cache_type=0
query_cache_size=0
bulk_insert_buffer_size=32M
thread_cache_size=64
thread_stack=256K
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_buffer_pool_size=80G
innodb_buffer_pool_instances=8
innodb_additional_mem_pool_size=16M
innodb_use_sys_malloc=0
innodb_log_file_size=8196M
innodb_log_buffer_size=64M
innodb_log_files_in_group=3
innodb_lock_wait_timeout=30
innodb_spin_wait_delay=96
innodb_thread_concurrency=0
innodb_max_dirty_pages_pct=75
innodb_support_xa=0
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=2000
innodb_flush_neighbors=0
innodb_page_size=4K
innodb_log_block_size=4096
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
innodb_strict_mode=1
innodb_purge_threads=2
innodb_purge_batch_size=32
innodb_old_blocks_pct=37
innodb_old_blocks_time=1000
innodb_change_buffering=all
innodb_stats_on_metadata=off
sync_binlog=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
showengineinnodbstatus\G
=====================================
2016-03-2211:08:077f8496af9700INNODBMONITOROUTPUT
=====================================
Persecondaveragescalculatedfromthelast3seconds
-----------------
BACKGROUNDTHREAD
-----------------
srv_master_threadloops:35735srv_active,0srv_shutdown,2389srv_idle
srv_master_threadlogflushandwrites:38124
----------
SEMAPHORES
----------
OSWAITARRAYINFO:reservationcount27319
OSWAITARRAYINFO:signalcount694283
Mutexspinwaits13779381,rounds15836207,OSwaits12843
RW-sharedspins3653866,rounds16984184,OSwaits11004
RW-exclspins562597,rounds1648229,OSwaits2980
Spinroundsperwait:1.15mutex,4.65RW-shared,2.93RW-excl
....................................................
----------------------
BUFFERPOOLANDMEMORY
----------------------
Totalmemoryallocated107356939316;inadditionalpoolallocated8441088
Totalmemoryallocatedbyreadviews496
Internalhashtables(constantfactor+variablefactor)
Adaptivehashindex2118609544(1359985288+758624256)
Pagehash42500456(bufferpool0only)
Dictionarycache340576340(339997712+578628)
Filesystem867904(812272+55632)
Locksystem849995224(849992296+2928)
Recoverysystem0(0+0)
Dictionarymemoryallocated578628
Bufferpoolsize20971512
Bufferpoolsize,bytes85899313152
Freebuffers793287
Databasepages19993014
Olddatabasepages7380256
Modifieddbpages857786
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung1500483,notyoung791938
3.00youngs/s,3.67non-youngs/s
Pagesread19777369,created365977,written1705540
6.33reads/s,9.00creates/s,255.25writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:19993014,unzip_LRUlen:0
I/Osum[36104]:cur[1320],unzipsum[0]:cur[0]
----------------------
INDIVIDUALBUFFERPOOLINFO
----------------------
---BUFFERPOOL0
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers1024
Databasepages2597173
Olddatabasepages958702
Modifieddbpages110539
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung497074,notyoung305154
0.67youngs/s,3.67non-youngs/s
Pagesread2603703,created46719,written271137
1.00reads/s,0.33creates/s,17.33writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2597173,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL1
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers1024
Databasepages2597316
Olddatabasepages958754
Modifieddbpages115355
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung496041,notyoung238137
1.00youngs/s,0.00non-youngs/s
Pagesread2593888,created50425,written264354
0.33reads/s,3.00creates/s,29.66writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2597316,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL2
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers1024
Databasepages2597259
Olddatabasepages958733
Modifieddbpages115748
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung496770,notyoung248647
1.00youngs/s,0.00non-youngs/s
Pagesread2595651,created51694,written240683
1.00reads/s,0.00creates/s,35.99writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2597259,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL3
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers117082
Databasepages2481220
Olddatabasepages915938
Modifieddbpages112864
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung2305,notyoung0
0.00youngs/s,0.00non-youngs/s
Pagesread2429753,created51467,written248967
0.00reads/s,0.00creates/s,40.99writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2481220,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL4
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers207591
Databasepages2390638
Olddatabasepages882501
Modifieddbpages103991
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung2163,notyoung0
0.33youngs/s,0.00non-youngs/s
Pagesread2345049,created45589,written214434
1.00reads/s,1.00creates/s,32.99writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2390638,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL5
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers202652
Databasepages2395634
Olddatabasepages884332
Modifieddbpages99353
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung2061,notyoung0
0.00youngs/s,0.00non-youngs/s
Pagesread2354114,created41520,written147347
0.67reads/s,3.67creates/s,30.99writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2395634,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL6
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers209285
Databasepages2389036
Olddatabasepages881910
Modifieddbpages96899
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung1913,notyoung0
0.00youngs/s,0.00non-youngs/s
Pagesread2349930,created39106,written150174
1.33reads/s,0.67creates/s,38.65writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2389036,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
---BUFFERPOOL7
Bufferpoolsize2621439
Bufferpoolsize,bytes10737414144
Freebuffers53605
Databasepages2544738
Olddatabasepages939386
Modifieddbpages103037
Pendingreads0
Pendingwrites:LRU0,flushlist0,singlepage0
Pagesmadeyoung2156,notyoung0
0.00youngs/s,0.00non-youngs/s
Pagesread2505281,created39457,written168444
1.00reads/s,0.33creates/s,28.66writes/s
Bufferpoolhitrate1000/1000,young-makingrate0/1000not0/1000
Pagesreadahead0.00/s,evictedwithoutaccess0.00/s,Randomreadahead0.00/s
LRUlen:2544738,unzip_LRUlen:0
I/Osum[4513]:cur[165],unzipsum[0]:cur[0]
--------------
ROWOPERATIONS
--------------
0queriesinsideInnoDB,0queriesinqueue
1readviewsopeninsideInnoDB
0RWtransactionsactiveinsideInnoDB
0ROtransactionsactiveinsideInnoDB
0outof1000descriptorsused
---OLDESTVIEW---
Normalreadview
Readviewlowlimittrxn:o4402678824
Readviewuplimittrxid4402678824
Readviewlowlimittrxid4402678824
Readviewindividuallystoredtrxids:
-----------------
Mainthreadprocessno.70096,id140207583508224,state:sleeping
Numberofrowsinserted1347486,updated6484099,deleted505223,read4665086631
106.63inserts/s,1017.66updates/s,0.00deletes/s,1682192.60reads/s
----------------------------
ENDOFINNODBMONITOROUTPUT
============================