【MariaDB】設定ファイルを最適化しよう！

MariaDBはMySQLから分岐したオープンソースのリレーショナルデータベース管理システムで、広く利用されています。

本ブログでは、MariaDBのWindows環境における設定ファイル（my.ini）について、設定例を元に各パラメータの内容や利用シーン、推奨設定を紹介します。

MariaDBの設定ファイルを最適化する理由

MariaDBの設定ファイルは、データベースの動作を細かく制御するために使用されます。

デフォルトでは、通常C:\Program Files\MariaDB XX\data\my.iniに配置されており、設定ファイルの形式はセクション（[セクション名]）とキーと値のペア（キー=値）で構成されます。

MariaDBの設定ファイルを最適化する理由は以下の通りです。

  • パフォーマンス向上
  • スケーラビリティ強化
  • エラー管理の向上
  • リソースの効率化
  • 安定性の向上

設定ファイルの例

Windows Server上でMariaDBを効率的に運用するための[mysqld]セクションと[client]セクションの設定例を紹介します。

下記はMariaDB11.4を使用する条件で、メモリ16GB・接続人数200人・レコード件数100万件、エラー管理・性能を考慮した設定例です。

下記の設定例を元にして内容を紹介していきます。

  • [mysqld]セクションの例
[mysqld]
# 基本設定
port = 3306
bind_address = 0.0.0.0
datadir = "C:\Program Files\MariaDB 11.4\data"
socket = "C:\Program Files\MariaDB 11.4\data\mysql.sock"
log_error = "C:\Program Files\MariaDB 11.4\log\mariadb_error.log"

# 接続とスレッド管理
max_connections = 200
thread_cache_size = 64
thread_stack = 256K

# パフォーマンス設定
innodb_buffer_pool_size = 8G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 2000
innodb_lock_wait_timeout = 50

# ストレージ管理
tmp_table_size = 128M
max_heap_table_size = 128M
table_open_cache = 4000
query_cache_size = 256M
max_allowed_packet = 64M

# ログ設定
slow_query_log = 1
slow_query_log_file = "C:\Program Files\MariaDB 11.4\log\slow_query.log"
long_query_time = 1
general_log = 0
  • [client]セクションの設定例
[client]
# 接続設定
port = 3306
socket = "C:\Program Files\MariaDB 11.4\data\mysql.sock"

# クエリと接続の最大パケットサイズ
max_allowed_packet = 64M

# クエリの簡易化
default-character-set = utf8mb4

#プラグインディレクトリ
plugin-dir="C:\Program Files\MariaDB 11.4/lib/plugin"

[mysqld]セクションの考察

  • [mysql]セクションとは
    • MariaDBサーバープロセスの動作を制御するための設定を記述するセクション。

基本設定

  1. port = 3306（デフォルト）
    MariaDBがリッスンするポート番号。一般的にデフォルトの3306を使用。ただし、セキュリティ上の理由で変更も可能。


  2. bind_address = 0.0.0.0（デフォルト：127.0.0.1）
    接続を受け付けるIPアドレスを指定する。0.0.0.0はすべてのIPから接続を許可するがァイアウォールでIPを制限して安全性を確保します。

    bind_address = 0.0.0.0は、複数クライアントや外部接続が必要な場合に便利であり、適切なセキュリティ対策を講じれば安全に運用できます。ただし、セキュリティ要件が厳しい場合は、127.0.0.1または特定のIPアドレスに制限する方が安全です。


  3. datadir = “C:\Program Files\MariaDB 11.4\data”（デフォルト）
    データベースのデータを保存するディレクトリのパスを指定します。データ容量の関係やデータをシステムを切り離して管理したい場合等は、他のディレクトリを指定します。


  4. socket = “C:/ProgramData/MariaDB/mysql.sock”
    名前付きパイプとして利用されており、ローカルマシン内でのデータ転送が軽減されます。
    例えば、クライアントとMariaDBサーバーが同一ホスト上に存在する場合、この方法が最適です。

接続とスレッド管理

  1. max_connections = 200
    条件通り同時接続の上限を200としています。但し、200以上に余裕を持たせる場合もあります。


  2. thread_cache_size = 64
    クライアントがMariaDBに接続するたびに、サーバーはスレッドを生成します。接続が終了したスレッドをキャッシュに保存し再利用することでスレッドの生成コストを削減します。
    一般的な推奨値は、最大接続数（max_connections）の25%から50%程度です。


  3. thread_stack = 256K
    各スレッドに割り当てるスタックメモリのサイズです。スタックは、関数呼び出しやローカル変数の保存に使用されるメモリ領域です。

    thread_stack の値は、MariaDBの運用環境やクエリの複雑さによって異なりまが標準的な設定は256K または 512Kです。

