From the objective of OSS-DB Exam Gold
- Performance Tuning - Parameters related to performance
Sample Question
3.10
Select all the correct statements regarding the causes of performance degradation.
-
Setting shared_buffers to an excessively large value caused performance degradation of queries during checkpointing.
-
Setting maintenance_work_mem higher than work_mem caused performance degradation of VACUUM processing.
-
Concurrent sessions issuing a high volume of INSERT statements caused write contention on the WAL files, resulting in degraded INSERT performance.
-
The absence of periodic tuple-level statistics collection using pgstattuple prevented PostgreSQL from generating optimal execution plans, leading to degraded query performance.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/06/02
Answer and Explanation
A. Increasing the value of shared_buffers increases the amount of data written to disk during checkpoints, which can temporarily degrade performance.
B. Increasing maintenance_work_mem improves VACUUM performance. Setting it higher than work_mem has no negative impact, but be aware that multiple concurrent VACUUM processes will consume memory for proportionally.
C. With default settings, a COMMIT or auto COMMIT is issued after an INSERT, and the transaction is considered complete once the WAL record is written to disk. Contention for WAL writes (due to locking) can lead to performance degradation.
D. You can use the pgstattuple module to gather tuple-level statistics for examination. Use the ANALYZE command to refresh the statistics with the latest data.
Therefore, the correct answers are A and C.