Clickhouse [read Meta] Unexpected Packet [5] From Server
Clickhouse Server - Troubleshooting
Abstruse
- When we get clickhouse performance issue like high CPU usage, in society to investigate what is the real problem and how to solve or provide workaround, we need to understand Clickhouse arrangement/user config attributes.
Table Of Contents
- max_part_loading_threads
- max_part_removal_threads
- number_of_free_entries_in_pool_to_execute_mutation
- background_pool_size
- background_schedule_pool_size
- max_threads
- Get tables size
- Sympathise clickhouse
- Enable allow_introspection_functions for query profiling
- parts_to_throw_insert
🚀 max_part_loading_threads
The maximum number of threads that read parts when ClickHouse starts.
-
Possible values:
Any positive integer.
Default value: auto (number of CPU cores). -
During startup ClickHouse reads all parts of all tables (reads files with metadata of parts) to build a list of all parts in memory. In some systems with a large number of parts this procedure can take a long time, and this time might be shortened by increasing max_part_loading_threads (if this procedure is not CPU and disk I/O bound).
-
Query check
SELECT * FROM organization.merge_tree_settings WHERE proper name = 'max_part_loading_threads' Query id: 5f8c7c7a-5dec-4e89-88dc-71f06d800e04 ┌─name─────────────────────┬─value─────┬─changed─┬─clarification──────────────────────────────────────────┬─type───────┐ │ max_part_loading_threads │ 'auto(4)' │ 0 │ The number of threads to load data parts at startup. │ MaxThreads │ └──────────────────────────┴───────────┴─────────┴──────────────────────────────────────────────────────┴────────────┘ 1 rows in gear up. Elapsed: 0.003 sec.
🚀 max_part_removal_threads
The number of threads for concurrent removal of inactive information parts. One is usually enough, simply in 'Google Compute Surround SSD Persistent Disks' file removal (unlink) operation is extraordinarily slow and you probably have to increment this number (recommended is up to xvi).
🚀 number_of_free_entries_in_pool_to_execute_mutation
- This attribute must be align with
background_pool_size, its values must be <= value ofbackground_pool_size
SELECT * FROM organization.merge_tree_settings WHERE name = 'number_of_free_entries_in_pool_to_execute_mutation' ┌─name───────────────────────────────────────────────┬─value─┬─changed─┬─description──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ number_of_free_entries_in_pool_to_execute_mutation │ 10 │ 0 │ When at that place is less than specified number of free entries in puddle, do not execute office mutations. This is to go out free threads for regular merges and avoid "Also many parts" │ └────────────────────────────────────────────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
🚀 background_pool_size
-
background_pool_size
Sets the number of threads performing background operations in table engines (for case, merges in MergeTree engine tables). This setting is practical from thedefault contour at the ClickHouse server first and can't exist changed in a user session. By adjusting this setting, you manage CPU and disk load. Smaller pool sizeutilizes less CPU and disk resources, but background processes advance slower which might somewhen bear on query performance. -
Before changing it, please also accept a expect at related MergeTree settings, such equally number_of_free_entries_in_pool_to_lower_max_size_of_merge andnumber_of_free_entries_in_pool_to_execute_mutation.
-
Possible values:
Whatever positive integer.
Default value: 16. -
Starting time log
2021.08.29 04:22:30.824446 [ 12372 ] {} <Information> BackgroundSchedulePool/BgSchPool: Create BackgroundSchedulePool with 16 threads 2021.08.29 04:22:47.891697 [ 12363 ] {} <Information> Awarding: Available RAM: 15.08 GiB; physical cores: four; logical cores: 8.
- How to update this value eg. 5
Update config.xml
<merge_tree> <number_of_free_entries_in_pool_to_execute_mutation>five</number_of_free_entries_in_pool_to_execute_mutation> </merge_tree>
Update users.xml
<profiles> <default> <background_pool_size>5</background_pool_size> </default> </profiles>
🚀 background_schedule_pool_size
-
background_schedule_pool_size
Sets the number of threads performing background tasks for replicated tables, Kafka streaming, DNS cache updates. This setting is applied at ClickHouse server offset and tin't be changed in a user session. -
Possible values:
Whatever positive integer.
Default value: 128. -
How to update this value? - At user profile -> update
users.xml(disablebackground_schedule_pool_sizeif we don't utiliseReplicatedMergeTreeengine)
<profiles> <default> <background_schedule_pool_size>0</background_schedule_pool_size> </default> </profiles>
- Get pool size
SELECT proper name, value FROM organization.settings WHERE name LIKE '%puddle%' ┌─name─────────────────────────────────────────┬─value─┐ │ connection_pool_max_wait_ms │ 0 │ │ distributed_connections_pool_size │ 1024 │ │ background_buffer_flush_schedule_pool_size │ sixteen │ │ background_pool_size │ 100 │ │ background_move_pool_size │ viii │ │ background_fetches_pool_size │ eight │ │ background_schedule_pool_size │ 0 │ │ background_message_broker_schedule_pool_size │ 16 │ │ background_distributed_schedule_pool_size │ 16 │ └──────────────────────────────────────────────┴───────┘
- Get background pool task
SELECT metric, value FROM system.metrics WHERE metric LIKE 'Background%' ┌─metric──────────────────────────────────┬─value─┐ │ BackgroundPoolTask │ 0 │ │ BackgroundFetchesPoolTask │ 0 │ │ BackgroundMovePoolTask │ 0 │ │ BackgroundSchedulePoolTask │ 0 │ │ BackgroundBufferFlushSchedulePoolTask │ 0 │ │ BackgroundDistributedSchedulePoolTask │ 0 │ │ BackgroundMessageBrokerSchedulePoolTask │ 0 │ └─────────────────────────────────────────┴───────┘
- Get BgSchPool
# ps H -o 'tid comm' $(pidof -s clickhouse-server) | tail -due north +2 | awk '{ printf("%s\t%s\n", $ane, $2) }' | grep BgSchPool 7346 BgSchPool/D
- Viewing cluster
SELECT cluster, shard_num, replica_num, host_name FROM system.clusters ┌─cluster───────────────────────────┬─shard_num─┬─replica_num─┬─host_name─┐ │ test_cluster_two_shards │ 1 │ 1 │ 127.0.0.1 │ │ test_cluster_two_shards │ 2 │ ane │ 127.0.0.ii │ │ test_cluster_two_shards_localhost │ 1 │ 1 │ localhost │ │ test_cluster_two_shards_localhost │ 2 │ 1 │ localhost │ │ test_shard_localhost │ 1 │ 1 │ localhost │ │ test_shard_localhost_secure │ 1 │ 1 │ localhost │ │ test_unavailable_shard │ i │ 1 │ localhost │ │ test_unavailable_shard │ 2 │ 1 │ localhost │ └───────────────────────────────────┴───────────┴─────────────┴───────────┘
🚀 max_threads
max_threads
-
The maximum number of query processing threads, excluding threads for retrieving data from remote servers (see the 'max_distributed_connections' parameter).
-
This parameter applies to threads that perform the same stages of the query processing pipeline in parallel.
For example, when reading from a table, if it is possible to evaluate expressions with functions, filter with WHERE and pre-aggregate for Grouping By in parallel using at least 'max_threads' number of threads, then 'max_threads' are used. -
Default value: the number of physical CPU cores.
-
For queries that are completed quickly because of a LIMIT, you can fix a lower 'max_threads'. For example, if the necessary number of entries are located in every block and max_threads = eight, then 8 blocks are retrieved, although it would accept been enough to read just one.
-
The smaller the max_threads value, the less retention is consumed.
-
Update this value at user profile
🚀 Get tables size
clickhouse-get-tables-size.sql
select concat(database, '.', tabular array) as table, formatReadableSize(sum(bytes)) as size, sum(rows) equally rows, max(modification_time) equally latest_modification, sum(bytes) as bytes_size, any(engine) as engine, formatReadableSize(sum(primary_key_bytes_in_memory)) every bit primary_keys_size from system.parts where active group by database, table order past bytes_size desc
- For table detail of database
select parts.*, columns.compressed_size, columns.uncompressed_size, columns.ratio from ( select table, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size, formatReadableSize(sum(data_compressed_bytes)) Equally compressed_size, sum(data_compressed_bytes) / sum(data_uncompressed_bytes) As ratio from system.columns where database = currentDatabase() grouping by table ) columns right bring together ( select table, sum(rows) as rows, max(modification_time) every bit latest_modification, formatReadableSize(sum(bytes)) equally disk_size, formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size, any(engine) equally engine, sum(bytes) as bytes_size from arrangement.parts where active and database = currentDatabase() group by database, tabular array ) parts on columns.table = parts.table guild by parts.bytes_size desc;
🚀 Understand clickhouse Compression
- Pinch in ClickHouse
🚀 Enable allow_introspection_functions for query profiling
- Introspection Functions. Update at user profile
<default> <allow_introspection_functions>one</allow_introspection_functions> </default>
- Get thread stack trace
WITH arrayMap(x -> demangle(addressToSymbol(10)), trace) Equally all SELECT thread_id, query_id, arrayStringConcat(all, '\n') AS res FROM system.stack_trace WHERE res LIKE '%SchedulePool%' ┌─thread_id─┬─query_id─┬─res──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ 7346 │ │ pthread_cond_wait DB::BackgroundSchedulePool::delayExecutionThreadFunction() ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) start_thread clone │ └───────────┴──────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
🚀 parts_to_throw_insert
- parts_to_throw_insert If the number of agile parts in a single partitioning exceeds the parts_to_throw_insert value, INSERT is interrupted with the Too many parts (N). Merges are processing significantly slower than inserts exception.
Possible values:
Whatsoever positive integer.
Default value: 300.
To achieve maximum operation of SELECT queries, it is necessary to minimize the number of parts processed, see Merge Tree.
Yous can ready a larger value to 600 (1200), this will reduce the probability of the Likewise many parts fault, but at the same time SELECT performance might degrade. Also in case of a merge issue (for instance, due to bereft disk infinite) you will find it later on than it could exist with the original 300.
- Facing issue?
2021.08.30 11:thirty:44.526367 [ 7369 ] {} <Error> void DB::SystemLog<DB::MetricLogElement>::flushImpl(const std::vector<LogElement> &, uint64_t) [LogElement = DB::MetricLogElement]: Code: 252, due east.displayText() = DB::Exception: Too many parts (300). Parts cleaning are processing significantly slower than inserts, Stack trace (when copying this message, e'er include the lines below):
- And you decide to increment
parts_to_throw_insert-> Updateconfig.xml
<merge_tree> <parts_to_throw_insert>600</parts_to_throw_insert> </merge_tree>
Source: https://dev.to/vumdao/clickhouse-server-troubleshooting-2gb7
0 Response to "Clickhouse [read Meta] Unexpected Packet [5] From Server"
Postar um comentário