基于开源应用快速构建HTAP系统

原标题：基于开源应用快速构建HTAP系统

利用ProxySQL、MySQL、ClickHouse快速构建HTAP系统

1. 关于ClickHouse

企业里随着数据量的增加，以及日趋复杂的分析性业务需求，主要适用于OLTP场景的MySQL压力越来越大。多年前还能免费试用的infobright社区版也早就销声匿迹，infinidb被MariaDB收入囊中之后改头换面变成ColumnStore，但最近几年发展的平平淡淡，都不是理想的OLAP方案。

战斗民族出品的ClickHouse这几年则是风头正劲，国内用户也越来越多，几大公有云上也提供相应的产品服务，是目前市面上最快的OLAP数据库，性能远超Vertica、Sybase IQ等。关于ClickHouse的性能也可以关注我以前的一份测试报告： ClickHouse性能测试 。

ClickHouse对MySQL的兼容性也很好，除了很多语法相同或接近外，甚至还可以利用MySQL客户端（协议）连接到ClickHouse。

ClickHouse 可以挂载为 MySQL 的一个从库 ，先全量再增量的实时同步 MySQL 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 OLTP 和 OLAP 的融合从此不再头疼。目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。只需要安装最新版本的ClickHouse就可以体验到这个新特性了，不过该特性还处于experimental阶段，还在不断完善中。

2. 关于ProxySQL

ProxySQL是一款强大的中间件为MySQL的架构提供了有力的支持，支持传统主从复制、半同步复制、MGR、PXC等多种MySQL架构，还支持故障自动检测和切换、连接池、读写分离、日志记录、监控、集群部署等多项实用功能。当然了，ProxySQL最大的缺点是性能损失较大，预计至少有20% ~ 30%的性能损失，因此如果是在高性能场景下可能不太合适。不过可以考虑通过分库分表等方式降低单节点压力，充分发挥ProxySQL的集群功能。

3. 构建HTAP系统

ClickHouse和ProxySQL的安装本文不再赘述，直接开始动手构建HTAP系统。下面是整体架构示意图

3.1 将ClickHouse配置成为MySQL的从库

登入ClickHouse后，执行下面的命令启用新特性：

clickhouse 🙂 SET allow_experimental_database_materialize_mysql = 1;

在ClickHouse中，创建一个复制通道，即可构建一个MySQL复制从库，例如：

clickhouse 🙂 CREATE DATABASE testENGINE = MaterializeMySQL( ‘172.24.10.10:3306’, ‘test’, ‘repl’, ‘repl’);

clickhouse 🙂 use test;

clickhouse 🙂 show tables;

┌─name─────┐

│ sbtest1 │

│ sbtest10 │

│ sbtest11 │

…

32 rows inset. Elapsed: 0.006 sec.

首次创建复制通道后，ClickHouse会快速从MySQL主库读取所有数据并应用，可以查看数据复制的进度：

[[email protected]] # cat metadata/sbtest/.metadata

Version: 2

Binlog File: binlog.001496

Executed GTID: 097ee9f2-2ded-11eb-9211-e4434ba52b50:1-952676723

Binlog Position: 789663343

Data Version: 2

提醒：在这里，我设置的是主从复制专用账号。相对于普通的主从复制账号，用于ClickHouse从库的账号至少还要加上只读权限，例如：

[[email protected]]> show grants forrepl;

+————————————————————————–+

| Grants forrepl@% |

+————————————————————————–+

| GRANT RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `repl`@`%` |

| GRANT SELECT ON ` test`.* TO `repl`@`%` |

+————————————————————————–+

ClickHouse的MaterializeMySQL引擎从MySQL复制数据的速度非常之快，甚至比MySQL原生的从库还要更快，大家可以亲身感受体验下。

接下来，再在ClickHouse中创建一个业务账号，以及一个服务监控账号（用于ProxySQL对后端服务的监控）。编辑ClickHouse的配置文件 users.xml，增加两个用户：

<app_user>

<password>app_user</password>

<networks incl= “networks”replace= “replace”>

<ip>::/0</ip>

</networks>

<profile>default</profile>

<quota>default</quota>

</app_user>

<monitor>

<password>monitor</password>

<networks incl= “networks”replace= “replace”>

<ip>::/0</ip>

</networks>

<profile>default</profile>

<quota>default</quota>

</monitor>

我只设置了简单密码，也没有修改其权限和quota模式，仅演示用，线上生产环境请自行调整以合规。

3.2 配置ProxySQL

配置mysql_servers表，增加两条记录，并使配置生效：

proxysql> insert into mysql_servers(hostgroup_id, hostname, port) values( ‘0’, ‘172.24.10.10’, ‘3306’);

proxysql> insert into mysql_servers(hostgroup_id, hostname, port) values( ‘1’, ‘172.24.10.11’, ‘9004’);

proxysql> save mysql servers to disk; load mysql servers to run;

其中，172.24.10.10:3306 是MySQL主库，172.24.10.11:9004是ClickHouse从库，9004端口是ClickHouse里给MySQL客户端连接专用的，可以用MySQL客户端（协议）连接进来执行各种操作。

hostgroup分别为0和1， 0用于读写组，1用于只读组。

配置mysql_users表，加入业务账号和监控账号：

proxysql> select username,password,active from mysql_users;

+———–+———-+——–+

| username | password | active |

+———–+———-+——–+

| app_user | app_user | 1 |

| monitor | monitor | 1 |

+———–+———-+——–+

proxysql> save mysql users to disk; load mysql users to runtime;

