4.3. Properties Reference

This section describes the most important config properties thatmay be used to tune Presto or alter its behavior when required.

General Properties

distributed-joins-enabled

- Type: boolean- Default value: trueUse hash distributed joins instead of broadcast joins. Distributed joinsrequire redistributing both tables using a hash of the join key. This canbe slower (sometimes substantially) than broadcast joins, but allows muchlarger joins. Broadcast joins require that the tables on the right side ofthe join after filtering fit in memory on each node, whereas distributed joinsonly need to fit in distributed memory across all nodes. This can also bespecified on a per-query basis using the distributed_join session property.

redistribute-writes

- Type: boolean- Default value: trueThis property enables redistribution of data before writing. This caneliminate the performance impact of data skew when writing by hashing itacross nodes in the cluster. It can be disabled when it is known that theoutput data set is not skewed in order to avoid the overhead of hashing andredistributing all the data across the network. This can also be specifiedon a per-query basis using the redistribute_writes session property.

resources.reserved-system-memory

- Type: data size- Default value: JVM max memory * 0.4The amount of JVM memory reserved, for accounting purposes, for thingsthat are not directly attributable to or controllable by a user query.For example, output buffers, code caches, etc. This also accounts formemory that is not tracked by the memory tracking system.The purpose of this property is to prevent the JVM from running out ofmemory (OOM). The default value is suitable for smaller JVM heap sizes orclusters with many concurrent queries. If running fewer queries with alarge heap, a smaller value may work. Basically, set this value largeenough that the JVM does not fail with OutOfMemoryError.

Spilling Properties

experimental.spill-enabled

- Type: boolean- Default value: falseTry spilling memory to disk to avoid exceeding memory limits for the query.Spilling works by offloading memory to disk. This process can allow a query with a large memoryfootprint to pass at the cost of slower execution times. Currently, spilling is supported only foraggregations and joins (inner and outer), so this property will not reduce memory usage required forwindow functions, sorting and other join types.Be aware that this is an experimental feature and should be used with care.This config property can be overridden by the spill_enabled session property.

experimental.spiller-spill-path

- Type: string- No default value. Must be set when spilling is enabledDirectory where spilled content will be written. It can be a comma separatedlist to spill simultaneously to multiple directories, which helps to utilizemultiple drives installed in the system.It is not recommended to spill to system drives. Most importantly, do not spillto the drive on which the JVM logs are written, as disk overutilization mightcause JVM to pause for lengthy periods, causing queries to fail.

experimental.spiller-max-used-space-threshold

- Type: double- Default value: 0.9If disk space usage ratio of a given spill path is above this threshold,this spill path will not be eligible for spilling.

experimental.spiller-threads

- Type: integer- Default value: 4Number of spiller threads. Increase this value if the default is not ableto saturate the underlying spilling device (for example, when using RAID).

experimental.max-spill-per-node

- Type: data size- Default value: 100 GBMax spill space to be used by all queries on a single node.

experimental.query-max-spill-per-node

- Type: data size- Default value: 100 GBMax spill space to be used by a single query on a single node.

experimental.aggregation-operator-unspill-memory-limit

- Type: data size- Default value: 4 MBLimit for memory used for unspilling a single aggregation operator instance.

Exchange Properties

Exchanges transfer data between Presto nodes for different stages ofa query. Adjusting these properties may help to resolve inter-nodecommunication issues or improve network utilization.

exchange.client-threads

- Type: integer- Minimum value: 1- Default value: 25Number of threads used by exchange clients to fetch data from other Prestonodes. A higher value can improve performance for large clusters or clusterswith very high concurrency, but excessively high values may cause a dropin performance due to context switches and additional memory usage.

exchange.concurrent-request-multiplier

- Type: integer- Minimum value: 1- Default value: 3Multiplier determining the number of concurrent requests relative toavailable buffer memory. The maximum number of requests is determinedusing a heuristic of the number of clients that can fit into availablebuffer space based on average buffer usage per request times thismultiplier. For example, with an exchange.max-buffer-size of 32 MBand 20 MB already used and average size per request being 2MB,the maximum number of clients ismultiplier ((32MB - 20MB) / 2MB) = multiplier 6. Tuning thisvalue adjusts the heuristic, which may increase concurrency and improvenetwork utilization.

exchange.max-buffer-size

- Type: data size- Default value: 32MBSize of buffer in the exchange client that holds data fetched from othernodes before it is processed. A larger buffer can increase networkthroughput for larger clusters and thus decrease query processing time,but will reduce the amount of memory available for other usages.

exchange.max-response-size

