Server configuration tuning in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of max_connections on concurrent sessions

Consider a MySQL server configured with max_connections=100. If 105 clients try to connect simultaneously, what will happen to the last 5 connection attempts?

A The last 5 clients will be refused with an error indicating too many connections.

B The server will automatically increase max_connections to 105 to allow all clients.

C The server will queue the last 5 clients until some connections close.

D All 105 clients will connect successfully without any error.

💡 Hint

Think about the maximum allowed simultaneous connections setting.

The max_connections setting limits how many clients can connect at the same time. If more clients try to connect, the server refuses the extra connections with an error.

❓ query_result

intermediate

Impact of innodb_buffer_pool_size on query performance

Given a MySQL InnoDB table of size 10GB, if innodb_buffer_pool_size is set to 1GB, what is the expected impact on query performance compared to setting it to 12GB?

A Queries will be slower with 1GB buffer pool because less data fits in memory, causing more disk reads.

B Queries will be faster with 1GB buffer pool because less memory usage means less overhead.

C Query performance will be the same regardless of buffer pool size.

D Queries will fail if buffer pool size is less than table size.

💡 Hint

Consider how caching data in memory affects disk access.

The buffer pool caches data pages in memory. A larger buffer pool reduces disk I/O, improving query speed. A smaller buffer pool causes more disk reads, slowing queries.

📝 Syntax

advanced

Correct syntax to change max_allowed_packet size

Which of the following commands correctly changes the max_allowed_packet size to 64MB for the current MySQL session?

A SET max_allowed_packet = 64MB;

B SET GLOBAL max_allowed_packet = 67108864;

C SET SESSION max_allowed_packet = 64MB;

D SET SESSION max_allowed_packet = 67108864;

💡 Hint

Remember the unit for max_allowed_packet is bytes and how to set session variables.

The max_allowed_packet variable expects a value in bytes. To set it for the current session, use SET SESSION max_allowed_packet = 67108864;. Using '64MB' is invalid syntax.

📝 Syntax

advanced

Optimizing query cache usage

A MySQL server has query_cache_size set to 256MB but query cache hit ratio remains very low. Which change is most likely to improve query cache effectiveness?

A Disable query cache by setting <code>query_cache_size</code> to 0.

B Increase <code>query_cache_size</code> to 1GB to cache more queries.

C Reduce <code>query_cache_size</code> to 64MB to reduce overhead and fragmentation.

D Increase <code>max_connections</code> to allow more concurrent queries.

💡 Hint

Think about how large query cache sizes can cause fragmentation and reduce efficiency.

Very large query cache sizes can cause fragmentation and overhead, reducing hit ratio. Reducing the size can improve cache efficiency and hit ratio.

🧠 Conceptual

expert

Choosing the right thread_cache_size value

What is the main benefit of increasing thread_cache_size in MySQL server configuration?

A It improves query execution speed by caching query plans.

B It reduces the overhead of creating new threads by reusing existing threads for client connections.

C It increases the maximum number of concurrent client connections allowed.

D It limits the number of threads that can run simultaneously to prevent overload.

💡 Hint

Consider what happens when a client disconnects and a new client connects.

Increasing thread_cache_size allows MySQL to reuse threads for new connections instead of creating new ones, reducing thread creation overhead and improving connection performance.