CompTIA DataSys+ (DS0-001)Database DeploymentMedium
A database administrator is deploying a new MySQL database server that will serve a high-volume transactional application. To optimize read performance and reduce I/O contention, the administrator wants to ensure that frequently accessed data and indexes are cached in memory. Which MySQL configuration parameter should be tuned to achieve this goal?
- Aquery_cache_size
- Bkey_buffer_size
- Cmax_connections
- Dinnodb_buffer_pool_size
Show answer & explanationAnswer & explanation
Correct answer: D. innodb_buffer_pool_size
For InnoDB, which is the default and most common storage engine for transactional workloads in MySQL, the `innodb_buffer_pool_size` parameter controls the amount of RAM dedicated to caching data and indexes. A larger buffer pool size reduces disk I/O and significantly improves performance for read-heavy applications.
Why the other options are wrong
- A. query_cache_size was deprecated in MySQL 5.7 and removed in MySQL 8.0; it cached query results, not raw data/indexes.
- B. key_buffer_size is for caching indexes of MyISAM tables, which is less common for high-volume transactional applications and not the primary cache for InnoDB.
- C. max_connections controls the number of concurrent client connections, not data caching.
InnoDB Buffer Pool
The main memory cache for InnoDB storage engine in MySQL, used to store data, indexes, and other internal structures to reduce disk I/O and improve performance.
- Crucial for InnoDB performance.
- Larger size generally means better performance (up to available RAM).
- Caches both data and index pages.
Memory trick: Memory is speed: cache data where it counts.