|||||||||||||||||||||

なんぶ電子

- 更新:　

MariaDBのチューニングの基本

PCの能力に頼っていままであまり考慮せずにMariaDBを使っていましたが、公式ガイドを参考ににチューニングについて理解を深めたいと思います。

MariaDB

ハード・OSのチューニング

チューニングは my.ini や 50-server.cnf といった設定ファイルだけかと思っていたのですが、ハードウェアやOSレベルからチューニングの対象となっているようです。

一番重要なのは、メモリだそうです。当然メモリは大きければ大きいほどいいのですが、同じサイズだったらオーバーヘッドが減る分使用スロット（メモリの数）が少ない方がいいようです。

あたりまえな事が続きますがディスクも高速なものがいいです。重要になるのはシーク時間です。他に、参考になるのはIOPS（Input/Output Operations Per Second）という秒単位のオペレーション数です。

あと、CPUやネットワークなどの要素もあります。

Linuxではインストール時にスワップ領域を設定すると思いますが、これを利用するとディスクだけを利用した場合より遅くなる事があるそうです。

内部アルゴリズムがスワップ領域を使うことを想定していないからだそうです。

なので、マシンがデータベース専用のサーバーである場合はOSの設定からスワップを利用しないように修正します。

Linuxでスワップの状態(利用率)を確認するには sysctl を使います。

# sysctl vm.swappiness

筆者の環境では 60 が設定されていました。これは0-100までの値をとり小さいほどスワップを利用しない設定になるので、小さい値を設定します。

MariaDB的にはスワップを利用されるのは考慮されていないので、0のほうがいいかもしれませんがメモリ不足でOSがクラッシュする可能性を考えたら、ある程度残しておくのがいいと思います。

sysctlファイルに記述します。

/etc/syssctl.conf

vm.swappiness = 5

再起動するか sysctl -p コマンドで設定を読み込ませます。

ストレージエンジンの選択

ストレージエンジンとはいわばデータを保存する際に用いられるプログラムです。この選択もチューニングのひとつと言えます。

ストレージエンジンMariaDBにおけるストレージエンジンの例を上げると次のようなものがあります。

  • InnoDB

    汎用的なストレージエンジンでデフォルト設定です。特筆すべきことがない限りこれを選択するのが最善です。

    InnoDBはトランザクション処理に対応し、ACIDに準拠しています。また、行レベルのロックも可能です。

  • MyISAM

    昔のMySQLでデフォルトだったストレージエンジンです。MariaDBでも少しまえのバージョンまでは管理テーブルのストレージエンジンはこちらでした。

    高速でフットプリント(データ総量)を小さくできますが、トランザクションには対応していません。

  • Aria

    MyISAMの後継にあたるMariaDBのストレージエンジンでクラッシュセーフになっています。バージョン10.4からはMyISAMにかわってシステムテーブルで用いられています。

  • ColumnStore

    規模の大きい（ペタバイト規模）のデータへのスケーリングを想定しているストレージエンジンです。

  • Spider

    複数のデータベースを1つの論理的なデータベースとして扱うことができます。

  • Connect

    データの形式にとらわれずテキストファイルやリモートソースにアクセスできるストレージエンジンです。

  • Mroonga

    以前の記事で、全文検索をMariaDBに設定しました。その際は使いませんでしたが、日本語などのCJKの全文検索に対応したストレージエンジンです。

  • S3

    Amazon S3に保存したデータを読み出すためのもので読み取り専用。

my.cnf

以前はmy-small.cnf や my-large.cnfといったサンプルのコンフィグファイルが存在しました。 これらをベースににすることもできますが、既に古くなっているので名前に見合わない設定になっているかもしれません。

