Slave memory leak and trigger oom-killer, leakoom-killer
Bug Description
We have this problem:
We have set innodb_buffer_pool = 80 GB on both master and slave, master offer usually workload, but slave with nothing workload handle T these slave threads, But with the memory consumption is increasing, after a few days, it uses about 120 GB of memory (RES) and sometime the machine starts swapping out. and in the/var/log/message
(Loged:
Out of memory: Kill process 43817 (mysqld) score 847 or sacrifle ice child
Killed process 43817, UID 500, (mysqld) total-vm: 126723628kB, anon-rss: 125497616kB, file-rss: 3388kB)
We have the exact version of PerconaServer on the master (with production workload) and it doesn' t have this problem
This problem is the same for a few recent versions of Percona server, not just the last one.
We do not user MyISAM tables and just use Innodb Tables;
TokuDB is not installed.
Other Information:
This one just OOM last night T-T:
Xiean @ (none) 11: 02> \ s
--------------
Mysql Ver 14.14 Distrib 5.6.23-72.1, for Linux (x86_64) using EditLine wrapper
Connection id: 252482
Current database:
Current user: xiean @ localhost
SSL: Not in use
Current pager: stdout
Using outfile :''
Using delimiter :;
Server version: 5.6.23-72.1-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket:/data/mysql/3307/var/run/mysql. sock
Uptime: 10 hours 32 min 9 sec
Threads: 6 Questions: 5888709 Slow queries: 312 Opens: 200 Flush tables: 1 Open tables: 193 Queries per second avg: 155.256
--------------
CentOS release 6.5 (Final)
Linux xxxxxxx 2.6.32-431. el6.x86 _ 64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/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 = 'set names utf8'
Connect_timeout = 20
Back_log = 600
Log_bin_trust_function_creators = 1
Binlog_format = row
Max_binlog_size = 512 M
Binlog_cache_size = 2 M
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_connection= 1000
Max_connect_errorrs = 100000
Key_buffer_size = 64 M
Max_allowed_packet = 512 M
Table_open_cache = 6144
Table_definition_cached = 4096
Sort_buffer_size = 512 K
Read_buffer_size = 512 K
Read_rnd_buffer_size = 512 k
Join_buffer_size = 512 K
Tmp_table_size = 64 M
Max_heap_table_size = 128 M
Query_cache_type = 0
Query_cache_size = 0
Bulk_insert_buffer_size = 32 M
Thread_cache_size = 64
Thread_stack = 256 K
Innodb_data_file_path = ibdata1: 1024 M: autoextend
Innodb_buffer_pool_size = 80G
Innodb_buffer_pool_instances = 8
Innodb_additional_mem_pool_size = 16 M
Innodb_use_sys_malloc = 0
Innodb_log_file_size = 8196 M
Innodb_log_buffer_size = 64 M
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 = 4 K
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
Show engine innodb status \ G
============================================
2016-03-22 11:08:07 7f8496af9700 INNODB MONITOR OUTPUT
============================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
Srv_master_thread loops: 35735 srv_active, 0 srv_shutdown, 2389 srv_idle
Srv_master_thread log flush and writes: 38124
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 27319
OS WAIT ARRAY INFO: signal count 694283
Mutex spin waits 13779381, rounds 15836207, OS waits 12843
RW-shared spins 3653866, rounds 16984184, OS waits 11004
RW-excl spins 562597, rounds 1648229, OS waits 2980
Spin rounds per wait: 1.15 mutex, 4.65 RW-shared, 2.93 RW-excl
........................................ ............
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 107356939316; in additional pool allocated 8441088
Total memory allocated by read views 496
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2118609544 (1359985288 + 758624256)
Page hash 42500456 (buffer pool 0 only)
Dictionary cache 340576340 (339997712 + 578628)
File system 867904 (812272 + 55632)
Lock system 849995224 (849992296 + 2928)
Recovery system 0 (0 + 0)
Dictionary memory allocated 578628
Buffer pool size 20971512
Buffer pool size, in bytes 85899313152
Free buffers 793287
Database pages 19993014
Old database pages 7380256
Modified db pages 857786
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1500483, not young 791938
3.00 youngs/s, 3.67 non-youngs/s
Pages read 19777369, created 365977, written 1705540
6.33 reads/s, 9.00 creates/s, 255.25 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: 19993014, unzip_LRU len: 0
I/O sum [36104]: cur [1320], unzip sum [0]: cur [0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
--- Buffer pool 0
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 1024
Database pages 2597173
Old database pages 958702
Modified db pages 110539
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 497074, not young 305154
0.67 youngs/s, 3.67 non-youngs/s
Pages read 2603703, created 46719, written 271137
1.00 reads/s, 0.33 creates/s, 17.33 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: 2597173, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 1
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 1024
Database pages 2597316
Old database pages 958754
Modified db pages 115355
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 496041, not young 238137
1.00 youngs/s, 0.00 non-youngs/s
Pages read 2593888, created 50425, written 264354
0.33 reads/s, 3.00 creates/s, 29.66 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: 2597316, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 2
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 1024
Database pages 2597259
Old database pages 958733
Modified db pages 115748
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 496770, not young 248647
1.00 youngs/s, 0.00 non-youngs/s
Pages read 2595651, created 51694, written 240683
1.00 reads/s, 0.00 creates/s, 35.99 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: 2597259, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 3
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 117082
Database pages 2481220
Old database pages 915938
Modified db pages 112864
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2305, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2429753, created 51467, written 248967
0.00 reads/s, 0.00 creates/s, 40.99 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: 2481220, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 4
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 207591
Database pages 2390638
Old database pages 882501
Modified db pages 103991
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2163, not young 0
0.33 youngs/s, 0.00 non-youngs/s
Pages read 2345049, created 45589, written 214434
1.00 reads/s, 1.00 creates/s, 32.99 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: 2390638, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 5
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 202652
Database pages 2395634
Old database pages 884332
Modified db pages 99353
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2061, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2354114, created 41520, written 147347
0.67 reads/s, 3.67 creates/s, 30.99 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: 2395634, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 6
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 209285
Database pages 2389036
Old database pages 881910
Modified db pages 96899
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1913, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2349930, created 39106, written 150174
1.33 reads/s, 0.67 creates/s, 38.65 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: 2389036, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--- Buffer pool 7
Buffer pool size 2621439
Buffer pool size, in bytes 10737414144
Free buffers 53605
Database pages 2544738
Old database pages 939386
Modified db pages 103037
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2156, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2505281, created 39457, written 168444
1.00 reads/s, 0.33 creates/s, 28.66 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: 2544738, unzip_LRU len: 0
I/O sum [4513]: cur [165], unzip sum [0]: cur [0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
--- Oldest view ---
Normal read view
Read view low limit trx n: o 4402678824
Read view up limit trx id 4402678824
Read view low limit trx id 4402678824
Read view individually stored trx ids:
-----------------
Main thread process no. 70096, id 140207583508224, state: sleeping
Number of rows inserted 1347486, updated 6484099, deleted 505223, read 4665086631
106.63 inserts/s, 1017.66 updates/s, 0.00 deletes/s, 1682192.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
======================================