Introduction
In this lab, you will learn the fundamentals of MySQL server configuration and performance tuning. You will start by inspecting the current server settings, then modify a key performance parameter, the innodb_buffer_pool_size, to see how changes are applied.
The lab will guide you through editing the MySQL configuration file, restarting the server to apply changes, and verifying that the new settings are active. Finally, you will learn a basic method for analyzing query performance using MySQL's built-in profiler. This will provide a foundation for optimizing your database for different workloads.
View the Current MySQL Configuration
Before making any changes, it is crucial to understand the current configuration of your MySQL server. In this step, you will connect to MySQL and inspect the value of a critical performance variable, innodb_buffer_pool_size.
First, open the terminal from your desktop.
Connect to the MySQL server as the root user. In this lab environment, you can use sudo to connect without a password.
sudo mysql -u root
Once connected, you will see the MySQL prompt (mysql>).
System variables control the behavior of the MySQL server. The innodb_buffer_pool_size variable determines the amount of memory allocated to cache data and indexes for InnoDB tables. A properly sized buffer pool is essential for good performance.
Use the SHOW VARIABLES command with a LIKE clause to find the current value of this variable.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
You will see output similar to the following, showing the default value in bytes. Note this value, as you will change it in the next step.
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+--------------------------+-----------+
1 row in set (0.01 sec)
Now that you have checked the current configuration, you can exit the MySQL shell.
exit
Modify the MySQL Configuration File
MySQL settings can be changed temporarily for the current session or permanently in a configuration file. To make a change persist across server restarts, you must edit the configuration file. In this step, you will modify /etc/mysql/my.cnf to increase the innodb_buffer_pool_size.
Open the MySQL configuration file using the nano text editor with sudo privileges.
sudo nano /etc/mysql/my.cnf
Scroll down to find the [mysqld] section. This section contains settings specific to the MySQL server daemon. Add the following line under the [mysqld] heading to set the buffer pool size to 256 megabytes.
innodb_buffer_pool_size=256M
Your [mysqld] section should now look something like this:
[mysqld]
innodb_strict_mode=OFF
init_connect='SET NAMES utf8'
character-set-server = utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size=256M
Now, save the file and exit nano. Press Ctrl+X, type Y to confirm the changes, and press Enter to write to the file.
The changes in the configuration file will only take effect after the MySQL server is restarted. Use the service command to restart it.
sudo service mysql restart
You have now permanently updated the configuration. In the next step, you will verify that the change is active.
Verify the Configuration Change
After modifying the configuration file and restarting the server, you must verify that the new setting has been applied correctly. In this step, you will reconnect to MySQL and check the innodb_buffer_pool_size variable again.
Connect to the MySQL server.
sudo mysql -u root
Now, run the SHOW VARIABLES command again to see the new value.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
The output should now show the new value in bytes. MySQL automatically converts 256M (256 megabytes) to 268435456 bytes.
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+--------------------------+-----------+
1 row in set (0.00 sec)
Comparing this value to the one you noted in Step 1 confirms that your configuration change was successful and is now active.
You can now exit the MySQL shell.
exit
Analyze Query Performance
Tuning server variables is done to improve query performance. While a deep analysis is complex, you can use MySQL's built-in query profiler to get a basic measurement of query execution time. In this step, you will create a test table, insert data, and analyze a simple query.
First, connect to the MySQL server.
sudo mysql -u root
Create a new database named testdb and switch to it.
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
Next, create a table named employees to store sample data.
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary INT
);
Insert a few records into the employees table.
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 60000),
('Bob', 'Engineering', 80000),
('Charlie', 'Sales', 65000),
('David', 'Marketing', 55000);
Now, enable the query profiler for your session. This will record performance data for subsequent queries.
SET profiling = 1;
Execute a query that you want to analyze. For example, let's find all employees in the 'Sales' department.
SELECT * FROM employees WHERE department = 'Sales';
To see the performance results, use the SHOW PROFILES command. This lists the queries you have run since enabling profiling and their durations.
SHOW PROFILES;
The output will look similar to this, showing the duration of each query in seconds.
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------+
| 1 | 0.00038500 | SELECT * FROM employees WHERE department = 'Sales' |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
This Duration column gives you a baseline for query performance. In a real-world scenario, you would use this tool to compare query speeds before and after configuration changes on much larger datasets.
You have successfully used the profiler to analyze a query. You can now exit the MySQL shell.
exit;
Summary
In this lab, you have learned the basic process of configuring and tuning a MySQL server. You practiced how to view the current server configuration by inspecting system variables like innodb_buffer_pool_size.
You gained hands-on experience modifying the MySQL configuration file (my.cnf) to make permanent changes, restarting the server to apply them, and verifying that the new settings are active. Finally, you were introduced to a basic performance analysis technique using MySQL's built-in query profiler to measure query duration.
These fundamental skills are essential for any developer or administrator responsible for maintaining a healthy and performant MySQL database.
