PolarDB-X 1.0和RDS性能对比之慢DML（跑批）

测试环境：

PolarDB-X 标准版：16C64G(1台)

RDS MySQL5.7： 4C8G(4台)

ECS 压测机： 8C16G(1台)

RDS测试表 单表1亿，PolarDB-X +RDS x4分库分表，单表1亿

直连RDS：

MySQL [sysbenchtest]> show create table sbtest1;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                   |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  KEY `xid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [sysbenchtest]> explain update sbtest1 set k=100 where k=49910688;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | UPDATE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 98517160 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set (0.00 sec)
MySQL [sysbenchtest]> 
MySQL [sysbenchtest]> update sbtest1 set k=100 where k=49910688;
Query OK, 78 rows affected (5 min 43.04 sec)
Rows matched: 78  Changed: 78  Warnings: 0

直连RDS更新78条记录，单表1亿，执行了5min43s。

查看监控CPU消耗：

查看监控IO消耗：

PolarDB-X +RDS x4分库分表：

MySQL [polardb2]> show create table sbtest1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                    |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
	`id` int(11) NOT NULL AUTO_INCREMENT BY GROUP,
	`k` int(11) NOT NULL DEFAULT '0',
	`c` char(120) NOT NULL DEFAULT '',
	`pad` char(60) NOT NULL DEFAULT '',
	KEY `id` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 99999875 DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
MySQL [polardb2]> update sbtest1 set k=100 where k=49910688;
Query OK, 78 rows affected (1 min 13.94 sec)

PolarDB-X +RDS x4分库分表只用了1min13s就查询完毕。

查看监控CPU使用情况：

查看监控IO消耗：

总结：

        对于慢DML(跑批)， PolarDB-X +RDS执行过程中CPU使用率要高一些，IO资源消耗低一些，但是由于数据分布在4个RDS节点，总体耗时较短。