TL;DR

使用Canal + ClickHouse实时分析MySQL事务信息

作为DBA, 有时候我们会希望能够了解线上核心库更具体的”样貌”, 如:

• 这个库主要的DML类型是什么?
• 这个库的事务大小, 执行时间, 影响行数大概是什么样的?

以上信息也许没什么价值, 但大事务对复制的影响不用多说, 并且当我们希望升级当前主从架构到MGR/PXC等高可用方案的场景时以上信息就比较重要了(毕竟用数据说话更有力度).

大事务对MGR和PXC都是不友好的, 尤其是MGR(起码在5.7版本)严重时会导致整个集群hang死

在Galera 4.0中新特性Streaming Replication对大事务有了更好的支持

当然, 有人可能会说, 通过分析binlog就可以完成这样的工作, 最简单的方法写个shell脚本就可以, 比如这篇文章中介绍的方法Identifying Useful Info from MySQL Row-Based Binary Logs(这篇文章介绍的方法比较简单, 分析速度也较慢, 可以试试analysis_binlog). 当然还有很多其他工具, 比如infobin.

但个人认为上面的方法从某种角度来看还是比较麻烦, 而且现在ClickHouse越来越流行, 使用ClickHouse去完成这个工作也能帮助我们更好的学习ClickHouse

先看一下最终的成果

类MHA高可用方案存在的问题

MHA Generaly Available since 2011?

MHA在当时主要解决两个问题:

1. 自动的数据补偿
2. 自动的主从切换

还有两个重要的背景需要交代:

1. 当时主要使用异步复制
2. 当时还没有ProxySQL

所以当时基本使用MHA+VIP作为MySQL复制集的高可用方案.

不谈vip的脑裂问题, 这种架构的一个关键点在于, MHA是作为一个外部机制检测MySQL复制集状态, 并变更复制集拓扑, 变更后漂移vip, 也就是说MHA既控制了集群拓扑的变化, 又控制了app的访问路径(写通过vip)

TL;DR

如果预估自己的业务数据量不大(日增不到百万行), 那么写分布式表和本地表都可以, 但要注意如果选择写本地表, 请保证每次写入数据都建立新的连接, 且每个连接写入的数据量基本相同

如果预估自己的业务数据量大(日增百万以上, 并发插入大于10), 那么请写本地表

建议每次插入50W行左右数据, 最多不可超过100W行. 总之CH不像MySQL要小事务. 比如1000W行数据, MySQL建议一次插入1W左右, 使用小事务, 执行1000次. CH建议20次,每次50W. 这是MergeTree引擎原理决定的, 频繁少量插入会导致data part过多, 合并不过来.

再有, AP不像TP, TP为了避免建立新连接产生的损耗影响性能, 通常会使用长连接, 连接池等技术做优化. 但AP业务不需要, 因为AP的属性就不会有高并发, 小SQL.

只讨论5.7增强半同步和双1的情况

增强半同步会不会丢数据?

这里涉及两个过程:

• 主库 Innodb与Binlog日志的2PC
• 增强半同步

Innodb与Binlog日志的2PC

在开启Binlog后, MySQL内部会自动将普通事务当做一个XA事务来处理：

• 自动为每个事务分配一个唯一的ID
• COMMIT会被自动的分成Prepare和Commit两个阶段
• Binlog会被当做事务协调者(Transaction Coordinator), Binlog Event会被当做协调者日志

innodb_status_file

这个参数官方文档https://dev.mysql.com/doc/refman/5.7/en/server-system-variable-reference.html 中没有

在https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html 中有

--innodb-status-file

The --innodb-status-file startup option controls whether InnoDB creates a file named innodb_status.*pid* in the data directory and writes SHOW ENGINE INNODB STATUS output to it every 15 seconds, approximately.

The innodb_status.*pid* file is not created by default. To create it, start mysqld with the --innodb-status-file option. InnoDB removes the file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.

The --innodb-status-file option is intended for temporary use, as SHOW ENGINE INNODB STATUS output generation can affect performance, and the innodb_status.*pid* file can become quite large over time.

For related information, see Section 14.18.2, “Enabling InnoDB Monitors”.

由一个慢查询衍生出的问题

我们线上有一个ClickHouse集群, 总共6个服务器, 配置均为16C 64G SSD, 集群配置为三分片两副本

有两个表这里称为small_table和big_table. 都是ReplicatedMergeTree引擎(三个分片两个副本).

small_table有79w数据, big_table有5亿数据(数据在之后的示例中没有任何变化), 在下文中small_table和big_table都为分布式表, 可以获取全量数据, small_table_local和big_table_local为各节点上的本地表名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT 
    table, 
    formatReadableSize(sum(data_compressed_bytes)) AS tc, 
    formatReadableSize(sum(data_uncompressed_bytes)) AS tu, 
    sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS ratio
FROM system.columns
WHERE (database = currentDatabase()) AND (table IN ('small_table_local', 'big_table_local'))
GROUP BY table
ORDER BY table ASC

┌─table─────────────────────────┬─tc────────┬─tu────────┬──────────────ratio─┐
│ small_table_local             │ 12.87 MiB │ 14.91 MiB │ 0.8633041477100831 │
│ big_table_local               │ 15.46 GiB │ 57.31 GiB │ 0.2697742507036428 │
└───────────────────────────────┴───────────┴───────────┴────────────────────┘

ClickHouse Materialized Views Illuminated, Part 2

Read part 1

ClickHouse Materialized Views Illuminated, Part 1

Altinity blog的读者们知道我们喜欢ClickHouse的物化视图. 物化视图可以实现聚合计算, 从Kafka读取数据, 实现最后点查询(last point queries)以及重组表主键索引和排序顺序. 除了这些功能之外, 物化视图可以在大量节点上很好地扩缩, 并可以处理大型数据集. 它们是ClickHouse的独特功能之一.

在计算领域, 强大的功能至少意味着一点点复杂性. 这篇由两部分组成的文章通过解释物化视图的工作原理来填补空白, 从而使初学者也可以有效地使用它们. 我们将通过几个详细的示例, 您可以根据自己的使用进行调整. 在此过程中, 我们将探索用于创建视图的语法的确切含义, 并让您深入了解ClickHouse在做什么. 示例是完全自包含的, 因此您可以将它们复制/粘贴到clickhouse-client中并自己运行它们.

insert IGNORE into select lock in share mode 的作用

pt-osc和gh-osc在拷贝旧数据时逻辑是一样的, 都是用insert ignore into 影子表 select * from 原表force index (PRIMARY) where chunk范围 lock in share mode