Today, we're tackling a crucial topic for any developer or system administrator: database optimization. A well-configured database can make the difference between a slow, frustrating application and one that's responsive and performant.

In this article, we'll focus on the essential settings for optimizing both MySQL 8+ and MariaDB, providing detailed instructions using both the phpMyAdmin graphical interface and the command line.

Why optimize your database?

An unoptimized MySQL/MariaDB database can lead to:

  • Slow queries: Long loading times for web pages or applications.
  • Excessive resource usage: High RAM and CPU consumption on the server.
  • Database locks: Situations where operations are queued or become stuck.
  • General instability: The database server might crash or become unreachable.

Ready to give your database a performance boost? Let's get started

1. Prerequisites and Initial Considerations

Before making any changes, it's crucial to:

  • Perform a full database backup! This is the most important step. In case of errors, you'll have a safe rollback point.
  • Understand your database workload: Is it primarily read-heavy or write-heavy? How many concurrent connections does it handle?
  • Monitor current performance: Use tools like mysqltop, percona-toolkit, or simply the output of SHOW STATUS to get a baselin

2. Key Settings for Optimization

We'll analyze the most common and influential settings. Remember that optimal configurations depend on your specific use case.

a) innodb_buffer_pool_size

This is arguably the most critical setting for databases using the InnoDB storage engine (the default for MySQL and MariaDB). The buffer pool is a memory area where InnoDB stores frequently accessed data and indexes. A buffer pool that's too small will force the database to constantly read from disk, drastically slowing down operations.

General rule: Allocate 70-80% of the available RAM on the server to innodb_buffer_pool_size if the server is dedicated solely to the database. If the server also hosts the web server and other applications, you'll need to be more conservative.

Example: On a server with 8GB of RAM, you might set it to 6GB (6144M).

How to modify via phpMyAdmin:

  1. Log in to phpMyAdmin.
  2. In the left panel, click on "Variables."
  3. Search for innodb_buffer_pool_size.
  4. Click on "Edit" next to the variable.
  5. Enter the new value in bytes (e.g., 6442450944 for 6GB).
  6. Click "Save." Caution: This modification is temporary and will last until the MySQL server is restarted. To make it permanent, you must modify it in the configuration file.

How to modify via Command Line (to make permanent):

  1. Access your server via SSH.
  2. Open the MySQL/MariaDB configuration file. It's usually located at /etc/mysql/my.cnf, /etc/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf. You might need to search for the exact path for your distribution.
  3. Find the [mysqld] section and add or modify the following line:
    Ini, TOML
    [mysqld]
innodb_buffer_pool_size = 6G
    (Replace 6G with your desired value. You can use M for Megabytes or G for Gigabytes).
  4. Save the file and restart the MySQL/MariaDB service:
    Bash
    sudo systemctl restart mysql # For MySQL
# Or
sudo systemctl restart mariadb # For MariaDB

b) key_buffer_size (for MyISAM)

If your database uses MyISAM tables (less common with MySQL 8+ and MariaDB, which prefer InnoDB), this setting is crucial. It's the amount of memory dedicated to buffering MyISAM table indexes.

General rule: Allocate a small percentage of total RAM (e.g., 128M - 256M) if you still have MyISAM tables. If you don't have MyISAM tables, you can keep it at a low or default value.

How to modify via phpMyAdmin:

  1. Log in to phpMyAdmin.
  2. Go to "Variables."
  3. Search for key_buffer_size.
  4. Modify the value in bytes and save. (Remember it's temporary).

How to modify via Command Line (to make permanent):

  1. Open the my.cnf file (or equivalent).
  2. In the [mysqld] section, add or modify:
    Ini, TOML
    [mysqld]
key_buffer_size = 128M
  3. Save and restart the MySQL/MariaDB service.

c) max_connections

This parameter defines the maximum number of simultaneous connections the MySQL/MariaDB server can accept. If you reach this limit, new connections will be rejected.

General rule: Set it to a value slightly higher than the peak number of connections you observe, but don't overdo it. Each connection consumes resources. A value that's too high on a server with low RAM can lead to out-of-memory issues. Start with values like 150-300 and monitor.

How to modify via phpMyAdmin:

  1. Log in to phpMyAdmin.
  2. Go to "Variables."
  3. Search for max_connections.
  4. Modify the numerical value and save. (Temporary).

How to modify via Command Line (to make permanent):

  1. Open the my.cnf file.
  2. In the [mysqld] section, add or modify:
    Ini, TOML
    [mysqld]
max_connections = 300
  3. Save and restart the MySQL/MariaDB service.

d) query_cache_size and query_cache_type (Deprecated in MySQL 8+)

The query cache was a feature that stored the results of identical queries to serve them faster. However, in MySQL 8 and higher versions, it has been removed because it often caused more performance problems than benefits, especially on systems with high traffic and many writes.

