ClickHouse 学习笔记

发表于 分类于 Waline： 本文字数： 4.4k 阅读时长 ≈ 4 分钟

ClickHouse 优缺点及性能情况

「优点」

  1. 为了高效的使用CPU，数据不仅仅按列存储，同时还按向量进行处理；
  2. 数据压缩空间大，减少IO；处理单查询高吞吐量每台服务器每秒最多数十亿行；
  3. 索引非B树结构，不需要满足最左原则；只要过滤条件在索引列中包含即可；即使在使用的数据不在索引中，由于各种并行处理机制 ClickHouse 全表扫描的速度也很快；
  4. 写入速度非常快，50-200M/s，对于大量的数据更新非常适用。

「缺点」

  1. 不支持事务，不支持真正的删除/更新；
  2. 不支持高并发，官方建议qps为100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下；
  3. SQL满足日常使用80%以上的语法，join写法比较特殊；最新版已支持类似SQL的join，但性能不好；
  4. 尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作，因为ClickHouse底层会不断的做异步的数据合并，会影响查询性能，这个在做实时数据写入的时候要尽量避开；
  5. Clickhouse快是因为采用了并行处理机制，即使一个查询，也会用服务器一半的CPU去执行，所以ClickHouse不能支持高并发的使用场景，默认单查询使用CPU核数为服务器核数的一半，安装时会自动识别服务器核数，可以通过配置文件修改该参数。

ClickHouse 建宽表多少列最合适？

ClickHouse 对于10000列也是能扛住的，但是在插入数据和执行维护操作(例如合并或突变)时可能需要额外的调优和大量 RAM。但只要 RAM 足够，用户可以获得出色的 ClickHouse 查询性能。对于典型的分析查询，10个列表和10000个列表没有什么不同。当不需要处理数千列时，可以使用 Map 数据类型。它可以在方便性和性能之间提供很好的折衷，特别是对于稀疏表，因为在一行中只使用了10K 列的一小部分。

10000列调优解决方案：

  • 将 max_query_size 设置增加到1M，这是处理大型 SQL 所必需的
  • 将 max_ast_element 设置增加到256K，这也是处理大型 SQL 所必需的
  • 内存超过64GB
  • 如果内存不足，减小 max_block_size。
  • 使用紧凑的部件可能会降低 RAM 的利用率，但也有其他缺点，如插入速度较慢和合并速度非常慢。

结论：1000列内都可以，原因看奇想派文章实验即可。

ClickHouse 数据导入

  • 全量数据导入：
    数据导入临时表 -> 导入完成后，将原表改名为tmp1 -> 将临时表改名为正式表 -> 删除原表

  • 增量数据导入：
    增量数据导入临时表 -> 将原数据除增量外的也导入临时表 -> 导入完成后，将原表改名为tmp1-> 将临时表改成正式表-> 删除原数据表

有人可能觉得上面的数据导入的时候，数据肯定缓存在内存里了，这个的确，但是ClickHouse基本上是顺序IO。对IO基本没有太高要求，当然，磁盘越快，上层处理越快，但是99%的情况是，CPU先跑满了（数据库里太少见了，大多数都是IO不够用）。

ClickHouse 相关优化

  1. 关闭虚拟内存，物理内存和虚拟内存的数据交换，会导致查询变慢。
  2. 为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。
  3. JOIN 操作时一定要把数据量小的表放在右边，ClickHouse 中无论是 Left Join 、Right Join 还是 Inner Join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。
  4. 批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致ClickHouse无法及时对新导入的数据进行合并，从而影响查询性能。
  5. 尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数。有些时候，先GROUP BY再JOIN比先JOIN再GROUP BY查询时间更短。
  6. ClickHouse的分布式表性能性价比不如物理表高，建表分区字段值不宜过多，防止数据导入过程磁盘可能会被打满。
  7. CPU一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，CPU是最关键的指标，要非常关注。

