Linux Ext4 文件系统对 MySQL 性能的影响

最近要做一个 MySQL 数据库迁移,就是将一个 MySQL 中的数据迁移到另外一个 MySQL 数据库中。我们碰到了一个 Ext4 文件系统 barrier 的问题。Linux Ext4 文件系统默认开启了 barrier,我们可以在 man mount 里看到下面的这段话

The ext4 filesystem enables write barriers by default.

默认开启的 barrier=1 会极大的影响 MySQL 的性能,约三倍左右。
我做了一个实验测试 barrier=1 对 MySQL 性能的影响。

首先测试环境的准备

CentOS 6.x, MySQL 5.6

CREATE TABLE `dim_content_stream` (
  `stream_id` int(11) NOT NULL,
  `stream_name` varchar(500) DEFAULT NULL,
  `stream_url` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
  `stream_type` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `stream_length` int(11) DEFAULT NULL,
  `game_id` bigint(20) DEFAULT NULL,
  `program_id` bigint(20) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `stream_last_hb_utc` bigint(20) unsigned DEFAULT NULL COMMENT 'seconds from 1970',
  `stream_first_hb_utc` bigint(20) unsigned DEFAULT '0' COMMENT 'seconds from 1970',
  PRIMARY KEY (`stream_id`),
  KEY `game_id` (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用来导入到该表的数据为一个 CSV 文件,1427690 行,约 330MB,导入到数据库之后,数据库的大小也差不多是 330MB。

测试的步骤

导入数据库的语句

mysql> load data infile '/tmp/dim_content_stream_history' 
  into table dim_content_stream 
  FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' 
  (stream_id, stream_name, stream_url, stream_type, stream_length, 
  game_id, program_id, product_id, stream_last_hb_utc, stream_first_hb_utc);

当 barrier=1 的时候,我们将数据导入 MySQL,结果如下所示

Query OK, 1427690 rows affected (1 min 19.60 sec)
Records: 1427690  Deleted: 0  Skipped: 0  Warnings: 0

将 barrier 设为 0

sudo mount -o remount,barrier=0 /dev/mapper/VolGroup-lv_home /home

重复前面的步骤,重新导入数据库,结果如下所示

Query OK, 1427690 rows affected (22.24 sec)
Records: 1427690  Deleted: 0  Skipped: 0  Warnings: 0
结论

从上面的实验结果可以看出 barrier=0 时,MySQL 的性能要比 barrier=1 时快 3 倍之多。因此对于一些不重要的数据库我们应该将 barrier 设为 0 以提供性能。

另外我们也可以看出在做大量数据快速导入 MySQL 时候,load data infile 语句可以提供非常快的速度。即便该表有一个 primary key 和一个普通的 key 时,还能达到这么快的速度: 63736 row/s,相当于每秒导入 14.7MB 的数据。

更多的测试

做到这里我就另外再加了两个测试
1. 删除 index
删除两个 index 之后,重新进行了一次导入操作,结果如下,可以看出导入速度和有两个key时是差不多的,相差仅 19%。(这里需要注意的是 InnoDB 不支持禁用索引)

alter table dim_content_stream drop index game_id;
alter table dim_content_stream drop primary index;
Query OK, 1427690 rows affected (18.57 sec)
Records: 1427690  Deleted: 0  Skipped: 0  Warnings: 0

2. 使用 MyISAM,并且不加索引

alter table dim_content_stream engine=myisam;
Query OK, 1427690 rows affected (5.95 sec)
Records: 1427690  Deleted: 0  Skipped: 0  Warnings: 0

这里我们可以看到当使用 MyISAM 并且不加索引时,这导入速度是极快的,达到了 56.6MB/s。这已经相当于磁盘的写入速度了,如下我们的磁盘写入速度也就只有 93.7MB/s。

[qos@camphor ~]$ dd bs=64k count=4k if=/dev/zero of=test conv=fdatasync
4096+0 records in
4096+0 records out
268435456 bytes (268 MB) copied, 2.86356 s, 93.7 MB/s

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注