MariaDB Serverのチューニング設定（2025年8月版）

投稿日：

MariaDB Serverのチューニング設定を見直したときの覚書。

環境： FreeBSD 14.3-RELEASE-p2, MariaDB Server 10.11.13


１． MySQLTunerを実行

git cloneして実行。
# cd /home/software/
# git clone https://github.com/major/MySQLTuner-perl.git
# cd MySQLTuner-perl/
# perl mysqltuner.pl

実行結果をGemini 2.5 Proに貼り付けて提案を聞く。


２． 設定変更する前にデータをバックアップ

全データをバックアップ。
# cd 
# mysqldump -u root -p --all-databases --single-transaction --routines --events --default-character-set=utf8mb4 | gzip > ./hoge01_20250818.sql.gz

設定ファイルをコピーしてGemini先生が提案した新設定を記述。
# cd /usr/local/etc/mysql/conf.d/
# cp server.cnf server.cnf.bak20250818
# less /usr/local/etc/mysql/conf.d/server.cnf

[server]
# Basic Settings
character-set-server          = utf8mb4
collation-server              = utf8mb4_general_ci
user                          = mysql
basedir                       = /usr/local
datadir                       = /var/db/mysql  # ZFS上のパスを指定
log_error                     = /var/log/mysql/mysqld.err

# Networking
# Webサーバーと同一筐体であれば skip-networking はセキュリティ・パフォーマンス面で良い設定です
skip-networking               = 1
skip-name-resolve

# Connections
max_connections               = 100
thread_handling               = pool-of-threads
net_retry_count               = 16384

# Performance Tuning (General)
performance_schema            = ON
table_open_cache              = 4000
table_definition_cache        = 2000
sort_buffer_size              = 2M

# 一時テーブルがディスクに溢れないように増強
tmp_table_size                = 256M
max_heap_table_size           = 256M

# --- Query Cache は MariaDB 10.6 で廃止されたため完全に削除 ---

# InnoDB (最重要)
# [最重要改善] 搭載メモリ12GBに合わせて大幅に増強 (物理メモリの50-60%)
innodb_buffer_pool_size       = 1G

# [改善] 書き込みパフォーマンス向上のため増強 (ログファイルの再作成が必要！)
innodb_log_file_size          = 512M

# [改善] 書き込みパフォーマンス向上のため推奨(トレードオフあり)
innodb_flush_log_at_trx_commit = 2

# [改善] 過剰な値を適正化。メモリの無駄遣いを防ぐ
innodb_log_buffer_size        = 16M

# [ZFS環境向けの重要設定]
# ZFSはアトミック書き込みを保証するため、InnoDBの二重書き込みは不要
innodb_doublewrite            = 0

# FreeBSD/ZFS環境では O_DSYNC が推奨されることが多い
innodb_flush_method           = O_DSYNC


# Slow Query Log (設定維持)
slow_query_log                = ON
long_query_time               = 1
log_output                    = TABLE

構文エラーがないか確認するコマンド。
# /usr/local/libexec/mariadbd --help --verbose > /dev/null

MariaDBサーバー停止
# service mysql-server stop

innodb_log_file_sizeを変更したので、古いログファイルをリネイム。
# cd /var/db/mysql/
# mv ib_logfile0 ib_logfile0.bak20250818
Gemini先生に言われたけど不要だった。

MariaDBサーバー起動して確認
# service mysql-server start
# service mysql-server status
# less /var/log/mysql/mysqld.err

2025-08-19  5:58:53 0 [Note] Starting MariaDB 10.11.13-MariaDB-log source revision 8fb09426b98583916ccfd4f8c49741adc115bac3 server_uid 6VnBdg/ooz5yG9DdyD0r+pSVCIM= as process 18694
2025-08-19  5:58:53 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2025-08-19  5:58:53 0 [Note] InnoDB: Number of transaction pools: 1
2025-08-19  5:58:53 0 [Note] InnoDB: Using AVX512 instructions
2025-08-19  5:58:53 0 [Note] InnoDB: innodb_buffer_pool_size_max=1024m, innodb_buffer_pool_size=1024m
2025-08-19  5:58:53 0 [Note] InnoDB: Completed initialization of buffer pool
2025-08-19  5:58:53 0 [Note] InnoDB: End of log at LSN=31706198269
2025-08-19  5:58:53 0 [Note] InnoDB: Resizing redo log from 96.000MiB to 512.000MiB; LSN=31706198269
2025-08-19  5:58:54 0 [Note] InnoDB: 128 rollback segments are active.
2025-08-19  5:58:54 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2025-08-19  5:58:54 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2025-08-19  5:58:54 0 [Note] InnoDB: log sequence number 31706198269; transaction id 43751344
2025-08-19  5:58:54 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/mysql/ib_buffer_pool
2025-08-19  5:58:54 0 [Note] Plugin 'FEEDBACK' is disabled.
2025-08-19  5:58:54 0 [Warning] 'proxies_priv' entry '@% mysql@hoge.co.jp' ignored in --skip-name-resolve mode.
2025-08-19  5:58:54 0 [Note] /usr/local/libexec/mariadbd: ready for connections.
Version: '10.11.13-MariaDB-log'  socket: '/var/run/mysql/mysql.sock'  port: 0  FreeBSD Ports
2025-08-19  5:58:54 0 [Note] InnoDB: Buffer pool(s) load completed at 250819  5:58:54

