Xupeng's blog

圆外之大,心向往之

诡异的 MySQL 内存突增问题

一个稳定运行了大半年的 MySQL 5.0.84 实例,InnoDB buffer pool 为 15GB,正常情况下会使用 17GB 左右物理内存,但是它的物理内存使用量突然在五分钟内飙升至 35GB,导致 kernel 忙于 OOM 而使整个服务器处于假死状态,不得已重启了系统。

MySQL 的错误日志中没有记录下任何信息,也没有内存突增导致内存耗尽时的现场了,如何能够知道是什么原因导致 MySQL 的物理内存使用量骤增的呢?

服务器 OS 为 Gentoo,MySQL 为 5.0.84-r1,这是 Gentoo 官方维护的版本,打了一堆 patch,这些 patch 主要有:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
xupeng@localhost [(none)] mysql> show patches \G
*************************** 1. row ***************************
   File: show_patches.patch
   Name: SHOW PATCHES
Version: 1.0
 Author: Jeremy Cole
License: N/A
Comment: 
*************************** 2. row ***************************
   File: microslow_innodb.patch
   Name: Extended statistics in slow.log
Version: 1.2
 Author: Percona <info @percona.com>
License: GPL
Comment: 
*************************** 3. row ***************************
   File: profiling_slow.info
   Name: profiling from SHOW PROFILE to slow.log
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 4. row ***************************
   File: userstatsv2.patch
   Name: SHOW USER/TABLE/INDEX statistics
Version: V2
 Author: Google
License: GPL
Comment: Added INFORMATION_SCHEMA.*_STATISTICS
*************************** 5. row ***************************
   File: microsec_process.patch
   Name: Adds INFOMATION_SCHEMA.PROCESSLIST with TIME_MS column
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 6. row ***************************
   File: innodb_io_patches.patch
   Name: Cluster of past InnoDB IO patches
Version: 1.1
 Author: Percona
License: GPL
Comment: This patch contains fixed (control_flush_and_merge_and_read, control_io-threads, adaptive_flush)
*************************** 7. row ***************************
   File: mysqld_safe_syslog.patch
   Name: Patch allows redirect output of error.log to syslog-ng
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: Ported from Debian
*************************** 8. row ***************************
   File: innodb_locks_held.patch
   Name: Add locks held, remove locked records in SHOW INNODB STATUS
Version: 1.0
 Author: Baron Schwartz <baron @xaprb.com>
License: GPL
Comment: Bug #29126 fix
*************************** 9. row ***************************
   File: innodb_show_bp.patch
   Name: show innodb buffer pool content
Version: 1.0
 Author: Percona <info @percona.com>
License: GPL
Comment: 
*************************** 10. row ***************************
   File: innodb_check_fragmentation.patch
   Name: Session status to check fragmentation of the last InnoDB scan
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: The names are Innodb_scan_*
*************************** 11. row ***************************
   File: innodb_io_pattern.patch
   Name: Information schema table of InnoDB IO counts for each datafile pages
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: INFORMATION_SCHEMA.INNODB_IO_PATTERN
*************************** 12. row ***************************
   File: innodb_fsync_source.patch
   Name: Information of fsync callers in InnoDB
Version: 1.0
 Author: Google
License: GPL
Comment: 
*************************** 13. row ***************************
   File: innodb_show_hashed_memory.patch
   Name: Adds additional information of InnoDB internal hash table memories in SHOW INNODB STATUS
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 14. row ***************************
   File: innodb_dict_size_limit.patch
   Name: Limit dictionary cache size
Version: 1.0
 Author: Percona
License: GPL
Comment: Variable innodb_dict_size_limit in bytes
*************************** 15. row ***************************
   File: innodb_extra_rseg.patch
   Name: allow to create extra rollback segments
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 16. row ***************************
   File: thread_concurrency_timer_based.patch
   Name: Use InnoDB timer based concurrency throttling (backport from MySQL 5.4.0)
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 17. row ***************************
   File: innodb_use_sys_malloc.patch
   Name: InnoDB uses malloc directly (backport from InnoDB-Plugin)
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 18. row ***************************
   File: innodb_recovery_patches.patch
   Name: Bugfixes and adjustments about recovery process
Version: 1.0
 Author: Percona </info><info @percona.com>
License: GPL
Comment: 
*************************** 19. row ***************************
   File: innodb_split_buf_pool_mutex.patch
   Name: InnoDB patch to fix buffer pool scalability
Version: 1.0
 Author: Yasufumi Kinoshita
License: BSD
Comment: Backport from XtraDB
*************************** 20. row ***************************
   File: innodb_rw_lock.patch
   Name: Fix of InnoDB rw_locks
Version: 1.0
 Author: Yasufumi Kinoshita
License: BSD
Comment: 
20 rows in set (0.00 sec)
</info></baron></info><

MySQL配置如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
[mysqld]
skip-name-resolve
replicate-ignore-db=test
max_connect_errors=999999999
character-set-server = utf8 
default-character-set = utf8
user = mysql                
port = 3308                 
socket = /var/run/mysqld/mysqld-orc.sock
pid-file = /var/run/mysqld/mysqld-orc.pid
log-error = /log/mysql-orc-log/mysqld.err
basedir = /usr              
datadir = /data/mysql-orc   
skip-locking
key_buffer = 256M           
thread_cache = 48           
max_allowed_packet = 12M    
max_connections = 1500      
max_user_connections = 1450 
table_cache = 4096          
sort_buffer_size = 1M       
net_buffer_length = 8K      
read_buffer_size = 1M       
read_rnd_buffer_size = 1M   
myisam_sort_buffer_size = 1M
language = /usr/share/mysql/english
server-id = 33083           
log-slave-updates
skip_slave_start
expire_logs_days = 7
log-bin = /log/mysql-orc-log/binary-logs/log
binlog-ignore-db = test
sync_binlog = 0
relay-log=/log/mysql-orc-log/relay-logs/relay.log
tmpdir = /log/mysql-orc-log/tmp
innodb_file_per_table
innodb_buffer_pool_size = 15G
innodb_additional_mem_pool_size = 20M
innodb_data_home_dir = /data/mysql-orc
innodb_log_arch_dir = /data/mysql-orc
innodb_log_group_home_dir = /data/mysql-orc
innodb_data_file_path = ibdata1:2000M:autoextend
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
set-variable = innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_sync_spin_loops = 50
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 14
wait_timeout = 86400
transaction-isolation = READ-COMMITTED
report-host = treebeard
report-port = 3308
read_only = 1
sql_mode = ""
open_files_limit = 65535
default-storage-engine = innodb
ft_min_word_len = 1
ft_stopword_file = '/etc/mysql/douban.stopwords'
log-slow-queries = /log/mysql-orc-log/mysql-slow.log
long_query_time = 1

Comments