Slave memory leak and trigger oom-killer
bug description
we have this problem:
we have set innodb_buffer_pool =80gb on both master and slave , master offer usually workload,but slave with nothing workload except these slave threads, but with the memory consumption is increasing , after a few days , it uses about 120gb 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 sacrifice 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 = 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
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, 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, 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, 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, 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, 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, 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, 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, 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, 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
============================