PowerCMS ブログ

PowerCMSは、ほぼすべての情報をデータベース(DB)に格納します。管理画面を利用する際にも、あるいは検索、フォームや会員サイトのサインアップなどにもDBは利用されます。

このため、PowerCMSにとってDBの設定は重要です。特に、DBのパフォーマンスはPowerCMSのパフォーマンスに大きな影響を与えます。ここでは、PowerCMSのDBとして多く利用されるMySQLを対象とした設定のポイントをご紹介します。

MySQLの設定の考慮点

MySQLの設定で注意すべき点は次の2つです。

1. ストレージ・エンジン
2. パラメータ調整

このMySQLには、古くから使われてきたMyISAMとInnoDBという2つのメジャーなストレージエンジンがあります。

「MyISAMとInnoDBのどちらがよいのか」というFAQがありますが、クラッシュリカバリ性能やマルチCPUに対するスケールなどを考慮すると、少なくともエンタープライズ用途ではInnoDBを採用する方がメリットが多く、MyISAMを選択する理由はありません。

現在多く利用されているCentOSやRed Hat Enterprise Linuxに同梱されているMySQLは5.0系や5.1系が多く、それらのバージョンではデフォルトのストーレジエンジンはMyISAMとなります。このため、デフォルトのMySQLをそのまま利用していると、MyISAMでデータを格納しており、運用開始後にパフォーマンスの問題が出てInnoDBへの変更が必要になる場合があります(MySQL 5.5以降はInnoDBがデフォルトに変更されました)。

MySQLクライアントを使って各テーブルにalter table文を発行することでストーレジエンジンを変更できます。

アルファサードでは、MyISAM→InnoDBへストレージエンジンを変換するスクリプトを書いて利用しています。Linux専用ですが以下のURLからダウンロードいただけます。

• https://github.com/motchie-alfasado/change_to_innodb

MySQL設定ファイルのパラメタ調整

MySQLの設定ファイルのキャッシュ等の値を調整することによりパフォーマンスを改善することができます。

例 : /etc/my.cnf

以下、InnoDBを利用している場合に重要なパラメタです。括弧内の値は一例です。負荷状況やメモリ量、データ容量等を見て調整してください。

MySQL のバージョンによって設定できるパラメタが異なりますので、設定については公式リファレンスも参照してください。

query_cache_size(8M) ※MySQL8.0以降では利用できません

MySQLクライアントからのクエリとその実行結果をキャッシュし、次回から同じ内容のクエリが要求された場合にキャッシュから応答します。そのキャッシュの容量。

thread_cache_size(4)

MySQLは接続終了後のサーバスレッドをいきなり解放せず、次の接続時に再利用できるようにキャッシュできます(切断後の再接続時のオーバーヘッドを軽減できる)。そのデータ容量。

query_cache_limit(8M)

キャッシュするクエリ結果の最大値の指定です。

tmp_table_size、max_heap_table_size(8M)

メモリ上に一時テーブルを作ります。このサイズを超えるとディスク上の/tmpディレクトリに一時テーブルのファイルを作るようになります。

thread_cache_size(4)

通常、MySQLのスレッドはクライアント接続ごとに生成・破棄されるますが、このパラメータを設定すると、設定した値の数だけスレッドが破棄されず、他のクライアント接続で再利用することができます。

table_open_cache(1024) ※MySQL 5.0x 以下のバージョンでは table_cache です

1度開かれたテーブルをメモリ上に維持しておき、再利用します。

innodb_buffer_pool_size(16M ※メモリが潤沢であればデータベースのデータ容量に応じて大きくします)

InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域です。

こうしたパラメタの設定を補助するツールとしてMySQLTunerを利用しています。オリジナルはLinux専用で以下のURLからダウンロードすることができます。

• https://github.com/major/MySQLTuner-perl

Windows対応版については以下のURLで公開しています。

• https://github.com/motchie-alfasado/MySQLTuner-perl

Perlスクリプトですので、ダウンロードして以下のように実行します。実行時にMySQLのrootアカウント名とパスワードを入力します。

perl ./mysqltuner.pl

他のホストのMySQLを調査する場合など、詳しい使用方法は --helpオプションをつけて実行すると参照できます。

以下、結果の例です。

% perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 68M (Tables: 696)
[--] Data in InnoDB tables: 2G (Tables: 608)
[!!] Total fragmented tables: 83

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 88d 18h 59m 59s (63M q [8.315 qps], 406K conn, TX: 313M, RX: 1B)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 332.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 600.7M (29% of installed RAM)
[OK] Slow queries: 0% (0/63M)
[OK] Highest usage of available connections: 11% (11/100)
[OK] Key buffer size / total MyISAM indexes: 50.0M/19.1M
[OK] Key buffer hit rate: 99.9% (88M cached / 46K reads)
[OK] Query cache efficiency: 90.4% (55M cached / 61M selects)
[!!] Query cache prunes per day: 8736
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 28% (304K on disk / 1M total)
[OK] Thread cache hit rate: 99% (11 created / 406K connections)
[!!] Table cache hit rate: 6% (256 open / 3K opened)
[OK] Open file limit used: 46% (479/1K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[OK] InnoDB data size / buffer pool: 2.5G/3.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increasing the query_cache size over 128M may reduce performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 256M) [see warning above]
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 256)

最後のVariables to adjust:に表示されているのかパラメタの提案です。これをもとに、設定ファイルを修正したあとでMySQLをrestartし、効果を確認します。