Clickhouse: v20.5 increase memory usage reported by MemoryTracking in system.metrics

[peaksnail]

hi team,

the version 20.5.4.40 has increase memory usage by queries

I upgrade clickhouse server from 19.7 to 20.5.4.40, I don't change any configuration， how to explain ？

configuration

<profiles>
        <!-- Default settings. -->
        <default>
            <!-- Maximum memory usage for processing single query, in bytes. -->
            <max_memory_usage>3221225472</max_memory_usage>
            <max_bytes_before_external_group_by>1000000000</max_bytes_before_external_group_by>
            <max_query_size>1048576</max_query_size>

            <max_ast_elements>100000</max_ast_elements>
            <max_expanded_ast_elements>100000</max_expanded_ast_elements>

            <enable_optimize_predicate_expression>1</enable_optimize_predicate_expression>
            <optimize_move_to_prewhere>1</optimize_move_to_prewhere>
            <any_join_distinct_right_table_keys>1</any_join_distinct_right_table_keys>
            <joined_subquery_requires_alias>0</joined_subquery_requires_alias>

            <!-- record query info -->
            <log_queries>1</log_queries>

            <!-- Use cache of uncompressed blocks of data. Meaningful only for processing many of very short queries. -->
            <use_uncompressed_cache>0</use_uncompressed_cache>

            <!-- How to choose between replicas during distributed query processing.
                 random - choose random replica from set of replicas with minimum number of errors
                 nearest_hostname - from set of replicas with minimum number of errors, choose replica
                  with minumum number of different symbols between replica's hostname and local hostname
                  (Hamming distance).
                 in_order - first live replica is choosen in specified order.
            -->
            <load_balancing>random</load_balancing>
        </default>

        <!-- Profile that allows only read queries. -->
        <readonly>
            <readonly>1</readonly>
        </readonly>
    </profiles>

thanks

[den-crane]

19.7 or 19.17 ?
Try 20.5.5.74
Your old version is used less than 1 GB ? I don't see the first digit on your graph.

[alexey-milovidov]

How the metric is measured?
How does the RSS of clickhouse-server process look on the graph (for comparison)?

[peaksnail]

@den-crane sorry ,the old version is 19.17.6.19 and most used less than 1G, some used 2Gb, but after upgrade most used more than 2GB,I don't change anything

[peaksnail]

@alexey-milovidov I use the template from template

I do nothing except upgrade, so I doubt the version change it

[filimonov]

It plots MemoryTracking from system.metrics.

https://github.com/Altinity/clickhouse-zabbix-template/blob/7e643a555a335635b2ea8dc305838864a4990897/zbx_clickhouse_template.xml#L759-L763

That metric was misbehaving in prev versions (since switching to jemalloc, afair) and the behavior of that was changed in 20.5, see

• https://github.com/ClickHouse/ClickHouse/issues/10293
• https://github.com/ClickHouse/ClickHouse/pull/10361
• https://github.com/ClickHouse/ClickHouse/pull/10362
• https://github.com/ClickHouse/ClickHouse/pull/10496
• https://github.com/ClickHouse/ClickHouse/pull/12182

For clickhouse >=20.4 the description of the metric should be smth like "ClickHouse memory usage (RSS)"

[filimonov]

SELECT 
    version(), 
    description
FROM system.metrics
WHERE metric = 'MemoryTracking'

┌─version()───┬─description──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 20.3.17.173 │ Total amount of memory (bytes) allocated in currently executing queries. Note that some memory allocations may not be accounted. │
└─────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT 
    version(), 
    description
FROM system.metrics
WHERE metric = 'MemoryTracking'

┌─version()──┬─description─────────────────────────────────────────────┐
│ 20.4.9.110 │ Total amount of memory (bytes) allocated by the server. │
└────────────┴─────────────────────────────────────────────────────────┘

[filimonov]

I've talked with @Slach about that:

Mikhail Filimonov 08:45
Может быть надо поправить описание метрики у нас в заббикс шаблоне (там в 20.4 поменялось её значение)? См. https://github.com/ClickHouse/ClickHouse/issues/14367#issuecomment-685353507

