Docs

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.

How-to over SSH AlmaLinux, Rocky Linux, Ubuntu, Debian 3 min read Updated

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_connections is close to max_connections, raise the limit.
  • If Innodb_buffer_pool_reads is more than about 1% of Innodb_buffer_pool_read_requests, the buffer pool is too small for the data that is used often.
  • Many Created_tmp_disk_tables compared with Created_tmp_tables suggests raising tmp_table_size and max_heap_table_size together.

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 = ON

Keep to options the panel does not set, so the two files never disagree.

Step 4: Choose sensible values#

OptionA good starting point
innodb_buffer_pool_sizeAbout 25–40% of memory on a shared hosting server (websites and PHP need the rest); the panel allows up to 70%
max_connections150–300; each connection can use several MB under load
tmp_table_size and max_heap_table_sizeThe same value, 64M–256M
table_open_cache2000–4000 on servers with many databases
slow_query_log with long_query_time = 2On 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-pager
  • 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 in slow_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.

Was this article helpful?

Something missing? Ask in the community or open a ticket.