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

系统管理员 2023-01-01 07:49 134阅读 0赞

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

## 1. 关于ClickHouse ##

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

战斗民族出品的ClickHouse这几年则是风头正劲，国内用户也越来越多，几大公有云上也提供相应的产品服务，是目前市面上最快的OLAP数据库，性能远超Vertica、Sybase IQ等。关于ClickHouse的性能也可以关注我以前的一份测试报告：[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系统。下面是整体架构示意图

![format_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>
                <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
    [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测试，记录的耗时如下：

<table> 
 <thead> 
  <tr> 
   <th>Query</th> 
   <th>MySQL</th> 
   <th>ClickHouse(从库)</th> 
   <th>ClickHouse(原生)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Q1.1</td> 
   <td>308.388684</td> 
   <td>0.149</td> 
   <td>0.107</td> 
  </tr> 
  <tr> 
   <td>Q1.2</td> 
   <td>320.373203</td> 
   <td>0.280</td> 
   <td>0.027</td> 
  </tr> 
  <tr> 
   <td>Q1.3</td> 
   <td>279.673361</td> 
   <td>0.346</td> 
   <td>0.030</td> 
  </tr> 
  <tr> 
   <td>Q2.1</td> 
   <td>286.451062</td> 
   <td>1.246</td> 
   <td>0.489</td> 
  </tr> 
 </tbody> 
</table>

很明显，直接在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 :)

## 延伸阅读 ##

*  [ClickHouse性能测试][ClickHouse]
 *  [MySQL实时复制与实现][MySQL]
 *  [ClickHouse MaterializeMySQL实时同步MySQL汇总][ClickHouse MaterializeMySQL_MySQL]
 *  [MySQL-ProxySQL中间件（一）][MySQL-ProxySQL]
 *  [MySQL-ProxySQL中间件（二）][MySQL-ProxySQL 1]

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

![format_png 1][]

[ClickHouse]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653933490&scene=21&sn=241293f4dce3e37f704033fbba7be310#wechat_redirect
[format_png]: /images/20221120/62462c5e9d8e4041b8b690110b76d536.png
[MySQL]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=2&mid=2653935347&scene=21&sn=f632f387d71fcbf9a45038f463f0965d#wechat_redirect
[ClickHouse MaterializeMySQL_MySQL]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653935347&scene=21&sn=6f20411f6d720c9b84430fcefb093b09#wechat_redirect
[MySQL-ProxySQL]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653932547&scene=21&sn=e046c57d893b86477f87de410ab3eecc#wechat_redirect
[MySQL-ProxySQL 1]: https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&idx=1&mid=2653932552&scene=21&sn=7c563eecde49aa0599af57b2d2b6af2a#wechat_redirect
[format_png 1]: /images/20221120/505cdd3b2d7b41bf9e458e48e2c11a0f.png

发表评论取消回复

表情：

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

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

相关阅读

相关实战应用：基于Java构建企业级数据库系统

构建一个企业级的数据库系统，通常涉及以下几个步骤，这些步骤都是用Java语言实现的： 1. **设计数据库模式**：使用UML（统一建模语言）或工具如Eclipse

比眉伴天荒/ 2024年11月28日 05:06/ 0 赞/ 54 阅读

相关企业级分布式 HTAP 数据库管理系统，腾讯 TBase 正式开源

![format_png][] TBase简介 TBase是腾讯数据平台团队在开源的PostgreSQL基础上研发的企业级分布式HTAP数据库管理系统：具备高性能

骑猪看日落/ 2023年05月30日 06:23/ 0 赞/ 48 阅读

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

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

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

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

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

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

相关使用Spring Boot快速构建应用

最近项目使用Spring Boot做微服务架构，开始学习下Spring Boot 随着[Spring 4新版本的发布][Spring 4]，[Spring Boot][]这个

妖狐艹你老母/ 2022年08月03日 12:39/ 0 赞/ 230 阅读

相关基于Docker构建企业应用实战

基于Docker构建企业应用实战基础部分 [Docker 版本概述][Docker] [CentOS 环境下安装Docker][CentOS _Docker]

快来打我*/ 2022年05月23日 00:30/ 0 赞/ 240 阅读

相关 vue-cli + webpack快速构建vue应用

1.安装生成器 (npm install -g vue-cli )　所谓的生成器，可以理解为脚手架工具。使用vue --version检查是否安装成功。在安装v

谁践踏了优雅/ 2022年05月11日 05:12/ 0 赞/ 241 阅读

相关使用PowerApps快速构建基于主题的轻业务应用 —— 入门篇

为了使得业务用户自己有能力构建基于主题的轻业务应用，微软给出的答案是一套全新定义的商业应用平台，主要包括了PowerApps，Flow，PowerBI这三个组件。他们与Offi

「爱情、让人受尽委屈。」/ 2022年02月17日 09:30/ 0 赞/ 360 阅读

相关【阿里内部应用】基于Blink构建搜索全链路debug系统快速定位搜索问题

> 案例与解决方案汇总页： > [阿里云实时计算产品案例&解决方案汇总][Link 1] 一、背景介绍以往在处理用户投诉或者开发过程中遇到的（特定商品在淘宝搜索中

青旅半醒/ 2022年02月13日 00:31/ 0 赞/ 148 阅读

相关开源GIS平台应用系统

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

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