MariaDB 10.6开辟了OLAP数仓的新领域：安装、部署与应用

贺春旸 2024-01-17 10:38:33

作者介绍

贺春旸，dbaplus社群金牌专家，凡普金科和爱钱进DBA团队负责人，《MySQL管理之道：性能调优、高可用与监控》第一&二版、《MySQL运维进阶指南》作者，曾任职于中国移动飞信、安卓机锋网。五次荣获dbaplus年度MVP，致力于MariaDB、MongoDB等开源技术的研究，主要负责数据库性能调优、监控和架构设计。

MariaDB ColumnStore利用分布式列式存储和大规模并行处理（MPP）共享无架构扩展了MariaDB企业服务器，将其转变为独立或分布式数据仓库，用于复杂SQL查询和高级分析，而无需创建任何索引。

以下是 MariaDB ColumnStore 的一些关键特性和优势：

列式存储：MariaDB ColumnStore 使用列式存储方式，将数据按列存储在磁盘上。这种存储方式在处理大规模数据时具有出色的压缩比和查询性能，尤其适合分析型查询。
分布式架构：MariaDB ColumnStore 支持水平扩展，可以在多个节点上分布数据和查询工作负载。通过添加更多的节点，可以实现更高的处理能力和吞吐量。
并行查询处理：MariaDB ColumnStore 可以并行执行查询操作，充分利用多核处理器和分布式环境的计算资源。这样可以加快查询速度，并提供更快的响应时间。
数据压缩：MariaDB ColumnStore 使用高效的数据压缩算法，可以显著减少存储需求，并提高查询性能。压缩后的数据可以更快地加载到内存中，并节省磁盘空间。
支持标准SQL：MariaDB ColumnStore 是基于 MariaDB 数据库构建的，因此它保留了 MariaDB/MySQL 的兼容性和广泛的 SQL 支持。这使得开发人员可以使用熟悉的 SQL 查询语言进行数据分析和查询操作。

总之，MariaDB ColumnStore 是一个功能强大的列式存储引擎，适用于大规模数据分析和数据仓库工作负载。它提供高性能、可扩展性和丰富的 SQL 功能，帮助用户快速处理和分析海量数据。

以下是MySQL领域的数仓解决方案：

MySQL HeatWave（收费）
MariaDB Columnstore（开源）
MariaDB Enterprise Columnstore（收费）
Percona ClickHouse（开源）

行式存储和列式存储的区别：

在MariaDB 10.6版本中，ColumnStore迎来了一系列显著的改进：

MariaDB ColumnStore 10.6支持一键部署，简化了安装过程，使用户能够更轻松地搭建环境。

实现了MySQL InnoDB到ColumnStore的同步复制（可以使用 "CHANGE MASTER TO" 命令与业务库中的 InnoDB 引擎建立同步复制），无需借助任何ETL工具，实现了增量数据的实时同步。这一创新性的改进使数据同步变得更加高效和便捷。

这些升级和改进为用户提供了更加强大和灵活的数据仓库解决方案，使其能够更方便地进行复杂查询和高级分析，为企业的数据处理提供了更高的效率和实时性。

安装与部署

采用国内镜像阿里云YUM源：

shell> vim /etc/yum.repos.d/mariadb.repo# MariaDB 10.6 CentOS repository list - created 2023-12-29 01:20 UTC# https://mariadb.org/download/[mariadb]name = MariaDB# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.# baseurl = https://rpm.mariadb.org/10.6/centos/$releasever/$basearchbaseurl = https://mirrors.aliyun.com/mariadb/yum/10.6/centos/$releasever/$basearchmodule_hotfixes = 1# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDBgpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDBgpgcheck = 1

1.计算层安装部署：

shell> yum  install  epel-releaseshell> yum  install  jemallocshell> yum  install  MariaDB-server  MariaDB-backup  MariaDB-shared MariaDB-client  MariaDB-columnstore-engine

注：yum完会自动启动UM模块和PM模块服务

2.数据存储引擎层安装：

shell> vim /etc/my.cnf# MariaDB Columnstore OLAP#[client-server]port    =  3306socket          =  /tmp/mysql_mariadb.sock
[mysqld]server-id          =  133061port          =  3306user            =  mysqlbasedir    =  /usrdatadir    =  /data/mysql/columnstore/datatmpdir    =  /data/mysql/columnstore/tmplog-error        =  /data/mysql/columnstore/log/error.logsocket    =  /tmp/mysql_mariadb.sock
skip-slave-startskip-external-lockingskip-name-resolvesql_mode = ''
plugin-load-add       =  ha_columnstore.sodefault-storage-engine  =  Columnstorecharacter-set-server    =  utf8mb4collation-server       =  utf8mb4_general_ciinit_connect          =  'set global character_set_database = "utf8mb4"'
slow_query_log       =  1slow_query_log_file   =  /data/mysql/columnstore/log/mysql-slow.loglog-slow-verbosity    =  query_plan,explainlong_query_time      =  5columnstore_replication_slave  =  1  #启动与innodb同步复制
max_allowed_packet   =   256M

