GoAdmio

my

Regenerate the MariaDB configuration file with settings tuned to the server's RAM, CPU, and database usage, then restart MariaDB

On this page: Overview · Quick start · Syntax · Output · Examples · Troubleshooting · Details · See also


Overview

/go/my writes a new MariaDB configuration file from scratch and restarts MariaDB so it takes effect. It chooses values from:

[!WARNING] The command overwrites /etc/my.cnf and does not keep a copy of the old file. Any settings you added by hand are lost. It also restarts MariaDB, which briefly drops all connections. Back up the file first (see Examples).

Running it again re-tunes the settings to the server's current state.


Quick start

# Back up the current file, then regenerate the configuration and restart MariaDB
cp /etc/my.cnf /etc/my.cnf.bak-$(date +%F)
/go/my

Syntax

/go/my [system] [--log]
Option Notes
system Writes /var/go/etc/my.cnf for GoAdmio's separate MariaDB instance (port 3307, set up by mysql system) instead of /etc/my.cnf. MariaDB is not restarted in this mode.
--log Turns on the slow query log and the general query log. See Query logging. Without it, logging is off.

Options can be combined. --log is detected anywhere in the arguments.


Output

Nothing is printed on success. The only messages are:

Message Meaning
MariaDB requires at least 0.5 GB of RAM to install. The server has less than 512 MB of RAM. Exit status 1, nothing is changed. The wording is copied from installmariadb.
Instance (<name>) already running. Another command holds the lock file (see Lock). Exit status 1, nothing is changed.

When installmariadb runs this command, it hides the output, so these messages don't appear there.


Examples

# Back up the current configuration first
cp /etc/my.cnf /etc/my.cnf.bak-$(date +%F)
# Regenerate the configuration, with query logging off
/go/my
# Regenerate with the slow query log and general query log on
/go/my --log
# Regenerate the configuration for GoAdmio's separate instance (no restart)
/go/my system
# Check the tier and the main values that were chosen
grep -E 'RAM:|innodb_buffer_pool_size|max_connections|key_buffer_size' /etc/my.cnf
# Confirm MariaDB is using them
mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# See the query logs that earlier runs moved aside
ls /var/log/go/mysql/

Troubleshooting

Symptom Likely cause Fix
MariaDB requires at least 0.5 GB of RAM to install. The server has less than 512 MB of RAM. Add RAM. Swap doesn't count.
Instance (<name>) already running. A backup, or another command that uses the same lock file, is running. A lock older than one hour is ignored. Wait for it to finish, then run again.
Your custom settings are gone The file is rewritten from scratch each run. Restore your backup and re-add the settings after each run, or change the script.
Files in /etc/my.cnf.d are no longer read The stock /etc/my.cnf on the dnf systems typically includes that directory. The generated file has no include line. Copy the settings you need into the script, or add an !includedir line after each run.
The server picked a smaller tier than expected Tiers use decimal thresholds, such as 8,000,000 KB. A server sold as 8 GB can report slightly less and fall into the 4 GB tier. Check the # RAM: comment in the file. See Tiers by RAM.
MariaDB doesn't start after the run A generated value is rejected by the installed MariaDB version, or the buffer sizes are too large for the available memory. Check journalctl -u mariadb. Restore the backup and start MariaDB.
max_connections is always 151 and table_open_cache always 2000 The command couldn't query MariaDB (it's down, or the root password in GoAdmio's config doesn't work), so it used its defaults. Start MariaDB, check the root password, and run again.
Disk fills up after running with --log The general query log records every query. Run /go/my again without --log, which comments the logging out and moves the logs aside.
mv: ... No such file or directory The folder /var/log/go/mysql doesn't exist, so the old query log couldn't be moved. The log is then emptied anyway. Create /var/log/go/mysql.
Query logs are empty after a run Both log files are emptied on every run. With --log they are not backed up first. Copy them before running the command again.
Invalid data is silently truncated instead of rejected The generated file sets sql_mode to empty, which turns strict mode off. Edit the setting by hand, and re-apply it after each run.
Changes don't apply with system system mode doesn't restart MariaDB. Restart GoAdmio's separate instance yourself.
Swap behaves differently after a run The command sets vm.swappiness to 0 for the running system. Reapply your value after the run, or reboot. See Other changes.

Details

Requirements

How it works

flowchart TD
    A["my [system] [--log]"] --> B["Write log line"]
    B --> C{"RAM below 512 MB?"}
    C -- "Yes" --> X1["Message, exit 1"]
    C -- "No" --> D{"Lock held in the last hour?"}
    D -- "Yes" --> X2["Message, exit 1"]
    D -- "No" --> E["Read RAM, cores, disk type, plan, table count, connections, MyISAM index size"]
    E --> F["Write the whole config file"]
    F --> G["Write /etc/galera.cnf if missing"]
    G --> H["Reset the two query log files"]
    H --> I{"system mode?"}
    I -- "No" --> J["Restart MariaDB"]
    I -- "Yes" --> K["Done"]

Lock

The command checks /usr/local/goadmio/lock/backup.lock and exits if the file is younger than one hour. It only checks the lock. Unlike copytable, it doesn't create it.

Tiers by RAM

The tier is chosen from the server's total memory in KB. The first row that fits wins. Where a cell shows three values, they are for the Starter plan, the Standard plan, and any other plan (including when the plan can't be read).

