One of the most common and popular relational databases is MySQL. Web applications and services are often built on this database. However, to achieve stable operation and high performance, proper MySQL configuration is required, especially when running it on a VPS. Optimizing the MySQL server allows for efficient resource usage and improves response times for queries, which is particularly important for high-load applications.
MySQL: installation on VPS
Installing MySQL on your server is necessary before starting optimization. To understand how to do this, we will provide you with a step-by-step guide for this process for two operating systems.
Installation process for Ubuntu/Debian
-
Install the latest version of the package manager:
sudo apt update
2. Install MySQL:
sudo apt install mysql-server
3. The next step is to bring MySQL into a working state, after which you need to configure it to start automatically when your operating system boots:
sudo systemctl start mysql
sudo systemctl enable mysql
Installation process for CentOS/RHEL
-
You need to install MySQL from the repository:
sudo yum install mysql-server -
Next, you need to bring MySQL into a working state and configure it to start automatically:
sudo systemctl start mysqld
sudo systemctl enable mysqld -
Run the initial configuration for security improvements:
sudo mysql_secure_installation
MySQL: optimization process
To improve performance, it is necessary to make changes to the MySQL configuration after the installation process is complete. Optimization includes several areas: caching, indexing, and configuration settings.
1. Caching settings
Caching in MySQL allows storing frequently requested data, which reduces the load on the database and speeds up response times. Below is a list of key parameters for configuring caching:
-
query_cache_size — the size of the query cache. A larger size improves performance.
-
query_cache_limit — the limit on the size of cached results.
-
innodb_buffer_pool_size — a key parameter for InnoDB that sets the amount of memory allocated for storing data and indexes in the cache.
When configuring MySQL caching, changes are most often made in the my.cnf file, located in the /etc/mysql/ or /etc/ directory on most Linux servers.
Here is a detailed guide for applying caching parameters:
Step 1: Open the MySQL configuration file
Open the my.cnf file for editing. Depending on the system, you may need to run the command as an administrator:
sudo nano /etc/mysql/my.cnf
Step 2: Find the [mysqld] section
To ensure that caching parameters take effect on the MySQL server, they need to be included in the [mysqld] section.
Step 3: Add caching parameters
Copy the configurations listed below and apply them in the my.cnf document under the [mysqld] section:
[mysqld]
query_cache_type = 1 # Enable query cache
query_cache_size = 64M # Define cache size
query_cache_limit = 1M # Maximum result size for caching
table_open_cache = 2000 # Total number of tables stored in cache
thread_cache_size = 50 # Number of threads that can be cached
Note: The set values, such as 64M for query_cache_size, can be adjusted according to your needs.
Step 4: Restart MySQL for the changes to take effect
After editing, save the file and exit the editor. To do this, restart MySQL using the command:
sudo systemctl restart mysql
Checking caching settings
To verify that the changes have taken effect, execute the command:
SHOW VARIABLES LIKE 'query_cache%';
SHOW VARIABLES LIKE 'table_open_cache';
SHOW VARIABLES LIKE 'thread_cache_size';
These commands will display the current state of the parameters, allowing you to verify their correctness and adjust the settings if necessary.
2. Indexing tables
Indexing in MySQL is a method of improving performance that optimizes the search and retrieval of information from the database. Indexes are created for table columns and help the MySQL server find rows that match query conditions faster by reducing the number of rows scanned. Similar to a table of contents in a book, an index indicates where to find the needed data without scanning the entire dataset.
Why is indexing necessary?
-
Speeding up queries: indexes provide faster detection and retrieval of information by the MySQL server, significantly enhancing query processing efficiency.
-
Reducing server load: the number of input/output operations is reduced due to indexing, easing query processing and consequently reducing server load.
-
Optimizing join operations (JOIN): indexes help find and match rows faster when joining multiple tables.
-
Improving sorting and grouping: when using indexes, MySQL can perform ORDER BY and GROUP BY operations faster.
When and which indexes to use?
-
Primary key (PRIMARY KEY): created for the unique identifier of each record (e.g., user ID). MySQL automatically creates an index for the primary key.
-
Unique indexes (UNIQUE): applied to fields whose values must be unique (e.g., email).
-
Regular indexes (INDEX): useful for columns frequently used in WHERE conditions or JOINs.
-
Composite indexes: index multiple columns at once, which is convenient for optimizing complex queries with multiple conditions.
Examples of using indexing
Let's explore a few real cases where indexes are applied.
1. Indexing a column with frequent queries
Suppose we have a table orders containing columns id, customer_id, and status. If queries frequently select orders by status, then to significantly improve the speed of such queries, you need to create an index for the status column.
CREATE INDEX idx_status ON orders(status);
After executing the query:
SELECT * FROM orders WHERE status = 'completed';
MySQL will use the index to quickly find rows with status = 'completed', without scanning all rows in the table.
2. Unique indexes to limit data
If we want to limit duplication in the email field of the users table, we can create a unique index:
CREATE UNIQUE INDEX idx_email ON users(email);
Now, attempting to insert a duplicate email will raise an error, as the unique index prohibits duplicates.
3. Indexes for complex queries consisting of multiple components
When we have queries that often select data by multiple fields, such as firstname and lastname in the employees table, we can create a composite index:
CREATE INDEX idx_name ON employees(firstname, lastname);
Now, when executing the command:
SELECT * FROM employees WHERE firstname = 'John' AND lastname = 'Doe';
The selection will be faster, as MySQL will use the composite index and there is no longer a need to check all records.
Indexes: maintenance and updating
It is worth remembering that the main recommendation is to index only columns that are frequently involved in queries, and to periodically review their relevance. After all, indexes actively use disk space, significantly slow down the process of updating, deleting records, and inserting. The reason for this is the need to recalculate with every change in data.
Indexes: example of monitoring
You can use the following command to view indexes:
SHOW INDEX FROM orders;
This gives us information about which indexes are created, their type, and which columns they apply to.
3. Configuring connection parameters
Parameters that control the number of simultaneous connections allow for flexible management of server load. The main parameters are:
-
max_connections — the maximum number of connections.
-
wait_timeout — the time to wait for inactive connections to close.
[mysqld]
max_connections = 200
wait_timeout = 600
Support and monitoring of MySQL
To maintain MySQL performance, it is important to monitor its state and periodically perform optimization. Use the following approaches:
-
Monitoring MySQL processes: to observe running queries, you can use the SHOW PROCESSLIST commands or specialized utilities.
Regularly perform the table optimization process using the appropriate command:
OPTIMIZE TABLE table_name;
-
Automating monitoring: Set up monitoring using tools like MySQL Workbench, Percona Monitoring and Management, or Grafana for continuous performance observation.
Conclusion
Configuring and optimizing MySQL on VPS helps improve the efficiency and stability of application performance. By applying the methods described, you can enhance data processing speed, reduce latency, and increase the reliability of the MySQL server. Regular updates and monitoring of settings help maintain high performance in the long term.
Indexing — an important technique for optimizing MySQL performance. It becomes particularly effective when working with queries that involve filtering and sorting, as well as with large tables. When applied correctly, indexes significantly improve database performance. However, it is important to remember to apply them cautiously, focusing on truly important columns for search conditions, sorting, or joining.