PostgreSQL Key Tuning Parameters - stolostron/search-v2-operator GitHub Wiki

List of Key Database Tuning Parameters that may effect Search. This is by no means all the parameters available for tuning, but we suspect that we should first focus on these only.

Summary

Postgres tuning for ACM Search.

Configuration parameter	Formula	System Default	Search Default (2.8)	Search Default (2.9)	5 MIL (50 clusters * 100k)	15 MIL (150 clusters)
--- CONTAINER config ---
Memory Limit	MemLimit	N/A	4 GB	4 GB	4 GB	8 GB ?
Memory Request	MemLimit /2	N/A	128 MB	1 GB	2 GB	4 GB ?
Shared Memory	MemLimit * 0.25	64 MB	64 MB	1 GB	1 GB	2 GB ?
Storage	??? TBD	N/A	10 GB	10 GB	10 GB	30 GB ?
--- POSTGRES config ---	---	---	---	---	---	---
work_mem (searchuser)	MemLimit * 0.25 / max_connections	4 MB	16 MB	64 MB	64 MB
effective_cache_size	MemLimit * 0.5	128 MB	128 MB	2 GB	2 GB
shared_buffers	MemLimit * 0.25	64 MB	64 MB	1GB	1 GB
max_parallel_workers_per_gather	??? TBD	2	2	8	8
maintenance_work_mem	MemLimit *.05	64 MB	64 MB	64 MB	64 MB

Reference:

postgres-# \c search
You are now connected to database "search" as user "postgres".

search=# SHOW work_mem;
 4MB

search=# show shared_buffers;
 1GB

search=# show effective_cache_size;
 2GB

search=# show max_connections;
 100

work_mem

If you do a lot of complex sorts, and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.

This size is applied to each and every sort done by each user, and complex queries can use multiple working memory sort buffers. Set it to 50MB, and have 30 users submitting queries, and you are soon using 1.5GB of real memory. Furthermore, if a query involves doing merge sorts of 8 tables, that requires 8 times work_mem. You need to consider what you set max_connections to in order to size this parameter correctly. This is a setting where data warehouse systems, where users are submitting very large queries, can readily make use of many gigabytes of memory.

log_temp_files can be used to log sorts, hashes, and temp files which can be useful in figuring out if sorts are spilling to disk instead of fitting in memory. You can see sorts spilling to disk using EXPLAIN ANALYZE plans as well. For example, if you see a line like Sort Method: external merge Disk: 7526kB in the output of EXPLAIN ANALYZE, a work_mem of at least 8MB would keep the intermediate data in memory and likely improve the query response time (although it may take substantially more than 8MB to do the sort entirely in memory, as data on disk is stored in a more compact format).

shared_buffers

The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data. One reason the defaults are low is because on some platforms (like older Solaris versions and SGI), having large values requires invasive action like recompiling the kernel. Even on a modern Linux system, the stock kernel will likely not allow setting shared_buffers to over 32MB without adjusting kernel settings first. (PostgreSQL 9.4 and later use a different shared memory mechanism, so kernel settings will usually not have to be adjusted there.)

If you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system. If you have less RAM you'll have to account more carefully for how much RAM the OS is taking up; closer to 15% is more typical there. There are some workloads where even larger settings for shared_buffers are effective, but given the way PostgreSQL also relies on the operating system cache, it's unlikely you'll find using more than 40% of RAM to work better than a smaller amount.

Be aware that if your system or PostgreSQL build is 32-bit, it might not be practical to set shared_buffers above 2 ~ 2.5GB. See this blog post for details.

Changing this setting requires restarting the database. Also, this is a hard allocation of memory; the whole thing gets allocated out of virtual memory when the database starts.

effective_cache_size

effective_cache_size should be set to an estimate of how much memory is available for disk caching by the operating system and within the database itself, after taking into account what's used by the OS itself and other applications. This is a guideline for how much memory you expect to be available in the OS and PostgreSQL buffer caches, not an allocation! This value is used only by the PostgreSQL query planner to figure out whether plans it's considering would be expected to fit in RAM or not. If it's set too low, indexes may not be used for executing queries the way you'd expect. The setting for shared_buffers is not taken into account here--only the effective_cache_size value is, so it should include memory dedicated to the database too.

Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount. You might find a better estimate by looking at your operating system's statistics. On UNIX-like systems, add the free+cached numbers from free or top to get an estimate. On Windows see the "System Cache" size in the Windows Task Manager's Performance tab. Changing this setting does not require restarting the dat

max_connections

max_connections sets the maximum number of client connections allowed. This is very important to some of the other parameters (particularly work_mem) because there are some memory resources that are or can be allocated on a per-client basis, so the maximum number of clients suggests the maximum possible memory use. Generally, PostgreSQL on good hardware can support a few hundred connections. If you want to have thousands instead, you should consider using connection pooling software to reduce the connection overhead.

sh-4.4$ psql
psql (13.5)
Type "help" for help.

postgres=# \c search
You are now connected to database "search" as user "postgres".
search=# SHOW max_worker_processes;
 max_worker_processes 
----------------------
 8
(1 row)

search=# SHOW max_parallel_workers_per_gather;
 max_parallel_workers_per_gather 
---------------------------------
 2
(1 row)

search=# SHOW max_parallel_workers;
 max_parallel_workers 
----------------------
 8
(1 row)

max_worker_processes

Set this to the number of CPUs you want to share for PostgreSQL exclusively. This is the number of background processes the database engine can use. Setting this parameter will require a server restart. The default is 8. When running a standby server, you must set this parameter to the same value or higher than on the master server. Otherwise, queries will not be allowed on the standby server.

max_parallel_workers_per_gather

The maximum workers a Gather or GatherMerge node can use. This parameter should be set equal to max_worker_processes. When the Gather node is reached during query execution, the process that is implementing the user's session will request a number of background worker processes equal to the number of workers chosen by the planner. The number of background workers that the planner will consider using is limited to max_parallel_workers_per_gather or below. The total number of background workers that can exist at any one time is limited by both max_worker_processes and max_parallel_workers. Therefore, it is possible for a parallel query to run with fewer workers than planned, or even with no workers at all. The optimal plan may depend on the number of workers that are available, so this can result in poor query performance. If this occurrence is frequent, consider increasing max_worker_processes and max_parallel_workers so that more workers can be run simultaneously or alternatively reducing max_parallel_workers_per_gather so that the planner requests fewer workers.

Sets the maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers. Note that the requested number of workers may not actually be available at run time. If this occurs, the plan will run with fewer workers than expected, which may be inefficient. The default value is 2. Setting this value to 0 disables parallel query execution. Note that parallel queries may consume very substantially more resources than non-parallel queries, because each worker process is a completely separate process which has roughly the same impact on the system as an additional user session. This should be taken into account when choosing a value for this setting, as well as when configuring other settings that control resource utilization, such as work_mem. Resource limits such as work_mem are applied individually to each worker, which means the total utilization may be much higher across all processes than it would normally be for any single process. For example, a parallel query using 4 workers may use up to 5 times as much CPU time, memory, I/O bandwidth, and so forth as a query which uses no workers at all.

max_parallel_workers

Maximum parallel worker processes for parallel queries. Same as for max_worker_processes. The default value is 8. Note that a setting for this value that is higher than max_worker_processes will have no effect, since parallel workers are taken from the pool of worker processes established by that setting.