CompTIA DataSys+ (DS0-001)Database DeploymentMedium
A database administrator is deploying a new PostgreSQL database server. During the configuration phase, the administrator needs to optimize the `shared_buffers` parameter. This parameter directly impacts which of the following aspects of database performance?
- ACPU core allocation for background processes
- BDisk I/O and query caching
- CMaximum number of concurrent user sessions
- DNetwork latency for client connections
Show answer & explanationAnswer & explanation
Correct answer: B. Disk I/O and query caching
`shared_buffers` in PostgreSQL (and similar parameters in other DBMS) determines the amount of memory dedicated to caching data pages. A larger `shared_buffers` value can reduce disk I/O by keeping frequently accessed data in memory, thus improving query performance.
Why the other options are wrong
- A. CPU core allocation is typically managed by the operating system and database's process model, not directly by `shared_buffers`.
- C. The maximum number of concurrent sessions is controlled by parameters like `max_connections`.
- D. Network latency is primarily affected by network infrastructure, not `shared_buffers`.
shared_buffers (PostgreSQL)
The `shared_buffers` parameter in PostgreSQL sets the amount of dedicated memory for shared buffer pool, used to cache data pages read from disk.
- Crucial for reducing disk I/O by keeping data in RAM.
- Typically set to 15-25% of total system RAM on a dedicated database server.
- Impacts overall query performance, especially for frequently accessed data.
Memory trick: Shared buffers 'S'ave 'S'torage I/O.