MariaDB ColumnStore在OLAP离线分析中的实践与应用（附脚本）

作者介绍

贺春旸，《MySQL 管理之道：性能调优、高可用与监控》第一、二版一书作者，从事数据库管理工作多年，曾经任职于中国移动飞信，安卓机锋网，凡普金科(爱钱进)，致力于MariaDB、MongoDB等开源技术的研究，主要负责数据库性能调优、监控和架构设计。

继之前写的《MariaDB ColumnStore初探：安装、使用及测试》一文，这次把生产落地使用经验与大家分享一下。

大数据OLAP两种技术流派

MapReduce

代表作：Hadoop家族

MPP（Massively Parallel Processing - 大规模并行处理）

代表作：

1. Infobright for MySQL - 商业版

2. MariaDB Columnstore（收购InfiniDB）for MySQL - 开源版

3. Greenplum for PostgreSQL - 开源版

架构选型基于MPP方式（MariaDB Columnstore），我考虑的首位因素是用户体验。它支持标准MySQL协议，支持Navicat/SQLyog/WebSQL等客户端工具，对使用者来说，完全没有必要学习一门新的语言，或熟悉一种新型的数据库，从而增加他们的学习成本，就像使用MySQL一样。

对于DBA而言，你不需要创建任何索引，不需要修改业务方的复杂SQL（自身就支持复杂的关联查询、聚合、存储过程和用户定义的函数），你唯一要做的就是把数据导入到ColumnStore里。

MariaDB Columnstore是数据仓库的未来，使我们能够存储更多的数据并更快地分析数据，易于使用和维护。在生产环境中，我们的使用场景是用作离线OLAP分析。

MariaDB Columnstore技术特性

1、数据分布式列式存储（本地化）

Shard Nothing架构，数据会分散落到各个PM节点上。

注：建议预估未来3年的数据增长量，一次性采购18台机器，2台UM，16台PM（PM你可以想象成汽车的发动机：W型16缸涡轮增压直喷发动机）。MPP是通过允许用户配置尽可能多的PM机器，以实现更高的处理能力。

另外新增加PM节点，你需要进行数据的重新分配。

当你执行mcsadmin> redistributeData start，重新分配可能需要很长时间，这取决于你的数据量。在此期间，增量数据的写入会被阻塞，直到rebalance完毕。

2、分布式并行计算

任务并行执行，在几秒钟内就可以查询数百万甚至数十亿行的数据，而无需等待。

3、横向扩展

存储和处理能力可以通过扩展节点来增加。当运行多个PM节点时，数据会自动分区和分配 - 查询更多数据，添加更多节点。

MariaDB Columnstore架构设计

UM模块：SQL协议接口，接收客户端连接访问，推送SQL请求给PM性能模块代为执行，最后收集性能模块的处理结果做数据汇总，并返回给客户端最终查询结果。

UM模块包含了以下进程。

• MariaDB服务器进程：mysqld

  负责连接验证，SQL语句解析，生成SQL执行计划，存储表结构元数据信息。

• 执行管理器：ExeMgr

  ExeMgr负责将查询分析树转换为作业列表，包括字段的过滤，表连接的处理和数据汇总与排序的操作。

• DMLProc、DDLProc和导入分发管理器

  DMLProc和DDLProc将DML和DDL分发到相应的PM模块。在UM模块上运行时，cpimport将源文件分发到PM模块。

PM模块：负责数据的列式存储，处理查询请求（分布式扫描数据块、分布式hash join、分布式聚合），将数据提取到内存中计算。

PM模块包含了以下进程。

• PrimProc：处理查询请求。检索和管理数据，处理对查询操作的块请求，并将其传递回用户模块以完成查询请求。

• WriteEngineServer：负责协调每个PM性能模块上的DML，DDL和Load data写入操作。

• ProcMgr进程管理：负责启动，监视和重新启动所有MariaDB ColumnStore进程。

• ProcMon：节点之间的心跳监控进程。

MariaDB Columnstore高可用两种模式

1、外挂SAN光纤存储（资金成本高，维护成本高）

借助外部存储，在PM服务器出现故障时，ColumnStore可以提供自动故障转移，这是因为给定的DBRoot存储位于发生故障的PM服务器的外部，可以重新mount挂载在另一个PM服务器上。

2、StandBy主备

部署两套MPP环境，通过ETL工具抽数据同时入库两份集群。一个集群挂掉，另一个集群提供服务。

使用过程中的遇到的问题

1字段属性限制

1、不支持CHARACTER SET语法

MariaDB [test]> create table t1(id int,name varchar(10) CHARACTER SET utf8)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

2、不支持COLLATE语法

MariaDB [test]> create table t1(id int)
-> engine=Columnstore COLLATE=utf8_bin;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB [test]> create table t1(id int,name varchar(10) COLLATE utf8_bin)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

3、不支持TEXT/BLOB（1.1版本已支持）

MariaDB [test]> create table t1(id int,info text)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.
MariaDB [test]>

MariaDB [test]> create table t1(id int,info blob)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

4、不支持timestamp，后面的DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP全部删除

