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.