解决clickhouse报在后台将较小的parts合并为较大parts异常的问题

缺乏、安全感 2023-01-13 04:14 302阅读 0赞

2021.04.15 17:44:53.489322 [ 354952 ] {00bacdfb-5de8-48b3-ab07-a14a8a083cf7} <Error> DynamicQueryHandler: Code: 252, e.displayText() = DB::Exception: Too many parts (300). Merges are processing significantly slower than inserts., Stack trace (when copying this message, always include the lines below):
    
    0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x13cd24bc in /usr/bin/clickhouse
    1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0xa4346c9 in /usr/bin/clickhouse
    2. ? @ 0x9f1fdf0 in /usr/bin/clickhouse
    3. DB::PushingToViewsBlockOutputStream::writePrefix() @ 0x114eb938 in /usr/bin/clickhouse
    4. DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::__1::atomic<bool>*) @ 0x10a91ea5 in /usr/bin/clickhouse
    5. DB::NullAndDoCopyBlockInputStream::readImpl() @ 0x10cf211d in /usr/bin/clickhouse
    6. DB::IBlockInputStream::read() @ 0x10a7675d in /usr/bin/clickhouse
    7. void DB::copyDataImpl<std::__1::function<bool ()> const&, std::__1::function<void (DB::Block const&)> const&>(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::__1::function<bool ()> const&, std::__1::function<void (DB::Block const&)> const&) @ 0x10a92303 in /usr/bin/clickhouse
    8. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>) @ 0x10e102a0 in /usr/bin/clickhouse
    9. DB::HTTPHandler::processQuery(DB::Context&, Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0x117e3a2e in /usr/bin/clickhouse
    10. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0x117e7260 in /usr/bin/clickhouse
    11. Poco::Net::HTTPServerConnection::run() @ 0x13c1466e in /usr/bin/clickhouse
    12. Poco::Net::TCPServerConnection::start() @ 0x13c11197 in /usr/bin/clickhouse
    13. Poco::Net::TCPServerDispatcher::run() @ 0x13c1158b in /usr/bin/clickhouse
    14. Poco::PooledThread::run() @ 0x13d481ae in /usr/bin/clickhouse
    15. Poco::ThreadImpl::runnableEntry(void*) @ 0x13d44ea9 in /usr/bin/clickhouse
    16. start_thread @ 0x7e65 in /usr/lib64/libpthread-2.17.so
    17. __clone @ 0xfe88d in /usr/lib64/libc-2.17.so
     (version 20.8.3.18)

[root@localhost conf]# cat config.xml|grep merge -A10
        <merge_tree>
            <max_suspicious_broken_parts>5</max_suspicious_broken_parts>
            <parts_to_delay_insert>300</parts_to_delay_insert>
            <parts_to_throw_insert>600</parts_to_throw_insert>
            <max_delay_to_insert>2</max_delay_to_insert>
        </merge_tree>

What is the real meaning of parts here?

每个INSERT都在/path/to/clickhouse/.../table\_name/中创建一个文件夹。在该文件夹内，每列有2个文件，一个包含数据（压缩）的文件，另一个包含索引的文件。数据通过这些文件中的主键进行物理排序。例如：

![image.png][]

ClickHouse在后台将较小的parts合并为较大parts。它根据一些规则选择要合并的parts。当合并两个（或多个）parts后，将创建一个更大的parts，并将旧parts排入队列以将其删除。

如果您创建新parts的速度过快（例如，通过执行许多小的插入操作），而ClickHouse无法以适当的速度合并它们，则会抛出异常“合并正在处理”比插入的速度慢。可以尝试增加限制，但可能会出现文件/目录数量过多（如inodes限制）引起的文件系统问题。

[image.png]: /images/20221022/6b2719e3d3e94b83a734328bf8db1a53.png