​______ Minimizes the Number of Disk Reads Necessary to Retrieve a Row of Data.
Clickhouse Server - Troubleshooting
Abstract
- When nosotros get clickhouse operation upshot like high CPU usage, in lodge to investigate what is the existent trouble and how to solve or provide workaround, we need to understand Clickhouse system/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
- Empathise 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: car (number of CPU cores). -
During startup ClickHouse reads all parts of all tables (reads files with metadata of parts) to build a listing of all parts in memory. In some systems with a large number of parts this process can take a long time, and this time might exist shortened by increasing max_part_loading_threads (if this procedure is not CPU and disk I/O bound).
-
Query cheque
SELECT * FROM system.merge_tree_settings WHERE name = 'max_part_loading_threads' Query id: 5f8c7c7a-5dec-4e89-88dc-71f06d800e04 ┌─name─────────────────────┬─value─────┬─changed─┬─description──────────────────────────────────────────┬─type───────┐ │ max_part_loading_threads │ 'auto(4)' │ 0 │ The number of threads to load data parts at startup. │ MaxThreads │ └──────────────────────────┴───────────┴─────────┴──────────────────────────────────────────────────────┴────────────┘ 1 rows in set. Elapsed: 0.003 sec.
🚀 max_part_removal_threads
The number of threads for concurrent removal of inactive information parts. I is usually enough, but in 'Google Compute Surround SSD Persistent Disks' file removal (unlink) performance is extraordinarily slow and you lot probably have to increment this number (recommended is upwards to 16).
🚀 number_of_free_entries_in_pool_to_execute_mutation
- This attribute must be align with
background_pool_size, its values must exist <= value ofbackground_pool_size
SELECT * FROM arrangement.merge_tree_settings WHERE proper name = 'number_of_free_entries_in_pool_to_execute_mutation' ┌─name───────────────────────────────────────────────┬─value─┬─changed─┬─description──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ number_of_free_entries_in_pool_to_execute_mutation │ ten │ 0 │ When there is less than specified number of free entries in pool, do not execute part mutations. This is to exit complimentary threads for regular merges and avoid "Too many parts" │ └────────────────────────────────────────────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
🚀 background_pool_size
-
background_pool_size
Sets the number of threads performing background operations in tabular array engines (for case, merges in MergeTree engine tables). This setting is applied from thedefault contour at the ClickHouse server outset and can't be changed in a user session. By adjusting this setting, yous manage CPU and deejay load. Smaller pool sizeutilizes less CPU and disk resources, but background processes accelerate slower which might eventually bear on query performance. -
Before changing it, please too take a expect at related MergeTree settings, such as number_of_free_entries_in_pool_to_lower_max_size_of_merge andnumber_of_free_entries_in_pool_to_execute_mutation.
-
Possible values:
Any positive integer.
Default value: 16. -
First 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: Bachelor RAM: xv.08 GiB; concrete cores: 4; logical cores: viii.
- How to update this value eg. 5
Update config.xml
<merge_tree> <number_of_free_entries_in_pool_to_execute_mutation>5</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 beginning and can't be changed in a user session. -
Possible values:
Any positive integer.
Default value: 128. -
How to update this value? - At user profile -> update
users.xml(disablebackground_schedule_pool_sizeif nosotros don't applyReplicatedMergeTreeengine)
<profiles> <default> <background_schedule_pool_size>0</background_schedule_pool_size> </default> </profiles>
- Get pool size
SELECT name, value FROM system.settings WHERE name Similar '%pool%' ┌─proper name─────────────────────────────────────────┬─value─┐ │ connection_pool_max_wait_ms │ 0 │ │ distributed_connections_pool_size │ 1024 │ │ background_buffer_flush_schedule_pool_size │ 16 │ │ background_pool_size │ 100 │ │ background_move_pool_size │ 8 │ │ background_fetches_pool_size │ 8 │ │ background_schedule_pool_size │ 0 │ │ background_message_broker_schedule_pool_size │ sixteen │ │ background_distributed_schedule_pool_size │ 16 │ └──────────────────────────────────────────────┴───────┘
- Go background pool task
SELECT metric, value FROM organization.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("%south\t%s\n", $1, $2) }' | grep BgSchPool 7346 BgSchPool/D
- Viewing cluster
SELECT cluster, shard_num, replica_num, host_name FROM organization.clusters ┌─cluster───────────────────────────┬─shard_num─┬─replica_num─┬─host_name─┐ │ test_cluster_two_shards │ 1 │ i │ 127.0.0.1 │ │ test_cluster_two_shards │ ii │ one │ 127.0.0.2 │ │ test_cluster_two_shards_localhost │ 1 │ ane │ localhost │ │ test_cluster_two_shards_localhost │ two │ one │ localhost │ │ test_shard_localhost │ 1 │ 1 │ localhost │ │ test_shard_localhost_secure │ 1 │ one │ 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 information 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 instance, when reading from a table, if it is possible to evaluate expressions with functions, filter with WHERE and pre-amass for Group 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 apace because of a LIMIT, you lot tin set 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 have been plenty to read just one.
-
The smaller the max_threads value, the less memory is consumed.
-
Update this value at user profile
🚀 Get tables size
clickhouse-get-tables-size.sql
select concat(database, '.', table) as table, formatReadableSize(sum(bytes)) as size, sum(rows) as rows, max(modification_time) as latest_modification, sum(bytes) as bytes_size, whatever(engine) as engine, formatReadableSize(sum(primary_key_bytes_in_memory)) equally primary_keys_size from organization.parts where active grouping by database, tabular array order by bytes_size desc
- For table detail of database
select parts.*, columns.compressed_size, columns.uncompressed_size, columns.ratio from ( select tabular array, 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 organization.columns where database = currentDatabase() grouping by tabular array ) columns right bring together ( select table, sum(rows) every bit rows, max(modification_time) as latest_modification, formatReadableSize(sum(bytes)) as disk_size, formatReadableSize(sum(primary_key_bytes_in_memory)) equally primary_keys_size, whatever(engine) equally engine, sum(bytes) every bit bytes_size from organisation.parts where active and database = currentDatabase() group by database, table ) parts on columns.table = parts.table order by parts.bytes_size desc;
🚀 Understand clickhouse Compression
- Pinch in ClickHouse
🚀 Enable allow_introspection_functions for query profiling
- Introspection Functions. Update at user contour
<default> <allow_introspection_functions>1</allow_introspection_functions> </default>
- Become thread stack trace
WITH arrayMap(x -> demangle(addressToSymbol(x)), trace) AS all SELECT thread_id, query_id, arrayStringConcat(all, '\due north') AS res FROM system.stack_trace WHERE res Similar '%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 active parts in a single partition exceeds the parts_to_throw_insert value, INSERT is interrupted with the As well many parts (North). Merges are processing significantly slower than inserts exception.
Possible values:
Whatever positive integer.
Default value: 300.
To achieve maximum performance of SELECT queries, information technology is necessary to minimize the number of parts processed, see Merge Tree.
Yous can set a larger value to 600 (1200), this volition reduce the probability of the Besides many parts error, but at the same time SELECT functioning might degrade. Also in case of a merge upshot (for example, due to insufficient deejay space) you volition find it subsequently than it could exist with the original 300.
- Facing issue?
2021.08.thirty eleven:30:44.526367 [ 7369 ] {} <Error> void DB::SystemLog<DB::MetricLogElement>::flushImpl(const std::vector<LogElement> &, uint64_t) [LogElement = DB::MetricLogElement]: Code: 252, e.displayText() = DB::Exception: Too many parts (300). Parts cleaning are processing significantly slower than inserts, Stack trace (when copying this message, always include the lines beneath):
- And you lot 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 "​______ Minimizes the Number of Disk Reads Necessary to Retrieve a Row of Data."
Postar um comentário