Server configuration tuning in MySQL - Mini Project: Build & Apply

Server Configuration Tuning in MySQL

📖 Scenario: You are a database administrator for a small online store. The store's MySQL server needs some basic tuning to improve performance and resource usage. You will adjust server configuration settings to optimize the server for the store's workload.

🎯 Goal: Learn how to set and update MySQL server configuration variables using SQL commands to tune the server for better performance.

📋 What You'll Learn

Check the current wait_timeout value

Set a new wait_timeout value for the session

Update the global wait_timeout variable

Verify the updated wait_timeout setting

💡 Why This Matters

🌍 Real World

Database administrators often need to tune server settings to improve performance and handle more users.

💼 Career

Knowing how to adjust MySQL server variables is essential for roles like DBA, backend developer, and system administrator.

1

Check current wait_timeout value

Write a SQL query to select the current value of the wait_timeout system variable using SHOW VARIABLES LIKE 'wait_timeout'.

MySQL

-- Write the SQL query to show the current wait_timeout value
-- Your code here

Need a hint?

This command shows the current setting for wait_timeout.

2

Set a new wait_timeout value for the session

Write a SQL statement to set the session variable wait_timeout to 200 using SET SESSION wait_timeout = 200;.

MySQL

SHOW VARIABLES LIKE 'wait_timeout';
-- Set the session wait_timeout to 200
-- Your code here

Need a hint?

Use SET SESSION wait_timeout = 200; to change the setting only for your current session.

3

Update the global wait_timeout variable

Write a SQL statement to set the global variable wait_timeout to 300 using SET GLOBAL wait_timeout = 300;. This change affects all new connections.

MySQL

SHOW VARIABLES LIKE 'wait_timeout';
SET SESSION wait_timeout = 200;
-- Set the global wait_timeout to 300
-- Your code here

Need a hint?

Use SET GLOBAL wait_timeout = 300; to change the setting for all new connections.

4

Verify the updated wait_timeout setting

Write a SQL query to verify the global wait_timeout value by running SHOW GLOBAL VARIABLES LIKE 'wait_timeout';.

MySQL

SHOW VARIABLES LIKE 'wait_timeout';
SET SESSION wait_timeout = 200;
SET GLOBAL wait_timeout = 300;
-- Verify the global wait_timeout value
-- Your code here

Need a hint?

This command confirms the global wait_timeout setting has been updated.