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.

Practice

1. What is the main purpose of tuning MySQL server configuration settings?

easy

A. To backup the database automatically

B. To change the database schema structure

C. To improve server performance and handle more users efficiently

D. To write complex SQL queries

Solution

1. Step 1: Understand server tuning goal

   Server tuning adjusts settings like memory and cache to make the server faster and support more users.

2. Step 2: Differentiate from other tasks

   Changing schema, writing queries, or backups are different tasks unrelated to tuning configuration.

3. Final Answer:

   To improve server performance and handle more users efficiently -> Option C

4. Quick Check:

   Server tuning = performance improvement [OK]

Hint: Tuning means making server faster and handle more users [OK]

Common Mistakes:

• Confusing tuning with schema changes
• Thinking tuning is about writing queries
• Assuming tuning is for backups

2. Which of the following commands correctly changes a global MySQL server variable at runtime?

easy

A. SET GLOBAL max_connections = 200;

B. CHANGE GLOBAL max_connections TO 200;

C. UPDATE GLOBAL max_connections = 200;

D. ALTER GLOBAL max_connections = 200;

Solution

1. Step 1: Recall correct syntax for changing global variables

   The correct syntax uses SET GLOBAL variable_name = value;

2. Step 2: Check each option

   Only SET GLOBAL max_connections = 200; uses the correct SET GLOBAL syntax. Others use invalid keywords like CHANGE, UPDATE, ALTER.

3. Final Answer:

   SET GLOBAL max_connections = 200; -> Option A

4. Quick Check:

   SET GLOBAL = correct syntax [OK]

Hint: Use SET GLOBAL to change server variables at runtime [OK]

Common Mistakes:

• Using UPDATE or ALTER instead of SET
• Forgetting GLOBAL keyword
• Using TO instead of =

3. After running SET GLOBAL max_connections = 300;, what will the output of SHOW VARIABLES LIKE 'max_connections'; show?

medium

A. max_connections = 300

B. max_connections = 100 (default value)

C. Syntax error

D. max_connections = NULL

Solution

1. Step 1: Understand effect of SET GLOBAL

   SET GLOBAL changes the variable for the running server immediately.

2. Step 2: Check SHOW VARIABLES output

   SHOW VARIABLES LIKE 'max_connections' shows the current global value, which is now 300.

3. Final Answer:

   max_connections = 300 -> Option A

4. Quick Check:

   SET GLOBAL changes variable value immediately [OK]

Hint: SHOW VARIABLES shows current global values after SET GLOBAL [OK]

Common Mistakes:

• Thinking default value remains after SET GLOBAL
• Expecting syntax error from valid commands
• Assuming NULL means unset

4. You tried to run SET GLOBAL innodb_buffer_pool_size = 1073741824; but got an error. What is the most likely cause?

medium

A. The value is too small

B. You need SUPER privilege to change this variable

C. The variable name is misspelled

D. SET GLOBAL cannot change innodb variables

Solution

1. Step 1: Check privileges needed for changing global variables

   Changing global variables like innodb_buffer_pool_size requires SUPER privilege.

2. Step 2: Verify other options

   The variable name is correct, value is valid, and SET GLOBAL can change innodb variables if privileges allow.

3. Final Answer:

   You need SUPER privilege to change this variable -> Option B

4. Quick Check:

   SUPER privilege required for global changes [OK]

Hint: Changing global variables often needs SUPER privilege [OK]

Common Mistakes:

• Assuming syntax error means misspelling
• Thinking value size is the problem
• Believing SET GLOBAL can't change innodb variables

5. You want to increase the maximum number of allowed connections to 500 and verify the change without restarting the server. Which sequence of commands is correct?

hard

A. UPDATE max_connections = 500; SELECT max_connections;

B. SET max_connections = 500; SHOW STATUS LIKE 'max_connections';

C. ALTER SYSTEM SET max_connections = 500; SHOW VARIABLES;

D. SET GLOBAL max_connections = 500; SHOW VARIABLES LIKE 'max_connections';

Solution

1. Step 1: Identify correct command to change max_connections at runtime

   SET GLOBAL max_connections = 500; changes the setting immediately without restart.

2. Step 2: Verify the change

   SHOW VARIABLES LIKE 'max_connections'; displays the current value of max_connections.

3. Step 3: Check other options

   UPDATE and ALTER SYSTEM are invalid for this purpose; SET without GLOBAL affects session only; SHOW STATUS shows runtime stats, not variables.

4. Final Answer:

   SET GLOBAL max_connections = 500; SHOW VARIABLES LIKE 'max_connections'; -> Option D

5. Quick Check:

   SET GLOBAL + SHOW VARIABLES = correct sequence [OK]

Hint: Use SET GLOBAL then SHOW VARIABLES to change and check [OK]

Common Mistakes:

• Using UPDATE or ALTER SYSTEM commands
• Forgetting GLOBAL keyword in SET
• Using SHOW STATUS instead of SHOW VARIABLES