CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is reviewing the performance of a critical reporting database. They observe that many complex queries involving multiple `JOIN`s and `GROUP BY` clauses are consistently slow, even after ensuring proper indexing. The database has sufficient memory and CPU. The DBA suspects that the sheer volume of data being processed for each query is the bottleneck, requiring extensive temporary disk space for intermediate results. Which database tuning approach should they investigate to reduce the need for temporary disk space and speed up these complex queries?
- AAdjusting temporary table/sort buffer sizes (e.g., `temp_buffers`, `work_mem`).
- BOptimizing `ORDER BY` clauses to use existing indexes.
- CImplementing a read-only replica for reporting.
- DIncreasing the `max_connections` parameter.
Show answer & explanationAnswer & explanation
Correct answer: A. Adjusting temporary table/sort buffer sizes (e.g., `temp_buffers`, `work_mem`).
Complex queries with `JOIN`s and `GROUP BY` often require sorting and temporary tables. If the allocated memory for these operations (controlled by parameters like `temp_buffers` or `work_mem`) is insufficient, the database spills data to temporary disk files, leading to significant I/O and slowdowns. Adjusting these parameters to keep operations in memory directly addresses this bottleneck.
Why the other options are wrong
- B. Optimizing `ORDER BY` clauses with indexes is a good practice, but the question implies the bottleneck is beyond indexing, specifically 'extensive temporary disk space for intermediate results'.
- C. Implementing a read-only replica offloads reporting workload but does not inherently optimize the performance of complex queries on the replica itself if they still spill to disk.
- D. Increasing `max_connections` allows more users but does not alleviate temporary disk space usage for complex queries.
Temporary Table/Sort Buffer Tuning
The process of adjusting database configuration parameters that control the memory allocated for sorting, hashing, and temporary tables during query execution.
- Crucial for performance of complex queries with JOINs, GROUP BY, ORDER BY.
- Insufficient memory leads to 'spilling to disk', causing I/O bottlenecks.
- Parameters often include `work_mem` (PostgreSQL), `sort_buffer_size` (MySQL), `temp_buffers`.
Memory trick: Temp buffers: Keep your 'work' in memory, not on slow disk.