MySQL发生内存泄漏了怎么办

导读

作为DBA,如何快速确认Linux系统下是否有内存泄漏风险呢?和叶师傅一起探索究竟。

什么是内存泄漏

wikipedia中这样定义内存泄漏的

在计算机科学中,内存泄漏指由于疏忽或错误造成程序未能释放已经不再使用的内存的情况。

内存泄漏并非指内存在物理上的消失,而是应用程序分配某段内存后,由于设计错误,导致在释放该段内存之前就失去了对该段内存的控制,从而造成了内存的浪费。

简言之,我理解的内存泄漏是:

内存申请并用完后,没有被正常释放,而C/C++中又没有内存回收机制,这部分内存无法再次被重复利用,这些内存就像是进入了”内核黑洞“(这是我瞎造的名词),凭空消失了。

内存泄漏可能会导致相应的进程持续申请内存,最终导致触发OOM killer机制,自身可能会被杀掉。如果是MySQL进程的话,在没有设置双1时,还可能会导致数据丢失的风险。

应对内存泄漏的方法有:

  • 重启进程,释放内存;
  • 检查升级代码,修复bug。

如何确认内存泄漏风险

既然内存泄漏有如此风险,我们应该及早发现确定并提前处理,避免潜在风险。

来说说我的个人经验吧,下面的实验所用环境是aliyunOS,大概相对应于RHEL 7/CentOS 7。

[yejr@imysql.com ~]# cat /etc/redhat-release
Alibaba Cloud Enterprise Linux Server release 17.01.2 (Golden Toad)

[yejr@imysql.com ~]# uname -a
Linux zhishutang.com 3.10.0-514.2.3.al7.x86_64 #1 SMP Thu Jan 5 09:53:52 CST 2017 x86_64 x86_64 x86_64 GNU/Linux

首先,当前系统完全空闲,没运行任何内存高消耗进程:

[yejr@imysql.com ~]# free -h
              total        used        free  shared  buff/cache   available
Mem:           3.7G         67M        3.5G    360K        102M        3.5G
Swap:            0B          0B          0B

运行一个人为制造内存泄漏的进程,运行一段时间后,再看下内存使用情况:

[yejr@imysql.com ~]# free -h
              total        used        free  shared  buff/cache   available
Mem:           3.7G        2.2G        1.4G    360K        104M        1.4G
Swap:            0B          0B          0B

看看系统进程内存消耗情况:

[yejr@imysql.com ~]# top
top - 15:28:19 up 76 days, 17 min,  2 users,  load average: 0.05, 0.05, 0.05
Tasks:  65 total,   2 running,  63 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.3 us,  2.4 sy,  0.0 ni, 97.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  3882068 total,  1555840 free,  2219108 used,   107120 buff/cache
KiB Swap:        0 total,        0 free,        0 used.  1483188 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
1582 root      20   0 2161324 2.050g   1008 R  4.0 55.4   0:13.44 memleak   <--- 人为制造的内存泄漏进程
24021 polkitd   20   0  525836   8060    500 S  0.0  0.2   1:11.44 polkitd
12643 root      20   0  129028   5612    820 S  0.0  0.1  10:44.95 AliYunDun

当我们停止该进程后,内存消耗又恢复原样:

[yejr@imysql.com ~]# free -h
              total        used        free  shared  buff/cache   available
Mem:           3.7G         64M        3.5G    360K        104M        3.5G
Swap:            0B          0B          0B

从上面的测试过程中,我们能发现一个特征,那就是:buff/cache 列的值,相对于 used 的值小了很多,至少相差。

再来看多个mysqld进程启动前后的内存消耗对比。

所有mysqld进程未启动前:

[yejr@imysql.com ~]# free -h
              total        used       free    shared  buff/cache   available
Mem:           3.7G         62M       3.1G      360K        552M        3.4G
Swap:            0B          0B         0B

四个mysqld进程全部启动之后:

[yejr@imysql.com ~]# free -h
              total        used        free  shared  buff/cache   available
Mem:           3.7G        2.1G        601M    360K        1.0G        1.4G
Swap:            0B          0B          0B

我们看到,buff/cache 的值是 1.0G,而 used 是 2.1G,也是疑似有内存泄漏风险。

看看几个进程分别占用的内存比例:

