Slave memory leak and trigger oom-killer

推荐整理分享Slave memory leak and trigger oom-killer，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:,内容如对您有帮助，希望把文章链接给更多的朋友！

Bug Description

We have this problem:

we have set innodb_buffer_pool =GB 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 GB of memory(RES) and sometime the machine starts swapping out. and in the /var/log/message(loged :Out of memory: Kill process (mysqld) score or sacrifice childKilled process , UID , (mysqld) total-vm:kB, anon-rss:kB, file-rss:kB)

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) :> s--------------mysql Ver . Distrib 5.6.-.1, for Linux (x_) using EditLine wrapper

Connection id: Current database:Current user: xiean@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.6.-.1-log Source distributionProtocol version: Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8UNIX socket: /data/mysql//var/run/mysql.sockUptime: hours min 9 sec

Threads: 6 Questions: Slow queries: Opens: Flush tables: 1 Open tables: Queries per second avg: .--------------

CentOS release 6.5 (Final)Linux xxxxxxx 2.6.-.el6.x_ #1 SMP Fri Nov :: UTC x_ x_ x_ GNU/Linux

my.cnf-----------------------------------------------------[mysqld]

skip-external-lockingskip-name-resolvedefault-storage-engine = InnoDBcharacter-set-client-handshake = FALSEcharacter-set-server = utf8collation-server = utf8_unicode_ciinit_connect='set names utf8'connect_timeout = back_log = log_bin_trust_function_creators = 1

binlog_format = rowmax_binlog_size = Mbinlog_cache_size = 2Mexpire-logs-days = 7

slave-net-timeout = log_slave_updates = 1relay_log_recovery = 1sync_master_info = 1sync_relay_log_info = 1master_info_repository = TABLErelay_log_info_repository = TABLE

slow_query_log = 1long_query_time = 1

max_connections = max_user_connections = max_connect_errors = key_buffer_size = Mmax_allowed_packet = Mtable_open_cache = table_definition_cache = sort_buffer_size = Kread_buffer_size = Kread_rnd_buffer_size = kjoin_buffer_size = Ktmp_table_size = Mmax_heap_table_size = Mquery_cache_type = 0query_cache_size = 0bulk_insert_buffer_size = Mthread_cache_size = thread_stack = K

innodb_data_file_path = ibdata1:M:autoextendinnodb_buffer_pool_size = Ginnodb_buffer_pool_instances = 8innodb_additional_mem_pool_size = Minnodb_use_sys_malloc = 0innodb_log_file_size = Minnodb_log_buffer_size = Minnodb_log_files_in_group = 3innodb_lock_wait_timeout = innodb_spin_wait_delay = innodb_thread_concurrency = 0innodb_max_dirty_pages_pct = innodb_support_xa = 0innodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECTinnodb_read_io_threads = innodb_write_io_threads = innodb_io_capacity = innodb_flush_neighbors = 0innodb_page_size = 4Kinnodb_log_block_size = innodb_file_per_table = 1innodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_strict_mode = 1innodb_purge_threads = 2innodb_purge_batch_size = innodb_old_blocks_pct = innodb_old_blocks_time = innodb_change_buffering = allinnodb_stats_on_metadata = off

sync_binlog = 0

innodb_buffer_pool_dump_at_shutdown = 1innodb_buffer_pool_load_at_startup = 1

show engine innodb statusG

=====================================-- :: 7faf INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 3 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: srv_active, 0 srv_shutdown, srv_idlesrv_master_thread log flush and writes: ----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count OS WAIT ARRAY INFO: signal count Mutex spin waits , rounds , OS waits RW-shared spins , rounds , OS waits RW-excl spins , rounds , OS waits Spin rounds per wait: 1. mutex, 4. RW-shared, 2. RW-excl

....................................................

----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated ; in additional pool allocated Total memory allocated by read views Internal hash tables (constant factor + variable factor) Adaptive hash index ( + ) Page hash (buffer pool 0 only) Dictionary cache ( + ) File system ( + ) Lock system ( + ) Recovery system 0 (0 + 0)Dictionary memory allocated Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 3. non-youngs/sPages read , created , written . reads/s, 9. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]----------------------INDIVIDUAL BUFFER POOL INFO-------------------------BUFFER POOL 0Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 3. non-youngs/sPages read , created , written . reads/s, 0. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 3. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 0. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 3Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 0. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 4Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 1. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 5Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 3. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 6Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 0. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]---BUFFER POOL 7Buffer pool size Buffer pool size, bytes Free buffers Database pages Old database pages Modified db pages Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young , not young . youngs/s, 0. non-youngs/sPages read , created , written . reads/s, 0. creates/s, . writes/sBuffer pool hit rate / , young-making rate 0 / not 0 / Pages read ahead 0./s, evicted without access 0./s, Random read ahead 0./sLRU len: , unzip_LRU len: 0I/O sum[]:cur[], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDB0 RW transactions active inside InnoDB0 RO transactions active inside InnoDB0 out of descriptors used---OLDEST VIEW---Normal read viewRead view low limit trx n:o Read view up limit trx id Read view low limit trx id Read view individually stored trx ids:-----------------Main thread process no. , id , state: sleepingNumber of rows inserted , updated , deleted , read . inserts/s, . updates/s, 0. deletes/s, . reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

MySQL 5.6 & 5.7最优配置文件模板(my.ini) Inside君整理了一份最新基于MySQL5.6和5.7的配置文件模板，基本上可以说覆盖%的调优选项，用户只需根据自己的服务器配置稍作修改即可，如InnoDB缓冲池

MYSQL神秘的HANDLER命令与实现方法 MySQL自古以来都有一个神秘的HANDLER命令，而此命令非SQL标准语法，可以降低优化器对于SQL语句的解析与优化开销，从而提升查询性能。看到这里，可能

全面解析Windows下安装 mysql5.7的方法 关于在windows下安装mysql5.7要注意的新坑：5.7版本安装后ROOT账号是有默认的密码的，这个密码在windows下可以在mysql.ini配置文件指定的data文件夹下面，那