For MySQL 8+: Don't worry about these settings; they no longer exist.

For MariaDB or MySQL 5.7: If you're still using it, it's advisable to disable it or set it to a very low value.

How to modify via phpMyAdmin (if present):

  1. Log in to phpMyAdmin.
  2. Go to "Variables."
  3. Search for query_cache_size and query_cache_type.
  4. Set query_cache_size to 0 and query_cache_type to OFF.

How to modify via Command Line (to make permanent):

  1. Open the my.cnf file.
  2. In the [mysqld] section, add or modify:
    Ini, TOML
    [mysqld]
query_cache_size = 0
query_cache_type = OFF
  3. Save and restart the MySQL/MariaDB service.

e) innodb_log_file_size and innodb_log_files_in_group

InnoDB redo log files are used to ensure transaction durability and consistency. Properly sizing these files can improve write performance.

General rule: Larger values reduce checkpoint frequency but increase recovery time in case of a crash. A good starting point might be 256M or 512M for innodb_log_file_size, with innodb_log_files_in_group set to 2.

How to modify via phpMyAdmin:

  • You cannot directly modify these variables via phpMyAdmin permanently, as they require deleting old log files and recreating them, an operation typically done manually. phpMyAdmin only shows the current values.

How to modify via Command Line (to make permanent):

This operation requires care and a service interruption.

  1. Stop the MySQL/MariaDB service:
    Bash
    sudo systemctl stop mysql # Or mariadb
  2. Edit the my.cnf file:
    Ini, TOML
    [mysqld]
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
  3. Delete the old InnoDB log files: These are typically located in the MySQL data directory (e.g., /var/lib/mysql/). The files are named ib_logfile0, ib_logfile1, etc.
    Bash
    sudo rm /var/lib/mysql/ib_logfile*
    ATTENTION: Make sure you are in the correct directory and only delete the InnoDB log files!
  4. Start the MySQL/MariaDB service:
    Bash
    sudo systemctl start mysql # Or mariadb
    The server will automatically create new log files with the specified size.

f) tmp_table_size and max_heap_table_size

These settings control the maximum size of in-memory temporary tables created by MySQL/MariaDB to handle complex queries (e.g., those with GROUP BY, ORDER BY, DISTINCT, complex joins).

General rule: Set them to a value that can accommodate most of your temporary tables in RAM (e.g., 64M - 256M). If a temporary table exceeds this size, it will be written to disk, slowing down the operation. Both variables should have the same value.

How to modify via phpMyAdmin:

  1. Log in to phpMyAdmin.
  2. Go to "Variables."
  3. Search for tmp_table_size and max_heap_table_size.
  4. Modify the values in bytes and save. (Temporary).

How to modify via Command Line (to make permanent):

  1. Open the my.cnf file.
  2. In the [mysqld] section, add or modify:
    Ini, TOML
    [mysqld]
tmp_table_size = 128M
max_heap_table_size = 128M
  3. Save and restart the MySQL/MariaDB service.

g) join_buffer_size

The join_buffer_size is used for joins that cannot utilize an index. If you have queries that perform full table scans for joins, increasing this value can help.

General rule: Don't overdo this value. Start with a default value (e.g., 256K - 1M) and increase it only if your monitoring indicates that joins are frequently writing to disk.

How to modify via phpMyAdmin:

  1. Log in to phpMyAdmin.
  2. Go to "Variables."
  3. Search for join_buffer_size.
  4. Modify the value in bytes and save. (Temporary).

How to modify via Command Line (to make permanent):

  1. Open the my.cnf file.
  2. In the [mysqld] section, add or modify:
    Ini, TOML
    [mysqld]
join_buffer_size = 1M
  3. Save and restart the MySQL/MariaDB service.

3. Post-Optimization Monitoring

Optimization is not a "set it and forget it" operation. After each change, it's crucial to monitor your database's performance.

  • phpMyAdmin: The "Status" section in phpMyAdmin provides a useful overview of server variables and statistics. Pay attention to Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads to understand buffer pool efficiency.
  • Command Line (CLI):
    • SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
    • SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
    • SHOW GLOBAL STATUS LIKE 'Max_used_connections'; (for max_connections)
    • SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; (for tmp_table_size and max_heap_table_size)
    • SHOW GLOBAL STATUS LIKE 'Key_reads'; (for key_buffer_size if you use MyISAM)
  • External monitoring tools: Grafana with Prometheus, Nagios, Zabbix, or tools offered by your cloud provider.

Conclusion

Optimizing your MySQL or MariaDB database is an ongoing process of analysis, modification, and monitoring. The settings we've covered in this article represent the most impactful starting points for improving performance. Always remember to back up your data, understand your workload, and carefully monitor the results of each change.

We hope this detailed guide helps you take your database to a new level of efficiency! Do you have other settings you find essential? Share them in the comments!