ClickHouse性能情况

  1. 单个查询吞吐量：如果数据被放置在page cache中，则一个不太复杂的查询在单个服务器上大约能够以2-10GB／s（未压缩）的速度进行处理（对于简单的查询，速度可以达到30GB／s）。如果数据没有在page cache中的话，那么速度将取决于你的磁盘系统和数据的压缩率。例如，如果一个磁盘允许以400MB／s的速度读取数据，并且数据压缩率是3，则数据的处理速度为1.2GB/s。这意味着，如果你是在提取一个10字节的列，那么它的处理速度大约是1-2亿行每秒。对于分布式处理，处理速度几乎是线性扩展的，但这受限于聚合或排序的结果不是那么大的情况下。
  2. 处理短查询的延时时间：数据被page cache缓存的情况下，它的延迟应该小于50毫秒(最佳情况下应该小于10毫秒)。 否则，延迟取决于数据的查找次数。延迟可以通过以下公式计算得知： 查找时间（10 ms） * 查询的列的数量 * 查询的数据块的数量。
  3. 处理大量短查询：ClickHouse可以在单个服务器上每秒处理数百个查询（在最佳的情况下最多可以处理数千个）。但是由于这不适用于分析型场景。建议每秒最多查询100次。
  4. 数据写入性能：建议每次写入不少于1000行的批量写入，或每秒不超过一个写入请求。当使用tab-separated格式将一份数据写入到MergeTree表中时，写入速度大约为50到200MB/s。如果您写入的数据每行为1Kb，那么写入的速度为50，000到200，000行每秒。如果您的行更小，那么写入速度将更高。为了提高写入性能，您可以使用多个INSERT进行并行写入，这将带来线性的性能提升。
  • count: 千万级别，500毫秒，1亿 800毫秒 2亿 900毫秒 3亿 1.1秒
  • group: 百万级别 200毫米，千万 1秒，1亿 10秒，2亿 20秒，3亿 30秒
  • join: 千万-十万 600 毫秒， 千万-百万：10秒，千万-千万 150秒

ClickHouse 常见参数配置推荐

  1. max_concurrent_queries

最大并发处理的请求数(包含select,insert等)，默认值100，推荐150(不够再加)，在我们的集群中出现过“max concurrent queries”的问题。

  1. max_bytes_before_external_sort

当order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值order by可以正常完成，但是速度相对存内存来说肯定要慢点（实测慢的非常多，无法接受）。

  1. max_thread_pool_size

后台线程池的大小，merge线程就是在该线程池中执行，当然该线程池不仅仅是给merge线程用的，默认值16，推荐32提升merge的速度（CPU允许的前提下）。

  1. max_server_memory_usage

单个SQL在单台机器最大内存使用量，该值可以设置的比较大，这样可以提升集群查询的上限。

  1. max_memory_usage_for_all_queries

单机最大的内存使用量可以设置略小于机器的物理内存(留一点内操作系统)。

  1. max_bytes_before_external_group_by

在进行group by的时候，内存使用量已经达到了max_bytes_before_external_group_by的时候就进行写磁盘(基于磁盘的group by相对于基于磁盘的order by性能损耗要好很多的)，一般max_bytes_before_external_group_by设置为max_memory_usage / 2

  1. max_bytes_before_external_sort

调整orderby参数：max_bytes_before_external_sort更少的RAM使用，如果一个足够小LIMIT除了指定ORDERBY，否则，所花费的内存量与用于排序的数据量成正比。对于分布式查询处理，如果GROUPBY省略排序，在远程服务器上部分完成排序，并将结果合并到请求者服务器上。这意味着对于分布式排序，要排序的数据量可以大于单个服务器上的内存量。如果没有足够的RAM，则可以在外部存储器中执行排序（在磁盘上创建临时文件）。使用设置max_bytes_before_external_sort为此目的。如果将其设置为0（默认值），则禁用外部排序。如果启用，则当要排序的数据量达到指定的字节数时，将对收集的数据进行排序并转储到临时文件中。读取所有数据后，将合并所有已排序的文件并输出结果。文件被写入到/var/lib/clickhouse/tmp/目录中的配置（默认情况下，但你可以使用tmp_path参数来更改此设置）。运行查询可能占用的内存比max_bytes_before_external_sort大。因此，此设置的值必须大大小于max_memory_usage.例如，如果您的服务器有128GB的RAM，并且您需要运行单个查询，请设置max_memory_usage到100GB，和max_bytes_before_external_sort至80GB。外部排序的工作效率远远低于在RAM中进行排序。

  1. max_execution_time

最大查询执行时间（以秒为单位）。
此时，不会检查其中一个排序阶段，也不会在合并和最终确定聚合函数时进行检查。

1
<max_execution_time>3600</max_execution_time>

ClickHouse与MySQL区别对比：

  1. MySQL单条SQL是单线程的，只能跑满一个core，ClickHouse相反，有多少CPU，吃多少资源，所以飞快；
  2. ClickHouse不支持事务，不存在隔离级别。ClickHouse的定位是分析性数据库，而不是严格的关系型数据库。
  3. IO方面，MySQL是行存储，ClickHouse是列存储，后者在count()这类操作天然有优势，同时，在IO方面，MySQL需要大量随机IO，ClickHouse基本是顺序IO。

避坑指南

  • Golang-banding-validator注册翻译接口的时候，不可以放在中间件中，会导致多次注册，使得map同时读取写入panic。