[yejr@imysql.com ~]# top
top - 16:15:42 up 76 days,  1:04,  2 users,  load average: 0.00, 0.01, 0.05
Tasks:  67 total,   2 running,  65 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.3 us,  0.3 sy,  0.0 ni, 99.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  3882068 total,   572780 free,  2184924 used,  1124364 buff/cache
KiB Swap:        0 total,        0 free,        0 used.  1470612 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 4952 mysql     20   0 4473076 733244   6188 S  0.0 18.9   0:00.54 mysqld   <--- MySQL 5.6
 4950 mysql     20   0 4575432 524816   9184 S  0.0 13.5   0:00.62 mysqld   <--- MySQL 8.0
 4949 mysql     20   0 4529040 457248  11164 S  0.0 11.8   0:00.49 mysqld   <--- MySQL 5.7
 4951 mysql     20   0 4626516 430492   9876 S  0.0 11.1   0:01.35 mysqld   <--- Percona Server 5.7
24021 polkitd   20   0  525836   8060    500 S  0.0  0.2   1:11.47 polkitd
12643 root      20   0  129028   5836   1044 R  0.0  0.2  11:05.11 AliYunDun

备注:这四个mysqld进程的内存相关参数都一样。

  • innodb_buffer_pool_size = 2867M
  • read_rnd_buffer_size = 4M
  • join_buffer_size = 4M
  • sort_buffer_size = 4M

附录

模拟内存泄漏的C++代码

[yejr@imysql.com~] # cat memleak.cpp
#include <stdlib.h>
#include <iostream>
#include <unistd.h>

using namespace std;

void GetMemory(char *p, int num)
{
    //只申请内存不释放
    p = (char*)malloc(sizeof(char) * num);
}

int main(int argc,char** argv)
{
    char *str = NULL;
    cout<<"Memory leak test!"<<endl;
    //每0.1秒调用一次
    //运行一段时间后记得终止本程序
    while(1){GetMemory(str, 4096); usleep(100); }
    return 0;
}

参考

听说JOIN的列类型一定要一样?

导读

我们在制定表DDL设计规范时,通常都会要求一条:如果有两个表要做JOIN,那么关联条件列类型最好完全一样,才能保证查询效率,真的如此吗?

相信不少朋友主动或被动告知这样一个规范要求(其实我也制定过这个规范),当多表JOIN时,关联条件列类型最好是完全一样的,这样才可以确保查询效率。果真如此吗?

关于多表JOIN的几点结论及建议

为了节省大家时间,我先把几点结论写在前面,没耐心的同学可忽略后面测试过程。
– 当被驱动表的列是字符串类型,而驱动表的列类型是非字符串时,则会发生类型隐式转换,无法使用索引;
– 当被驱动表和驱动表的列都是字符串类型,两边无论是 CHAR 还是 VARCHAR,均不会发生类型隐式转换,都可以使用索引;
– 当被驱动表的列是字符串且其字符集比驱动表的列采用的字符集更小或无法被包含时(latin比utf8mb4小,gb2312 比 utf8mb4 小,另外 gb2312 虽然比 latin1 大,但并不兼容,也不行,详见下方测试 ),则会发生类型隐式转换,无法使用索引;
– 综上,虽然有很多场景下,JOIN列类型不一致也能用到索引,但保不准啥时候就掉坑了。因此,最后回答一下本文题目,JOIN列的类型定义完全一致,包括长度、字符集。

几点说明
– 测试表t1、t2表均为UTF8MB4字符集。
– 字符串类型列char_col默认设置VARCHAR(20)。
– 测试MySQL 版本 5.7.18。

场景1:驱动表列是MEDIUMINT/INT/BIGINT

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引
case1.1 INT INT 可用
case1.2 INT CHAR(20) 不可用
case1.3 INT VARCHAR(20) 不可用
case1.4 INT MEDIUMINT 可用
case1.5 INT BIGINT 可用
case1.6 MEDIUMINT INT 可用
case1.7 MEDIUMINT BIGINT 可用
case1.8 BIGINT MEDIUMINT 可用
case1.9 BIGINT INT 可用