パフォーマンス設定

  1. innodb_buffer_pool_size = 8G
    innoDBストレージエンジンが使用するバッファプールのサイズです。テーブルのデータやインデックスがバッファプールにキャッシュされ、ディスクI/Oを減らしてデータベースのパフォーマンスを向上しています。

    innodb_buffer_pool_size の値は、サーバーの利用状況やシステムのメモリ容量に基づいて設定しますが、推奨値はサーバー全体のメモリ容量の 50% ～ 75% をです。


  2. innodb_log_file_size = 1G
    InnoDB ストレージエンジンで使用されるredoログファイルの1つあたりのサイズを指定します。Redoログはトランザクション中のデータ変更を記録し、クラッシュ時にRedoログを使ってデータの一貫性を確保してトランザクションをリカバリします。

    一般的には、バッファプールサイズ（innodb_buffer_pool_size）の25%～50%の範囲が推奨されます。


  3. innodb_flush_log_at_trx_commit = 1
    トランザクションのコミット時にRedoログ（トランザクションログ）をディスクに書き込むタイミングを制御する設定です。
    推奨値は１（デフォルト）で、各トランザクションのコミット時に必ずログをディスクにフラッシュします。


  4. innodb_io_capacity = 2000
    InnoDB ストレージエンジンのバックグラウンドタスクが1秒間に実行するI/O操作の上限を設定します。値が低いと処理の遅延やスループットの低下を引き起こす場合があります。

    適切な値は、使用しているストレージデバイスの性能（I/Oスループット）に大きく依存しますが参考値は以下の通りです。
    • HDD（ハードディスクドライブ）
      • 100～200
    • SSD（ソリッドステートドライブ）
      • 2000～10000
    • NVMe SSD
      • 推奨値: 10000～50000


  5. innodb_lock_wait_timeout = 50（デフォルト）
    InnoDB ストレージエンジンで、トランザクションがロックを獲得するために待機する最大秒数を指定します。デッドロックが発生する状況を未然に防ぎ、システム全体のスループットを確保します。

    • 設定の考え方
      • 低い値（例: 10～30秒）
        • 利点：ロックが長時間保持される状況を早く検出でき、スループットを改善します。
        • 欠点:：一時的なロック競合が頻発する環境では、タイムアウトが多発してアプリケーション側でのリトライ処理が増える可能性があります。
      • 高い値（例: 60～300秒以上）
        • 利点：複雑なクエリやバッチ処理がロック競合を解消するまで待機できます。
        • 欠点：ロック保持が長引くと、他のトランザクションに悪影響を及ぼす可能性があります。

ストレージ管理

  1. tmp_table_size = 128M
    クエリ実行中に一時的なデータを格納するために作成されるメモリ内テーブルの最大値を指定します。この値を超えた場合一時テーブルはディスク上に作成されため、パフォーマンスに影響を与える可能性があります。
    • 設定値の考え方
      • 小規模なシステム（8GB以下のメモリ）
        • 推奨値: 16MB ～ 32MB
      • 中規模なシステム（16GB以上のメモリ）
        • 推奨値: 64MB ～ 256MB
      • 大規模なシステム（32GB以上のメモリ）
        • 推奨値: 256MB ～ 1GB


  2. max_heap_table_size = 128M
    メモリ内で作成されるHEAP（MEMORY）ストレージエンジンのテーブルの最大サイズを定義するパラメータです。この値を超えると新しいデータを追加できなくなりエラーが発生します。
    • 設定値の考え方
      • 小規模システム（メモリ 8GB以下）
        • 32MB ～ 64MB
      • 中規模システム（メモリ 16GB以上）
        • 128MB ～ 256MB
      • 大規模システム（メモリ 32GB以上）
        • 256MB ～ 512MB


  3. table_open_cache = 4000（デフォルト）
    サーバー全体で同時にキャッシュできる「開いているテーブル」の最大数を指定します。このキャッシュは、テーブルを効率的に再利用することでディスクアクセスを減らし、パフォーマンスを向上させます。
    • 設定の考え方
      • 小規模システム（10～50ユーザー）
        • 推奨値: 1000 ～ 4000
      • 中規模システム（50～200ユーザー）
        • 推奨値: 4000 ～ 8000
      • 大規模システム（200ユーザー以上）
        • 8000 ～ 20000


  4. query_cache_size = 256M（デフォルト：0）
    クエリ結果キャッシュするサイズを指定します。この値が設定されていると、クエリ結果が一時的にキャッシュされ、同じクエリが繰り返し実行された場合に、再計算する必要がなくなります。
    • 設定値の考え方
      • 小規模システム
        • 32M ～ 256M
      • 中規模システム
        • 256M ～ 512M
      • 大規模システム
        • 1G ～ 2G 以上


  5. max_allowed_packet = 64M（デフォルト：16M）
    データの最大送信サイズを制御するためのパラメータです。この値が設定されていると、クライアントからサーバーに送信できる最大データサイズを制限します。
    • 設定値の考え方
      • 小規模システム:
        • 16MB ～ 64MB
      • 中規模システム:
        • 64MB ～ 256MB
      • 大規模システム
        • 256MB ～ 1024MB 以上

