Sample questions
Performance TuningDifficulty 1
Why do busy applications keep a pool of database connections open and reuse them instead of opening a fresh connection for each request?
- aCreating a connection each time is costly (TCP setup, authentication, a new server-side backend), so reuse avoids that work✓
- bA single pooled connection can encrypt the network traffic, while separate connections are always sent in plaintext
- cThe pool splits one query across many connections so a single slow query finishes several times faster
- dThe database refuses to run more than one query on the same connection, so without a pool most queries would simply fail
Explanation:Opening a connection involves a TCP handshake, authentication and allocating a server-side process or thread — tens of milliseconds that dwarf a simple query. A pool keeps warm connections and hands them out, so that cost is paid once. Pooling does not parallelize a single query (c) and has nothing to do with encryption (b).
Performance TuningDifficulty 2
A service under load starts failing with 'too many connections' errors, even though CPU and disk on the database server are barely used. Each request opens its own direct connection. What is the most appropriate fix?
- aRaise the operating system's file-descriptor limit on the application servers to a very high value and keep opening connections
- bSwitch every query to SELECT * so the database spends less time planning each individual statement
- cPut a connection pool in front of the database and cap concurrent connections to a sane number✓
- dGive the database user superuser privileges so its connections are not counted against the limit
Explanation:Each backend connection consumes memory and a server process, and the server enforces a hard max_connections limit; opening one per request exhausts it. A pool bounds and reuses connections, matching demand to what the server can hold. Superuser does not exempt you from the limit (d), and SELECT * only makes things worse (b).
Performance TuningDifficulty 1
Most relational databases keep a large in-memory cache of recently used data pages. What is the main purpose of this cache?
- aTo store the database's user accounts and permission rules so that authentication stays fast
- bTo serve frequently accessed data from RAM and avoid slow reads from disk✓
- cTo keep a durable backup copy of the data so it survives a total disk failure
- dTo compress old data pages so the table takes up less space on disk over time
Explanation:The buffer/page cache holds hot data pages in memory so repeated reads are satisfied without touching disk, which is orders of magnitude slower. It is not a durability mechanism — that is the job of the write-ahead log and on-disk files (c) — and it does not compress data or hold auth rules.
Performance TuningDifficulty 2
A report query takes 4 seconds the very first time it runs after the database restarts, then only 80 ms on the next few runs with the same data and no other changes. What most likely explains this?
- aThe query planner rewrote the query into a faster form after seeing it run once
- bThe first run had to rebuild every index on the table from scratch before it was allowed to return any rows
- cThe database compiled the SQL text into native machine code after the first execution
- dThe first run read the data pages from disk into the memory cache; later runs found them already cached✓
Explanation:On a cold cache the first execution pays for physical disk reads to load the needed pages into the buffer cache; subsequent runs hit those pages in RAM and skip the disk. The plan and the compiled statement are not what changed here, and indexes are not rebuilt on read (b).
Performance TuningDifficulty 1
For a typical database workload, which resource is most often the primary performance bottleneck?
- aDisk I/O — reading and writing data pages is much slower than operating on data already in memory✓
- bThe amount of available RAM, since any query whose working set fits in memory can never become slow
- cCPU clock speed, since every query is almost always CPU-bound rather than I/O-bound
- dThe total number of tables in the schema, because the engine must scan every table's header on each query
Explanation:Databases work with far more data than fits in CPU registers, and disk (even SSD) is orders of magnitude slower than RAM, so I/O usually dominates. That is exactly why caching hot data in memory matters. Most OLTP queries are I/O-bound, not CPU-bound (c), and table counts are not the bottleneck (d).
Performance TuningDifficulty 2
An import loads 10,000 rows by sending 10,000 separate INSERT INTO t (...) VALUES (...) statements, each in its own network call. Which change most directly reduces the total time?
- aAdd several more indexes on the target table so that each individual INSERT is written to disk faster
- bRename the columns to shorter names so each INSERT statement is a bit smaller to parse
- cGroup the rows into multi-row INSERTs (or one bulk statement) so far fewer round trips are needed✓
- dWrap each individual INSERT in its own separate transaction to isolate any single failure
Explanation:The dominant cost here is 10,000 network round trips plus per-statement parsing. Batching many rows into each INSERT (or a bulk-load command) collapses those round trips into a handful. Adding indexes makes each INSERT slower, not faster (a), and per-row transactions add commit overhead (d).