High CPU usage due to clickhouse restarting? Memory too low?

[russellballestrini]

Since for at least 14 days the clickhouse docker container has been using 100% CPU seemingly rebooting in a very tight loop. This makes a single core server utilize 4.0 load (4 times more than healthly). Plausible seems to function and collect my metrics but it seems like something is very broken.

performing a docker logs -f yields hundreds or thousands of lines per second, this seems related?

 (version 24.3.3.102 (official build))
2024.10.27 17:36:29.485178 [ 621 ] {} <Error> MergeTreeBackgroundExecutor: Exception while executing background task {322de74c-73d3-4c28-829c-71146847af75::20241025_22689_33730_313}: Code: 241. DB::Exception: Memory limit (total) exceeded: would use 892.09 MiB (attempt to allocate chunk of 4361232 bytes), maximum: 861.81 MiB. OvercommitTracker decision: Memory overcommit isn't used. Waiting time or overcommit denominator are set to zero. (MEMORY_LIMIT_EXCEEDED), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbc851b
1. DB::Exception::Exception<char const*, char const*, String, long&, String, char const*, std::basic_string_view<char, std::char_traits<char>>>(int, FormatStringHelperImpl<std::type_identity<char const*>::type, std::type_identity<char const*>::type, std::type_identity<String>::type, std::type_identity<long&>::type, std::type_identity<String>::type, std::type_identity<char const*>::type, std::type_identity<std::basic_string_view<char, std::char_traits<char>>>::type>, char const*&&, char const*&&, String&&, long&, String&&, char const*&&, std::basic_string_view<char, std::char_traits<char>>&&) @ 0x000000000cbde72a
2. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbde368
3. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbddda9
4. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbddda9
5. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbddda9
6. Allocator<false, false>::alloc(unsigned long, unsigned long) @ 0x000000000cb9b7ed
7. DB::Memory<Allocator<false, false>>::alloc(unsigned long) @ 0x000000000cc220a2

Please let me know what I should do. I grew my swap file from 1024 to 2048 but that didn't help.

[ruslandoga]

👋 @russellballestrini

Code: 241. DB::Exception: Memory limit (total) exceeded: would use 892.09 MiB

ClickHouse is running out of memory. The easiest solution is to give it more RAM.

[ruslandoga]

Did my clickhouse have some big object that it needed to process

Yes, the message seems to come from a background job that merges merge tree parts.

now I can scale back down or will the issue come back?

It would probably happen again, once the big parts get selected to be merged again.

[ruslandoga]

https://clickhouse.com/docs/en/operations/tips#using-less-than-16gb-of-ram might be helpful.

Is there any stacktrace in the error message?

Stack trace (when copying this message, always include the lines below):

It might help us identify which configuration options for which subsystem to tune.

---

And also setting these lower than defaults might help:

• https://clickhouse.com/docs/en/operations/settings/query-complexity#settings_max_memory_usage
• https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#merges_mutations_memory_usage_to_ram_ratio
• https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#merges_mutations_memory_usage_soft_limit
• https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#background_pool_size

[russellballestrini]

 (version 24.3.3.102 (official build))                                                                                                                                                       [56/9238]
2024.10.28 12:54:57.683968 [ 628 ] {} <Error> MergeTreeBackgroundExecutor: Exception while executing background task {322de74c-73d3-4c28-829c-71146847af75::20241028_52864_57099_302}: Code: 241. DB::
Exception: Memory limit (total) exceeded: would use 1.73 GiB (attempt to allocate chunk of 4361232 bytes), maximum: 1.73 GiB. OvercommitTracker decision: Memory overcommit isn't used. Waiting time o
r overcommit denominator are set to zero. (MEMORY_LIMIT_EXCEEDED), Stack trace (when copying this message, always include the lines below):                                                           
                                                                                                                                                                                                      
