Database performance is the foundation of stable web project operation. It is precisely the DB that most often becomes the hidden bottleneck: the application code may be optimal, the server may be powerful, but one inefficient query or an incorrectly configured buffer can nullify the entire potential of the infrastructure.

As traffic and data volume grow, problems appear gradually: pages begin to “hang,” the API responds inconsistently, CPU and disk load increase, locks and connection timeouts appear. Without a systematic diagnostic approach, such symptoms are difficult to interpret — it is unclear where exactly the degradation occurs: in queries, indexes, server parameters, or in the storage architecture itself.

This article offers a practical and reproducible approach to accelerating DB performance for projects hosted on VPS/VDS and dedicated servers. We will cover:

• how to record the initial state and understand what exactly needs improvement;
• how to diagnose the OS before diving into SQL;
• how to analyze query execution plans and design indexes for actual workload;
• how to properly configure buffers, cache, and connections with specific values;
• how to verify the result and when to move on to scaling.

The key principle is measurability. Any optimization must be confirmed by metrics and have a predictable effect. Instead of chaotic changes, you will get a structured cycle: record → diagnose → fix → verify the result.

Step 1. Define the goal and record the baseline

Before making any changes, you need to record the initial metrics. Without a baseline, it is impossible to assess the real effect of optimization — there is no point in speeding up what has not been measured.

In practice, the following are used:

• API or page response time in 95% and 99% of requests (p95/p99);
• QPS/TPS (number of queries or transactions per second) on the current server;
• execution time of key queries;
• CPU usage;
• I/O subsystem wait time;
• efficiency of cache systems usage (cache hit ratio).

Step 2. Record symptoms at the OS level

Before analyzing SQL queries, you need to check the state of the operating system. If the server is limited by disk subsystem or memory, changing DB indexes or parameters will not eliminate the root cause — query optimization only helps on healthy infrastructure.

Basic command set:

uptime
free -h
df -h
iostat -x 1 5
vmstat 1 5

Step 3. Query analysis and acceleration

After checking the infrastructure, the next step is query analysis. Queries are usually what create the main load. Tuning server parameters without understanding query structure leads only to superficial improvements.

Enable the slow query log and identify the main load drivers

The slow query log helps identify operations that execute longer than the defined threshold. Analysis should take into account not only the longest queries, but also frequently repeated ones — together they create the biggest load.

MySQL / MariaDB

Add to the configuration file:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Restart MySQL:

# systemctl restart mysql

To analyze the logs, use the following command. It will show the 10 slowest queries sorted by execution time.

# mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Use EXPLAIN and analyze the execution plan

The execution plan shows how the DBMS interprets the query. The key task is to ensure that an index is being used rather than a full table scan. For quick analysis, use the EXPLAIN FORMAT=TRADITIONAL directive:

EXPLAIN FORMAT=TRADITIONAL
SELECT *
FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

For detailed analysis, use the EXPLAIN FORMAT=JSON directive:

EXPLAIN FORMAT=JSON
SELECT *
FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50\G

Example result:

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "orders",
          "access_type": "range",
          "possible_keys": ["idx_user_status_created"],
          "key": "idx_user_status_created",
          "used_key_parts": ["user_id", "status", "created_at"],
          "key_length": "14",
          "rows_examined_per_scan": 25,
          "rows_produced_per_join": 25,
          "filtered": "100.00",
          "extra": {
            "using_index": true,
            "using_filesort": true
          }
        }
      }
    ]
  }
}

Indexes: design for real-world scenarios

Indexing should be based on actual queries. Every index increases write load and consumes additional disk space, so excessive indexing is just as harmful as having no indexes.

Main principles:

• an index should significantly reduce the result set;
• the column order in a composite index should match filters and sorting (rule: WHERE first, then ORDER BY);
• indexes not used by the optimizer should be removed.

Example of a composite index:

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);

This index is optimal for the query:

SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

How to check index usage:

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_STAR DESC;

Removing unused indexes:

-- Find indexes that have not been used
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE COUNT_STAR = 0 AND INDEX_NAME != 'PRIMARY'
AND OBJECT_SCHEMA != 'mysql' AND OBJECT_SCHEMA != 'performance_schema';

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at), ALGORITHM=INPLACE, LOCK=NONE;

Optimizing expensive patterns

Some constructions create excessive load regardless of whether indexes exist.

Recommendations:

• instead of SELECT *, specify exact fields;
• in SQL queries, avoid LIKE '%term%' without a full-text index;
• do not use OFFSET on large datasets;
• eliminate N+1 queries through joins or batches.

Added brief explanations for each case in Russian.

Example of a non-optimal query

This query uses LIKE with the pattern %2024%, because of which the index on the created_at field cannot be used. As a result, the database performs a full table scan, which significantly reduces performance on large datasets.

