Ibrar Ahmed
![Lower frequency higher energy Lower frequency higher energy](/uploads/1/2/4/7/124781582/126565650.png)
![Lower Query Performance Frequency Lower Query Performance Frequency](https://docs.microsoft.com/en-us/windows/desktop/sysinfo/images/qpc-precision.png)
Windows 7, Visual Studio 2008, C The code queried for performance frequency and for the counter two times. The results look very suspicious because the counter has much greater resolution than the frequency should allow. Here is the code fragment: LARGEINTEGER d1, d2, d3, dx. Seems to me the counter is counting much faster than the 14 Mhz. Microsoft has introduced many new features to improve query performance in SQL Server. For example, SQL Server 2017 and Azure SQL Database have Query Store, Adapting Query Processing, Automatic Tuning of SQL Query, etc. To improve the query performance. In previous articles, we've learned how Query.
Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. Default values are set to ensure that PostgreSQL runs everywhere, with the least resources it can consume and so that it doesn’t cause any vulnerabilities. It has default settings for all of the database parameters. It is primarily the responsibility of the database administrator or developer to tune PostgreSQL according to their system’s workload. In this blog, we will establish basic guidelines for setting PostgreSQL database parameters to improve database performance according to workload.
Bear in mind that while optimizing PostgreSQL server configuration improves performance, a database developer must also be diligent when writing queries for the application. If queries perform full table scans where an index could be used or perform heavy joins or expensive aggregate operations, then the system can still perform poorly even if the database parameters are tuned. It is important to pay attention to performance when writing database queries.
Nevertheless, database parameters are very important too, so let’s take a look at the eight that have the greatest potential to improve performance
PostgreSQL’s Tuneable Parameters
shared_buffer
PostgreSQL uses its own buffer and also uses kernel buffered IO. That means data is stored in memory twice, first in PostgreSQL buffer and then kernel buffer. Unlike other databases, PostgreSQL does not provide direct IO. This is called double buffering. The PostgreSQL buffer is called shared_buffer which is the most effective tunable parameter for most operating systems. This parameter sets how much dedicated memory will be used by PostgreSQL for cache.
The default value of shared_buffer is set very low and you will not get much benefit from that. It’s low because certain machines and operating systems do not support higher values. But in most modern machines, you need to increase this value for optimal performance.
The recommended value is 25% of your total machine RAM. You should try some lower and higher values because in some cases we achieve good performance with a setting over 25%. The configuration really depends on your machine and the working data set. If your working set of data can easily fit into your RAM, then you might want to increase the shared_buffer value to contain your entire database, so that the whole working set of data can reside in cache. That said, you obviously do not want to reserve all RAM for PostgreSQL.
In production environments, it is observed that a large value for shared_buffer gives really good performance, though you should always benchmark to find the right balance.
Check shared_buffer Value
2 4 6 8 10 | testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; ----------------------------------------------------------------------------------- Gather Merge(cost=509181.84..1706542.14rows=10000116width=24) ->Sort(cost=508181.79..514431.86rows=2500029width=24) ->Parallel Seq Scan on bar(cost=0.00..88695.29rows=2500029width=24) |
The initial query’s sort node has an estimated cost of 514431.86. Cost is an arbitrary unit of computation. For the above query, we have a work_mem of only 2MB. For testing purposes, let’s increase this to 256MB and see if there is any impact on cost.
work_mem = 256MB
2 4 6 8 | testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b; ----------------------------------------------------------------------------------- Gather Merge(cost=355367.34..1552727.64rows=10000116width=24) ->Sort(cost=354367.29..360617.36rows=2500029width=24) ->Parallel Seq Scan on bar(cost=0.00..88695.29rows=2500029width=24) |
The query cost is reduced to 360617.36 from 514431.86 — a 30% reduction.
maintenance_work_mem
maintenance_work_mem is a memory setting used for maintenance tasks. The default value is 64MB. Setting a large value helps in tasks like VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
maintenance_work_mem = 10MB
2 4 6 | postgres=# SET maintenance_work_mem to '10MB'; postgres=# CREATE INDEX foo_idx ON foo (c); Time:170091.371ms(02:50.091) |
maintenance_work_mem = 256MB
2 4 6 | postgres=# set maintenance_work_mem to '256MB'; postgres=# CREATE INDEX foo_idx ON foo (c); Time:111274.903ms(01:51.275) |
The index creation time is 170091.371ms when maintenance_work_mem is set to only 10MB, but that is reduced to 111274.903 ms when we increase maintenance_work_mem setting to 256MB.
synchronous_commit
This is used to enforce that commit will wait for WAL to be written on disk before returning a success status to the client. This is a trade-off between performance and reliability. If your application is designed such that performance is more important than the reliability, then turn off synchronous_commit. This means that there will be a time gap between the success status and a guaranteed write to disk. In the case of a server crash, data might be lost even though the client received a success message on commit. In this case, a transaction commits very quickly because it will not wait for a WAL file to be flushed, but reliability is compromised.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL writes changes into WAL. The checkpoint process flushes the data into the data files. This activity is done when CHECKPOINT occurs. This is an expensive operation and can cause a huge amount of IO. This whole process involves expensive disk read/write operations. Users can always issue CHECKPOINT whenever it seems necessary or automate the system by PostgreSQL’s parameters checkpoint_timeout and checkpoint_completion_target.
The checkpoint_timeout parameter is used to set time between WAL checkpoints. Setting this too low decreases crash recovery time, as more data is written to disk, but it hurts performance too since every checkpoint ends up consuming valuable system resources. The checkpoint_completion_target is the fraction of time between checkpoints for checkpoint completion. A high frequency of checkpoints can impact performance. For smooth checkpointing, checkpoint_timeout must be a low value. Otherwise the OS will accumulate all the dirty pages until the ratio is met and then go for a big flush.
Conclusion
![Lower frequency higher energy Lower frequency higher energy](/uploads/1/2/4/7/124781582/126565650.png)
There are more parameters that can be tuned to gain better performance but those have less impact than the ones highlighted here. In the end, we must always keep in mind that not all parameters are relevant for all applications types. Some applications perform better by tuning a parameter and some don’t. Tuning PostgreSQL Database Parameters must be done for the specific needs of an application and the OS it runs on.
Related posts
You can read my post about tuning Linux parameters for PostgreSQL database performance
Plus another recent post on benchmarks:
You May Also Like
If your PostgreSQL database is running on a Linux OS, be sure to read my post about tuning Linux parameters to optimize PostgreSQL database performance.
After tuning your PostgreSQL database to improve its performance, the next step is to put your optimized database to the test. With sysbench, you can quickly evaluate your database’s performance. This process is especially important if you plan to run your database under an intensive workload. Our blog article, Tuning PostgreSQL for sysbench-tpcc, can guide you through the benchmarking process.
![Lower Query Performance Frequency Lower Query Performance Frequency](https://docs.microsoft.com/en-us/windows/desktop/sysinfo/images/qpc-precision.png)
Optimizing your database is one way to course-correct poor database performance. However, the problem is not always the database itself! There are several causes that can contribute to your database’s lackluster performance. Our solution brief details the Top 5 Causes of Poor Database Performance. With this crucial information, you can better implement a quick and efficient resolution.