初始化mysql目录

shell> mysql_install  --defaults-file=/etc/my.cnf  --user=mysql

启动mysqld进程

shell> mysqld_safe  --defaults-file=/etc/my.cnf  --user=mysql  &

注：

mysqld服务启动后，sql语法使用起来跟InnoDB无差异，你可以用Navicat/Sqlyog等MySQL客户端工具连接，Java/php/python的driver驱动无需变更（兼容MySQL）。
Columnstore数据目录在/var/lib/columnstore/
Columnstore配置文件在/etc/columnstore/Columnstore.xml

一、MariaDB Columnstore引擎使用注意事项

字段属性限制

1、varchar最大8000（如果是utf8，即8000/3；如果是utf8mb4，即8000/4）

2、不支持bit类型

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

4、不支持zerofill

5、不支持enum枚举类型

6、comment不能携带''引号

create table t1(id int comment '主键''ID')engine=Columnstore;

7、不支持主键自增

SQL语句限制

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

错误写法：

MariaDB [test]> select id from t1 group by name;  ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`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.

MariaDB [test]> select id,name from t1 group by name;  ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`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.

正确写法：

MariaDB [test]> select name from t1 group by name;

2、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.

3、字段类型不同 join 关联查询报错，比如表1的id字段为int，表2的字段id为varchar，进行关联查询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.

4、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

建表范例

CREATE TABLE `sbtest` (  `id` int(10)   unsigned NOT NULL,  `k` int(10)   unsigned NOT NULL DEFAULT '0',  `c` char(120)   DEFAULT '',  `pad` char(60)   NOT NULL DEFAULT '') ENGINE=Columnstore  DEFAULT  CHARSET=utf8;

二、将InnoDB全量一致性快照导入到

MariaDB Columnstore引擎

第一步：只导出表结构

shell>/usr/local/mysql-5.7.42/bin/mysqldump -S /tmp/mysql_mysql57_1.sock test sbtest1 --compact -d -q --set-gtid-purged=OFF > ./sbtest.sql

第二步：更改表结构字段属性，改为Columnstore

shell> ./convert.sh  sbtest.sql  sbtest_new.sqlsed -e 's/InnoDB/Columnstore/;s/NOT NULL//g;/.*KEY.*/d;s/AUTO_INCREMENT=[0-9]*//g;s/AUTO_INCREMENT//g;s/\<timestamp\>.*TIMESTAMP\|\<timestamp\>/datetime /g;s/\<COLLATE.*utf8_bin\>//g;s/\<CHARACTER.*SET.*[utf8|utf8mb4]\>//g;s/decimal([0-9]*/decimal(18/g;s/ROW_FORMAT=COMPACT//g;s/bit/int/g;s/COLLATE.* utf8mb4_unicode_ci//g;s/DEFAULT CURRENT_TIMESTAMP//g;s/\<text\>/varchar(2000)/g;s/\<longtext\>/varchar(2000)/g;s/\<mediumtext\>/varchar(2000)/g;s/COLLATE utf8mb4_bin//g;s/ON UPDATE CURRENT_TIMESTAMP//g' /data/bak/$1 | sed '/,/{:loop; N; /,\s*)/! bloop; s/,\s*)/\n)/}' > /data/bak/$2

第三步：用mydumper多线程导出数据(CSV格式)

shell>yum  install https://github.com/mydumper/mydumper/releases/download/v0.15.1-3/mydumper-0.15.1-3.el7.x86_64.rpm
shell>/usr/bin/mydumper -S /tmp/mysql_mysql57_1.sock --regex 'test.sbtest1' -t 16 --csv -v 3 --rows 10000000 --no-schemas -o ./

第四步：恢复到MariaDB Columnstore列式存储里

shell>/usr/bin/myloader -S /tmp/mysql_mariadb.sock -t 1 -v 3 -B test -d ./

注：这里需要用1个线程导入，多个线程导入会引发锁争用，数据不全。

三、将InnoDB binlog增量数据实时同步到MariaDB Columnstore引擎

架构图示意：

下面就来介绍MariaDB 10.6多源复制的搭建方法。

1）创建通道，命令如下：

mysql > SET @@default_master_connection = ${connect_name};注：${connect_name}为自定义连接的名字。

2）建立同步复制，命令如下：

mysql > CHANGE MASTER ${connect_name} TO MASTER_HOST='192.168.1.10',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10;

注：要使用基于binlog和position位置点建立同步复制，因为MySQL GTID不兼容MariaDB。

3）启动多源复制，命令如下：

mysql > START  SLAVE  ${connect_name};mysql > START  ALL  SLAVES;

4）停止多源复制，命令如下：