- Type: data size- Minimum value: 1MB- Default value: 16MBMaximum size of a response returned from an exchange request. The responsewill be placed in the exchange client buffer which is shared across allconcurrent requests for the exchange.Increasing the value may improve network throughput if there is highlatency. Decreasing the value may improve query performance for largeclusters as it reduces skew due to the exchange client buffer holdingresponses for more tasks (rather than hold more data from fewer tasks).

sink.max-buffer-size

- Type: data size- Default value: 32MBOutput buffer size for task data that is waiting to be pulled by upstreamtasks. If the task output is hash partitioned, then the buffer will beshared across all of the partitioned consumers. Increasing this value mayimprove network throughput for data transferred between stages if thenetwork has high latency or if there are many nodes in the cluster.

Task Properties

task.concurrency

- Type: integer- Restrictions: must be a power of two- Default value: 16Default local concurrency for parallel operators such as joins and aggregations.This value should be adjusted up or down based on the query concurrency and workerresource utilization. Lower values are better for clusters that run many queriesconcurrently because the cluster will already be utilized by all the runningqueries, so adding more concurrency will result in slow downs due to contextswitching and other overhead. Higher values are better for clusters that only runone or a few queries at a time. This can also be specified on a per-query basisusing the task_concurrency session property.

task.http-response-threads

- Type: integer- Minimum value: 1- Default value: 100Maximum number of threads that may be created to handle HTTP responses. Threads arecreated on demand and are cleaned up when idle, thus there is no overhead to a largevalue if the number of requests to be handled is small. More threads may be helpfulon clusters with a high number of concurrent queries, or on clusters with hundredsor thousands of workers.

task.http-timeout-threads

- Type: integer- Minimum value: 1- Default value: 3Number of threads used to handle timeouts when generating HTTP responses. This valueshould be increased if all the threads are frequently in use. This can be monitoredvia the com.facebook.presto.server:name=AsyncHttpExecutionMBean:TimeoutExecutorJMX object. If ActiveCount is always the same as PoolSize, increase thenumber of threads.

task.info-update-interval

- Type: duration- Minimum value: 1ms- Maximum value: 10s- Default value: 3sControls staleness of task information, which is used in scheduling. Larger valuescan reduce coordinator CPU load, but may result in suboptimal split scheduling.

task.max-partial-aggregation-memory

- Type: data size- Default value: 16MBMaximum size of partial aggregation results for distributed aggregations. Increasing thisvalue can result in less network transfer and lower CPU utilization by allowing moregroups to be kept locally before being flushed, at the cost of additional memory usage.

task.max-worker-threads

- Type: integer- Default value: Node CPUs * 2Sets the number of threads used by workers to process splits. Increasing this numbercan improve throughput if worker CPU utilization is low and all the threads are in use,but will cause increased heap space usage. Setting the value too high may cause a dropin performance due to a context switching. The number of active threads is availablevia the RunningSplits property of thecom.facebook.presto.execution.executor:name=TaskExecutor.RunningSplits JXM object.

task.min-drivers

- Type: integer- Default value: task.max-worker-threads * 2The target number of running leaf splits on a worker. This is a minimum value becauseeach leaf task is guaranteed at least 3 running splits. Non-leaf tasks are alsoguaranteed to run in order to prevent deadlocks. A lower value may improve responsivenessfor new tasks, but can result in underutilized resources. A higher value can increaseresource utilization, but uses additional memory.

task.writer-count

- Type: integer- Restrictions: must be a power of two- Default value: 1The number of concurrent writer threads per worker per query. Increasing this value mayincrease write speed, especially when a query is not I/O bound and can take advantageof additional CPU for parallel writes (some connectors can be bottlenecked on CPU whenwriting due to compression or other factors). Setting this too high may cause the clusterto become overloaded due to excessive resource utilization. This can also be specified ona per-query basis using the task_writer_count session property.

Node Scheduler Properties

node-scheduler.max-splits-per-node

- Type: integer- Default value: 100The target value for the total number of splits that can be running foreach worker node.Using a higher value is recommended if queries are submitted in large batches(e.g., running a large group of reports periodically) or for connectors thatproduce many splits that complete quickly. Increasing this value may improvequery latency by ensuring that the workers have enough splits to keep themfully utilized.Setting this too high will waste memory and may result in lower performancedue to splits not being balanced across workers. Ideally, it should be setsuch that there is always at least one split waiting to be processed, butnot higher.

node-scheduler.max-pending-splits-per-task

- Type: integer- Default value: 10The number of outstanding splits that can be queued for each worker nodefor a single stage of a query, even when the node is already at the limit fortotal number of splits. Allowing a minimum number of splits per stage isrequired to prevent starvation and deadlocks.This value must be smaller than node-scheduler.max-splits-per-node,will usually be increased for the same reasons, and has similar drawbacksif set too high.

