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

russellballestrini · 2024-10-27T18:05:47Z

russellballestrini
Oct 27, 2024

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 · 2024-10-28T01:50:47Z

ruslandoga
Oct 28, 2024
Collaborator

👋 @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.

14 replies

ruslandoga Oct 28, 2024
Collaborator

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 Oct 28, 2024
Collaborator

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:

russellballestrini Oct 28, 2024
Author

 (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 Oct 28, 2024
Author

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

ruslandoga Oct 28, 2024
Collaborator

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.

salomvary · 2024-11-20T12:17:51Z

salomvary
Nov 20, 2024

I am having the same issue, Clickhouse is using 100% repeatedly logs MEMORY_LIMIT_EXCEEDED errors.

Memory limit (total) exceeded: would use 3.36 GiB (attempt to allocate chunk of 4361184 bytes), current RSS 1.66 GiB, maximum: 3.35 GiB. OvercommitTracker decision: Memory overcommit isn't used. Waiting time or overcommit denominator are set to zero. (MEMORY_LIMIT_EXCEEDED)

Full stack trace here: https://gist.github.com/salomvary/34c1cf60f56e52a5e14433115f226512

The amount of memory (~4GB) available is reasonable compared to how small the database is:

SELECT
    database,
    `table`,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (database LIKE 'plausible_events_db') AND (`table` LIKE '%')
GROUP BY
    database,
    `table`
ORDER BY size DESC

Query id: 3274f20f-2e0e-4ad3-ab87-f99b05949c12

   ┌─database────────────┬─table───────────┬─compressed─┬─uncompressed─┬─compr_rate─┬───rows─┬─part_count─┐
1. │ plausible_events_db │ location_data   │ 2.89 MiB   │ 3.92 MiB     │       1.35 │ 203370 │          1 │
2. │ plausible_events_db │ sessions_v2     │ 4.77 KiB   │ 10.61 KiB    │       2.22 │     61 │          1 │
3. │ plausible_events_db │ events_v2       │ 4.25 KiB   │ 11.15 KiB    │       2.62 │     90 │          1 │
4. │ plausible_events_db │ ingest_counters │ 710.00 B   │ 1.85 KiB     │       2.67 │     77 │          1 │
   └─────────────────────┴─────────────────┴────────────┴──────────────┴────────────┴────────┴────────────┘

4 rows in set. Elapsed: 0.015 sec.

Using clickhouse/clickhouse-server:24.10.2.80 Docker image.

Also getting get_mempolicy: Operation not permitted to stderr, which seems to be this bug: ClickHouse/ClickHouse#70960. Not sure if relevant here.

11 replies

salomvary Nov 20, 2024

Update: I am still getting MEMORY_LIMIT_EXCEEDED errors on 24.3.3.102, but not continuously, more like every few minutes.

charleslcso Dec 3, 2024

With the help of @ruslandoga I got my first instance of Plausible working last night. Immediately I noticed CPU usage is very high. Nothing hitting Plausible, but CPU usage goes to almost 50%. I thought it might be doing some background stuff, and let it sat there.

12hrs later the CPU usage hasn't dropped.

Found out Postfix is triggering Clickhouse.

Captured from 'top':
16247 postfix 20 0 12.1g 540212 154768 S 31.6 25.8 356:19.84 clickhouse-serv

ruslandoga Dec 3, 2024
Collaborator

👋 @charleslcso

We don't use Postfix anywhere
postfix in your top line is the name of the user running clickhouse-serv and not the name of the program

We have an issue open to try and lower the default resource requirements for ClickHouse plausible/community-edition#185 but unless you are experiencing OOMs or other problems, having ClickHouse consume available resources is not an issue. After all, you have already paid for these resources (assuming you are using a cloud provider), so you might as well give them to ClickHouse :)

charleslcso Dec 3, 2024

Yes, point 2 is what's been observed. I thought Plausible would use postfix to run clickhouse-serv...

Weird. I never heard of clickhouse before using Plausible, so I never configured it.

Letting Clickhouse use resources is OK with me. But user Postfix using Clickhouse? I believe I need to get to bottom of this...

ruslandoga Dec 3, 2024
Collaborator

But user Postfix using Clickhouse? I believe I need to get to bottom of this...

In the default setup ClickHouse is being run in a container, and the ClickHouse container uses UID 101 by default, which might correspond to an existing user on the host. You can configure different UID / GID with compose.override.yml using user: directive.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

{{title}}

Replies: 2 comments 25 replies

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Select a reply

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

russellballestrini Oct 27, 2024

Replies: 2 comments · 25 replies

ruslandoga Oct 28, 2024 Collaborator

ruslandoga Oct 28, 2024 Collaborator

ruslandoga Oct 28, 2024 Collaborator

russellballestrini Oct 28, 2024 Author

russellballestrini Oct 28, 2024 Author

ruslandoga Oct 28, 2024 Collaborator

salomvary Nov 20, 2024

salomvary Nov 20, 2024

charleslcso Dec 3, 2024

ruslandoga Dec 3, 2024 Collaborator

charleslcso Dec 3, 2024

ruslandoga Dec 3, 2024 Collaborator

russellballestrini
Oct 27, 2024

Replies: 2 comments 25 replies

ruslandoga
Oct 28, 2024
Collaborator

ruslandoga Oct 28, 2024
Collaborator

ruslandoga Oct 28, 2024
Collaborator

russellballestrini Oct 28, 2024
Author

russellballestrini Oct 28, 2024
Author

ruslandoga Oct 28, 2024
Collaborator

salomvary
Nov 20, 2024

ruslandoga Dec 3, 2024
Collaborator

ruslandoga Dec 3, 2024
Collaborator