Linux

MariaDB/MySQL性能チューニング（パラメータ設定見直し）

  /
＊記事内に商品プロモーションを含む場合があります

MariaDBのチューニングの必要があり、MariaDB(MySQL)の性能チューニングのポイントをまとめました。

MariaDBとMySQLは、互換性が高いので、今回の内容は両方のDBに適用可能です。

日本ではPostgreSQLのシェアが高いですが、世界的にはMySQL/MariaDBのほうがシェアが高いです。

ちなみにブログのサイトで使われているWordPressのデータベースは、MySQL(MariaDB)です。

目次
  1. MariaDBのチューニングとは
  2. MariaDBのデータベースチューニングとは
  3. MariaDBのパラメータ設定
    1. 基本(メモリ関連)
    2. その他の設定
  4. MySQLTuner:チューニングの足掛かりに
  5. 最後に

MariaDBのチューニングとは

データベースのパフォーマンスに関するチューニングは、大きく分けて以下の３通りです。

(a)データベースのチューニング：パラメータ設定を変更

(b)SQLのチューニング：SQLの実行計画(実行プラン)などを分析しSQLを変更

(c)テーブルのチューニング：索引やカラムなどの見直し

今回は、どのSQLに対しても効果が高い、「(a)データベースのチューニング」に関して説明します。

デフォルトの設定値の状態で使わずに、キャッシュの調整ぐらいは実施しましょう。

MariaDBのデータベースチューニングとは

データベースの基本的なチューニングのポイントは、ディスクのI/Oを出来る限り減らすことです。

高速にアクセスできるメモリ領域を増やし、ディスクへのアクセス回数を減らすことで、パフォーマンスの向上を目指します。

メモリキャッシュを効率的に使う設定にすることが、最初に行う対策です。

例えば、以下の図のように物理メモリが4GBのマシンで、OSやその他のソフトで１GBのメモリを使っている場合、残りの3GBを効率的に割り当てていくかがポイントになります。

割り当ての配分を間違って、OSスワップしないように気を付けましょう。

スワップするとディスクI/Oが発生するので、遅くなります。

(ディスクのI/Oを減らすためにチューニングしているのに、I/O増やしたら意味ないです)

MariaDBのパラメータ設定

設定ファイルは以下。

・MariaDB バージョン5系： /etc/my.cnf

・MariaDB バージョン10系： /etc/mysql/mariadb.conf.d/50-server.cnf

MariaDBとMySQLのバージョン

MariaDB MySQL
5.5 5.5
10.0 – 10.1 5.6
10.2 – 10.3 5.7
10.4 – 10.8 8.0

基本(メモリ関連)

InnoDB関連パラメータ設定マニュアル(公式サイト)

物理メモリ3GBを以下のように割り当て

・データキャッシュ(innodb_buffer_pool_size)に2GB

・クエリキャッシュ(query_cache_size)に256MB

・その他の細々したもの

■設定ファイル(/etc/my.cnf , /etc/mysql/mariadb.conf.d/50-server.cnf)

[mysqld]
# ——————————————–
# InnoDBのデータとインデックスをキャッシュするバッファのサイズ(推奨は物理メモリの7割)
innodb_buffer_pool_size=2G

# InnoDBの更新ログを記録するディスク上のファイルサイズ(innodb_buffer_pool_sizeの25%程度)
# innodb_log_fileがいっぱいになると、メモリ上のinnodb_buffer_poolの中の更新された部分のデータをデータファイルに書き出す仕組み。
# innodb_buffer_pool_sizeを大きくしたら、innodb_log_file_sizeもあわせて調整しないとパフォーマンスが向上しない。
innodb_log_file_size=512M

# コミットされていないトランザクションのためのバッファサイズ(デフォルト 8MB)
# 多数の行を更新、挿入、または削除するトランザクションの場合、ログバッファーを大きくすると、ディスクI/O を節約可能。
innodb_log_buffer_size=16M

# InnoDB のデータ領域をテーブル単位に保存
innodb_file_per_table=ON

# ——————————————–
# キャッシュするクエリ結果の最大サイズ（１クエリの最大サイズ）
query_cache_limit=16M

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