node-scheduler.min-candidates

- Type: integer- Minimum value: 1- Default value: 10The minimum number of candidate nodes that will be evaluated by thenode scheduler when choosing the target node for a split. Settingthis value too low may prevent splits from being properly balancedacross all worker nodes. Setting it too high may increase querylatency and increase CPU usage on the coordinator.

node-scheduler.network-topology

- Type: string- Allowed values: legacy, flat- Default value: legacy

Optimizer Properties

optimizer.dictionary-aggregation

- Type: boolean- Default value: falseEnables optimization for aggregations on dictionaries. This can also be specifiedon a per-query basis using the dictionary_aggregation session property.

optimizer.optimize-hash-generation

- Type: boolean- Default value: trueCompute hash codes for distribution, joins, and aggregations early during execution,allowing result to be shared between operations later in the query. This can reduceCPU usage by avoiding computing the same hash multiple times, but at the cost ofadditional network transfer for the hashes. In most cases it will decrease overallquery processing time. This can also be specified on a per-query basis using theoptimize_hash_generation session property.It is often helpful to disable this property when using EXPLAIN in orderto make the query plan easier to read.

optimizer.optimize-metadata-queries

- Type: boolean- Default value: falseEnable optimization of some aggregations by using values that are stored as metadata.This allows Presto to execute some simple queries in constant time. Currently, thisoptimization applies to max, min and approx_distinct of partitionkeys and other aggregation insensitive to the cardinality of the input (includingDISTINCT aggregates). Using this may speed up some queries significantly.The main drawback is that it can produce incorrect results if the connector returnspartition keys for partitions that have no rows. In particular, the Hive connectorcan return empty partitions if they were created by other systems (Presto cannotcreate them).

optimizer.optimize-single-distinct

- Type: boolean- Default value: trueThe single distinct optimization will try to replace multiple DISTINCT clauseswith a single GROUP BY clause, which can be substantially faster to execute.

optimizer.push-aggregation-through-join

- Type: boolean- Default value: trueWhen an aggregation is above an outer join and all columns from the outer side of the joinare in the grouping clause, the aggregation is pushed below the outer join. This optimizationis particularly useful for correlated scalar subqueries, which get rewritten to an aggregationover an outer join. For example:
  1. SELECT * FROM item i    WHERE i.i_current_price > (        SELECT AVG(j.i_current_price) FROM item j            WHERE i.i_category = j.i_category);
Enabling this optimization can substantially speed up queries by reducingthe amount of data that needs to be processed by the join. However, it may slow down somequeries that have very selective joins. This can also be specified on a per-query basis usingthe push_aggregation_through_join session property.

optimizer.push-table-write-through-union

- Type: boolean- Default value: trueParallelize writes when using UNION ALL in queries that write data. This improves thespeed of writing output tables in UNION ALL queries because these writes do not requireadditional synchronization when collecting results. Enabling this optimization can improveUNION ALL speed when write speed is not yet saturated. However, it may slow down queriesin an already heavily loaded system. This can also be specified on a per-query basisusing the push_table_write_through_union session property.

Regular Expression Function Properties

The following properties allow tuning the Regular Expression Functions.

regex-library

- Type: string- Allowed values: JONI, RE2J- Default value: JONIWhich library to use for regular expression functions.JONI is generally faster for common usage, but can require exponentialtime for certain expression patterns. RE2J uses a different algorithmwhich guarantees linear time, but is often slower.

re2j.dfa-states-limit

- Type: integer- Minimum value: 2- Default value: 2147483647The maximum number of states to use when RE2J builds the fastbut potentially memory intensive deterministic finite automaton (DFA)for regular expression matching. If the limit is reached, RE2J will fallback to the algorithm that uses the slower, but less memory intensivenon-deterministic finite automaton (NFA). Decreasing this value decreases themaximum memory footprint of a regular expression search at the cost of speed.

re2j.dfa-retries

- Type: integer- Minimum value: 0- Default value: 5The number of times that RE2J will retry the DFA algorithm whenit reaches a states limit before using the slower, but less memoryintensive NFA algorithm for all future inputs for that search. If hitting thelimit for a given input row is likely to be an outlier, you want to be ableto process subsequent rows using the faster DFA algorithm. If you are likelyto hit the limit on matches for subsequent rows as well, you want to use thecorrect algorithm from the beginning so as not to waste time and resources.The more rows you are processing, the larger this value should be.

原文: https://prestodb.io/docs/current/admin/properties.html