【MariaDB・MySQL】ログ出力の設定とパフォーマンス・チューニングまとめ(Windows版)

log-output

SHARE

こんにちはフロントエンドエンジニアのまさにょんです！

今回は、MariaDBやMySQLにおいて、ログ出力の設定とパフォーマンス・チューニングについてまとめて解説します。

my.ini or my.cnf ファイルが設定ファイル

MariaDB・MySQL に関する設定を行うファイルをオプションファイルと呼び、

Linux 系の OS では my.cnf 、 Windows では my.ini というファイルが使用されます。

ログ出力の設定に関する事項まとめ

今回取り上げるログ出力の設定項目は、次の4つです。

ログ名説明
errorログサーバーから出力されるエラーメッセージを記録するログになります。
slow queryログ処理に時間のかかったクエリを記録するログになります。
queryログ実行されたすべてのSQLを記録するログになります。
バイナリログデータの登録/更新のSQL文だけをバイナリ形式で記録するログになります。
（データ復旧やレプリケーション設定時に使用されます。）
【 引用元: MariaDB(MySQL) での各種ログ出力設定 】

それでは、1つずつ設定内容を見ていきます。

errorログ

サーバの起動や停止の情報に加え、発生したエラーや警告が出力されるログファイルとなります。

基本的にはデフォルトで常に有効となってますので、設定が入っている事を確認します。

# [ errorログ設定 ]
log-error = error.log

上記のような設定の場合は「error.log 」と言うファイル名で errorログのファイルが出力されます。

log-warningsを設定することで、警告レベルのログも出力することができます。

slow queryログ

設定した処理時間を超えたクエリを記録するログになります。

設定設定内容
slow_query_log出力設定。0で無効、1で有効
slow_query_log_fileログの出力先ファイル名
long_query_time何秒以上のクエリをログに出力するか
（※１秒以下も設定する事ができます。例）0.3）
log_queries_not_using_indexesインデックスが使用されなかったログも出力
【 引用元: MariaDB(MySQL) での各種ログ出力設定 】

設定が完了したら再起動を行い、設定を反映させます。

# [ slow queryログ設定 ]
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

queryログ

実行されたすべてのSQLを記録するログになりますので、アクセス数が多かったり、

発行されるSQLが多いサイトの場合には、ファイル容量が膨大になる事もあります。

そのため、注意が必要で、開発時のデバック用であったり、本番運用時に一時的に調査をする場合に、設定するぐらいでの利用の方がいいかもしれません。

もし、運用上出力設定を入れた状態で利用する場合には、ログローテーションの設定を入れる事をおすすめします。

ただし、Windowsには、Linuxのようなlogrotate機能は存在しないので、batファイルで定期実行するような仕組みを作る必要があります。

# [ queryログ設定 ] 
# Debug用 => 本番環境での運用で、ONにする場合は、ログローテーションのbatファイルを作る必要がある
# => Windowsには、Linuxのようなlogrotate機能は存在しないので、batファイルで定期実行する

general_log = 1
general_log_file = query.log

バイナリログ

データの登録/更新のクエリだけをバイナリ形式で記録し、データ復旧やレプリケーション設定時に使用されます。

データ復旧の際には、データのフルバックアップを１日単位で取得しているシステムに障害が発生し、

データの復旧を行う場合に、フルバックアップ取得から障害発生時までの差分データを復旧する場合に使用します。

レプリケーションの際には、マスターとスレーブが同期をとるための情報として使用されます。

他にもオプションはありますが、今回はシンプルに出力先と保持期間のみ設定します。

log-bin： ログの出力先ファイル名を指定します。

expire_logs_days： バイナリログの保持期間となります。

# [ バイナリログ設定 ]
log-bin=mysql-bin
expire_logs_days = 7
binlog_cache_size=32M

mysql-bin.XXX のようなファイル名でバイナリログのファイルが出力されます。

ログ出力の設定SampleCode全文

# [ バイナリログ設定 ]
log-bin=mysql-bin
expire_logs_days = 7
binlog_cache_size=32M

# [ errorログ設定 ]
log-error = error.log

# [ slow queryログ設定 ]
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 1
log_queries_not_using_indexes = 1


# [ queryログ設定 ] 
# Debug用 => 本番環境での運用で、ONにする場合は、ログローテーションのbatファイルを作る必要がある
# => Windowsには、Linuxのようなlogrotate機能は存在しないので、batファイルで定期実行する

