Slave memory leak and trigger oom-killer_mysql数据库教程-查字典教程网
Slave memory leak and trigger oom-killer
Slave memory leak and trigger oom-killer
发布时间:2016-12-29 来源:查字典编辑
摘要:BugDescriptionWehavethisproblem:wehavesetinnodb_buffer_pool=80GBonboth...

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 statusG

=====================================

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

============================

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新mysql数据库学习
    热门mysql数据库学习
    编程开发子分类