Sample Questions of OSS-DB Exam Gold "Performance Tuning"

[Performance Tuning - Parameters related to performance]

3.12

Choose the one INCORRECT description of a GUC parameter.

  1. shared_buffers sets the size of the shared memory buffers used by the PostgreSQL server.

  2. max_connections sets the maximum number of concurrent client connections allowed to the PostgreSQL server.

  3. work_mem sets the maximum amount of memory used for maintenance tasks like VACUUM and CREATE INDEX.

  4. Setting ssl to on enables SSL connections.

  5. wal_level is a parameter that controls the level of information written to the WAL.


[Performance Tuning - Parameters related to performance]

3.11

Choose all the correct explanations for the GUC parameter deadlock_timeout related to deadlocks.

  1. If a lock cannot be acquired within the time specified by deadlock_timeout, a deadlock is assumed to have occurred.

  2. Adjusting the value of deadlock_timeout can help mitigate the occurrence of deadlocks.

  3. Reducing the value of deadlock_timeout decrease the number of processes waiting for a lock, potentially leading to a reduction in CPU load.

  4. Deadlocks should be avoided by devising ways to create applications, and it is desirable for the value of deadlock_timeout to be as large as possible.

  5. With the default setting of deadlock_timeout, deadlock detection is not performed automatically.


[Performance Tuning - Parameters related to performance]

3.10

Select all the correct statements regarding the causes of performance degradation.

  1. Setting shared_buffers to an excessively large value caused performance degradation of queries during checkpointing.

  2. Setting maintenance_work_mem higher than work_mem caused performance degradation of VACUUM processing.

  3. Concurrent sessions issuing a high volume of INSERT statements caused write contention on the WAL files, resulting in degraded INSERT performance.

  4. The absence of periodic tuple-level statistics collection using pgstattuple prevented PostgreSQL from generating optimal execution plans, leading to degraded query performance.


[Performance Tuning - Parameters related to performance]

3.09

Choose two statements that are NOT accurate regarding the case where the full_page_writes parameter is set to OFF.

  1. Response performance during data update may improve.

  2. It may reduce the amount of WAL written during data updates.

  3. Response performance does not change if the wal_level parameter is set to "minimal".

  4. Unrecoverable data corruption may occur in the event of system crash.

  5. Point-in-time recovery operations will become impossible.


[Performance Tuning - Tuning database and queries]

3.08

Overall processing of PostgreSQL has been slowing down periodically. Select two tuning measures that can be effective in this case.

  1. Review slow-running SQL queries  and optimize the code that is causing the performance issues.

  2. Adjust the checkpoint_completion_target parameter to smooth out the load caused by checkpoints.

  3. Decrease the autovacuum_vacuum_cost_limit or vacuum_cost_limit value to reduce the load caused by VACUUM processing.

  4. Change to a streaming replication configuration to achieve load balancing.

  5. This is a characteristic of PostgreSQL, and there are no countermeasure.


[Performance Tuning - Tuning database and queries]

3.07

Choose two correct statements regarding reindexing.

  1. Reindexing must be done by stopping the service.

  2. Since REINDEX does not lock reads on the underlying table, it has no impact on read operations when executed while the service is running.

  3. CREATE INDEX CONCURRENTLY allows you to create an index without acquiring locks that conflict with concurrent inserts, updates, and deletes.

  4. CREATE INDEX CONCURRENTLY can also be used to create primary keys.

  5. Regularly re-creating indexes can help prevent index bloat.


[Performance Tuning - Tuning database and queries]

3.06

Before and after changing the GUC parameter enable_seqscan from ""on"" to ""off"", execution plans are obtained for the same query using EXPLAIN ANALYZE.
Choose the one statement that is the least accurate as a description of the change in the execution plan.
Assume all conditions other than enable_seqscan are the same.

  1. The "Total runtime" value may increase.

  2. The "Total runtime" value may decrease.

  3. The total estimated cost of the top node may increase.

  4. The total estimated cost of the top node may decrease.

  5. The exact same execution plan may be selected.


[Performance Tuning - Tuning database and queries]

3.05

Select two accurate statements regarding indexing.

  1. If FILLFACTOR is omitted, the target table's FILLFACTOR is used as the default value.

  2. If the UNLOGGED parameter is specified, WAL logging is skipped during index updates, which speeds up the update process.

  3. If the PARALLEL parameter is specified, multiple processes may be used to create the index, reducing creation time.

  4. If the CONCURRENTLY parameter is specified, the index is created without locking writes to the target table, but the creation process takes longer than a standard index build.

  5. Functions and operators used in index definitions must be immutable.


[Performance Tuning - Parameters related to performance]

3.04

Select all processes for which performance is expected to improve by tuning work_mem.

  1. ORDER BY

  2. CREATE INDEX

  3. Merge Join

  4. VACUUM

  5. Automatic VACUUM


[Performance Tuning - Parameters related to performance]

3.03

Select all the statements that are correct regarding query plans.

  1. Disabling enable_indexscan prevents the query planner from using index scans.

  2. Disabling enable_seqscan prevents the query planner from using sequential scans.

  3. Setting random_page_cost lower than seq_page_cost causes the query planner to favor index scans.

  4. Setting random_page_cost higher than seq_page_cost causes the query planner to favor index scans.

  5. Setting default_statistics_target to a smaller value can lead to the collection of more detailed statistics, potentially improving the quality of the planner's estimates.


[Performance Tuning - Parameters related to performance]

3.02

Select all the accurate statements regarding buffers_backend.

  1. It is displayed in the pg_stat_bgwriter view.

  2. The value increases during write operations caused by checkpoints.

  3. If the value of buffers_backend is large compared to buffers_alloc, you should consider tuning the value of shared_buffers.

  4. The value increases during write operations by the background writer.

  5. The value increases during write operations by backend processes.


[Performance Tuning - Parameters related to performance]

3.01

When operating a PostgreSQL server in a replication configuration, the synchronous_commit parameter can be used to tune the synchronization level between the primary and standby servers.
The following options describe the behavior of each setting value of the synchronous_commit parameter when a transaction commit is executed on the primary server. Choose the one that is INCORRECT.

  1. When set to "on", the commit is considered successful as soon as the WAL record is written to the standby's disk.

  2. When set to "off", the commit is considered successful even before the WAL has been written to either the primary or the standby server.

  3. When set to "local", the commit is considered successful when the WAL is written to the buffer, before it is written to the standby's disk.

  4. When set to "remote_apply", the commit is considered successful not only when the WAL is written to disk on the standby server, but also when the changes described in the WAL have been applied to the database.