general_log = 1
general_log_file = query.log

パフォーマンス・チューニングに関する事項まとめ

MariaDBのパフォーマンスチューニングとは？

MariaDB (MySQL) のパフォーマンスチューニングには「DBそのものの設定(Config)の最適化」と「Queryの最適化」の2系統があります。

DBそのものの設定(Config)の最適化

「DBそのものの設定を最適化する」アプローチでは、設定ファイルを操作 or CLI にて設定値を最適化していきます。

このパフォーマンスチューニングのアプローチは、後述するQueryの最適化が部分的なものであるのに対して、

全体最適化するアプローチだと言えます。

また設定がDBすべてに影響を及ぼすので「どの設定値をいじるのか？」を計画的に決めた方がいいです。

まずは、Server全体のメモリ容量を把握して、

その次にMariaDBに割り当てることのできるメモリ容量を書き出し、

そこから、どのオプションにどれだけ、割り当てるかなどを決めていけば間違いないはずです。

Query(SQL文)の最適化

今回は、取り扱いませんが、Queryを最適化すると言うパフォーマンスチューニングのアプローチもあります。

これは、SQLを最適化するアプローチであり、時間のかかっているSQLを分析していわゆる「Slow-Query」を改善する手法です。

ちなみに、先述したとおり、全体最適化する「DBそのものの設定を最適化する」アプローチに対して、

「Queryの最適化」部分最適化するアプローチだと言えます。

Slow-Queryを改善するためには、Slow-Query を測定して、Queryの最適化をする必要があります。

パフォーマンスチューニング後の my.iniファイルの設定値

パフォーマンスチューニング後の my.iniファイルの設定値は次のとおりです。

[mysqld]
datadir=C:/Program Files/MariaDB 10.6/data
port=3306

# スレッドの最大接続数
max_connections=150

# スレッドキャッシュ保持最大数
thread_cache_size=100

# 今日するパケットサイズデフォルト1MB
max_allowed_packet=16MB

# スレッド毎に作成される一時的なテーブルの最大サイズ。スレッドバッファ
tmp_table_size=16M

# テーブルスペース関連
innodb_buffer_pool_size=10G
 
# [ InnoDBログ設定 ]
innodb_log_file_size=3G
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=90
innodb_log_buffer_size=32M
innodb_log_files_in_group=2

#テーブルごとに書き込み
innodb_file_per_table=ON

key_buffer_size = 256M

# スレッドバッファ
read_buffer_size = 1M
sort_buffer_size = 2M
read_rnd_buffer_size = 4M

# クエリキャッシュ最大サイズ
query_cache_limit=32M

# クエリキャッシュで使用するメモリサイズ
query_cache_size=512M

# クエリキャッシュのタイプ
# (0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
query_cache_type=1

# 名前解決関連
skip-name-resolve

[client]
port=3306
plugin-dir=C:\Program Files\MariaDB 10.6/lib/plugin

ポイント解説-1: innnodb_buffer_pool_size の最適化

MariaDB (MySQL)のストレージ書き込みエンジンである「InnoDB」(イノデービー)は、ほぼすべてのデータを「 innodb_buffer_pool_size 」指定されたメモリ内に保存します。

なので「 innnodb_buffer_pool_size 」の値を最適化することが一番重要です！

「 innnodb_buffer_pool_size 」には、通常でも搭載メモリーの50~80%ほどを割り当てますが、

DB専用のServerなら、搭載メモリーの80%を割りあてる位でもOKです！

今回は、16Gの搭載メモリーの内、10Gをこの「 innodb_buffer_pool_size 」に割りあてています。

# テーブルスペース関連
innodb_buffer_pool_size=10G

ポイント解説-2: Thread の最大接続数とキャッシュサイズの拡張

MariaDBは、クライアントからの接続要求ごとに、同要求を処理するための接続スレッドを作成します。

なので、DBサーバへ接続するクライアント数が多い場合は、最大接続数を増やします。

今回のようなWebサイトでは、クライアント数が多いので最大接続数を増やした方がパフォーマンスが上がります。

また、最大接続数の拡張と合わせて、スレッドキャッシュ保持最大数も拡張しておくと、

パフォーマンスがさらに上がります。