Eugene Klimov 08:51
а там описание метрики поменялось в самом ClickHouse?

Mikhail Filimonov 08:53
Да. Кстати, надо бы добавить в тот тикет тоже. :+1: (добавил)

Eugene Klimov 08:55
чето на мой взгляд как то все сломали нафиг, непонятно зачем
если была метрика и она отображала то что написано
пусть и неккоректно
то лучше б убрали метрику и новую назвали MemoryTrackingTotal
потому что если кто-то мониторит шаблоном разные версии ClickHouse
то у него сломается мозг

Mikhail Filimonov 08:58
Мне нечего возразить ¯_(ツ)_/¯
С другой стороны сейчас менять - опять же безобразие будет какое-то. Впочем может быть ещё не поздно.
Давай спросим мнения общественности

Eugene Klimov 09:03
ну давай там спросим. я боюсь просто это пошло уже дофига куда

@alexey-milovidov what do you think?

[doom369]

I have a similar issue (probably). after updating 20.3 to 20.6 - our usual queries are no longer executed and failed due to memory limits. After updating to 20.8.2.3 situation is even worth.
"Memory limit (total) exceeded: would use 961.69 MiB (attempt to allocate a chunk of 4676694 bytes), maximum: 886.67 MiB".
I updated only 1 QA env (1 core, 1 GB of RAM). However, in my case, I accepted the config changes from the new version.
Look like right after start CH uses all the available memory for something and there is no longer memory for queries.

Errors on log:

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x1a80ae30 in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0xff9e75d in /usr/bin/clickhouse
2. ? @ 0xff8aee0 in /usr/bin/clickhouse
3. MemoryTracker::alloc(long) @ 0xff89798 in /usr/bin/clickhouse
4. MemoryTracker::alloc(long) @ 0xff89798 in /usr/bin/clickhouse
5. MemoryTracker::alloc(long) @ 0xff89798 in /usr/bin/clickhouse
6. DB::CompressedWriteBuffer::CompressedWriteBuffer(DB::WriteBuffer&, std::__1::shared_ptr<DB::ICompressionCodec>, unsigned long) @ 0x16fd0504 in /usr/bin/clickhouse
7. DB::MergeTreeDataPartWriterOnDisk::Stream::Stream(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::shared_ptr<DB::IDisk>, std::__1::basic_string<char, st
d::__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<cha
r>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::shared_ptr<DB::ICompressionCodec> const&, unsigned long, unsigned l
ong, unsigned long) @ 0x17c6bf7e in /usr/bin/clickhouse
8. ? @ 0x17c7139d in /usr/bin/clickhouse
9. DB::MergeTreeDataPartWriterWide::MergeTreeDataPartWriterWide(std::__1::shared_ptr<DB::IMergeTreeDataPart const> const&, DB::NamesAndTypesList const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> co
nst&, std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeIndex const> > > const&, std::__1::basic_string<char, std::__1::char_traits<char>, s
td::__1::allocator<char> > const&, std::__1::shared_ptr<DB::ICompressionCodec> const&, DB::MergeTreeWriterSettings const&, DB::MergeTreeIndexGranularity const&) @ 0x17c71e92 in /usr/bin/clickhouse
10. DB::MergeTreeDataPartWide::getWriter(DB::NamesAndTypesList const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::vector<std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::
allocator<std::__1::shared_ptr<DB::IMergeTreeIndex const> > > const&, std::__1::shared_ptr<DB::ICompressionCodec> const&, DB::MergeTreeWriterSettings const&, DB::MergeTreeIndexGranularity const&) const @ 0x17c5f
7e0 in /usr/bin/clickhouse
11. DB::MergedBlockOutputStream::MergedBlockOutputStream(std::__1::shared_ptr<DB::IMergeTreeDataPart const> const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, DB::NamesAndTypesList const&, s
td::__1::vector<std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeIndex const> > > const&, std::__1::shared_ptr<DB::ICompressionCodec>, std::__1::map<std::__
1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, unsigned long, std::__1::less<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > >, std::__1::al
locator<std::__1::pair<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const, unsigned long> > > const&, unsigned long, bool) @ 0x17d6379c in /usr/bin/clickhouse
12. DB::MergedBlockOutputStream::MergedBlockOutputStream(std::__1::shared_ptr<DB::IMergeTreeDataPart const> const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, DB::NamesAndTypesList const&, s
td::__1::vector<std::__1::shared_ptr<DB::IMergeTreeIndex const>, std::__1::allocator<std::__1::shared_ptr<DB::IMergeTreeIndex const> > > const&, std::__1::shared_ptr<DB::ICompressionCodec>, bool) @ 0x17d639d1 in
 /usr/bin/clickhouse
