Not long ago, a friend of the company, business people shouted slow all day long.
My friend is engaged in development and does not understand DB very well. He said that there is no problem with their application, but it is very slow to handle the application during the peak hours every day, with various jams and a bunch of requests that cannot be completed. They didn’t have a DBA and wanted me to look at it.
I opened their database after work to see a few eyes, let me drop eyes, the database is all the default configuration parameters. Then I gave them a random modification and told them to restart the database. To the next day he said, brother, you are too god, what operation did yesterday, our database suddenly become fast.
I said: nothing, just adjusted the default parameters according to experience value, sloppy very! Your parameters are default values out of the box. Certainly not for production use.
PostgreSQL should adjust the parameters
max_connections
Maximum number of client connections allowed. The size of this parameter has some relationship to work_mem. The higher the configuration, the more memory the system may use. You can usually set up hundreds of connections, but if you want to use thousands of connections, it is recommended to configure connection pooling to reduce overhead.
shared_buffers
PostgreSQL uses its own buffer as well as the Linux kernel buffer OS Cache. This means that the data is stored in memory twice, first in the PostgreSQL buffer and then in the operating system kernel buffer. Unlike other databases, PostgreSQL does not provide direct IO, so this is also known as double buffering. The PostgreSQL buffer is calledshared_buffer, you are advised to set it to 1/4 of the physical memory. The actual configuration depends on the hardware configuration and workload, but if you have a large amount of memory and you want to cache more data into memory, you can increase it furthershared_buffer.
Effective_cache_size
This parameter is used primarily by the Postgre query optimizer. Is an assumption of the effective size of the disk cache available for a single query. It is an estimate and does not occupy system memory. Because of the estimated cost to the optimizer, higher values are more likely to use index scans, and lower values are more likely to use sequential scans. In general, setting this value to 1/2 of memory is the normal conservative setting, and setting it to 3/4 of memory is the recommended value. You might get a better estimate of this value by viewing the operating system statistics with the free command.
[pg@e22 ~]$ free -g
total used free shared buff/cache available
Mem: 62 2 5 16 55 40
Swap: 7 0 7
Copy the codework_mem
This parameter is used to determine the amount of memory used by internal sort operations and hash tables before writing temporary files. Increasing the work_mem parameter will allow PostgreSQL to do larger memory sorts. This parameter has something to do with max_connections, assuming you set it to 30MB, 40 users performing query sorting at the same time will soon be using 1.2GB of real memory. At the same time, for complex queries, multiple sort and hash operations may be performed, such as merging a sort involving eight tables, requiring 8 times the work_MEm.
As shown in the following example, the environment uses 4MB work_mem, and the Sort Method is external Merge Disk when performing the Sort operation.
kms=> explain (analyze,buffers) select * from KMS_BUSINESS_HALL_TOTAL order by buss_query_info;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------ ---------------------------------
Gather Merge (cost=262167.99.567195.15. rows=2614336 width=52) (actual time=2782.203.5184.442. rows=3137204 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=68 read=25939, temp read=28863 written=28947
-> Sort (cost=261167.97.264435.89. rows=1307168 width=52) (actual time=2760.566.3453.783. rows=1045735 loops=3)
Sort Key: buss_query_info
Sort Method: external merge Disk: 50568kB
Worker 0: Sort Method: external merge Disk: 50840kB
Worker 1: Sort Method: external merge Disk: 49944kB
Buffers: shared hit=68 read=25939, temp read=28863 written=28947
-> Parallel Seq Scan on kms_business_hall_total (cost=0.00.39010.68. rows=1307168 width=52) (actual time=0.547.259.524. rows=1045735 loops=3)
Buffers: shared read=25939
Planning Time: 0.540 ms
Execution Time: 5461.516 ms
(14 rows)
Copy the codeWhen we change the parameter to 512MB, we can see that Sort Method becomes Quicksort Memory and becomes Memory Sort.
kms=> set work_mem to "512MB";
SET
kms=> explain (analyze,buffers) select * from KMS_BUSINESS_HALL_TOTAL order by buss_query_info;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------ ------------------
Sort (cost=395831.79.403674.80. rows=3137204 width=52) (actual time=7870.826.8204.794. rows=3137204 loops=1)
Sort Key: buss_query_info
Sort Method: quicksort Memory: 359833kB
Buffers: shared hit=25939
-> Seq Scan on kms_business_hall_total (cost=0.00.57311.. 04 rows=3137204 width=52) (actual time=0.019.373.067. rows=3137204 loops=1)
Buffers: shared hit=25939
Planning Time: 0.081 ms
Execution Time: 8419.994 ms
(8 rows)
Copy the codemaintenance_work_mem
Specifies the maximum amount of memory to use for maintenance operations, such as Vacuum, Create Index, and Alter Table Add Foreign Key. The default value is 64MB. Since normally functioning databases do not have a large number of concurrent operations of this kind, you can set it to a larger size to speed up the cleanup and creation of index foreign keys.
postgres=# set maintenance_work_mem to "64MB";
SET
Time: 1.971 ms
postgres=# create index idx1_test on test(id);
CREATE INDEX
Time: 7483.621 ms (00:07.484)
postgres=# set maintenance_work_mem to "2GB";
SET
Time: 0.543 ms
postgres=# drop index idx1_test;
DROP INDEX
Time: 133.984 ms
postgres=# create index idx1_test on test(id);
CREATE INDEX
Time: 5661.018 ms (00:05.661)
Copy the codeYou can see that the index creation speed is 7.4 seconds with the default 64MB, and 5.6 seconds with 2GB
wal_sync_method
PostgreSQL forces the commit to be written to WAL logs after each transaction. You can use the pg_test_fsync command to test on your operating system. Fdatasync is the default method on Linux. As shown below, fdatasync is still fast in my environment tests. Unsupported methods like fsync_writeThrough display n/ A directly.
postgres=# show wal_sync_method ; wal_sync_method ----------------- fdatasync (1 row) [pg@e22 ~]$ pg_test_fsync -s 3 3 seconds per test O_DIRECT supported on this platform for open_datasync and open_sync. Compare file sync methods using one 8kB write: (in wal_sync_method preference order, Except fdatasync is Linux's default) Open_DATASYNc 4782.871 OPS/SEC 209 USECS /op FdatasYNc 4935.556 OPS/SEC 203 USECs /op Fsync 3781.254 OPS/SEC 264 USECS/OP fsynC_writeThrough N /a open_sync 3850.219 OPS/SEC 260 USECs /op Compare file sync methods using two 8kB writes: (in wal_sync_method preference order, Except fdatasync is Linux's default) Open_DATASync 2469.646 OPS/SEC 405 USECs /op Fdatasync 4412.266 OPS/SEC 227 USECs /op Fsync 3432.794 OPS/SEC 291 USECS/OP fsynC_writeThrough N /a open_sync 1929.221 OPS/SEC 518 USECS/OP Compare Open_sync with different write sizes: (This is designed to compare the cost of writing 16kB in different write open_sync sizes.) 1 * 16kB open_sync write 3159.796 OPS/SEC 316 USecs /op 2 * 8kB open_sync INDEX 1944.723 OPS/SEC 514 USecs /op 4 * 4kB open_sync index 993.173 Ops/SEC 1007 USecs /op 8 * 2kB open_sync INDEX 493.396 OPS/SEC 2027 USecs /op 16 * 1kB open_sync index 249.762 OPS/SEC 4004 usecs/op Test if fsync on non-write file descriptor is honored: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, Ops/SEC 279USECs /op write, CLOSE, fsync 279ops/SEC 279USECs/OP non-sync 'Ed 8kB INDEX: Write 400577.329 OPS/SEC 2 USECS/OPCopy the codewal_buffers
The size of the transaction log buffer to which PostgreSQL writes WAL records before flushing the buffer to disk. In PostgreSQL 12, the default value is -1, which selects 1/32 of shared_buffers. You can set this value manually if the automatic selection is too large or too small. The recommended value is 16MB.
synchronous_commit
The client performs the commit, waits for WAL to write to disk, and then returns the success status to the client. You can set the values to on, remote_apply, remote_write, local, and off. The default setting is on. If set to off, sync_COMMIT is turned off and returned immediately after the client commits, without waiting for records to flush to disk. If the PostgreSQL instance crashes at this point, the last few asynchronous commits will be lost.
default_statistics_target
PostgreSQL uses statistics to generate execution plans. Statistics can be collected manually by using the Analyze command or automatic analysis started by the autoVACUUM process. The default_statistics_target parameter specifies how detailed statistics are to be collected and recorded. A default value of 100 is reasonable for most workloads, a smaller value may be useful for very simple queries, and a larger value may be better for complex queries, especially queries against large tables. To avoid a one-size-fits-all approach, use ALTER TABLE.. ALTER COLUMN .. SET STATISTICS overrides the level of detail of default collected STATISTICS for a particular table column.
Checkpoint_timeout, max_WAL_size, min_WAL_size, checkPOINt_completion_target
Before we look at these two parameters, let’s look at the actions that trigger a checkpoint.
- Run the checkpoint command
- Execute commands that require checkpoints (such as pg_start_backup,Create database,pg_ctl stop/start, etc.)
- The configured amount of time has been reached since the last checkpoint (checkpoint_timeout)
- Number of WAL’s generated since last checkpoint (max_WAL_size)
Using the default, the checkpoint will be at checkpoint_timeout=5min. That’s every five minutes. The max_WAL_SIZE setting is the maximum amount of write-ahead logging (WAL) that grows between automatic checkpoints. The default is 1GB, and if it exceeds 1GB, a checkpoint occurs. This is a soft limit. In a special case, such as a system experiencing a short period of high load, log generation can reach 1GB in a few seconds, which is significantly faster than checkpoint_timeout, and the size of the PG_WAL directory increases dramatically. At this point we can see a similar warning in the log.
LOG: checkpoints are occurring too frequently (9 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
Copy the codeSo configure max_WAL_size properly to avoid frequent checkpoints. It is generally recommended to set it to more than 16GB, but the specific size depends on the workload.
The min_WAL_size parameter is that as long as WAL disk usage stays below this setting, old WAL files are always recycled for future use at checkpoints rather than being deleted directly.
Checkpoints are not written all at once; PostgreSQL spreads all checkpoint operations over a period of time. This time is controlled by the parameter checkpoint_completion_target, which is a score and defaults to 0.5. That is, write disk operations at a ratio of 0.5 between checkpoints. If set to small, the checkpoint process will write disk more quickly, and if set to large, it will write disk more slowly. A setting of 0.9 is generally recommended to spread out checkpoint writes. But the disadvantage is that when the failure occurs, it affects the recovery time.
Use the PGTune tool to configure the parameters
For companies like Friends that don’t have DBA professionals, I generally recommend using PGTune, an online software, link, to configure their parameters. Setup is simple, you just need to know your database version, operating system type, amount of memory, number of cpus, disk type, number of connections, and type of application. You can easily get some suggested parameter values.
Use PostgresQLTuner to optimize the parameters
You can also use the PostgresqlTuner tool to optimize parameters, which the authors say was inspired by mysqlTuner, which is written in Perl.
This software is also very simple to use, directly download decompression, execute the script on the line.
postgresqltuner.pl --host=dbhost --database=testdb --user=username --password=qwerty
Copy the codeThe following output is displayed:
This software is a little more specialized than PGTune, and it also outputs some operating system configuration, and determines whether the memory parameters are reasonable based on the current load of the database, similar to the Advisor.
conclusion
The default configuration of the system is only suitable for playing by yourself, not out of the box. If you don’t have a DBA, you can also use PGTune or PostgresQLTuner scripts to optimize the system. These optimizations will greatly improve system performance.
Reference documentation
Tuning Your PostgreSQL Server
Tuning PostgreSQL Database Parameters to Optimize Performance