Clickhouse: The user may accidentially set background pool size lower than `number_of_free_entries_in_pool_to_execute_mutation` and it will prevent ALTERs to process.

[silviucpp]

Hello,

I upgrade to Clickhouse c811e1f0d0c439b74843770082569aa7fd5f9743 (master 2 days ago) and I'm facing lot of problems (https://github.com/ClickHouse/ClickHouse/issues/10892) .

For example:

CREATE DATABASE tb1
CREATE TABLE tb1.collection_ops_SUITE(`string` Nullable(String),`int` Nullable(Int64),`timestamp` DateTime,`created_at` DateTime) ENGINE = MergeTree PARTITION BY toYYYYMM(timestamp) ORDER BY (timestamp)
INSERT INTO tb1.collection_ops_SUITE(`created_at`,`timestamp`,`int`,`string`) VALUES (1589395126,1589395126,1,'str'),(1589395126,1589395126,2,'str')
ALTER TABLE tb1.collection_ops_SUITE DROP COLUMN `int`

Running following queries with HTTP client or tabbix will hang forever in the ALTER table.

In logs I see:

2020.05.13 18:44:13.557064 [ 2579621 ] {00000000-0000-4000-8000-000000000000} <Information> tb1.collection_ops_SUITE: Added mutation: mutation_2.txt
2020.05.13 18:44:13.557266 [ 2579621 ] {00000000-0000-4000-8000-000000000000} <Information> tb1.collection_ops_SUITE: Waiting mutation: mutation_2.txt

Then nothing more

Silviu

[den-crane]

No issue for me.

20.5.1.3327 https://repo.yandex.ru/clickhouse/deb/testing/main/
https://github.com/ClickHouse/ClickHouse/commit/c811e1f0d0c439b74843770082569aa7fd5f9743

Tested 4 times (clickhouse-client & http)

[silviucpp]

@den-crane Testing on linux ? I'm testing on OSX .

[den-crane]

Linux. I do not have OSX

[silviucpp]

Can you paste the config files :) I want to try with your config files to see maybe something related from config

[den-crane]

No configs. Only vendor's from deb.

[silviucpp]

Will try to replace the config files with the default one and see. Can you please test https://github.com/ClickHouse/ClickHouse/issues/10900 as well to see if for you crashes ?

Silviu

[den-crane]

Did you build it?
Try official binary clang-10-darwin https://clickhouse-builds.s3.yandex.net/0/c811e1f0d0c439b74843770082569aa7fd5f9743/report.html

[silviucpp]

I build myself and disabled some modules :)) but same build instruction are used on past version which works fine. I'll try to use a precompiled binary to see.

[silviucpp]

Ok thanks for links I see the prebuild works just fine. will recompile mine with all modules enabled then isolate the problem and post here the results.

[den-crane]

I would check a compiler and build options https://clickhouse-builds.s3.yandex.net/0/c811e1f0d0c439b74843770082569aa7fd5f9743/build_log_673063988_1589261189.txt

[silviucpp]

Hmm that seems a build did on linux for osx.. https://clickhouse.tech/docs/en/development/build-cross-osx/

[silviucpp]

@alexey-milovidov I can replicate this problem on OSX no matter what build I'm using. The offcial build or building myself...

Anything that can generate this ?

[alexey-milovidov]

@qoega Please validate.

[silviucpp]

If helps in my canse the code freeze in the mutation_wait_event.wait(lock, check); (see image)

In the file system I see a new file created mutation_2.txt with the following content:

format version: 1
create time: 2020-05-14 15:17:44
commands: DROP COLUMN int 

[silviucpp]

OK so I found the real problem. background_pool_size If is less than 11 mutations are freezing forever. If is 11 or more works like charm.

I have a feeling that's because of :

M(SettingUInt64, number_of_free_entries_in_pool_to_execute_mutation, 10, "When there is less than specified number of free entries in pool, do not execute part mutations. This is to leave free threads for regular merges and avoid \"Too many parts\"", 0) \

Silviu

[filimonov]

The issue caused by PR: https://github.com/ClickHouse/ClickHouse/pull/6617 (the feature was introduced in 19.14).

[sneakythr0ws]

Same with ALTER TABLE DROP PARTITION on 20.5.2.7

2020.07.10 09:43:14.382172 [ 31263 ] {} <Error> DynamicQueryHandler: Code: 473, e.displayText() = DB::Exception: READ locking attempt on "table" has timed out! (120000ms) Possible deadlock avoided. Client should retry.: While executing Tables, 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) @ 0x10ed0da0 in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x95c923d in /usr/bin/clickhouse
2. ? @ 0xe1a7746 in /usr/bin/clickhouse
3. DB::IStorage::lockStructureForShare(bool, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, DB::SettingTimespan<(DB::SettingTimespanIO)1> const&) @ 0xe1a1ed3 in /usr/bin/clickhouse
4. DB::TablesBlockSource::generate() @ 0xd6a6434 in /usr/bin/clickhouse
5. DB::ISource::work() @ 0xe6dd3ab in /usr/bin/clickhouse
6. DB::SourceWithProgress::work() @ 0xe91f337 in /usr/bin/clickhouse
7. ? @ 0xe70aa21 in /usr/bin/clickhouse
8. ? @ 0xe70f1a6 in /usr/bin/clickhouse
9. DB::PipelineExecutor::executeImpl(unsigned long) @ 0xe71072d in /usr/bin/clickhouse
10. DB::PipelineExecutor::execute(unsigned long) @ 0xe711161 in /usr/bin/clickhouse
11. 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&)>) @ 0xe078f00 in /usr/bin/clickhouse
12. DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0xe634b09 in /usr/bin/clickhouse
13. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0xe6382ba in /usr/bin/clickhouse
14. Poco::Net::HTTPServerConnection::run() @ 0x10db5183 in /usr/bin/clickhouse
15. Poco::Net::TCPServerConnection::start() @ 0x10deebcb in /usr/bin/clickhouse
16. Poco::Net::TCPServerDispatcher::run() @ 0x10def05b in /usr/bin/clickhouse
17. Poco::PooledThread::run() @ 0x10f6db86 in /usr/bin/clickhouse
18. Poco::ThreadImpl::runnableEntry(void*) @ 0x10f68f80 in /usr/bin/clickhouse
19. start_thread @ 0x7fa3 in /lib/x86_64-linux-gnu/libpthread-2.28.so
20. __clone @ 0xf94cf in /lib/x86_64-linux-gnu/libc-2.28.so
 (version 20.5.2.7 (official build))