mysql > STOP  SLAVE  ${connect_name};mysql > STOP  ALL  SLAVES;

5）查看状态，命令如下：

mysql > SHOW  SLAVE  ${connect_name}  STATUS;mysql > SHOW  ALL  SLAVES  STATUS;

6）清空同步信息和日志，命令如下：

mysql > RESET  SLAVE  ${connect_name}  ALL;

7）刷新中继日志，命令如下：

mysql > FLUSH  RELAY  LOGS  ${connect_name};

注：

MySQL 从库的binlog格式必须设置为binlog_format =
STATEMENT（ROW行格式会触发Columnstore的bug），会导致mysqld进程crash，这里特别注意！
如遇到Columnstore从库的列类型不一致导致的复制异常问题（例如InnoDB里有text类型，Columnstore不支持，转换位varchar(8000/3) ->utf8），需要设置set global slave_type_conversions = ALL_LOSSY,ALL_NON_LOSSY;

即：所有允许的数据类型转换都会执行，而不管数据是否被截取。

四、InnoDB VS Columnstore 复杂SQL官方性能测试

样本测试用例：

https://github.com/mariadb-corporation/mariadb-columnstore-sample-data

结论

在性能方面，这两种存储引擎在不同的领域表现出色。InnoDB被优化用于事务性工作负载，其中数据经常被更新或插入。它采用了预写式日志机制，以确保在系统故障的情况下数据始终保持一致和可恢复。

相反，ColumnStore被优化用于分析工作负载，其中数据以读为主，查询通常涉及聚合和过滤操作。由于其列式设计和矢量化处理，ColumnStore可以更快地执行这些查询。

此外，ColumnStore还提供了额外的好处，如高速批量加载器和更小的磁盘占用空间。与InnoDB不同，ColumnStore不使用传统的索引，这有助于减小其磁盘占用空间。此外，ColumnStore的列式设计允许更高的数据压缩比，减少存储数据所需的磁盘空间。这些优势使得ColumnStore成为那些希望优化其数据存储和处理以适应分析工作负载的组织的一个引人注目的选择。

参考：MariaDB ColumnStore Engine Docker Demo: Taking Analytics to New Speed

https://www.youtube.com/watch?v=RrGo3cJdq0M

五、Columnstore高可用与数据扩容

MariaDB Columnstore企业版支持高可用功能，它是基于挂载AWS S3对象存储或者SAN光纤共享存储加以实现，实现原理类似甲骨文Oracle RAC集群架构，参考下面的架构示意图，这里不重点介绍。

本文主要介绍的是MariaDB Columnstore社区版（GPL开源）如何实现高可用的。

答：DBA可以多部署几台MariaDB Columnstore（相当于多个slave节点），把数据导入进去且与InnoDB同步复制，Columnstore用keepalived或者Haproxy实现高可用故障转移。

那Columnstore的数据如何动态扩容呢？

答：官方推荐用GFS(Gluster File System)分部署文件系统解决动态数据扩容，只需要把Columnstore的数据目录/var/lib/columnstore/data1目录mount挂载到GFS分部署文件系统上即可。

六、MariaDB Columnstore数仓(列式存储)在企业里的优势

对dba友好，会装MySQL，会搭建主从复制，会备份恢复即可玩转大数据，并且无需更改业务的sql，不需要创建任何索引。
无需借助hadoop等生态ETL工具抽数据+同步增量实时数据，MariaDB利用自身的主从复制即可完成数据流的转换，零成本，不需要学习额外的技术栈。
对java研发和业务方友好，他们可以继续欢快地使用Navicat等客户端工具连接查询，jdbc driver驱动无需变更，sql语句高度兼容MySQL，不需要学习额外的sql技术栈。
可以为企业节省人力成本，大数据架构基础建设交给dba团队实施与管理，让数据分析人员可以专心地编写业务sql。
非常适合中小企业落地使用。

如在安装部署过程中遇到问题，也可以参考搭建视频：

大数据 MariaDB Columnstore 数仓OLAP 安装与部署

https://www.bilibili.com/video/BV1qg4y1k7ZK

如何将InnoDB数据迁移至MariaDB Columnstore 数仓OLAP系统里

https://www.bilibili.com/video/BV1xc411r7kY

将MySQL InnoDB 增量数据实时同步到 MariaDB Columnstore 数仓OLAP系统里

https://www.bilibili.com/video/BV18c411k7pa

MariaDB Columnstore 数仓OLAP 复杂SQL性能测试

https://www.bilibili.com/video/BV1Fe411D7BR

MariaDB 10.6开辟了OLAP数仓的新领域：安装、部署与应用

降本的Kubernetes何时成了“成本刺客”？

YouTube如何利用MySQL支撑24.9亿用户？

人肉运维100次后，年底出了P0级故障……

关于国产数据库我不得不说

分库分表，可能真的要退出历史舞台了！