-- Bad: full table scan for filtering
SELECT * FROM orders
WHERE created_at LIKE '%2024%'
LIMIT 100;

-- Good: precise filtering by date range
SELECT id, user_id, total, created_at FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
LIMIT 100;

The OFFSET problem on large datasets

When using OFFSET with large values, the DBMS is forced to read and sort all previous rows, even if they are not needed in the final result set. This creates unnecessary load on CPU, memory, and disk, and significantly slows down the query.

-- Bad: MySQL will read 1,000,050 rows and discard the first million
SELECT * FROM orders
OFFSET 1000000
LIMIT 50;

-- Good: keyset pagination
SELECT id, created_at, total FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

The N+1 query problem

The N+1 problem occurs when one query is first executed to fetch a list of records, and then for each record, a separate additional query is executed. On large selections, this can lead to hundreds or thousands of unnecessary database calls.

// Bad: N+1 queries
$orders = $db->query("SELECT * FROM orders WHERE user_id = ?", [$userId]);
foreach ($orders as $order) {
    // A separate query for each order!
    $items = $db->query("SELECT * FROM order_items WHERE order_id = ?", [$order['id']]);
}

// Good: one JOIN query
$data = $db->query("
    SELECT o.*, oi.*
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE o.user_id = ?
", [$userId]);

Step 4. Configuring DB parameters and connections

Configuration tuning should only be done after load analysis. Memory and connection parameters depend on RAM volume, number of concurrent users, and nature of operations.

DB buffers and cache

A database works fast only when “hot” data and indexes are in RAM instead of being read from disk every time. Allocating the correct amount of RAM for internal buffers is the most important tuning step, and it should be done before tuning connections, because the buffer size directly determines how much memory remains for connections.

InnoDB Buffer Pool (MySQL)

# Main cache for InnoDB data and indexes
# Recommendation: 50-75% of free RAM on a dedicated server
innodb_buffer_pool_size = 12G

# Page size in the buffer (in bytes)
innodb_page_size = 16K

# Number of buffer pool instances (for parallelism on multi-core servers)
innodb_buffer_pool_instances = 8

# Percentage of the buffer allocated to old data
innodb_old_blocks_pct = 37

# Time in milliseconds before moving a page from the "old" to the "new" part
innodb_old_blocks_time = 1000

# Server with 4GB RAM (small projects)
innodb_buffer_pool_size = 2G

# Server with 8GB RAM (medium projects)
innodb_buffer_pool_size = 6G

# Server with 16GB RAM (large projects)
innodb_buffer_pool_size = 12G

# Server with 32GB RAM (high-load projects)
innodb_buffer_pool_size = 24G

Other important buffers

# Buffer for sort operations and hash tables (per operation)
sort_buffer_size = 256K

# Buffer for join operations (per operation)
join_buffer_size = 256K

# Buffer for table reads (per operation)
read_rnd_buffer_size = 8M

# Input buffer (per connection)
read_buffer_size = 128K

# Size of the log buffer before writing to disk
binlog_cache_size = 32K

# Maximum size of a data packet
max_allowed_packet = 64M

# Buffer for temporary tables
tmp_table_size = 32M
max_heap_table_size = 32M

How to check buffer efficiency:

-- Find out the current buffer size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Check buffer status
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Cache hit ratio should be above 95%
SELECT (Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)) * 100 AS cache_hit_ratio;

If cache_hit_ratio is below 90%, increase innodb_buffer_pool_size. If RAM consumption sharply increases or SWAP becomes active after the increase, reduce it back: an oversized buffer is just as harmful as an undersized one.

Connection optimization

Each open database connection consumes RAM and CPU time for context switching. The calculation of max_connections is based on the already fixed value of the buffer pool.

# Maximum number of simultaneous connections
max_connections = 200

# Timeout for an inactive connection (in seconds)
wait_timeout = 28800
interactive_timeout = 28800

# Maximum number of connections from one host
max_connections_per_hour = 1000
max_user_connections = 50

How to calculate max_connections

max_connections = (available_memory - buffer_pool) / memory_per_connection

But it is recommended to be more conservative: max_connections = 200–500 for most applications.

Tools for connection pooling

• ProxySQL — a proxy server between the application and MySQL that manages a connection pool and reduces load on the database. It reuses already open connections, which reduces the cost of constantly creating and closing them. It also supports load balancing, read/write splitting, and flexible SQL routing.

• MySQL Router — the official connection routing tool from Oracle Corporation. It automatically directs requests to the appropriate MySQL server (for example, primary or replica) and is used in clusters such as MySQL InnoDB Cluster. The solution is easier to configure, but has fewer capabilities than ProxySQL.

Interpretation