配置mysql_query_rules表，这个是关键，用于判断将哪些SQL转发到MySQL主库，哪些转发到ClickHouse：

proxysql> select rule_id, active, match_pattern,destination_hostgroup from mysql_query_rules;

+———+——–+————————-+———————–+

| rule_id | active | match_pattern | destination_hostgroup |

+———+——–+————————-+———————–+

| 1 | 1 | ^SELECT.*+CLICKHOUSE.* | 1 |

+———+——–+————————-+———————–+

proxysql> save mysql query rules to disk; load mysql query rules to run;

上述规则的意思是，当SELECT语句中包含 “+CLICKHOUSE” 关键字时，就会自动转发到 ClickHouse 后端去处理，其余的都发送到MySQL后端处理。例如下面这两条SQL就会分别转发到MySQL和ClickHouse后端：

#SQL #1

[[email protected]]> SELECT * FROM sbtest1 WHERE id=1;

#SQL #2

[[email protected]]> SELECT /*+CLICKHOUSE*/ * FROM sbtest1 WHERE id=1;

第二条SQL利用MySQL的注释语法巧妙地实现规则HINT。

查询 stats_mysql_query_digest 表的结果予以确认：

proxysql> select hostgroup, schemaname, username, digest, digest_text from stats_mysql_query_digest;

+———–+————+———-+——————–+———————————-+

| hostgroup | schemaname | username | digest | digest_text |

+———–+————+———-+——————–+———————————-+

| 0 | sbtest | app_user | 0x5662D7CF0442E794 | select * from sbtest1 whereid=? |

| 1 | sbtest | app_user | 0x5662D7CF0442E794 | select * from sbtest1 whereid=? |

+———–+————+———-+——————–+———————————-+

可以看到，两条SQL看起来一样，但分别转发到不同的hostgroup了。

最后配置ProxySQL的监控服务（可选，非必须）：

proxysql> setmysql-monitor_enabled= “true”;

proxysql> setmysql-monitor_username= “monitor”;

proxysql> setmysql-monitor_password= “monitor”;

proxysql> save mysql variables to disk; load mysql variables to runtime;

至此，一个全部基于开源应用的简易HTAP系统就构建好了。

4. 性能对比

在这里，我选用ClickHouse官方提供的benchmark方案：Star Schema Benchmark。

编译完成后先是利用ssb-dbgen生成测试数据（指定参数 -s 50）：

./dbgen -s 50 -T c &

./dbgen -s 50 -T l &

./dbgen -s 50 -T p &

./dbgen -s 50 -T s &

./dbgen -s 50 -T d &

再创建几个测试库表，自行修改建表的DDL以适应MySQL语法。而后导入测试数据，最后根据文档并生成 lineorder_flat 表。

[[email protected]]> show table status;

+—————-+——–+———+————+———–+—————-+————–+

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |

+—————-+——–+———+————+———–+—————-+————–+

| customer | InnoDB | 10 | Dynamic | 1378209 | 120 | 166363136 |

| lineorder | InnoDB | 10 | Dynamic | 297927870 | 100 | 29871833088 |

| lineorder_flat | InnoDB | 10 | Dynamic | 292584926 | 430 | 125952851968 |

| part | InnoDB | 10 | Dynamic | 1192880 | 111 | 132792320 |

| supplier | InnoDB | 10 | Dynamic | 99730 | 110 | 11026432 |

+—————-+——–+———+————+———–+—————-+————–+

数据全部加载完毕后，再在ClickHouse中创建MaterializeMySQL复制通道：

clickhouse 🙂 CREATE DATABASE ssb ENGINE = MaterializeMySQL( ‘172.24.10.10:3380’, ‘ssb’, ‘repl’, ‘repl’);

数据量比较大，耐心静待它复制完成即可。

然后连接 ProxySQL，先简单执行大表count(*)，观察耗时的不同：

#直接执行count(*)，会转发到后端 MySQL 实例

[[email protected]]> select count(*) from lineorder_flat;

+———–+

| count(*) |

+———–+

| 300005811 |

+———–+

1 row inset(3 min 2.14 sec)

#加上HINT规则，会转发到后端 ClickHouse 实例

[[email protected]]> select /*+CLICKHOUSE*/ count(*) from lineorder_flat;

+———–+

| count(*) |

+———–+

| 300005811 |

+———–+

1 row inset(5.67 sec)

光是 count(*) 就差了好多倍。

再选取其中前4个SQL测试，记录的耗时如下：

Query MySQL ClickHouse(从库) ClickHouse(原生)
Q1.1 308.388684 0.149 0.107
Q1.2 320.373203 0.280 0.027
Q1.3 279.673361 0.346 0.030
Q2.1 286.451062 1.246 0.489

很明显，直接在MySQL上查询的效率实在太低了，而作为从库的MaterializeMySQL和ClickHouse原生的MergeTree表虽然也有一定差距，但相差也没那么大了，还算是很快的。

4. 其他说明

  • ClickHouse的MaterializeMySQL中 不支持 create like 语法。例如执行 create table db2.a like db1.a，其中db1是要复制到ClickHouse的，而db2是留在MySQL端，即便这样也会导致ClickHouse端复制报错，需要重启才行。

  • ClickHouse的MaterializeMySQL中也 不支持函数索引。

  • 偶尔发现ProxySQL的监控模块连接到ClickHouse后，会发送 SET wait_timeout=N 命令，会导致ClickHouse报错，但不影响正常使用。 重启ProxySQL，或者重启监控开关都可以解决。

Enjoy it 🙂

延伸阅读

责任编辑：

Thenews.cc