当前位置：Gxlcms > mysql > Slavememoryleakandtriggeroom-killer_MySQL

Slavememoryleakandtriggeroom-killer_MySQL

时间：2021-07-01 10:21:17 帮助过：26人阅读

Slave memory leak and trigger oom-killer_MySQL

  1. Bug Description
  2. We have this problem:
  3. we have set innodb_buffer_pool =80GB on both master and slave , master offer usually workload,
  4. but slave with nothing workload except these slave threads, But with the memory consumption is increasing , 
  5. after a few days , it uses about 120GB of memory(RES) and sometime the machine starts swapping out. 
  6. and in the /var/log/message(loged :Out of memory: Kill process 43817 (mysqld) score 847 or sacrifice 
  7. childKilled process 43817, UID 500, (mysqld) total-vm:126723628kB, anon-rss:125497616kB, file-rss:3388kB)
  8. We have the exact version of PerconaServer on the master (with production workload) and it doesn't 
  9. have this problem
  10. This problem is the same for a few recent versions of Percona server, not just the last one.
  11. we do not user MyISAM tables and just use Innodb Tables;TokuDB is not installed.
  12. Other Information:
  13. this one just OOM last night T-T:
  14. xiean@(none) 11:02> \s--------------mysql Ver 14.14 Distrib 5.6.23-72.1, for Linux (x86_64) 
  15. using EditLine wrapper
  16. Connection id: 252482Current database:Current user: xiean@localhostSSL: Not in useCurrent pager: 
  17. stdoutUsing outfile: ''Using delimiter: ;Server version: 5.6.23-72.1-log Source distributionProtocol 
  18. version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: 
  19. utf8Client characterset: utf8Conn. characterset: utf8UNIX socket: /data/mysql/3307/var/run/mysql.sockUptime:
  20.  10 hours 32 min 9 sec
  21. Threads: 6 Questions: 5888709 Slow queries: 312 Opens: 200 Flush tables: 1 Open tables: 193 Queries per 
  22. second avg: 155.256--------------
  23. CentOS release 6.5 (Final)Linux xxxxxxx 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 
  24. UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
  25. my.cnf-----------------------------------------------------[mysqld]
  26. skip-external-lockingskip-name-resolvedefault-storage-engine = InnoDBcharacter-set-client-handshake = 
  27. FALSEcharacter-set-server = utf8collation-server = utf8_unicode_ciinit_connect='set names utf8'
  28. connect_timeout = 20back_log = 600log_bin_trust_function_creators = 1
  29. binlog_format = rowmax_binlog_size = 512Mbinlog_cache_size = 2Mexpire-logs-days = 7
  30. slave-net-timeout = 20log_slave_updates = 1relay_log_recovery = 1sync_master_info = 1sync_relay_log_info = 
  31. 1master_info_repository = TABLErelay_log_info_repository = TABLE
  32. slow_query_log = 1long_query_time = 1
  33. max_connections = 2000max_user_connections = 1000max_connect_errors = 100000key_buffer_size = 
  34. 64Mmax_allowed_packet = 512Mtable_open_cache = 6144table_definition_cache = 4096sort_buffer_size = 
  35. 512Kread_buffer_size = 512Kread_rnd_buffer_size = 512kjoin_buffer_size = 512Ktmp_table_size = 
  36. 64Mmax_heap_table_size = 128Mquery_cache_type = 0query_cache_size = 0bulk_insert_buffer_size = 
  37. 32Mthread_cache_size = 64thread_stack = 256K
  38. innodb_data_file_path = ibdata1:1024M:autoextendinnodb_buffer_pool_size = 80Ginnodb_buffer_pool_instances = 
  39. 8innodb_additional_mem_pool_size = 16Minnodb_use_sys_malloc = 0innodb_log_file_size = 
  40. 8196Minnodb_log_buffer_size = 64Minnodb_log_files_in_group = 3innodb_lock_wait_timeout = 
  41. 30innodb_spin_wait_delay = 96innodb_thread_concurrency = 0innodb_max_dirty_pages_pct = 
  42. 75innodb_support_xa = 0innodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECTinnodb_read_io_threads = 
  43. 16innodb_write_io_threads = 16innodb_io_capacity = 2000innodb_flush_neighbors = 0innodb_page_size = 
  44. 4Kinnodb_log_block_size = 4096innodb_file_per_table = 1innodb_file_format = Barracudainnodb_file_format_max = 
  45. Barracudainnodb_strict_mode = 1innodb_purge_threads = 2innodb_purge_batch_size = 32innodb_old_blocks_pct = 
  46. 37innodb_old_blocks_time = 1000innodb_change_buffering = allinnodb_stats_on_metadata = off
  47. sync_binlog = 0
  48. innodb_buffer_pool_dump_at_shutdown = 1innodb_buffer_pool_load_at_startup = 1
  49. show engine innodb status\G
  50. =====================================2016-03-22 11:08:07 7f8496af9700 INNODB MONITOR OUTPUT====================
  51. =================Per second averages calculated from the last 3 seconds-----------------BACKGROUND THREAD------
  52. -----------srv_master_thread loops: 35735 srv_active, 0 srv_shutdown, 2389 srv_idlesrv_master_thread log flush
  53.  and writes: 38124----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 27319OS WAIT ARRAY INFO: 
  54.  signal count 694283Mutex spin waits 13779381, rounds 15836207, OS waits 12843RW-shared spins 3653866, rounds
  55.   16984184, OS waits 11004RW-excl spins 562597, rounds 1648229, OS waits 2980Spin rounds per wait: 1.15 mutex,
  56.   4.65 RW-shared, 2.93 RW-excl
  57. ....................................................
  58. ----------------------BUFFER POOL AND MEMORY----------------------
  59. Total memory allocated 107356939316; in additional pool allocated 8441088Total memory allocated by 
  60. read views 496Internal hash tables (constant factor + variable factor)    
  61. Adaptive hash index 2118609544 (1359985288 + 758624256)    
  62. Page hash 42500456 (buffer pool 0 only)    
  63. Dictionary cache 340576340 (339997712 + 578628)    
  64. File system 867904 (812272 + 55632)    
  65. Lock system 849995224 (849992296 + 2928)    
  66. Recovery system 0 (0 + 0)Dictionary memory allocated 578628Buffer pool size 20971512Buffer pool size, 
  67. bytes 85899313152Free buffers 793287Database pages 19993014Old database pages 
  68. 7380256Modified db pages 857786Pending reads 0Pending writes: LRU 0, flush list 0, 
  69. single page 0Pages made young 1500483, not young 7919383.00 youngs/s, 3.67 non-youngs/sPages read 19777369, 
  70. created 365977, written 17055406.33 reads/s, 9.00 creates/s, 255.25 writes/sBuffer pool hit rate 1000 / 1000, 
  71. young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, 
  72. Random read ahead 0.00/sLRU len: 19993014, unzip_LRU len: 0I/O sum[36104]:cur[1320], unzip sum[0]:cur[0]
  73. ----------------------INDIVIDUAL BUFFER POOL INFO-------------------------
  74. BUFFER POOL 0Buffer pool size 2621439Buffer pool size, bytes 10737414144Free buffers 1024Database
  75.  pages 2597173Old database pages 958702Modified db pages 110539Pending reads 0Pending writes: LRU 0, 
  76.  flush list 0, single page 0Pages made young 497074, not young 3051540.67 youngs/s, 3.67 non-youngs/sPages 
  77.  read 2603703, created 46719, written 2711371.00 reads/s, 0.33 creates/s, 17.33 writes/sBuffer pool hit 
  78.  rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without
  79.   access 0.00/s, Random read ahead 0.00/sLRU len: 2597173, unzip_LRU len: 0I/O sum[4513]:cur[165], 
  80.   unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size 2621439Buffer pool size, bytes 10737414144Free 
  81.   buffers 1024Database pages 2597316Old database pages 958754Modified db pages 115355Pending reads 
  82.   0Pending writes: LRU 0, flush list 0, single page 0Pages made young 496041, not young 2381371.00 youngs/s, 
  83.   0.00 non-youngs/sPages read 2593888, created 50425, written 2643540.33 reads/s, 3.00 creates/s, 29.66
  84.    writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, 
  85.    evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 2597316, unzip_LRU len: 0I/O 
  86.    sum[4513]:cur[165], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size 2621439Buffer pool size, 
  87.    bytes 10737414144Free buffers 1024Database pages 2597259Old database pages 958733Modified db pages 
  88.    115748Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 496770, 
  89.    not young 2486471.00 youngs/s, 0.00 non-youngs/sPages read 2595651, created 51694, 
  90.    written 2406831.00 reads/s, 0.00 creates/s, 35.99 writes/sBuffer pool hit rate 1000 / 1000, 
  91.    young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, 
  92.    Random read ahead 0.00/sLRU len: 2597259, unzip_LRU len: 0I/O sum[4513]:cur[165], 
  93.    unzip sum[0]:cur[0]---BUFFER POOL 3Buffer pool size 2621439Buffer pool size, bytes 
  94.    10737414144Free buffers 117082Database pages 2481220Old database pages 915938Modified db pages 
  95.    112864Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 2305, 
  96.    not young 00.00 youngs/s, 0.00 non-youngs/sPages read 2429753, created 51467, 
  97.    written 2489670.00 reads/s, 0.00 creates/s, 40.99 writes/sBuffer pool hit rate 1000 / 1000, 
  98.    young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, 
  99.    Random read ahead 0.00/sLRU len: 2481220, unzip_LRU len: 0I/O sum[4513]:cur[165], 
  100.    unzip sum[0]:cur[0]---BUFFER POOL 4Buffer pool size 2621439Buffer pool size, 
  101.    bytes 10737414144Free buffers 207591Database pages 2390638Old database pages 
  102.    882501Modified db pages 103991Pending reads 0Pending writes: LRU 0, flush list 0, single page 
  103.    0Pages made young 2163, not young 00.33 youngs/s, 0.00 non-youngs/sPages read 2345049, created 45589, 
  104.    written 2144341.00 reads/s, 1.00 creates/s, 32.99 writes/sBuffer pool hit rate 1000 / 1000, young-making 
  105.    rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU
  106.     len: 2390638, unzip_LRU len: 0I/O sum[4513]:cur[165], unzip sum[0]:cur[0]---BUFFER POOL 5Buffer pool size 
  107.     2621439Buffer pool size, bytes 10737414144Free buffers 202652Database pages 2395634Old database pages 
  108.     884332Modified db pages 99353Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made 
  109.     young 2061, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 2354114, created 41520, 
  110.     written 1473470.67 reads/s, 3.67 creates/s, 30.99 writes/sBuffer pool hit rate 1000 / 1000, young-making 
  111. rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU 
  112. len: 2395634, unzip_LRU len: 0I/O sum[4513]:cur[165], unzip sum[0]:cur[0]---BUFFER POOL 6Buffer pool size 
  113. 2621439Buffer pool size, bytes 10737414144Free buffers 209285Database pages 2389036Old database pages 
  114. 881910Modified db pages 96899Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages 
  115. made young 1913, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 2349930, created 39106, written 
  116. 1501741.33 reads/s, 0.67 creates/s, 38.65 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 
  117. 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU 
  118. len: 2389036, unzip_LRU len: 0I/O sum[4513]:cur[165], unzip sum[0]:cur[0]---BUFFER POOL 7Buffer pool 
  119. size 2621439Buffer pool size, bytes 10737414144Free buffers 53605Database pages 2544738Old database pages 
  120. 939386Modified db pages 103037Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made 
  121. young 2156, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 2505281, created 39457, written 1684441.00 
  122. reads/s, 0.33 creates/s, 28.66 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 
  123. 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 2544738, unzip_LRU 
  124. len: 0I/O sum[4513]:cur[165], unzip sum[0]:cur[0]
  125. --------------ROW OPERATIONS--------------
  126. 0 queries inside InnoDB, 
  127. 0 queries in queue1 read views open inside InnoDB0 RW transactions 
  128. active inside InnoDB0 RO transactions active inside InnoDB0 out of 1000 descriptors 
  129. used---OLDEST VIEW---Normal read viewRead view low limit trx n:o 4402678824Read view 
  130. up limit trx id 4402678824Read view low limit trx id 4402678824Read view inpidually stored trx ids:
  131. -----------------Main thread process no. 70096, id 140207583508224, 
  132. state: sleepingNumber of rows inserted 1347486, updated 6484099, deleted 505223, 
  133. read 4665086631106.63 inserts/s, 1017.66 updates/s, 0.00 deletes/s, 1682192.60 reads/s
  134. ----------------------------END OF INNODB MONITOR OUTPUT============================

以上就是Slave memory leak and trigger oom-killer_MySQL的内容，更多相关内容请关注PHP中文网（www.gxlcms.com）！

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！