設定ファイルにおけるチューニングでは主にInnoDBの設定について触れます。

  • innodb_buffer_pool_size

    メインのストレージエンジンであるinnodbのバッファサイズを指定する innodb_buffer_pool_size は設定の肝となるものです。

    これは、SELECT文の結果をキャッシュする Query Cache とは別物で、データベースに保持しているデータそのものをメモリ上に保持しておくものです。

    データをバッファ(メモリ)に配置してディスクIOを減らすために可能な限り大きくします。

    目安として利用可能なメモリの 70～80% の値が提示されています。

    サーバーのアクティブなデータのほとんどを保持できるようになっているのが理想です。

    このサイズが適正かどうかは、innodb_buffer_pool_read_requests,innodb_buffer_pool_reads,innodb_buffer_pool_wait_freeなどの値を確認します。

    これは Server Status 変数なので、確認するには show status を使います(show variables では表示されません)

    show status like '%innodb_buffer%';

    innodb_buffer_pool_read_requests はバッファからデータを読み込んだ件数です。innodb_buffer_pool_reads はデータがバッファになくディスクに読み込みいった回数です。

    データベースを稼働させてしばらくした後、のこのふたつの値を比較してバッファからの読み込みの数に対してディスクからの読み込みの数が非常に少なくなっていれば良好な状態です。

    もう少し具体的にいうと、ふたつの値の1分間の増分を比較した際ディスクからの読み込みがバッファからの読み込みの1%未満なら充分なバッファがあると言えます。

    innodb_buffer_pool_wait_free はページの空きを待った回数になります。この値が増加傾向にある場合も innodb_buffer_pool_size を増やす目安となります。ただしこちらはバッファプールのフラッシュの頻度が少ない事が原因の場合もありますので見極めが必要です。

    innodb_buffeer_pool_size の設定において気を付けないといけない点はこの値を大きくしすぎてOSでスワップが発生してしまうことです。

    また、10.2.2以降からinnodb_buffeer_pool_size を動的に変更できるようになりました。近年のバージョンで変更が激しいので詳しいことは公式ページを確認していただければと思いますが、innodb_buffeer_pool_size に1G以上の設定されている場合に動的にサイズ変更が行われ、その状況は「 SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status'; 」とすることで確認できます。

  • innodb_log_file_size

    InnoDBがクラッシュリカバリに用いられるRedoログ(ib_logfile0)のひとつのファイルサイズです。この値を大きくすればするほどディスクIOが減らせます。

    というのは、このファイルがいっぱいになるとチェックポイント(バッファプールにある変更された内容がディスクに書き込む)となるからです。

    視点を逆にすれば、バッファプールが大きくてもこのファイルサイズが小さいとバッファプールからディスクへの書き込みが頻繁に起きてバッファプールの容量を無駄にします。

    またバージョン10.5からはクラッシュリカバリの速度が早くなったので、安全に大きな値を設定可能だということです。ただし、大きければ大きいほどクラッシュ時のリカバリ速度は遅くなるのは変わりありません。

    設定可能な最大サイズはトータルで512GBです。デフォルト値は96Mです。

    10.4のデフォルト値は48M、10.5以上は96Mとなっています。設定可能な最大サイズは512GBです。

    ちなみに、MySQL「8.5.4 Optimizing InnoDB Redo Logging」では、この値は innodb_buffeer_pool_size と同じにするように提案されています。

  • innodb_max_dirty_pages_pct

    ダーティーページ(データベースに書き込みされていないページ）の比率が指定した値以上(0-99.999)になった時に、書き込み(フラッシュ)が実行さます。10.5.6以前は75%、以降は90%です。この比率は ダーティーページ数／全ページ数となっています。

    もう少し詳しく書くと、innodb_buffeer_poolには、ページという単位でデータベースの値が保持されています。ページにある値が更新されるとダーティーフラグが付きます。これは通常のチェックポイントやRedoログが埋まった場合、サーバーの停止時、バッファがいっぱいになった時にディスクに書き込まれてフラグが取り除かれます。それらの状況が発生しない場合ダーティーフラグの比率は上がっていきますがその上限を決めるものです。

  • innodb_max_dirty_pages_pct_lwm

    前述のフラッシュの設定を二段階にするものです。現在の設定は0が設定されていて、これは二段階目のフラッシュの設定はしないことを意味します。10.2.1までは0.001が設定されていて高頻度でフラッシュされます。さらに、10.5.7と10.5.8においてはデフォルトではダーティーページがある限りフラッシュするそうです。

    0 と innodb_max_dirty_pages_pct の間で設定しておくとその値を超えるとフラッシュ作業がバックグラウンドで走るようになります。

    限界まで溜まってからフラッシュするとIO待ちがひどくなるので、ある程度溜まったらこまめにフラッシュしておく、といったバランスをとる設定となります。

    ただ、従来のバージョンでは高頻度だったのに近年のバージョンのデフォルト値は0と、大きな変更があった理由がよくわからないので、この設定は各バージョンのデフォルト値にしておいた方が無難かもしれません。

  • innodb_read_ahead_threshold

    先読み(プリフェッチ）するしきい値です。extent(物理的に連続した複数のディスクブロック）から順次読み取られたページ数が指定した値以上の場合、次のextent全体を非同期に先読みします。

    値は0-64でデフォルトは56です。

    これに関してもMySQLの文献になってしまいますがMySQL「15.8.3.4 InnoDB バッファープールのプリフェッチ (先読み) の構成」にわかりやすい説明が載っています。

ここまでがInnoDB用の主な初期設定ですが、他には次のようなものがあります。

  • key_buffer_size(MyISAM)

    伝統的な理由により key_buffer_size というMyISAMテーブルのバッファーサイズを設定する設定がありますが、MyISAMを利用しないならばこの値は 64K といった小さな値にしておくことでメモリの節約になります。管理テーブルがAriaになっていて明示的にMyISAMテーブルを利用していない場合は小さな値を設定しておきましょう。

    key_buffer_size の他にMyISAMの設定にはkey_cache_age_threshold,key_cache_block_size,key_cache_division_limit,key_cache_file_hash_size,key_cache_segments,myisam_* がありますが、MyISAMを使わない場合はこれらの設定も使わないことになります。

  • aria_pagecache_buffer_size(Aria)

    key_buffer_sizeにかわる設定です。物理メモリサイズの20～50%ぐらいで設定しますが、先InnoDBとの関係もあるので負荷のモニタリングが必要です。

  • table_open_cache

    データベースがテーブルをオープンにしたままにできる数です。同じテーブルにアクセスする複数セッションの為に、テーブルを開いたままにしておければ効率が上がります。

    この値は次のようにして確認できますが、デフォルトは 2000 だと思います。

    select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
|               2000 |
+--------------------+

    また現在どれだけのテーブルが開かれているかを確認するには次のようにします。 

    show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 80    |
+---------------+-------+

    時間をおいてみた際に、開かれているテーブルの数が一定また微増なら最適な値です。逆に上記のように少なすぎる場合はキャッシュを減らすこともできます。

    増やす場合はOSで同時オープン可能なファイル数(SHOW GLOBAL VARIABLES LIKE 'open_files_limit')にも気を付ける必要があります。

  • table_definition_cache

    table_definition_cache はテーブルの定義をキャッシュする上限です。多数のテーブルがある場合はこの値を増やすとテーブルを開く速度が増します。

  • query_cache_type

    クエリキャッシュは現在のようなマルチコア高スループット環境おいては有効ではないのでOFFを設定します。MySQL:「MySQL 8.0: クエリ キャッシュのサポート終了」に詳しい話が書かれています。

    有効性が見いだせる場合は DEMAND 値が推奨されています。有効性がある例として、先のページでは、読み込みが主のデータベースにおいてWebページの選択肢のリストを抽出する為にインデックスのない大きなテーブルを走査する場合が挙げられています。

  • query_cache_size

    query_cache_type同様の理由で 0を指定します。この設定値が存在しなければ問題ありませんが、この値が0以外だとquery_cache_typeは OFF にしてあっても自動的に ON となります。

    有効性が見いだせる場合でも 50M以下が推奨されています。

    クエリキャッシュがヒットしたかどうかは、ステータス変数の qcache_hits によってわかります。この値を総数( qcache_hits + qcache_inserts :キャッシュに入った数 + qcache_not_cached :理由がありキャッシュに入らなかったもの)で割ることでヒット率が算出できます。

    (問題になっているのはクエリキャッシュを用いると用いない場合より遅くなるということで、その点の有用性はヒット率からは見いだせないので注意してください。)

Ariaストレージエンジン

Ariaストレージエンジンは明示して利用しなければ管理用のテーブルでのみ用いられます。

テーブルで利用しているストレージエンジンが何なのかは「 show table status 」コマンドで確認することができます。 

MariaDB [mysql]> show table status \G
...
*************************** 30. row ***************************
            Name: time_zone_transition_type
          Engine: Aria
         Version: 10
      Row_format: Page
            Rows: 0
  Avg_row_length: 0
     Data_length: 8192
 Max_data_length: 17592186011648
    Index_length: 8192
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2023-01-23 20:54:00
     Update_time: 2023-01-23 20:54:00
      Check_time: 2023-01-23 20:54:00
       Collation: utf8_general_ci
        Checksum: NULL
  Create_options: transactional=1
         Comment: Time zone transition types
Max_index_length: 9007199254732800
       Temporary: N

Ariaでの負荷のモニタリングには、InnoDBのバッファサイズの判定に使った、innodb_buffer_pool_read_requests,innodb_buffer_pool_readsに相当する、aria_buffer_pool_read_requests,aria_buffer_pool_readsがあります。

InnoDBより表示項目が少ないので「 show status like 'aria_%' 」でまとめて表示すればいいと思います。 

MariaDB [mysql]> show status like 'aria_%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Aria_pagecache_blocks_not_flushed | 0     |
| Aria_pagecache_blocks_unused      | 15636 |
| Aria_pagecache_blocks_used        | 37    |
| Aria_pagecache_read_requests      | 10659 |
| Aria_pagecache_reads              | 131   |
| Aria_pagecache_write_requests     | 1216  |
| Aria_pagecache_writes             | 1216  |
| Aria_transaction_log_syncs        | 0     |
+-----------------------------------+-------+

余分なバッファを設定しない為に

仮にサーバーの物理メモリをデータベースで全て利用していいという場合でも、それらを複数の各ストレージエンジン毎に振り分ける必要がでてきます。

その際の目安となる数値を出すのが次のSQL文です。 

SELECT ENGINE,
ROUND(SUM(data_length) /1024/1024, 1) AS 'Data MB',
ROUND(SUM(index_length)/1024/1024, 1) AS 'Index MB',
ROUND(SUM(data_length + index_length)/1024/1024, 1) AS 'Total MB',
COUNT(*) 'Num Tables'
FROM  INFORMATION_SCHEMA.TABLES
WHERE  table_schema not in ('information_schema', 'PERFORMANCE_SCHEMA', 'SYS_SCHEMA', 'ndbinfo')
GROUP BY ENGINE;

+--------+---------+----------+----------+------------+
| ENGINE | Data MB | Index MB | Total MB | Num Tables |
+--------+---------+----------+----------+------------+
| NULL   |    NULL |     NULL |     NULL |          1 |
| Aria   |     0.7 |      0.3 |      1.0 |         24 |
| CSV    |     0.0 |      0.0 |      0.0 |          2 |
| InnoDB |   610.0 |      5.3 |    615.4 |         51 |
+--------+---------+----------+----------+------------+

これはエンジン毎のデータとインデックスでどれだけの容量を使用しているかという値になります。

バッファサイズが、インデックスとデータすべてが保持できる値より多かったら無駄ということになります。

MariaDBメモリの割り当てによれば、MyISAMならインデックスの合計値を、InnoDBならインデックスとデータの合計値に1.1をかけた値(管理用データが含まれるというのでその分だと思います）が目安だということです。

その他

デフォルトでインストールされているmy.cnf(50-server.cnf)で、記述ある設定で、ここまで触れていないパフォーマンスにかかわる設定を紹介します。

多くはFine Tuning(微調整)としてコメントアウトされています。

  • skip-name-resolve

    「 skip-name-resolve=1 」で、IPアドレスのみが接続で使用されるようになります。接続許可(GRANT)はIPアドレスで指定する必要があります。ただし localhost は除きます。

    MySQL:「 skip_name_resolve 」にこのパラメータ設定の挙動が書かれています。

    デフォルトの挙動は、IPからホスト名検索を行いそのIPとホスト名を使って権限の確認をします。

    これによりユーザーに権限を付与する GRANT文におけるホスト名の設定が有効になります。

    権限設定時にホスト名を使う必要が無ければ、この問い合わせ(キャッシュもあるようですが)は無駄になるので、1にしておくとシステムやネットワークのオーバーヘッドが減ります。また、DNSの設定におかしなものがあると極端にレスポンスが悪くなることがありますのでそのような障害を事前に回避できます。

    Gihyo.jp：「MySQLと名前解決、skip-name-resolve」 には、これについての詳しい日本語解説があります。

  • thread_cache_size

    スレッドキャッシュのサイズです。通常スレッドは接続毎に生成されますが、それをキャッシュしておき可能なら再利用する場合の設定値です。

    デフォルト値は256のようですが、thread poolがアクティブな時は値は自動調整されます(MariaDBでは標準でthread poolがアクティブです)。

    マニュアルにはこの値の影響は軽微だということでした。キャッシュは5分で消えるようなので、それまでの短い間に多くのアクセスがある場合は調整するとレスポンスが向上するかもしれません。

    その場合はステータス変数の threads_created を参考にして新規作成されたスレッド数をチェックします。

    ちなみに thread pool の設定は thread_pool_handling で指定し、この値を pool-of-threads に指定すると thread pool を利用してスレッド管理をするようになり、で指定し、これが one-thread-per-connection だと接続毎にひとつのスレッドで処理する(プールを利用しない)ようになります。

    異なるクライアントから複数の同時接続がある場合はthread_pool_handling を pool-of-threads にする方が改善が見込めるかもしれません。

  • thread_stack

    スレッド毎に割り当てられるスタックのサイズです。メモリ内で処理できるSQLステートメントの複雑さや、ストアドプロシージャでの再帰深度に影響します。

    デフォルト値は299008です。

  • max_allowed_packet

    サーバーに送信される単一のSQLステートメント、クライアント側に送信される単一レスポンスの最大サイズでデフォルトは16Mです。

    この値はパフォーマンスに直接影響はしませんが、DOS攻撃等でメモリ－不足を招くのを防ぐためにこのような値になっています。

    逆にBLOB等の大きな値などを扱う場合はこの値を1Gといった大きな値にします。

    ちなみにレプリケーション用のパケットには、slave_max_allowed_packet の値が採用されます。

  • max_connections

    この値を超えると too_many_connections が返されます。

    デフォルトの値(151)で too_many_connections エラーがでるようなら、threads_connected (スレッド接続数)や max_used_connections (最大使用接続数)といったステータス変数を頼りに増やします。

    逆に接続数が多いことが原因でレスポンスの悪化を招いているようなら、この値を下げて接続を制限します。

  • table_cache

    これは非推奨で削除された設定の名残で、後継は前述の table_open_cache です。

  • innodb_lock_wait_timeout

    InnoDBでロックを取得取得する際に、ロック解除を待っている秒数になり、デフォルトは50です。

    InnoDBでロックを多用する場合は。データベースを利用するアプリの構造にもよると思いますが、早くロック取得エラーを返した方が実感としてのレスポンスはいいです。

ログ回りの設定

この後のログ周辺の設定は、レスポンスとデータ堅牢性のトレードオフになる項目となります。つまり、レスポンスを優先してクラッシュ時にデータが失われる可能性を受け入れるか、クラッシュ時に最大限の備えをしてレスポンスを悪化させるかのどちらかになります。

補足

このログ回りの前提知識として、データベースのファイルに書き込みが行われるまでの流れを整理しておきます。

プログラミングをしている方ならわかると思いますが、OS上のファイルへの書き込みは指示は実行しても即座に反映しないことがあります。書き込んだ内容がファイル書き込みバッファに保存されているからです。意図して反映させたい場合は書き込み指示(フラッシュ)をします。

MariaDBではこの手前にログバッファといわれるものが加わります。

  1. ログバッファ（データベースが管理するログ用のバッファ)
  2. ログファイル(OSの書き込みバッファに残る可能性がある)
  3. 物理ディスク

物理ディスクに書き込まれていないと、データはクラッシュ時に失われる可能性があります。

  • log_bin

    この値にバイナリログのファイル名を設定すると、バイナリログを出力するようになります。

    バックアップ(dump)から次のバックアップまでの差分を記録しておくもので、障害時のデータ復旧に用いられます。

    たとえば誤ってテーブルを truncate してしまった場合などは、前日のdumpデータからバイナリログを使ってロールフォワードします。

    また、レプリケーションにも用いられます。

    逆に、すべて揮発性の内容でいいデータベースの場合は、バイナリログは出力しない方がオーバーヘッドは減ります。

  • binlog_cache_size

    バイナリログにはトランザクションが完了した時点で書き込みが行われますが、トランザクションが終了するまでこのメモリ上のキャッシュの中で変更を記憶しています。

    デフォルト値は32768(byte)です。

    これから溢れるとキャッシュはファイル化されるためI/Oが増えます。

    それらを発生頻度調べるには、公式サイトにあるように、binlog_cache_disk_use(ディスクを利用した回数)とbinlog_cache_use(キャッシュを利用した回数）のステータス変数を参照します。

    また非トランザクションステートメントにおいては別枠でキャッシュやステートメント変数が用意されていて、それぞれbinlog_stmt_cache_size,binlog_stmt_cache_use,binlog_stmt_cache_disk_useとなります。

  • syc_binlog

    MariaDBはバイナリログをイベント毎に書き出しますが、その際のディスクへの同期を設定します。

    デフォルトは 0 で、ファイルへの同期は(バッファからの書き込み）はOSに任せられます。

    この値を 1 にするとデータベースがバイナリログに書き込むと、即座にファイルに反映されます。そのため対障害性はよくなりますが、レスポンスは悪くなります。

  • innodb_log_file_size

    こちらは「 Redoログ 」という InnoDB で用いられるログのファイルサイズを指定する項目です。

    Redoログはクラッシュ時にデータを復旧するのに用いられるログファイルである側面がある一方で、データベースファイルはこのファイルの内容にしたがって反映されますので、データベースファイルのひとつとも言えます。

    クラッシュ時バイナリログを用いた復旧は明示して作業する必要がありますが、こちらはシステムが自動で行います。またバイナリログにはコミットされていないトランザクション途中のデータは含まれませんが、Redoログには含まれます。

    Redoログファイルがいっぱいになるとチェックポイントのプロセスが稼働します。チェックポイントでは、Redo ログからデータベースファイルへの書き込みが行われます。

    ちなみに、Redoログからからデータベースファイルへの書き込みはチェックポイント時だけではなく、コミット時にも行われます。

  • innodb_log_buffer_size

    Redoログファイルに書き込むのに用いられるバッファのサイズ指定です。

    大きくすればその分IOを減らすことができますが、フラッシュに時間がかかったり、次に紹介する innodb_flush_log_at_trx_commit の値によってはクラッシュによりデータが失われることがあります。

    デフォルトは16Mです4096Mまで増やすことができます。

  • innodb_flush_log_at_trx_commit

    コミット時に redo ログを書き込む際の設定です。

    0だとredo ログバッファに書き込むのみです。

    1(デフォルト)にするのとともに、コミット時にディスク上のファイルの反映までします。

    2だとredo ログファイルに書き込まれるか否かは、OSの処理に依存する状態です。

    この値を 1にして、先のsyc_binlogを 1 とすると最も障害に強い（レスポンスは悪い）構成になります。

MySQLTuner

基本的なパラメータを理解した上で今度はツールを使って設定値の検証をしてみます。

MariaDBのパフォーマンスチェックにはMySQLTunerというツールが利用できます。

これを利用っするには Performance schema と Sys schema(データベース) が必要です。

Performance schema データベースは内部情報を保持するためのもので、Sysschema は主にその Performance schema から情報を簡単に取り出すための機能を提供するものです。

デフォルトの状態では performance_schema データベースは存在しても稼働していないと思います。

これは設定ファイル my.cnf の[mysqld]セクションに指定します。筆者の環境はDebianなので、50-server.cnf の[mysqld]セクションに指定しした。

また InnoDBストレージエンジンテーブルのメタデータの集計・更新をするフラグである innodb_stats_on_metadataを 1(on) にします。

この種の統計情報の蓄積は、値を 0(off)にしても完全になくなる訳ではないですが、off時よりは on時の方がオーバーヘッドが大きくなるので、必要なくなったらoffにしておきましょう。

50-server.cnf

...

[mysqld]
...
performance_schema = 1 
...
innodb_stats_on_metadata = 1
...

また、バージョン10.6より前では、sys(Sysschema) はデータベースも存在しないと思いますので、githubのmariadb-sysから clnoeします。

--depth 1 オプションをつけて履歴を最新のコミットのみに制限すると早いです。

# git clone --depth 1 https://github.com/FromDual/mariadb-sys.git

...
# cd mariadb-sys
...
# mysql -u root -p<sys_10.sql

筆者の環境では次のようなエラーが出ました。

ERROR 1054 (42S22) at line 78 in file: './views/p_s/metrics_56.sql': Unknown column 'STATUS' in 'field list'

エラーで表示されている、metrics_56.sql ファイルの 94行目付近にあるSQL文の「 STATUS='enabled' 」を「 ENABLED=1 」に変更すれば解消すると思います。

おそらくMariaDBのどこかのバージョンからカラムが変更がされたのだと思います。

ログインしてみて、performance_schemaにある users や hosts などのテーブルに値が入っているかどうか、sys データベースが存在するかを確認して問題なければMariaDBの設定変更は終了です。

MySQLTunerのインストール

次にMySQLTunerのインストールをします。インストールといっても全体がPerlのスクリプトなのでコピーといった方が正しいのかもしれません。

GithubのMySQLTunerからcloneしてもいいですが、ページに載っていたもう少し簡単な方法を紹介します。

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

basic_passwords.txt は利用していると危ういパスワードのリスト、vulnerabilities.csv は脆弱性に関するデータベースです。

MySQLTunerの利用

インストールしたら、あとはperlスクリプトを起動するだけです。DebianではOSでルートログインしている際には、MariaDBへのアクセスはパスワードレスになります。

なので、次のようにするだけで、パフォーマンスチェックの結果が出ます。

perl mysqltuner.pl
>>  MySQLTuner 2.1.1
       * Jean-Marie Renouard <jmrenouard@gmail.com>
       * Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from defaults file account.

[OK] Currently running supported MySQL version 10.3.38-MariaDB-0+deb10u1-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

この時引数に渡せる主なオプションは次の通りです。

  • --host

    対象のホストを選択します。先のように事前準備をしてあればリモートでの情報取得ができますが、その際は物理メモリの搭載量が取得できないため手動で設定する必要があります(M単位)。

    perl mysqltuner.pl --host 192.168.1.20 --forcemem 4000
  • --port

    デフォルト(3306)でない場合にポートを指定する事ができます。

  • --user

    ユーザー名を指定します。paformance_schema 等の読み込み権限があるユーザーを指定する必要があります。

  • --pass

    ユーザーに対するパスワードを指定します。コマンド履歴にプレーンテキストで残るので注意しましょう。

  • --outputfile

    結果を保存するファイルを指定します。似たようなオプションに --reportfile がありますが、こちらは --template 引数を伴ってテンプレート化した結果を出力するものです。

ユーザーとパスワードを記憶させたかったり、パスワードをコマンド履歴の中に残したくない場合は、既存の my.cnf を使うか別途作成して[client]セクションに次のように記入しておきます。

my.cnf

[client]
user=ユーザー名
password=パスワード

my.cnfを別途作成した場合は、引数で--defaults-fileの後に作成したファイルへのパスを指定します。

実行すると、メモリの使用量から、インデックスを使わない表の結合が実行された数、ユーザー名とパスワードが同じだったりする場合の警告、断片化されたテーブルのデフラグ案等、様々な項目を表示してくれます。これらは、ここまで出てきた設定項目がわかっていればほぼ理解できる内容だと思います。

稼働時間が24時間未満の場合は、適切な判断ができないないとのことなので、実行できることが確認できたらしばらく待った後で再度実行しましょう。

いくつか気になった点は、まずツール内では innodb_log_file_size のサイズを、innodb_buffer_pool_size の25%～50%にすべきだというメッセージと、必要なメモリ量を指定サイズ×接続可能クライアント数で見積るため「 MySQL's maximum memory usage is dangerously high 」がでる点、query_cache_size を設定するように指示がでる点です。

単に提案に従うのではなく、何をするか分かった上で設定を変更し、監視することが必要だとも公式ページには書かれていました。

参考にさせていただきましたサイトの皆様、ありがとうございました。

筆者紹介

自分の写真
がーふぁ、とか、ふぃんてっく、とか世の中すっかりハイテクになってしまいました。プログラムのコーディングに触れることもある筆者ですが、自分の作業は硯と筆で文字をかいているみたいな古臭いものだと思っています。 今やこんな風にブログを書くことすらAIにとって代わられそうなほど技術は進んでいます。 生活やビジネスでPCを活用しようとするとき、そんな第一線の技術と比べてしまうとやる気が失せてしまいがちですが、おいしいお惣菜をネットで注文できる時代でも、手作りの味はすたれていません。 提示されたもの(アプリ)に自分を合わせるのでなく、自分の活動にあったアプリを作る。それがPC活用の基本なんじゃなかと思います。 そんな意見に同調していただける方向けにLinuxのDebianOSをはじめとした基本無料のアプリの使い方を紹介できたらなと考えています。

広告

プライバシーポリシー