How to tune MariaDB (my.cnf) on an intoPanel server
Set MariaDB options such as innodb_buffer_pool_size and max_connections safely, from the panel or in your own my.cnf file over SSH, and check that they took effect.
MariaDB's defaults are made for small machines. On a hosting server with many WordPress and shop databases, a few settings make a large difference: how much memory InnoDB may use for caching, how many connections are allowed, and how slow queries are logged.
Before you start#
- You need root access over SSH, or an admin login to the panel.
- Applying new settings restarts MariaDB. Websites lose their database for a few seconds, so choose a quiet moment.
- Write down the current values so you can go back.
Step 1: Try the panel first#
In the admin area, open Databases → Database Server Settings. The page sets the options most servers need: max_connections, innodb_buffer_pool_size, max_allowed_packet, wait_timeout, interactive_timeout, max_connect_errors, open_files_limit, table_open_cache, thread_cache_size, the buffer sizes, innodb_flush_log_at_trx_commit, slow_query_log, long_query_time, performance_schema and sql_mode.
Click Apply and restart MariaDB. If MariaDB does not start with the new values, the panel puts the previous ones back by itself. For the options on that page, this is the safest way.
The panel writes these values to /etc/my.cnf.d/zz-intopanel-tuning.cnf (on Ubuntu and Debian, /etc/mysql/mariadb.conf.d/zz-intopanel-tuning.cnf). Do not edit that file by hand: the panel rewrites it.
Step 2: See what MariaDB uses now#
Over SSH, look at the current values:
mariadb -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','tmp_table_size','max_heap_table_size','table_open_cache','slow_query_log');"And how the server is doing:
mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Max_used_connections','Threads_connected','Created_tmp_disk_tables','Created_tmp_tables','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests');"- If
Max_used_connectionsis close tomax_connections, raise the limit. - If
Innodb_buffer_pool_readsis more than about 1% ofInnodb_buffer_pool_read_requests, the buffer pool is too small for the data that is used often. - Many
Created_tmp_disk_tablescompared withCreated_tmp_tablessuggests raisingtmp_table_sizeandmax_heap_table_sizetogether.
Step 3: Add options the panel does not offer#
Put your own options in a file of your own, which MariaDB reads after the panel's file:
nano /etc/my.cnf.d/zz-local.cnf # AlmaLinux, Rocky Linux
nano /etc/mysql/mariadb.conf.d/zz-local.cnf # Ubuntu, Debian[mysqld]
# options not offered on Database Server Settings
innodb_log_file_size = 512M
innodb_io_capacity = 1000
skip_name_resolve = ONKeep to options the panel does not set, so the two files never disagree.
Step 4: Choose sensible values#
| Option | A good starting point |
|---|---|
innodb_buffer_pool_size | About 25–40% of memory on a shared hosting server (websites and PHP need the rest); the panel allows up to 70% |
max_connections | 150–300; each connection can use several MB under load |
tmp_table_size and max_heap_table_size | The same value, 64M–256M |
table_open_cache | 2000–4000 on servers with many databases |
slow_query_log with long_query_time = 2 | On while you investigate slow sites |
Step 5: Restart and check#
systemctl restart mariadb
systemctl status mariadb --no-pager
mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"If MariaDB does not start, read why, fix or remove the line in your zz-local.cnf, and start it again:
journalctl -u mariadb -n 50 --no-pagerRelated#
- In the panel, Databases → Database Usage Limits limits connections and queries per hour for each database user, so one account cannot overload MariaDB.
- Read the slow query log at
/var/lib/mysql/*-slow.log(or the path inslow_query_log_file) to find the queries worth fixing.
Frequently asked questions
Where is my.cnf?
On AlmaLinux and Rocky Linux the main file is /etc/my.cnf, which includes every .cnf file in /etc/my.cnf.d/. On Ubuntu and Debian it is /etc/mysql/my.cnf, which includes the files in /etc/mysql/mariadb.conf.d/. intoPanel's settings are in zz-intopanel-tuning.cnf in that folder; put your own in a separate file such as zz-local.cnf.
How much memory should InnoDB get?
On a shared hosting server, start at about a quarter to a third of the server's memory and watch the buffer pool hit rate. A dedicated database server can use far more.
Can I upgrade MariaDB to a newer major version?
intoPanel uses the MariaDB version the operating system supports for its release. A major upgrade replaces the database server under running websites and cannot be undone, so it is not offered from the panel. If you need it, back up every database first and do it from the console.
Something missing? Ask in the community or open a ticket.