场景2:驱动列是CHAR(20)

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引
case2.1 CHAR(20) CHAR(20) 可用
case2.2 CHAR(20) UTF8 CHAR(20) 可用
case2.3 CHAR(20) CHAR(20) UTF8 不可用
case2.4 CHAR(20) UTF8MB4 CHAR(20) LATIN1 不可用
case2.5 CHAR(20) UTF8MB4 CHAR(20) GB2312 不可用
case2.6 CHAR(20) LATIN1 CHAR(20) UTF8MB4 可用
case2.7 CHAR(20) GB2312 CHAR(20) UTF8MB4 可用
case2.8 CHAR(20) GB2312 CHAR(20) LATIN1 SQL报错,要先转字符集
case2.9 CHAR(20) LATIN1 CHAR(20) GB2312 SQL报错,要先转字符集
case2.10 CHAR(20) VARCHAR(20) 可用
case2.11 CHAR(20) VARCHAR(30) 可用
case2.12 CHAR(20) CHAR(30) 可用
case2.13 CHAR(20) VARCHAR(260) 可用

场景3:驱动列是VARCHAR(20)

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引
case3.1 VARCHAR(20) CHAR(20) 可用
case3.2 VARCHAR(20) VARCHAR(20) 可用
case3.3 VARCHAR(20) VARCHAR(260) 可用

场景4:驱动列是VARCHAR(260)/VARCHAR(270)

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引
case4.1 VARCHAR(260) CHAR(20) 可用
case4.2 VARCHAR(260) VARCHAR(20) 可用
case4.3 VARCHAR(260) VARCHAR(260) 可用
case4.4 VARCHAR(260) VARCHAR(270) 可用
case4.5 VARCHAR(270) VARCHAR(260) 可用

场景5:驱动列是VARCHAR(30)

子场景 驱动表(t1)列 被驱动表(t2)列 是否可用索引
case5.1 CHAR(30) CHAR(20) 可用
case5.2 CHAR(30) VARCHAR(20) 可用

场景6:最后有排序的情况

最后的排序列不属于驱动表

yejr@imysql.com[yejr]> EXPLAIN SELECT * FROM t1 LEFT JOIN
    t2 ON (t1.int_col = t2.int_col) WHERE
    t1.id >= 5000 ORDER BY t2.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 51054
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: int_col
key: int_col
key_len: 4
ref: yejr.t1.int_col
rows: 10
filtered: 100.00
Extra: NULL

小结:当最后的排序列不属于驱动表时,则会生成临时表,且又有额外排序。

最后的排序列属于驱动表

yejr@imysql.com[yejr]> EXPLAIN SELECT * FROM t1 LEFT JOIN
    t2 ON (t1.int_col = t2.int_col) WHERE
    t1.id >= 5000 ORDER BY t1.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 51054
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: int_col
key: int_col
key_len: 4
ref: yejr.t1.int_col
rows: 10
filtered: 100.00
Extra: NULL

小结:当最后的排序列属于驱动表时,则不会生成临时表,也不需要额外排序。

更多的组合测试场景,请各位亲自行完成哈。
附录
测试表DDL

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `int_col` int(20) unsigned NOT NULL DEFAULT '0',
  `char_col` char(20) NOT NULL DEFAULT '',