• Upper limit: setting a hard limit (max_connections) protects the database from crashing due to lack of memory. Excess requests will wait in a queue at the web server level instead of “choking” the DB engine.
• How a pooler works: a tool like ProxySQL can keep, for example, 50 permanent connections to the database, but successfully serve 5000 connections from the web server by instantly reusing them.

Other critical performance parameters

# Storage engine (always use InnoDB for new projects)
default-storage-engine = InnoDB

# Row format (Barracuda supports more features)
innodb_file_format = Barracuda
innodb_file_per_table = ON

# Transaction isolation level
transaction_isolation = READ-COMMITTED

# Automatic commit
autocommit = 1

# Maximum query execution time (in seconds)
max_execution_time = 300

# Log flush level to disk
innodb_flush_log_at_trx_commit = 2

# InnoDB log size
innodb_log_file_size = 512M

# InnoDB log buffer
innodb_log_buffer_size = 16M

# Number of threads for input/output operations
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Preload buffer pool at startup
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

Step 5. Verify the result and compare it with the baseline

After each change cycle, you need to measure the effect. Any change is a hypothesis: without verification, it is unknown whether the original problem was solved or a new one was created (for example, SELECT became faster, but INSERT and UPDATE became slower).

Verification tools

Comparison of the baseline metrics recorded in step one with the current server metrics under real load.

Result interpretation

• Log cleanliness: the slow query log should become significantly “cleaner,” and the execution time of the heaviest queries should fall below your target threshold.
• Disk relief: iostat output should show a clear decrease in disk load (I/O wait) — this confirms that the new indexes or expanded buffers worked.
• p95/p99 stabilization: response time graphs no longer show random spikes, and the site runs smoothly even during traffic surges.

If the metrics did not improve, return to step 2 and repeat the cycle. Only after confirming a positive effect should you move on to maintenance and scaling.

Step 6. Data maintenance and degradation prevention

Regular maintenance is necessary to keep execution plans predictable and reduce fragmentation. Without it, performance degrades over time — regardless of the quality of the initial tuning.

Updating statistics

The MySQL query optimizer builds execution plans based on data distribution statistics. If they are outdated (after mass deletion, updates, or import), the database may choose a highly inefficient path — for example, run a full scan instead of using a newly created index.

Commands for updating statistics

-- Update statistics for a specific table
ANALYZE TABLE table_name;

-- Optimize the table (defragmentation)
OPTIMIZE TABLE table_name;

-- Check and repair the table
CHECK TABLE table_name;
REPAIR TABLE table_name;

Interpretation and usage

Sudden slowdown after import: if you loaded a large batch of data and the site immediately began to slow down, the first step is always a manual statistics update.

ANALYZE TABLE orders, order_items, customers;
FLUSH TABLE orders, order_items, customers;

Maintenance automation

This cron job is intended for regular updates of MySQL table statistics, which the query optimizer uses to choose efficient execution plans.

When statistics become outdated (for example, after a large number of INSERT, UPDATE, DELETE operations), the optimizer may start choosing suboptimal indexes or execution strategies, which reduces performance. The ANALYZE TABLE command updates these statistics.

# Weekly statistics analysis
0 2 * * 0 mysql -u root -p `'password'` `your_database` -e "SELECT CONCAT('ANALYZE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' INTO OUTFILE '/tmp/analyze.sql'; SOURCE /tmp/analyze.sql;"

Conclusion

Database optimization is a cycle: measurement, analysis, adjustment, repeated measurement. Only this approach ensures a stable and controlled result.

Chaotic changes without a baseline are the most common mistake. An engineer changes innodb_buffer_pool_size, adds several indexes, adjusts max_connections — and does not know what exactly worked and what made writes worse. The next incident then becomes impossible to reproduce and fix. That is why every change must be isolated, measurable, and reversible.

The second important point is the correct sequence of priorities. Speeding up one heavy query through an index gives more benefit than fine-tuning ten configuration parameters. Buffers and connections are amplifiers of an already optimized system, not a substitute for working with queries.

Finally, performance degrades on its own — as data grows, workload changes, and statistics become outdated. A one-time optimization without regular maintenance gives only a temporary effect. Monitoring cache_hit_ratio, running ANALYZE TABLE periodically, and controlling the slow query log are not one-time actions, but part of ongoing operations.

MySQL optimization checklist:

1. ✓ Record baseline metrics (response time, QPS, cache hit ratio)
2. ✓ Check OS state (CPU, I/O, memory, swap)
3. ✓ Enable slow query log and analyze slow queries
4. ✓ Analyze EXPLAIN for each problematic query
5. ✓ Add indexes for actual queries, remove unused ones
6. ✓ Configure innodb_buffer_pool_size (50–75% RAM)
7. ✓ Configure max_connections and connection pooling
8. ✓ Measure again and compare with the baseline
9. ✓ Set up regular maintenance (ANALYZE TABLE, monitoring)

By following this approach, you will significantly improve the performance of your MySQL server.