MariaDB [test]> create table t1(update_time timestamp)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB [test]> create table t1(update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

5、decimal不能大于18

MariaDB [test]> create table t1(money decimal(19,2)) engine=Columnstore;
ERROR 1815 (HY000): Internal error: CAL0009: (3)Create table failed due to  Syntax error: The maximum precision (total number of digits) that can be specified is 18

6、不支持ROW_FORMAT=COMPACT

MariaDB [test]> create table t1(id int)engine=Columnstore ROW_FORMAT=COMPACT;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

7、varchar最大8000

MariaDB [test]> create table t1(name varchar(8001))engine=Columnstore;
ERROR 1815 (HY000): Internal error: CAL0009: (3)Create table failed due to char, varchar and varbinary length may not exceed 8000

8、不支持bit类型

MariaDB [test]> create table t1(status bit)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

9、comment不能携带''引号

MariaDB [test]> create table t3(id int comment '主键''ID')engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

10、行溢出，一行varchar不能大于65535（UTF8要除以3）

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

11、不支持enum枚举类型

MariaDB [test]> create table t1(`type` enum('HOLIDAY','WORKDAY') DEFAULT NULL)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

12、不支持zerofill

MariaDB [test]> create table t2(id int(6) zerofill)engine=columnstore;       
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

2保留关键字限制

不支持Reserved keywords保留关键字user、comment、match、key、update、status作为表名、字段名或用户定义的变量、函数或存储过程的名称。

这里感谢DBA王加富提供文献

• https://mariadb.com/kb/en/library/columnstore-naming-conventions/

• https://jira.mariadb.org/browse/MCOL-1022

MariaDB [test]> create table user(id int)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB [test]> create table t1(comment varchar(100))engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

这块可以用sed命令替换一下，改成Columnstore能识别的语法

1、导出表结构

格式化为Columnstore支持的语法

# bash convert_schema.sh hcy_schema.sql hcy.sql

3SQL语句限制

1、distinct的字段不在order by里，就不能排序

MariaDB [test]> select distinct apply_time from test ORDER BY id limit 1;          
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-2022: ORDER BY column not in DISTINCT list.

https://jira.mariadb.org/browse/MCOL-1036

2、查询的字段不在group by里，就不能分组统计

MariaDB [test]> select id from test group by qq;  
ERROR 1815 (HY000): Internal error: IDB-2021: 'test.id' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be
included in the GROUP BY clause.

3、alter不支持change/modify更改字段属性

MariaDB [test]> alter table t1 change id id bigint;
ERROR 1815 (HY000): Internal error: CAL0001: Alter table Failed:  Changing the datatype of a column is not supported

4、alter不支持多列操作和不支持after

MariaDB [test]> alter table t1 add age tinyint,add address varchar(100);
ERROR 1178 (42000): The storage engine for the table doesn't support Multiple actions in alter table statement is currently not supported by Columnstore.

MariaDB [test]> alter table t1 add age tinyint after id;  
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

5、字段类型不同 join 关联查询报错

MariaDB [test]> select t1.id from t1 join t2 on t1.id=t2.cid;
ERROR 1815 (HY000): Internal error: IDB-1002: 't1' and 't2' have incompatible column type specified for join condition.

6、DML语句会非常慢

DML, i.e. INSERT, UPDATE, and DELETE, provide row level changes. ColumnStore is optimized towards bulk modifications and so these operations are slower than they would be in say InnoDB.

https://mariadb.com/kb/en/library/columnstore-data-ingestion/

7、大批量数据查询text字段报内存不足

https://jira.mariadb.org/browse/MCOL-1056

8、不支持replace into和load data infile replace into

https://jira.mariadb.org/browse/MCOL-1080

9、不支持表名前缀为数字

MariaDB [test]> create table 2017_12_12 (id int)engine=Columnstore;

ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB官方回复：覆盖写目前优先级较低，因为这是一个非常大的项目，预计执行成本很高。

注：由于1.1版本支持TEXT字段，但目前返回大数据量结果集报内存溢出，后来线上回退到1.0.11版本

自动抽全量+定时抽增量数据脚本

因之前采用阿里Canal解析binlog生成SQL语句，再通过Kafka消费到Columnstore里，但DML的插入性能无法接受，这个方案后来被废弃了。

这里感谢前京东DBA邱治军提供思路，采用Percona pt-archiver工具来实现全量和增量数据抽取，每次循环抽取1万行记录存储为csv格式文件，然后通过load data命令导入进去。

这里我对pt-archiver脚本修改了一些内容：

1、将DELETE命令改成了DELETE111，目的怕万一手滑，加了--purge参数，把原库数据都给删了。

2、由于Columnstore不支持保留关键字，要把原表和目的表字段名字检查代码去掉，因为我们线上涉及到保留关键字的字段，加1处理，如desc1。

3、Select 默认写死了字段分隔符为逗号，我这边由于varchar字段记录有逗号，所以要改成&宏符号，字符串用引号引起来。

4、--bulk-insert批量插入是在远程机器以Load data方式插入的，因此要添加FIELDS TERMINATED字段分隔符。

5、去掉FORCE INDEX和ORDER BY排序，让MySQL自己选择索引。

说明：因为我这边是以update_time为抽取条件，默认情况下pt-archiver生成的SQL会强制扫描主键id并对主键排序，如下面的语句：

这个执行计划是：先强制扫描主键id小于520766518（表的最大记录值），然后过滤出update_time>'2017-12-09 21:02:32'，最后通过ID主键倒排一万行记录，由于update_time用不上索引所以很慢。

去掉FORCE INDEX和ORDER BY排序后的执行计划：

可以看到update_time可以使用到索引。

注：我修改的是最新版pt-archiver 3.0.5，脚本可点击文末【阅读原文】下载。全量+增量抽数据脚本，可以自定义线程，指定并发抽几张表。

• 全量的方式，就是通过id>=1的方式来抽取。

• 增量数据，我们在创建表的时候，就要求开发必须指定update_time字段更新时间，如下：

• `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

方便抽取增量时，取max(update_time)得到当前最新的时间，然后抽取之后的数据，由于Columnstore 不支持load data replace into覆盖，所以这里是存在重复数据的，使用的时候需要做去重处理。

近期热文：

↓↓↓点这里下载本文脚本