Server configuration tuning in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to check the current value of the max_connections setting.

MySQL

SHOW VARIABLES LIKE '[1]';

Drag options to blanks, or click blank then click option'

Amax_connections

Bmax_allowed_packet

Cinnodb_buffer_pool_size

Dwait_timeout

Attempts:

3 left

💡 Hint

Common Mistakes

Using a variable name unrelated to connections.

Trying to use SELECT instead of SHOW VARIABLES.

The max_connections variable controls the maximum number of simultaneous client connections. Using SHOW VARIABLES LIKE 'max_connections'; displays its current value.

2fill in blank

medium

Complete the code to set the innodb_buffer_pool_size to 1GB dynamically.

MySQL

SET GLOBAL [1] = 1073741824;

Drag options to blanks, or click blank then click option'

Amax_connections

Bmax_allowed_packet

Cwait_timeout

Dinnodb_buffer_pool_size

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to set a read-only variable.

Using SET SESSION instead of SET GLOBAL.

The innodb_buffer_pool_size controls the size of the buffer pool for InnoDB tables. Setting it to 1073741824 bytes (1GB) dynamically uses SET GLOBAL innodb_buffer_pool_size = 1073741824;.

3fill in blank

hard

Fix the error in the command to persistently change the wait_timeout to 300 seconds.

MySQL

SET PERSIST [1] = 300;

Drag options to blanks, or click blank then click option'

Ainnodb_buffer_pool_size

Bwait_timeout

Cmax_connections

Dmax_allowed_packet

Attempts:

3 left

💡 Hint

Common Mistakes

Using SET GLOBAL instead of SET PERSIST for persistence.

Choosing a variable unrelated to timeout.

The wait_timeout variable controls how long the server waits before closing an idle connection. To persistently change it, use SET PERSIST wait_timeout = 300;.

4fill in blank

hard

Fill both blanks to check the current value of the slow query log and its file location.

MySQL

SHOW VARIABLES LIKE '[1]';
SHOW VARIABLES LIKE '[2]';

Drag options to blanks, or click blank then click option'

Aslow_query_log

Bmax_connections

Cslow_query_log_file

Dinnodb_buffer_pool_size

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the variable names.

Using unrelated variables like max_connections.

The slow_query_log variable shows if the slow query log is enabled. The slow_query_log_file variable shows the file path where slow queries are logged.

5fill in blank

hard

Fill all three blanks to create a persistent setting that enables the slow query log and sets the log file to '/var/log/mysql/slow.log'.

MySQL

SET PERSIST [1] = [2];
SET PERSIST [3] = '/var/log/mysql/slow.log';

Drag options to blanks, or click blank then click option'

Aslow_query_log

BON

Cslow_query_log_file

DOFF

Attempts:

3 left

💡 Hint

Common Mistakes

Using OFF instead of ON to enable the log.

Not using SET PERSIST for persistent changes.

To enable the slow query log persistently, set slow_query_log to ON. Then set slow_query_log_file to the desired file path. Both changes use SET PERSIST to survive server restarts.

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