...
  PRIMARY KEY (`id`),
  KEY `int_col` (`int_col`),
  KEY `char_col` (`char_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `t2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `int_col` int(8) unsigned NOT NULL DEFAULT '0',
  `char_col` char(20) NOT NULL DEFAULT '',
...
  PRIMARY KEY (`id`),
  KEY `int_col` (`int_col`),
  KEY `char_col` (`char_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

修改列字符集定义的DDL样例

/*
- 只修改长度
*/
ALTER TABLE t1 MODIFY char_col 
    VARCHAR(260) NOT NULL DEFAULT '';

/*
- 同时修改字符集
*/
ALTER TABLE t2 MODIFY char_col
    VARCHAR(20) CHARACTER SET UTF8 NOT NULL DEFAULT '';

修改完列定义后,还记得要重新执行 ANALYZE TABLE 重新统计索引信息哟。

yejr@imysql.com[yejr]> ANALYZE TABLE t1, t2;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| yejr.t1 | analyze | status   | OK       |
| yejr.t2 | analyze | status   | OK       |
+---------+---------+----------+----------+

执行测试的SQL样例

/*
- char_col 可以自行替换成 int_col
- 加上 t1.id >= 5000 是为了避免预估扫描数据量太多,变成全表扫描
*/
EXPLAIN SELECT * FROM t1 LEFT JOIN
    t2 ON (t1.char_col = t2.char_col) WHERE
    t1.id >= 5000\G

参考

扎心,MySQL表分区太多了

导读

之前分享过,表分区数量特别大,当有大事务时,可能会导致主从延迟特别严重。

那么如何减少表分区数量呢?

之前老叶分享的意想不到的MySQL复制延迟原因 中提到,规避问题的方法有:

• 删除不用的历史表分区;
• 将大表拆分;
• 将大事务拆分,不要在一个事务里做大批量更新;

针对第一条方法,今天我们来讲讲怎么做表分区的合并,也就是把历史表分区合并到一起。

首先,检查下哪些表的分区数量最大,优先对它们下手:

yejr@imysql.com [information_schema]>SELECT TABLE_NAME, COUNT(*) AS CNT 
    FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
    GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;
+------------+-----+
| TABLE_NAME | CNT |
+------------+-----+
| t1         | 600 |
...
+------------+-----+

可以看到表 t1 的分区共有 600 个之多。

再来看看这个表的分区情况:

yejr@imysql.com[mydb]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mydate` date NOT NULL,
...
  PRIMARY KEY (`id`,`mydate`),
....
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
PARTITION BY RANGE  COLUMNS(`mydate`)
(
 PARTITION p20160101 VALUES LESS THAN ('2016-01-01’) ENGINE = InnoDB,
 PARTITION p20160102 VALUES LESS THAN ('2016-01-02’) ENGINE = InnoDB,
...
 PARTITION p20170630 VALUES LESS THAN ('2017-06-30') ENGINE = InnoDB)
1 row in set (0.00 sec)

再看看这个表各个分区的数据量分布:

yejr@imysql.com[information_schema]> SELECT PARTITION_NAME, TABLE_ROWS FROM 
    PARTITIONS WHERE TABLE_NAME = ‘t1’;
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p20160101      |    8391013 |
| p20160102      |    7749149 |
| p20160103      |    8308234 |
....
| p20170729      |          0 |
| p20170730      |          0 |
+----------------+------------+

每天就要生成700-800万行数据,这个量是够大的了。

现在,我们把2016年的所有分区,按照每月一个分区(之前是每天一个分区),合并在一起。

yejr@imysql.com[mydb]> ALTER TABLE t1 REORGANIZE PARTITION
    p20160101,p20160102...p20160131 into (
    partition p201601 VALUES LESS THAN ('2016-01-31'));
Query OK, 0 rows affected (9 hours 56 min 55.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

亲娘啊,要跑9个多小时,好可怕。

合并完分区后,还记得要执行 ANALYZE TABLE 哟,及时更新统计信息。在有大量表分区的情况下,执行 ANALYZE TABLE 可能会略慢,要有耐心,在另一篇分享里 XX 也介绍过了。

我们可以用下面方法构造批量执行表分区合并的SQL指令:

yejr@imysql.com[information_schema]> SELECT TABLE_NAME,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA,
    '.' ,TABLE_NAME, ' REORGANIZE PARTITION ',
    GROUP_CONCAT(PARTITION_NAME),
    ' INTO (PARTITION P2017 VALUES LESS THAN (''2018-01-01''));')
    FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
    GROUP BY TABLE_SCHEMA, TABLE_NAME;

SQL中的条件请自行替换。

当然,除了合并分区,我们还可以利用MySQL 5.7支持 分区 & 物理表 直接交换新特性,将历史分区交换到外部物理表,再归档到历史库里。这个方案对在线数据库影响很小,优先推荐此法。

yejr@imysql.com[mydb]> ALTER TABLE t1 EXCHANGE PARTITION p20160101 WITH TABLE t1_20160101;

备注:一个分区只能交换到一个外部物理表中,不能将多个分区交换到同一个表。

最后,如果哪天boss抽风要求把原来的分区打散,重新变成每天一个分区,咋整呢?

其实不难啊,让老叶手把手教你

yejr@imysql.com[mydb]> ALTER TABLE t1  REORGANIZE PARTITION
    p201707 INTO (partition p20170701 VALUES LESS THAN ('2017-07-02’) ,
    partition p20170702 VALUES LESS THAN ('2017-07-03’) ,
...
    partition p20170731 VALUES LESS THAN ('2017-08-01’));

最后祝大家的表分区越来越多,嘿,别打我,快溜~

FAQ系列 – 调用存储过程时报错 Illegal mix of collations

导读

执行存储过程,提示 ERROR 1267 错误,咋整?

其实这是一篇旧文,只不过最近好几位朋友都遇到了,于是翻出来重发。

问题

调用存储过程时,发生报错,信息如下:

ERROR 1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

很明显嘛,这是字符集方面的问题。

解决

先检查数据表,确认字符集是 gb2312 没错。

再检查连接字符集(character_set_client、character_set_connection),服务器端字符集(character_set_server)也全都是 gb2312。

怀疑是字段的字符集有问题,修改成 gb2312,还是报错。

后来创建一个临时变量,设定其字符集为 gb2312,再调用存储过程,仍然不行

set @tmp_str1 = ‘abcde’ collate gb2312_chinese_ci;

有点糊涂之际,突然想起来数据库(schema)的字符集可能不是 gb2312。

检查了下,果然,schema的字符集是 latin1 的,修改成 gb2312 再执行存储过程就正常了。

ALTER DATABASE `yejr` CHARACTER SET gb2312;

建议

遇到字符集问题时,应从服务器端(character_set_server),到数据库(character_set_database),到数据表(table),然后客户端(character_set_client)、连接层(character_set_connection)、结果层(character_set_results)的字符集都检查一遍,确认是否都一致。

默认地,创建新 schema 时,它的字符集是从 character_set_server 直接继承的。
创建新 table 时从 schema 的字符集直接继承的,table 中的 fields 则从 table 继承,各层逐级继承。

但创建 procedure / function 时,则是从 character_set_database(而非 character_set_server) 直接继承的。个别情况下,我们创建 db 时,可能没注意到这点,就可能遇到本例的问题了。

因此,最好是在所有的环节,从头到尾,全都用同一套字符集,不要混合着用,保不准哪天就玩飞了~

UTF8字符集的表可以直接转UTF8MB4吗

导读

我的小密圈里有读者提到一个线上库是utf8字符集,想问怎样将其转成utf8mb4

问题

我的小密圈“「老叶茶馆」铁粉圈”上有读者提问:金** 提问:叶老师,有一个MySQL库是utf8的,不大,4g多,怎样转成utf8mb4.可以有一定的停机时间。

解读

utf8是utf8mb4的子集,一般情况下,应该是可以直接修改表字符集的。
修改字符集的几种方法

方法一

  1. 修改表默认字符集
mysql> alter table j1 default character set utf8mb4;
  1. 随后再修改所有字符型列的字符集
mysql> alter table j1 modify name varchar(20) character set utf8mb4 not null default '';

方法二

也是执行ALTER TABLE来修改,但有更简单的解法

mysql> alter table j1 convert to character set utf8mb4;

备注

上面两种方法,其实是有区别的。

  • 采用方法一,如果遇到某个列字符集转换完后字节数超限了,会提示错误。
  • 而采用方法二,如果遇到某个列字符集转换完后字节数超限了,则会将这个列数据类型转换成可以容纳更大长度的类型,比如从 TEXT 转成 LONGTEXT 等。

方法三

如果不放心,可以用mysqldump逻辑备份方式,用utf8mb4字符集把数据备份出来,新建表,恢复回去,应该也可以的。

结论

想从小字节数(2字节/3字节)字符集(gb2312、utf8)转换到大字节数(4字节)字符集(utf8mb4),是可以直接转换的。
相反,想从大字节数字符集转成小的,则会有风险,例如字符串被截断等。

案例测试

yejr@imysql.com [test]>set names gb2312;

yejr@imysql.com [test]>show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(2) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

yejr@imysql.com [test]>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|  232 | 你我 |
|   47 | 你我 |
|    3 | 你我 |
|    5 | 你我 |
|    8 | 你我 |
|   -1 | 你我 |
+------+--------+

//直接修改字符集
yejr@imysql.com [test]>alter table t1 convert to character set utf8mb4;

//把客户端工具的终端字符集从gb2312改成utf8
//同时修改mysql客户端字符集,重新读取数据
yejr@imysql.com [test]>set names utf8mb4;

yejr@imysql.com [test]>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|  232 | 你我 |
|   47 | 你我 |
|    3 | 你我 |
|    5 | 你我 |
|    8 | 你我 |
|   -1 | 你我 |
+------+--------+

可以看到,是可以直接转换的。

当然了,生产环境中,如果也想这么做,最好还是先在测试环境进行更严格的测试演练,确保无误后再实施。

参考

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。