Total RAM at least innodb_buffer_pool_size innodb_log_file_size innodb_log_buffer_size tmp_table_size, max_heap_table_size, bulk_insert_buffer_size
256,000,000 KB (256 GB) 32G / 64G / 128G 4G / 8G / 16G 256M 4G
128,000,000 KB 16G / 32G / 64G 2G / 4G / 8G 256M 4G
64,000,000 KB 8G / 16G / 32G 1G / 2G / 4G 128M 2G
32,000,000 KB 4G / 8G / 16G 2G 128M 2G
16,000,000 KB 1G / 2G / 4G 1G 128M 1G
8,000,000 KB 2G 512M 128M 512M
4,000,000 KB 1G not set 64M 256M
2,000,000 KB 128M 16M 32M 64M
1,000,000 KB 128M 16M 16M 32M
Below 1,000,000 KB 64M 8M 8M 16M

The plan only changes values on servers with 16 GB of RAM or more. A comment line such as # RAM: 8 GB (4 Cores / 8 Threads) at the top of each tier's block shows which tier was used.

Settings written on every run

The same settings are written in every tier. Several trade safety for speed:

Setting Value Effect
skip-innodb-doublewrite on Faster writes, but a crash can leave partially written pages unrecoverable.
innodb_flush_log_at_trx_commit 2 Up to about a second of committed transactions can be lost if the operating system or power fails.
disable-log-bin and sync_binlog = 0 on No binary log, so no point-in-time recovery, and this server can't act as a replication source.
sql_mode empty Strict mode is off. Invalid or oversized values are accepted and silently adjusted.
innodb_flush_method O_DIRECT Bypasses the operating system's file cache for data files.
innodb_adaptive_hash_index OFF Turns the adaptive hash index off.
innodb_read_io_threads, innodb_write_io_threads, innodb_buffer_pool_instances CPU core count Scales with the server.
innodb_purge_threads Half the core count Scales with the server.
innodb_open_files 65535 Fixed.
performance_schema ON Fixed.
query_cache_type, query_cache_size 0 The query cache is off.
read_buffer_size 8M Fixed.
default-time-zone The server's current UTC offset, such as +02:00 Doesn't follow daylight saving time changes. Run the command again after a change.
ft_min_word_len 3 Full-text search minimum word length.

On SSD servers it also sets innodb_flush_neighbors = 0, innodb_io_capacity = 2000, and innodb_io_capacity_max = 4000. The disk type is read from the first disk only, which may not be the one that holds the data. Many virtual servers report their disks as rotational, so the SSD settings may not be applied.

Values that follow usage

Value How it is chosen
max_connections Based on the highest number of connections used since MariaDB last started: 2500 if that was 1000 or more, 1000 for 500 or more, 500 for 250 or more, 250 for 50 or more, otherwise 151. A comment in the file records the value and when it was reached.
table_open_cache, table_definition_cache The total table count, rounded up to the next multiple of 2000, with a maximum of 8000. Without a count, 2000.
key_buffer_size Based on the total size of MyISAM indexes: 64M below 50 MB, 128M from 50 MB, 256M from 100 MB, 512M from 300 MB, 1G from 500 MB, 2G from 1 GB, then one more GB per extra GB up to 8G at 7 GB. With no MyISAM tables, it is 128M. It is not limited by the server's RAM.

Because the command restarts MariaDB, the connection statistic starts again from zero after each run, so a later run only sees the connections used since the previous restart.

Query logging

Mode What is written
Without --log The slow query and general query settings are written commented out. Any existing /var/log/mysql-slow.log and /var/log/mysql.log are first moved to /var/log/go/mysql/ with a date and time in the name.
With --log The slow query log is on (long_query_time = 1, and queries that don't use indexes are logged too), and the general query log is on, which records every query in /var/log/mysql.log. The existing logs are not moved.

[!WARNING] The general query log can grow fast and can contain passwords and personal data from queries. Use --log only for short troubleshooting sessions.

At the end of every run, both log files are created or emptied and set to mode 777 (readable and writable by every user), whatever the logging mode.

Galera template

The file ends with a [galera] section whose include line is commented out. The command also writes /etc/galera.cnf if it doesn't exist. It is a template with cluster settings, bind-address=0.0.0.0, and the server's public IP as the node address, and its provider path is the one used on the dnf systems. Nothing reads it unless you uncomment the include line in the configuration file.

System mode

With system, the command writes /var/go/etc/my.cnf instead, and also:

The statistics used for tuning are always read from the main MariaDB instance, not the separate one. MariaDB is not restarted.

Other changes

Exit status

Situation Exit status
Under 512 MB of RAM, or the lock is held 1
Normal run The status of systemctl restart mariadb.
system mode 0

Log file

Each call appends a line with all its arguments to /var/log/go/my.log:

[Nov 19 12:00:00]# my --log

See also

Command Description
installmariadb Install or upgrade MariaDB from the official repositories (default version 12.3, or a version you choose) and apply basic hardening
mysql Create, edit, or delete a MySQL/MariaDB database and user for a hosting account
mysqltop Identify the most accessed databases on a GoAdmio server
querytop Show the most frequently executed MySQL SQL queries
index Automatic indexing for MySQL performance optimization
speedtest Measure MariaDB write throughput
swap Create a swap file sized to RAM and set vm.swappiness
mem Check the top ten memory-consuming commands or services