How to optimise MySQL/MariaDB performance on a Linux server

The performance of a database has a major impact on the speed and stability of web applications. Both MariaDB and MySQL offer a wide range of optimisation options — from configuration adjustments to indexing, caching, and replication. With targeted optimisations, you can use database resources more efficiently and significantly reduce query response times.

Why should you optimise MariaDB and MySQL?

Optimising MariaDB and MySQL makes sense because unoptimised databases can quickly reach their limits. Poor performance can lead to long load times, timeouts, or even system failures.

Especially with large amounts of data or many simultaneous accesses, every inefficient query strains the CPU and RAM. Storing redundant data or missing indexes also negatively impact speed. An optimised database reduces system load, improves scalability, and ensures applications run stably even under high load.

How can MySQL/MariaDB be optimised?

There are several ways to optimise a MariaDB or MySQL database on a Linux server. These include configuration adjustments, index optimisation, query improvements, InnoDB tuning, as well as implementing caching or replication. The following sections outline the most important measures.

Option 1: Identify slow queries

An important step in optimising MySQL or MariaDB is identifying slow or inefficient queries. Even a single poorly structured query can negatively impact the performance of the entire database.

MySQL and MariaDB can be configured to log all queries that exceed a specified execution time. This allows you to track slow queries and optimise them as needed.

To enable slow query logging, log in to MySQL or MariaDB:

mysql -u root -p

Enter the following command to enable logging:

SET GLOBAL slow_query_log = 'ON';

The default threshold is 10 seconds. Use the following command to enable logging for any query that takes longer than 1 second:

SET GLOBAL long_query_time = 1;

Queries that take longer than 1 second will be logged under /var/lib/mysql/hostname-slow.log.

Monitoring tools like mysqltuner or performance_schema can also be used and provide valuable insights to identify which queries can be optimised.

Option 2: Adjust InnoDB configuration

Configuring MariaDB or MySQL is one of the most effective ways to sustainably improve database performance. Many default installations use generic settings designed for small test environments rather than production systems handling numerous queries. By fine-tuning InnoDB parameters, you can ensure that the database uses available resources more efficiently.

Key parameters include:

• innodb_flush_log_at_trx_commit: Balances performance and reliability. The default value 1 writes each transaction immediately to disk, ensuring maximum data safety but potentially reducing performance under heavy load. Setting it to 2 decreases I/O operations significantly, with a small risk of data loss in case of a crash.
• innodb_log_file_size: Controls the size of InnoDB log files. Larger values allow more transactions to be buffered in memory before being written to disk, improving write performance.
• innodb_file_per_table: Creates a separate tablespace file for each InnoDB table. This helps manage large tables more easily, reduces fragmentation in the shared tablespace, and can improve backup performance.
• innodb_buffer_pool_size: Determines how much memory is allocated to store data and indexes. For optimal performance, this should typically be set to 50–80% of the available RAM.
• innodb_flush_method: Defines how InnoDB writes data and logs to disk, which can affect I/O performance.

An example configuration in my.cnf could look like this:

[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: Adjust indexes

Indexes are essential for improving the performance of MySQL or MariaDB queries. Instead of scanning every row in a table, the database can use an index to jump directly to the relevant entries.

However, adding too many or inappropriate indexes can be counterproductive, as each additional index consumes storage space and slows down write operations. Therefore, it’s important to create indexes only on columns that are frequently queried.

For example, if you have a table called users and often search by the email column, adding an index can significantly speed up these queries:

CREATE INDEX idx_user_email ON users(email);

With this index, queries like

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

are executed much faster, because the database doesn’t have to search through every row of the table, but instead can directly access the matching entries.

Additionally, composite indexes can be useful when multiple columns are frequently queried together. They allow the database to use a single index to efficiently filter by several fields at once.

Indexes that are no longer needed or rarely used should also be removed regularly to free up storage space and improve write performance. In the following example, the index idx_old_column is deleted:

DROP INDEX idx_old_column ON users;

Option 4: Optimise queries

Complex or inefficient SQL queries can put a heavy load on the database and significantly reduce performance, especially when working with large tables. To optimise MySQL performance, first analyse how the database executes a query. The EXPLAIN command is particularly useful for this.

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

Using EXPLAIN, MySQL or MariaDB shows which indexes are used, how many rows need to be read, and in what order the tables are processed. This helps you assess whether a query is efficient or if further optimisations — such as adding indexes or adjusting joins — would be beneficial.

Avoid queries like SELECT *, as they retrieve all columns, including those not needed. Instead, explicitly select only the required columns. This reduces the amount of data transferred and improves query speed. For complex joins, also make sure that the conditions in the WHERE clause are as precise as possible to prevent unnecessary table scans.

Option 5: Set up replication and caching

Replication, which distributes the load across multiple servers, and caching, which reduces the number of direct database accesses, can also help optimise the performance of MariaDB and MySQL.

Replication typically follows the master-slave principle: the master server handles all write operations, while one or more slave servers replicate the data and handle read queries. This setup allows the database to process high loads more efficiently without overloading the master server. Although configuring replication requires some initial effort, it can greatly improve performance for heavily used applications.

Caching can also significantly reduce response times. MySQL and MariaDB offer a Query Cache that stores the results of frequently repeated queries so they don’t need to be re-executed. With the following settings, you can enable the query cache and define its size:

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

For modern applications, it’s also beneficial to use external caching solutions like Redis, which can access frequently needed data even faster.

Option 6: Use table partitioning

For very large tables, query processing can take longer because the database must scan every row. Partitioning allows you to split tables into smaller, logically separated parts — for example by date, ID range, or other criteria. Each partition is internally treated like a separate table, so queries that only target specific partitions can be executed much faster.

An example of partitioning an orders table by year might look like this:

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 case, all orders from 2023 will be stored in partition p2023, and all orders from 2024 in p2024.

Option 7: Use connection pooling

Each new connection to MySQL or MariaDB consumes time and resources. If your application opens and closes a connection for every single request, it can create unnecessary load on the server. To avoid this, you can use connection pooling to optimise MariaDB and MySQL. Connection pooling keeps a set number of database connections open permanently, allowing applications to reuse these existing connections instead of creating new ones each time.

An example in PHP using mysqli could look like this:

$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 existing connections. This leads to faster response times while also reducing the load on the database server.