Warningは無視しても大丈夫らしい。

メモリ容量も確認。
# top


３． パフォーマンスチェックView

Gemini先生にチェックするクエリを作ってもらった。
phpMyAdminでmysqlデータベースを選択した状態で下記クエリを実行する。

CREATE OR REPLACE VIEW `performance_dashboard` AS
SELECT
    gs.VARIABLE_NAME AS `ステータス変数`,
    FORMAT(gs.VARIABLE_VALUE, 0) AS `現在の値`,
    
    -- 分析/ヒット率など
    CASE
        WHEN gs.VARIABLE_NAME = 'Innodb_buffer_pool_reads' THEN
            CONCAT(
                ROUND(
                    (1 - gs.VARIABLE_VALUE / NULLIF((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)) * 100, 2
                ),
                ' % ヒット率'
            )
        WHEN gs.VARIABLE_NAME = 'Created_tmp_disk_tables' AND (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables') > 0 THEN
            CONCAT(
                ROUND(
                    (gs.VARIABLE_VALUE / NULLIF((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables'), 0)) * 100, 2
                ),
                ' % ディスク比率'
            )
        WHEN gs.VARIABLE_NAME = 'Table_open_cache_misses' AND (gs.VARIABLE_VALUE + (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Table_open_cache_hits')) > 0 THEN
            CONCAT(
                ROUND(
                    ((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Table_open_cache_hits') / 
                    NULLIF((gs.VARIABLE_VALUE + (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Table_open_cache_hits')), 0)) * 100, 2
                ),
                ' % ヒット率'
            )
        ELSE '--'
    END AS `分析/ヒット率など`,
    
    -- 健全な状態の目安 (日本語化 + 目標を先頭に)
    CASE gs.VARIABLE_NAME
        WHEN 'Innodb_buffer_pool_read_requests' THEN '総読み込み回数(メモリ+ディスク)。ヒット率の計算元。'
        WHEN 'Innodb_buffer_pool_reads'         THEN '[目標: ヒット率 > 99.5%] ディスクからの読み込み回数。低いほど良い。'
        WHEN 'Created_tmp_tables'               THEN '作成された一時テーブル総数。ディスク書き出し率の計算元。'
        WHEN 'Created_tmp_disk_tables'          THEN '[目標: ディスク比率 < 5%] ディスク上に作成された一時テーブル。低いほど良い。'
        WHEN 'Slow_queries'                     THEN '[目標: 急増しない] 遅いクエリの数。アプリ/プラグインの調査が必要な場合も。'
        WHEN 'Select_full_join'                 THEN '[目標: 常に 0] インデックス未使用のテーブル結合。0より大きい場合、インデックス不足を示唆。'
        WHEN 'Table_open_cache_hits'            THEN 'キャッシュからテーブル定義を取得した回数。ヒット率の計算元。'
        WHEN 'Table_open_cache_misses'          THEN '[目標: ヒット率 > 95%] キャッシュになかった回数。低いほど良い。'
        WHEN 'Max_used_connections'             THEN '[目標: max_connectionsの80%未満] ピーク時の最大同時接続数。'
        ELSE '--'
    END AS `健全な状態の目安`

FROM
    information_schema.GLOBAL_STATUS gs
WHERE
    gs.VARIABLE_NAME IN (
        'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
        'Created_tmp_tables', 'Created_tmp_disk_tables',
        'Table_open_cache_hits', 'Table_open_cache_misses',
        'Slow_queries', 'Select_full_join', 'Max_used_connections'
    )
ORDER BY
    FIELD(gs.VARIABLE_NAME,
        'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
        'Created_tmp_tables', 'Created_tmp_disk_tables',
        'Table_open_cache_hits', 'Table_open_cache_misses',
        'Slow_queries', 'Select_full_join', 'Max_used_connections'
    );

「performance_dashboard」というビューが追加されているので、実行して、再度Gemini先生に聞けば、改善点を教えてくれる。

24時間以上通常のアクセスがある状態で稼働させた後に再度確認するのがベストらしい。


▼ 関連記事