# クエリキャッシュのタイプ(0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
# 1=クエリに SELECT SQL_NO_CACHE と書いたクエリを除いてキャッシュ
query_cache_type=1

その他の設定

■遅いクエリ(スロークエリ)をログに残したいときに設定

# ——————————————–
# スロークエリの出力設定
slow_query_log=ON

# スロークエリと判定する秒数()
long_query_time=30

# スロークエリログの場所
log-slow-queries=/var/log/mariadb/mysql-slow.log

■クライアントの接続数に応じて設定する項目

基本のメモリ設定で性能的に問題なければ、変更する必要性は低い。

# ——————————————–
#クライアントの最大接続数。接続スレッドの最大数でもある。スレッドの数だけメモリを消費するため、メモリを潤沢に使用できない場合は値を減らす。
# (デフォルト151)
max_connections=50

# スレッドキャッシュ保持最大数。
# 接続スレッドプールのキャッシュサイズ。本パラメータの範囲内で、接続スレッドを使い回すことができる。
# デフォルト値は 0 (キャッシュなし) で、スレッドは新しい接続ごとに作成され、接続の終了時に破棄。
thread_cache_size=50

# サーバーが大きなクエリーを処理する必要がある場合 (たとえば、大きい BLOB カラムを操作している場合) は、この値を増やす。
# デフォルト 1MB。
max_allowed_packet=4MB

MySQLTuner:チューニングの足掛かりに

MySQLTunerはMariaDB/MySQLのパフォーマンスと安定性を向上するためのアドバイスをしてくれるツールです。

現状の変数やDBの状態を読み取って、基本的なパフォーマンスの設定を簡単に提案してくれます。

公式のGitHubはこちらになります。

MySQLTuner.zipをダウンロードして、ZIPファイルを解凍してPerlプログラムを実行するとヒントを教えてくれます。

$

cd /usr/local/src/
wget -O MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip
unzip MySQLTuner.zip
cd MySQLTuner-perl-master
perl ./mysqltuner.pl

出力結果です。
DBの内容を見て、アドバイスしてくれるようです。

[–] Data in InnoDB tables: 10.3G (Tables: 144)

DBサイズが、10.3GBで「 innodb_buffer_pool_size (>= 10.3G) if possible.」は大雑把すぎ。

DBファイルサイズ分をキャッシュメモリに割り当てできたら、それは速くなるでしょう。。。

アドバイスは、参考程度に使うぐらいですね。

$
-------- Recommendations -----------------------
General recommendations:
    You are using an unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `zabbix`.`history_str`; -- can free 1855 MB
      OPTIMIZE TABLE `zabbix`.`history_text`; -- can free 4429 MB
      OPTIMIZE TABLE `zabbix`.`history_uint`; -- can free 13493 MB
      OPTIMIZE TABLE `zabbix`.`trends`; -- can free 54 MB
      OPTIMIZE TABLE `zabbix`.`trends_uint`; -- can free 286 MB
    Total freed space after theses OPTIMIZE TABLE : 20117 Mb
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Increasing the query_cache size over 128M may reduce performance
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See  https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Set thread_cache_size to 4 as a starting value
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from  https://github.com/FromDual/mariadb-sys for MariaDB
    For MySQL 5.6.2 and lower, total innodb_log_file_size should have a ceiling of (4096MB / log files in group) - 1MB.
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:  https://bit.ly/2TcGgtU
Variables to adjust:
    SET innodb_stats_on_metadata = OFF
    long_query_time (<= 10) skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 128.0K, or always use indexes with JOINs)
    thread_cache_size (start at 4)
    performance_schema=ON
    innodb_buffer_pool_size (>= 10.3G) if possible.
    innodb_log_file_size should be (=256M) if possible, so InnoDB total log files size equals 25% of buffer pool size.
    innodb_buffer_pool_instances(=2)

最後に

数年経過するとデータの変化、アクセスの変化など設定したパラメータが合わなくなることが多いので、定期的にメモリ使用量などを確認しましょう。

スロークエリを設定して、頻度やクエリの実行時間など伸びていたら、設定を見直しましょう。

RELATED POST