0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbc851b                                                                                                           
1. DB::Exception::Exception<char const*, char const*, String, long&, String, char const*, std::basic_string_view<char, std::char_traits<char>>>(int, FormatStringHelperImpl<std::type_identity<char co
nst*>::type, std::type_identity<char const*>::type, std::type_identity<String>::type, std::type_identity<long&>::type, std::type_identity<String>::type, std::type_identity<char const*>::type, std::t
ype_identity<std::basic_string_view<char, std::char_traits<char>>>::type>, char const*&&, char const*&&, String&&, long&, String&&, char const*&&, std::basic_string_view<char, std::char_traits<char>
>&&) @ 0x000000000cbde72a                                                                                                                                                                             
2. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbde368                                                                                                                  
3. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbddda9                                                                                                                  
4. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbddda9                                                                                                                  
5. MemoryTracker::allocImpl(long, bool, MemoryTracker*, double) @ 0x000000000cbddda9                                                                                                                  
6. Allocator<false, false>::alloc(unsigned long, unsigned long) @ 0x000000000cb9b7ed                                                                                                                  
7. DB::Memory<Allocator<false, false>>::alloc(unsigned long) @ 0x000000000cc220a2                                                                                                                     
8. DB::WriteBufferFromFileDescriptor::WriteBufferFromFileDescriptor(int, unsigned long, char*, std::shared_ptr<DB::Throttler>, unsigned long, String) @ 0x000000000cc43634                            
9. DB::WriteBufferFromFile::WriteBufferFromFile(String const&, unsigned long, int, std::shared_ptr<DB::Throttler>, unsigned int, char*, unsigned long) @ 0x000000000cd37da4
10. DB::DiskLocal::writeFile(String const&, unsigned long, DB::WriteMode, DB::WriteSettings const&) @ 0x0000000010327773                                                                              
11. DB::DataPartStorageOnDiskFull::writeFile(String const&, unsigned long, DB::WriteMode, DB::WriteSettings const&) @ 0x0000000011c6c943                                                              
12. DB::MergeTreeDataPartWriterOnDisk::Stream<false>::Stream(String const&, std::shared_ptr<DB::IDataPartStorage> const&, String const&, String const&, String const&, String const&, std::shared_ptr<
DB::ICompressionCodec> const&, unsigned long, std::shared_ptr<DB::ICompressionCodec> const&, unsigned long, DB::WriteSettings const&) @ 0x0000000011e1491e
13. void std::__function::__policy_invoker<void (DB::ISerialization::SubstreamPath const&)>::__call_impl<std::__function::__default_alloc_func<DB::MergeTreeDataPartWriterWide::addStreams(DB::NameAnd
TypePair const&, std::shared_ptr<DB::IAST> const&)::$_0, void (DB::ISerialization::SubstreamPath const&)>>(std::__function::__policy_storage const*, DB::ISerialization::SubstreamPath const&) @ 0x000
0000011e2578d                                                                                      
14. DB::SerializationSparse::enumerateStreams(DB::ISerialization::EnumerateStreamsSettings&, std::function<void (DB::ISerialization::SubstreamPath const&)> const&, DB::ISerialization::SubstreamData 
const&) const @ 0x00000000101371af
15. DB::ISerialization::enumerateStreams(std::function<void (DB::ISerialization::SubstreamPath const&)> const&, std::shared_ptr<DB::IDataType const> const&, COW<DB::IColumn>::immutable_ptr<DB::IColu
mn> const&) const @ 0x00000000100f2c01
16. DB::MergeTreeDataPartWriterWide::MergeTreeDataPartWriterWide(std::shared_ptr<DB::IMergeTreeDataPart> const&, DB::NamesAndTypesList const&, std::shared_ptr<DB::StorageInMemoryMetadata const> cons
t&, std::vector<std::shared_ptr<DB::IMergeTreeIndex const>, std::allocator<std::shared_ptr<DB::IMergeTreeIndex const>>> const&, std::vector<std::shared_ptr<DB::IStatistic>, std::allocator<std::share
d_ptr<DB::IStatistic>>> const&, String const&, std::shared_ptr<DB::ICompressionCodec> const&, DB::MergeTreeWriterSettings const&, DB::MergeTreeIndexGranularity const&) @ 0x0000000011e1d0cb
17. DB::MergeTreeDataPartWide::getWriter(DB::NamesAndTypesList const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::vector<std::shared_ptr<DB::IMergeTreeIndex const>, std::allocat
or<std::shared_ptr<DB::IMergeTreeIndex const>>> const&, std::vector<std::shared_ptr<DB::IStatistic>, std::allocator<std::shared_ptr<DB::IStatistic>>> const&, std::shared_ptr<DB::ICompressionCodec> c
onst&, DB::MergeTreeWriterSettings const&, DB::MergeTreeIndexGranularity const&) @ 0x0000000011e073a4
18. DB::MergedBlockOutputStream::MergedBlockOutputStream(std::shared_ptr<DB::IMergeTreeDataPart> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, DB::NamesAndTypesList const&, std:
:vector<std::shared_ptr<DB::IMergeTreeIndex const>, std::allocator<std::shared_ptr<DB::IMergeTreeIndex const>>> const&, std::vector<std::shared_ptr<DB::IStatistic>, std::allocator<std::shared_ptr<DB
::IStatistic>>> const&, std::shared_ptr<DB::ICompressionCodec>, std::shared_ptr<DB::MergeTreeTransaction> const&, bool, bool, DB::WriteSettings const&, DB::MergeTreeIndexGranularity const&) @ 0x0000
000011f626a4
19. DB::MergeTask::ExecuteAndFinalizeHorizontalPart::prepare() @ 0x0000000011d0d962
20. DB::MergeTask::ExecuteAndFinalizeHorizontalPart::execute() @ 0x0000000011d13980
21. DB::MergeTask::execute() @ 0x0000000011d17e79 

[russellballestrini]

I see many tunable linked. thanks. any recommendations & where do I config it?

[ruslandoga]

You can add XML configs as bind mounts like https://github.com/plausible/community-edition/blob/5eb6ea318cfc57fcce520af1e568f56dac805916/compose.yml#L19-L22 and ClickHouse would merge them into the final configuration.