How to use MySQLTuner for MySQL or MariaDB

Running a tuning assessment tool highlights common configuration problems that waste RAM, increase disk I/O, or create avoidable contention under load.

MySQLTuner connects to a running MySQL or MariaDB server, reads server variables plus global status counters, and compares observed behavior to practical heuristics. The report typically calls out hotspots around caches, connection handling, temporary tables, and storage engine efficiency.

Recommendations are workload-sensitive and strongly influenced by uptime, traffic mix, and server role. Run the tool during representative load, treat suggestions as hypotheses to validate, and apply changes incrementally with before/after measurements to avoid “fixing” one bottleneck by creating another.

1. Run mysqltuner to capture a baseline report.

   $ mysqltuner --host sg-mysql --user root --pass 'STRONG_PASSWORD_HERE' --forcemem 1024 --forceswap 1024
    >>  MySQLTuner 2.5.2
   	 * Jean-Marie Renouard <jmrenouard@gmail.com>
   	 * Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
    >>  Run with '--help' for additional options and output filtering
   
   [--] Skipped version check for MySQLTuner script
   [--] Performing tests on sg-mysql:3306
   [OK] Logged in using credentials passed on the command line
   [--] Assuming 1024 MB of physical memory
   [--] Assuming 1024 MB of swap space
   ##### snipped #####

   Package and binary names vary; the command is commonly mysqltuner or mysqltuner.pl depending on packaging.

2. Scan key sections such as throughput, slow query, cache, InnoDB to identify the biggest constraints.

   -------- Performance Metrics -----------------------------------------------------------------------
   [--] Up for: 4m 13s (43 q [0.170 qps], 22 conn, TX: 63K, RX: 4K)
   [--] Reads / Writes: 100% / 0%
   [--] Binary logging is enabled (GTID MODE: OFF)
   [OK] Slow queries: 0% (0/43)
   [!!] Aborted connections: 4.55% (1/22)
   [OK] Temporary tables created on disk: 0% (0 on disk / 7 total)
   [OK] Thread cache hit rate: 95% (1 created / 22 connections)
   ##### snipped #####

3. Focus first on lines marked [!!] to prioritize actionable warnings.

   -------- Recommendations ---------------------------------------------------------------------------
   General recommendations:
       MySQL was started within the last 24 hours: recommendations may be inaccurate
       Reduce or eliminate unclosed connections and network issues
       Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
   Variables to adjust:
       innodb_redo_log_capacity should be (=64M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.

   MySQLTuner output is heuristic-driven; a warning indicates a likely pressure point, not an automatic “set X to Y” instruction.

4. Treat memory-related recommendations as a budget exercise before applying them.

   Increasing innodb_buffer_pool_size, tmp_table_size, or max_connections without headroom can push the host into swapping, which is a performance cliff.

5. Apply vetted changes under the [mysqld] section of the server configuration file.

   [mysqld]
   innodb_buffer_pool_size = 8G
   thread_cache_size = 64
   table_open_cache = 4096

   Configuration file locations vary, commonly /etc/mysql/mysql.conf.d/mysqld.cnf for MySQL on Debian-style layouts and /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB.

6. Restart the database service when a persisted change requires it.

   $ sudo systemctl restart mysql

   On systems where MariaDB runs as mariadb.service, use sudo systemctl restart mariadb.

   Restarting the database service drops existing connections and interrupts in-flight work.

7. Verify the new settings from SQL to confirm the server loaded the expected values.

   $ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
   +-------------------------+-----------+
   | Variable_name           | Value     |
   +-------------------------+-----------+
   | innodb_buffer_pool_size | 268435456 |
   +-------------------------+-----------+

8. Re-run mysqltuner after changes to confirm warning reduction plus correct sizing.

   $ mysqltuner --host sg-mysql --user root --pass 'STRONG_PASSWORD_HERE' --forcemem 1024 --forceswap 1024
   ##### snipped #####
   -------- Recommendations ---------------------------------------------------------------------------
   General recommendations:
       Reduce or eliminate unclosed connections and network issues
   ##### snipped #####

   Compare before/after counters such as Innodb_buffer_pool_reads and Threads_created to validate improvement.