MySQLTunerを使用してMariaDBをチューニングする

更新日：2024-10-04

MariaDB等のサーバ型のデータベースでは、初期設定で使用することはほぼなく、チューニングを行いサーバに合わせてパフォーマンスを発揮するように設定をします。

チューニングは設定を小さく変更しながら、サーバ上のMariaDBの挙動を確認し、少しずつ最適化するものですが、最初の取っ掛かりとして、

major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner等のツールに頼ることがあります。

MySQLTunerを使ってみることにします。

環境

マシン : Raspberry Pi 5 8G

OS : Raspberry Pi OS(64-bit)

$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 12 (bookworm)
Release:        12
Codename:       bookworm

MariaDB : 10.11.6

MySQLTuner : 2.6.0

下記コマンドでMySQLTunerをインストールします。

$ cd ~
$ mkdir mysqltuner
$ cd mysqltuner
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

MySQLTunerを使用してみます。

$ cd ~/mysqltuner
$ perl mysqltuner.pl

[--] Skipped version check for MySQLTuner script
[--] Using mysql to check login
Please enter your MySQL administrative login: 
Please enter your MySQL administrative password: 

権限を持つアカウント(MySQLTunerの動作確認であればルートアカウント)の情報を入力します。

実行すると下記のような内容が出力されます。

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in Aria tables: 32.0K (Tables: 1)
[!!] InnoDB is enabled, but isn't being used
[OK] Total fragmented tables: 0
 
[OK] Currently running supported MySQL version 10.11.6-MariaDB-0+deb12u1
 
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file  doesn't exist
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 44m 36s (70 q [0.026 qps], 40 conn, TX: 66K, RX: 9K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 7.9G
[--] Max MySQL memory    : 861.2M
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 419.9M (5.22% of installed RAM)
[OK] Maximum possible memory usage: 861.2M (10.69% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/70)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 7.50% (3/40)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 7 total)
[OK] Thread cache hit rate: 95% (2 created / 40 connections)
[OK] Table cache hit rate: 56% (44 hits / 78 requests)
[OK] table_definition_cache (400) is greater than number of tables (292)
[OK] Open file limit used: 0% (55/32K)
[OK] Table locks acquired immediately: 100% (76 immediate / 76 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 0
[--]  +-- Total MyISAM indexes : 0B
[--]  +-- KB Size :128.0M
[--]  +-- KB Used Size :23.3M
[--]  +-- KB used :18.2%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] No tables are Innodb
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 128.0M / 0B
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 64 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 0.00% (0 hits / 165 total)
[!!] InnoDB Write Log efficiency: 0% (0 hits / 0 total)
[OK] InnoDB log waits: 0% (0 waits / 0 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 20.0% (15 cached / 12 reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    Performance schema should be activated for better diagnostics
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
    skip-name-resolve=ON
    performance_schema=ON
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
    innodb_log_buffer_size (> 16M)

末尾の

Variables to adjust:
    skip-name-resolve=ON
    performance_schema=ON
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
    innodb_log_buffer_size (> 16M)

がチューニング案になりますので、

$ sudo micro /etc/mysql/mariadb.conf.d/50-server.cnf 

を開き、案に記載されている内容を

[mysqld]

の下の行に

[mysqld]
skip-name-resolve = ON
performance_schema = ON

のように加えて、ファイルを保存後に

$ sudo /etc/init.d/mariadb restart

でMariaDBを再起動します。

チューニングの細かい内容はここでは触れません。