ログ設定

  1. slow_query_log = 1（デフォルト：0）
    実行時間が遅いクエリをログに記録するか否かを設定です。この設定を有効にすると特定のクエリの実行時間が閾値を超えた場合に、ログファイルに記録されます。


  2. slow_query_log_file = “C:\Program Files\MariaDB 11.4\log\slow_query.log”
    実行時間が遅いクエリが記録されるログファイルの場所を指定します。


  3. long_query_time = 1
    ログに記録する遅いクエリ実行時間のしきい値を秒数で指定します。監視、トラブルシューティング、パフォーマンス最適化のために使用されます。
    • 設定値の考え方
      • 小規模システム:
        • OFF ～ 1 秒
      • 中規模システム:
        • 1 ～ 10 秒
      • 大規模システム
        • 10 秒以上


  4. general_log = 0（デフォルト）
    すべてのクエリをログに記録するか否かを設定します。この設定を有効にすると、サーバー上で実行されるすべてのクエリ（SELECT、INSERT、UPDATE、DELETEなど）がログファイルに記録されます。
    エラー発生時や特定のクエリの詳細情報を収集する際に使用されます。

[client]セクションの紹介

接続設定

  1. port = 3306
    複数のMariaDBサーバーがある場合異なるポートを使用するケースがあるため、接続先のportを明確にする為記述しています。


  2. socket = “C:\Program Files\MariaDB 11.4\data\mysql.sock”
    Windows サーバー など、リモートアクセスが少ない環境では、ネットワークのオーバーヘッドを減らすためにソケット接続が使用されます。socketを指定する事により、クライアントがローカルソケットを通じてMariaDBサーバーに接続するように指示します。

クエリと接続の最大パケットサイズ

  1. max_allowed_packet = 64M（デフォルト推奨値）
    1つのネットワークトランザクションやクエリ処理中にサーバーが受信できる最大のデータサイズ（バイト数）を指定します。
    大量のデータを一度に送信する際に、クライアントが制限内に収まることで通信の安定性が向上し、エラーやデータ損失、パフォーマンス低下を防ぐ効果があります。

クエリの簡易化

  • default-character-set = utf8mb4
    クライアントがMariaDBサーバーに接続した際に使用する文字セットを指定します。utf8mb4 は、多言語データ（例えば、日本語、韓国語、絵文字など）の取り扱いに対応しており、UTF-8 よりも多くの文字をサポートしています。

プラグインディレクトリ

  • plugin-dir=”C:\Program Files\MariaDB 11.4/lib/plugin”
    MariaDBサーバーがプラグインファイルを探すディレクトリを指定します。設定されたディレクトリ内にプラグインのバイナリファイル（通常は .dll や .so ファイル）が格納されており、サーバーはそのファイルをロードします。

その他の主な[セクション]

[mariadbd] セクション

  • 用途
    • MariaDB 10.5以降で追加されたセクションで、MariaDB専用のサーバープロセス（mariadbd）を設定する。

  • 特徴
    • MariaDB専用のセクションであり、MySQLでは無視される。

[mariadb] セクション

  • 用途
    • MariaDB全体に適用される汎用設定で、特定のコンポーネント（クライアントやサーバー）に依存しない、MariaDB専用の設定を記述する。

  • 特徴
    • MariaDBの拡張機能やプラグインに関連した設定を行うのに適している。

[server] セクション

  • 用途
    • MariaDBやMySQLのサーバー共通設定。特定のプロセスセクション（[mysqld]や[mariadbd]）に依存せず、広く適用される。

  • 特徴
    • MariaDBとMySQLの両方で使用可能。

MariaDB設定の最適化のポイント

  1. 利用状況に応じた調整
    使用するアプリケーションの特性に合わせてパラメータを設定します。

  2. リソースの有効活用
    メモリやディスクI/Oを効率的に活用するため、innodb_buffer_pool_sizeやtmp_table_sizeを適切に設定。

  3. セキュリティ
    本番環境ではbind-addressを適切に設定し、不要なリモートアクセスを防ぎます。

タイトルとURLをコピーしました