Slave memory leak and trigger Oom-killer_mysql

Source: Internet
Author: User
Tags flush mutex rounds socket percona server

Bug Description

We have this problem:

We have set Innodb_buffer_pool =80GB on both master and slave, master offer usually workload,but with nothing slave Oad except these slave threads, But with the memory consumption are 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, (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 was the same for a few recent versions to Percona server, not just the last one.

We don't 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 min 9 sec

Threads:6 questions:5888709 Slow queries:312 opens:200 Flush tables:1 Open tables:193 queries per second avg:155.25 6
--------------

CentOS Release 6.5 (Final)
Linux xxxxxxx 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 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 "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 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, no 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, no 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, no 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, no 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, no 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, no 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, no 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, no 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, no 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
============================

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.