CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is troubleshooting intermittent performance issues on a high-transaction OLTP database. They observe that queries involving complex joins and aggregations sometimes perform poorly, even with appropriate indexing. Further investigation reveals that the database server frequently experiences high I/O wait times, particularly for temporary files. Which of the following database parameters or configurations is MOST likely causing this issue?
- AInsufficient `work_mem` or `sort_buffer_size`.
- BLow `max_connections` setting.
- CHigh `checkpoint_segments` value.
- DAggressive `log_buffer` size.
Show answer & explanationAnswer & explanation
Correct answer: A. Insufficient `work_mem` or `sort_buffer_size`.
Complex joins and aggregations often require significant memory for sorting and hashing. If `work_mem` (PostgreSQL) or `sort_buffer_size` (MySQL) is too low, the database spills data to temporary files on disk, leading to high I/O wait times and degraded performance.
Why the other options are wrong
- B. Low `max_connections` would cause connection errors, not I/O wait times for temp files.
- C. High `checkpoint_segments` (PostgreSQL) relates to write-ahead log (WAL) management and recovery, not directly to temporary file I/O for queries.
- D. Aggressive `log_buffer` size (SQL Server) or similar parameters for transaction logs primarily affect transaction commit performance, not temporary file I/O for query processing.
Temporary File I/O
Disk input/output operations performed by a database when memory allocated for sorting, hashing, or other intermediate query operations is insufficient, forcing data to be written to and read from temporary files.
- Indicates insufficient memory for query processing.
- Leads to significant performance degradation due to disk access.
- Often seen with complex queries like large sorts, joins, or aggregations.
Memory trick: Memory too small, disk takes the fall.