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

分手后的思念是犯贱 2023-01-12 07:55 90阅读 0赞

利用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系统。下面是整体架构示意图

![15f2656336ef003769bb6b2575be6fa8.png][]

3.1 将ClickHouse配置成为MySQL的从库

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

clickhouse :) SET allow\_experimental\_database\_materialize\_mysql = 1;

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

clickhouse :) CREATE DATABASE test ENGINE = MaterializeMySQL('172.24.10.10:3306', 'test', 'repl', 'repl');

clickhouse :) use test;

clickhouse :) show tables;

┌─name─────┐

│ sbtest1  │

│ sbtest10 │

│ sbtest11 │

...

32 rows in set. Elapsed: 0.006 sec.

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

\[root@yejr.run\]\# 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从库的账号至少还要加上只读权限，例如：

\[root@yejr.run\]> show grants for repl;

\+--------------------------------------------------------------------------+

| Grants for repl@%                                                        |

\+--------------------------------------------------------------------------+

| 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

::/0

default

default

monitor

::/0

default

default

我只设置了简单密码，也没有修改其权限和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

\[root@yejr.run\]> SELECT \* FROM sbtest1 WHERE id=1;

\#SQL \#2

\[root@yejr.run\]> 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 where id=? |

| 1         | sbtest     | app\_user | 0x5662D7CF0442E794 | select \* from sbtest1 where id=? |

\+-----------+------------+----------+--------------------+----------------------------------+

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

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

proxysql> set mysql-monitor\_enabled="true";

proxysql> set mysql-monitor\_username="monitor";

proxysql> set mysql-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 表。

\[root@yejr.run\]> 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 实例

\[root@yejr.run\]> select count(\*) from lineorder\_flat;

\+-----------+

| count(\*)  |

\+-----------+

| 300005811 |

\+-----------+

1 row in set (3 min 2.14 sec)

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

\[root@yejr.run\]> select /\*+CLICKHOUSE\*/ count(\*) from lineorder\_flat;

\+-----------+

| count(\*)  |

\+-----------+

| 300005811 |

\+-----------+

1 row in set (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 :)

延伸阅读

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![7d36c3f317a3baca2a22b578292dc468.png][]

[15f2656336ef003769bb6b2575be6fa8.png]: /images/20221119/ad5d5630f63f442e8dc9fabcb07f92aa.png
[7d36c3f317a3baca2a22b578292dc468.png]: /images/20221119/6ba877e7dfef4b82b6a021ec248e6b22.png

发表评论取消回复

表情：

评论列表（有 0 条评论，90人围观）

还没有评论，来说两句吧...

相关阅读

相关分布式数据库的HTAP能统一OLTP和 OLAP吗？

![390766e2e84936e00baafc4dc58de4c4.jpeg][] OLAP和OLTP通过ETL衔接。为提升OLAP性能，需在ETL过程进行大量预计算，包括

向右看齐/ 2024年03月23日 21:40/ 0 赞/ 38 阅读

相关【开源】基于Vue+SpringBoot的二手车交易系统

![在这里插入图片描述][de167b6569ce4fcdbe679a412fdcb9f6.png_pic_center] -------------------- 目录

川长思鸟来/ 2024年02月05日 20:44/ 0 赞/ 18 阅读

相关【开源】基于Vue+SpringBoot的智慧家政系统

![在这里插入图片描述][1c6537ea881443c088a8d34434681519.png_pic_center] 项目编号： S 063 ，文末获取源码。 \\

桃扇骨/ 2024年02月05日 12:54/ 0 赞/ 20 阅读

相关 OLTP、OLAP与HTAP

参考文章：[OLTP、OLAP与HTAP][OLTP_OLAP_HTAP] OLTP On-Line Transaction Processing联机事务处理过程(OL

我不是女神ヾ/ 2023年03月01日 03:26/ 0 赞/ 66 阅读

相关 mysql htap 开源_基于开源应用快速构建HTAP系统

利用ProxySQL、MySQL、ClickHouse快速构建HTAP系统 1. 关于ClickHouse 企业里随着数据量的增加，以及日趋复杂的分析性业务需求，主要适用于

分手后的思念是犯贱/ 2023年01月12日 07:55/ 0 赞/ 91 阅读

相关最佳实践6|分布式数据库HTAP混合负载最佳实践

01 前言近年来，在金融科技的推动下，互联网金融业务蓬勃发展，金融行业数据处理呈现出新的特征，如数据量大、并发量高、处理性能高、类型繁多等。这就对海量数据的存储、并发访

拼搏现实的明天。/ 2023年01月06日 11:49/ 0 赞/ 143 阅读

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

> 利用ProxySQL、MySQL、ClickHouse快速构建HTAP系统 1. 关于ClickHouse 企业里随着数据量的增加，以及日趋复杂的分析性业务需求，主

系统管理员/ 2023年01月01日 07:49/ 0 赞/ 95 阅读

相关 OLTP VS OLAP VS HTAP

[https://blog.bcmeng.com/post/oltp-olap-htap.html][https_blog.bcmeng.com_post_oltp-olap-

﹏ヽ暗。殇╰゛Y/ 2022年02月23日 01:04/ 0 赞/ 228 阅读

相关开源GIS平台应用系统

先说点题外话，ArcGIS9.3发布了，较9.2版本革新并不是很多，更多的是增强原有功能，Server开发提供JavaScript API 和 Flex API

深藏阁楼爱情的钟/ 2021年12月11日 07:43/ 0 赞/ 405 阅读

相关基于Servlet+EasyUI+Mysql的学生信息系统开源

知识点：jsp servlet，MySQL数据库的基本操作，前端easyui框架。适合人群：Java初学者、在校学生，（已经学过Java基础语法，对html有简单的了解，熟

深碍√TFBOYSˉ_/ 2021年08月30日 15:33/ 0 赞/ 240 阅读