13. DB::MergeTreeDataWriter::writeTempPart(DB::BlockWithPartition&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0x17c9a3ac in /usr/bin/clickhouse
14. DB::MergeTreeBlockOutputStream::write(DB::Block const&) @ 0x17bc0828 in /usr/bin/clickhouse
15. DB::PushingToViewsBlockOutputStream::write(DB::Block const&) @ 0x17453176 in /usr/bin/clickhouse
16. DB::AddingDefaultBlockOutputStream::write(DB::Block const&) @ 0x17459329 in /usr/bin/clickhouse
17. DB::SquashingBlockOutputStream::finalize() @ 0x17458b77 in /usr/bin/clickhouse
18. DB::SquashingBlockOutputStream::writeSuffix() @ 0x17458c5d in /usr/bin/clickhouse
19. DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::__1::atomic<bool>*) @ 0x1704cfc0 in /usr/bin/clickhouse
20. DB::NullAndDoCopyBlockInputStream::readImpl() @ 0x1744bec1 in /usr/bin/clickhouse
21. DB::IBlockInputStream::read() @ 0x170282dd in /usr/bin/clickhouse
22. 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::fu
nction<void (DB::Block const&)> const&) @ 0x1704d56b in /usr/bin/clickhouse
23. 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&)>) @ 0x177d7302 in /usr/bin/clickhouse
24. DB::HTTPHandler::processQuery(DB::Context&, Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0x17e3cf99 in /usr/bin/clickhouse
25. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0x17e40a5b in /usr/bin/clickhouse
26. Poco::Net::HTTPServerConnection::run() @ 0x1a6eaa83 in /usr/bin/clickhouse
27. Poco::Net::TCPServerConnection::start() @ 0x1a728c9b in /usr/bin/clickhouse
28. Poco::Net::TCPServerDispatcher::run() @ 0x1a72912b in /usr/bin/clickhouse
29. Poco::PooledThread::run() @ 0x1a8a7c06 in /usr/bin/clickhouse
30. Poco::ThreadImpl::runnableEntry(void*) @ 0x1a8a3000 in /usr/bin/clickhouse
31. start_thread @ 0x76db in /lib/x86_64-linux-gnu/libpthread-2.27.so

[Slach]

@doom369 you have total different error
for @peaksnail use case we have just changed system.mertics value behavior

in your case probably you need look to

SELECT * FROM system.settings WHERE name LIKE '%max_mem%' OR name LIKE '%max_thead%' OR name LIKE '%background%';

and decrease max_threads and background_pool_size values
1 GB RAM is too low for ClickHouse

[doom369]

@Slach thanks for the answer.

and decrease max_threads and background_pool_size values

That's what I did and that helped. However, top command still show 100% memory usage for the CH server. That's wasn't like that for the previous versions. It was at the level of 40-50%.

1 GB RAM is too low for ClickHouse

But it's QA and it has like zero loads, it sounds strange for me that with low memory CH can't perform even simple inserts/selects.

[den-crane]

@doom369 try to set in config.xml <mlock_executable>false</mlock_executable>

[doom369]

@den-crane thanks. It helped. Now memory dropped to 14% and to 28% during query execution. Look like it now defaults due to https://github.com/ClickHouse/ClickHouse/pull/11139. Thanks for pointing out.

[alexey-milovidov]

@doom369 There is ongoing PR for improvement: https://github.com/ClickHouse/ClickHouse/pull/14929