なおスレッドが増えるごとにメモリ消費量も増えていくので、接続数があまりにも多い場合は、スレッドを増やすのではなく、

読み取り専用のレプリケーションサーバを用意して負荷分散することも検討する必要があります。

# スレッドの最大接続数
max_connections=150

# スレッドキャッシュ保持最大数
thread_cache_size=100

InnoDB(イノデービー)とは？

InnoDB(イノデービー)はデータベース管理システムであるMySQLとMariaDBのためのストレージエンジンです。

スストレージエンジン(Storag-Engine)とは、データを実際に読み書きする処理を担当する部分で「MyISAM」と「InnoDB」が特によく利用されます。

  1. SQL文を受け取って結果を返すデータベースエンジン部分
    • つまり、テーブルにデータを書き込んだり読みだしたりするプログラム
  2. 複数の種類があり、データの取得方法、保存方法、処理方法がストレージエンジンによって違います。
  3. MySQLの場合、テーブル毎にストレージエンジンを分けられる
# SHOW ENGINES で対応エンジンを確認する

MariaDB [concrete8]> SHOW ENGINES;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                                         | Transactions | XA   | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | Stores tables as CSV files                                                                      | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                           | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                       | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint                         | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                                  | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables                | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                              | NO           | NO   | NO         |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)

テーブルに設定されているストレージエンジン確認のコマンドは、次のとおりです。

SHOW TABLE STAUS WHERE NAME = [テーブル名] \G;

my.iniのSample全文掲載

[mysqld]
datadir=C:/Program Files/MariaDB 10.6/data
port=3306

# スレッドの最大接続数
max_connections=100

# スレッドキャッシュ保持最大数
thread_cache_size=100

# スレッド毎に作成される一時的なテーブルの最大サイズ。スレッドバッファ
tmp_table_size=16M

# [ バイナリログ設定 ]
log-bin=mysql-bin
expire_logs_days = 7
binlog_cache_size=32M

# [ errorログ設定 ]
log-error = error.log

# [ slow queryログ設定 ]
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 1
log_queries_not_using_indexes = 1


# [ queryログ設定 ] 
# Debug用 => 本番環境での運用で、ONにする場合は、ログローテーションのbatファイルを作る必要がある
# => Windowsには、Linuxのようなlogrotate機能は存在しないので、batファイルで定期実行する

general_log = 1
general_log_file = query.log


# テーブルスペース関連
innodb_buffer_pool_size=10G
skip-innodb_doublewrite

# [ InnoDBログ設定 ]
innodb_log_file_size=3G
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=90
innodb_log_buffer_size=32M
innodb_log_files_in_group=2

key_buffer_size = 256M

# スレッドバッファ
read_buffer_size = 1M
sort_buffer_size = 2M
read_rnd_buffer_size = 4M

# クエリキャッシュ最大サイズ
query_cache_limit=16M

# クエリキャッシュで使用するメモリサイズ
query_cache_size=512M

# クエリキャッシュのタイプ(0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
query_cache_type=1


# 文字コード
character-set-server=utf8

# 名前解決関連
skip-name-resolve

[client]
port=3306
plugin-dir=C:\Program Files\MariaDB 10.6/lib/plugin

DB・SQL関連書籍

参考・引用

  1. MariaDB(MySQL) での各種ログ出力設定
  2. MySQLのログについて確認する
  3. MySQL最低限のメモリ設定
  4. 「MySQLに割り当てられているメモリ量を確認して」と言われたときに見る記事

最近の投稿

【SQL】1回のINSERTで、複数行のレコードを一括挿入・一括追加する方法 JS_uuid JavaScriptでランダムなuuidを作成する方法(randomUUIDの使い方) py_csv_to_json PythonでCSVをJSONに変換する方法 py_csv_cut PythonでCSV・Dataframeを指定の行数まで切り取る方法 js_for_of JavaScript(TypeScirpt)のfor-of文でindexの値も取得する方法と、Type IterableIteratorエラーの解決方法 Python_VSCode VSCode上のPython import文の「インポート “XXXXX” をソースから解決できませんでした」エラーの解決方法

前の記事

感情に支配されないための考え方
【Mental-Hack】感情に支配されないための考え方 | …

次の記事

JavaScriptで日付や時間の取得・計算をする方法・Dat…
DateObject