How to optimize the performance of MySQL and MariaDB?

Database performance directly influences the speed and stability of web applications. MySQL and MariaDB offer many optimization options, from configuration to indexes, caching and replication. Targeted adjustments make it possible to use resources more efficiently and significantly reduce query response times.

Why optimize MariaDB and MySQL?

Optimizing MariaDB and MySQL is essential, as unoptimized databases quickly reach their limits. Insufficient performance can lead to high loading times, timeouts, or even system interruptions.

When the volume of data increases or several users access the database simultaneously, each inefficient request uses the processor and RAM unnecessarily. Storing redundant data or missing indexes also slows down processing. A well-optimized database reduces server load, improves scalability, and ensures application stability even during high activity.

There are many ways to optimize a MariaDB or MySQL database on a Linux server. This includes configuration adjustments, index optimization, query enhancement, InnoDB tuning as well as cache and replication usage. The main measures are presented below.

Option 1: Identify slow queries

An important step in optimizing MySQL/MariaDB is to spot slow or inefficient queries. A poorly structured query can slow down the entire database.

MySQL and MariaDB can be configured to log all queries that execute above a certain defined threshold. This allows you to analyze slow queries and fix them if necessary.

To enable slow query logging, connect to MySQL or MariaDB:

Enter the following command to enable logging:

SET GLOBAL slow_query_log = 'ON';

The default threshold value is set to 10 seconds. Use the following command to enable logging of all queries that take longer than one second to complete:

SET GLOBAL long_query_time = 1;

These requests will be saved in the file /var/lib/mysql/hostname-slow.log.

Monitoring tools like mysqltuner Or performance_schema can also be used to identify queries for optimization and provide valuable performance insights.

Option 2: adapt the InnoDB configuration

Configuring MariaDB/MySQL is one of the most effective ways to sustainably improve database performance. Many default installations use generic values ​​suitable for small test environments, but inappropriate for production systems with large numbers of queries. By fine-tuning InnoDB parameters, the database uses resources much more efficiently.

The most important parameters include:

• innodb_flush_log_at_trx_commit : this parameter balances performance and reliability. The default 1 writes each transaction immediately to disk, ensuring maximum security, but slowing performance under high transaction load. The value 2 Significantly reduces I/O operations, with minimal risk of data loss in the event of a crash.
• innodb_log_file_size : The size of InnoDB log files directly influences the write speed. Larger files allow more transactions to be stored in memory before they are written to disk.
• innodb_file_per_table : A separate tablespace file is created for each InnoDB table. This simplifies the management of large tables, reduces common tablespace fragmentation, and improves performance during backups.
• innodb_buffer_pool_size : this value should generally represent between 50 and 80% of the available RAM, in order to keep a maximum of data and indexes in memory.
• innodb_flush_method : This parameter defines the method of writing data and logs to disk. Set this option to O_DIRECT to avoid double buffering.

To modify InnoDB settings, open the file my.cnf and adjust the configuration. Here is an example of a typical configuration:

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT

Restart MariaDB/MySQL for the changes to take effect.

Option 3: adapt the indexes

Indexes are essential for speeding up MySQL or MariaDB queries. Rather than iterating through each row in a table, the database can access relevant records directly through the index.

However, too many indexes or poorly chosen indexes can harm performance. Each index consumes space and burdens write operations. It is therefore recommended to index only frequently used columns in clauses WHERE, JOIN Or ORDER BY.

Example: if the table users is often questioned by the column emailcreate an index to speed up these queries:

CREATE INDEX idx_user_email ON users(email);

With this index, queries such as

SELECT * FROM users WHERE email="xyz@example.com";

…run significantly faster, because the database does not need to go through each row in the table, but directly accesses the corresponding entries.

Combined indexes can also be useful when multiple columns are frequently used together in queries.

Indexes that are no longer needed or rarely accessed should be deleted regularly to save space and improve the performance of write operations. For example, to delete the index idx_old_column :

DROP INDEX idx_old_column ON users;

Option 4: optimize queries

Complex or poorly designed SQL queries can overload the database and significantly slow down performance, especially when dealing with large tables.

To optimize queries, start by analyzing how they are executed using the command EXPLAINwhich displays the detailed execution plan and helps identify areas for improvement.

EXPLAIN SELECT id, email FROM users WHERE status="active";

With EXPLAINMySQL and MariaDB indicate which indexes are used, how many rows are read and in what order the tables are processed. This allows you to evaluate the effectiveness of a query and determine whether optimizations are necessary, for example by adding indexes or adjusting joins.

Avoid queries like SELECT *because they load all columns, including those you don't need. Select only the necessary columns to reduce the volume of data transferred and speed up execution. For complex joins, formulate the conditions in the clause WHERE as precisely as possible to avoid full table scans.

Option 5: Replication and Caching

Replication, i.e. sharing the load across multiple servers, and caching, which reduces the number of direct database accesses, also help optimize MariaDB and MySQL.

Replication is generally based on the primary/replica principle: the master server handles all write operations, while one or more servers replicate the data and handle read requests. This model helps distribute the load and avoid saturation of the main server. Although setting up replication requires some initial work, it significantly improves performance, especially for high-traffic applications.

Caching can also significantly speed up response times. MySQL and MariaDB have the Query Cache, which keeps the results of frequently executed queries in memory. So the same queries do not need to be recalculated.

You can set the cache size and enable Query Cache using the following settings:

SET GLOBAL query_cache_size = 64*1024*1024;
SET GLOBAL query_cache_type = 1;

For modern applications, it also makes sense to use external caching solutions like Redis, which provide even faster access to frequently requested data.

Option 6: Table Partitioning

For very large tables, query processing may become slower because the database must scan through a large number of rows. Partitioning allows you to divide a table into smaller, logically separated segments, for example by date, ID range, or other criteria.

Each partition is managed internally as a separate table, which speeds up queries targeting only certain partitions.

Here is an example of partitioning by year for an orders table:

CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

In this example, all orders for 2023 are stored in the partition p2023and those of 2024 in the score p2024.

Option 7: Use Connection Pool

Each new connection to MySQL or MariaDB consumes time and resources. If your application opens and closes a connection on every request, this creates unnecessary load on the server.

Connection pooling, used to optimize MariaDB and MySQL, keeps a certain number of connections open. Applications then reuse these existing connections instead of creating new ones with each request.

Here is an example in PHP with mysqli :

$mysqli = new mysqli('localhost', 'user', 'password', 'db');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

Instead of opening a new connection for each request, the pool reuses already established connections. This reduces the load on the database server and improves faster response times.