41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

【MySQL】キャッシュウォームクエリ(InnoDB)の自動作成

そもそもキャッシュウォームとは?

MySQLデータベースにはInnodb Buffer Pool(通称バッファプール) と呼ばれるメモリ上に確保された領域があります。

その領域にデータやインデックスがキャッシュされます。(対象はInnoDBテーブル)

キャッシュはデータの初回参照時に行われます。

データ参照時、まず最初にバッファプールを確認してからデータファイルを見るので、それ以降はバッファプール上にあればそこから読み込まれます。

(ちなみにDMLDDLでも発生するんですが、SELECTのためのキャッシュウォームがメインなのでここでは話を割愛します。)

速度は知っての通り  メモリ>>>ファイル  なので、キャッシュに乗っているほうが早いです。

というわけで、データをメモリ上にキャッシュしておきましょう。

分かりやすくておススメなのはyoku0825さんのスライド

動的なキャッシュウォームのクエリの作り方

以下のような感じで動的に作れるかと。

主キー用

SET @var_name = 'スキーマ名';
SELECT 
    CONCAT('select * from ',table_schema,'.',table_name,';') 
FROM 
    information_schema.tables 
WHERE 
    table_schema = @var_name 
ORDER BY
    table_rows desc;

スキーマ名は適当に変えて使ってください。

主キー以外のインデックス用

SET @var_name = 'スキーマ名';
SELECT 
    CONCAT('select ',GROUP_CONCAT(cnt_col_name order by SEQ_IN_INDEX),' from (select ',GROUP_CONCAT(COLUMN_NAME order by SEQ_IN_INDEX),' from ',TABLE_SCHEMA,'.',TABLE_NAME,' order by ',GROUP_CONCAT(COLUMN_NAME order by SEQ_IN_INDEX),') as t1; -- cache warm for ',TABLE_NAME,'.',INDEX_NAME)
FROM
    (SELECT 
        CONCAT('count(', COLUMN_NAME, ')') AS cnt_col_name,TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.STATISTICS
    WHERE
        INDEX_NAME <> 'PRIMARY' AND TABLE_SCHEMA = @var_name
    ORDER BY TABLE_SCHEMA , TABLE_NAME , INDEX_NAME , SEQ_IN_INDEX ASC) AS X
GROUP BY TABLE_SCHEMA , TABLE_NAME , INDEX_NAME;

あとは、ここからメモリに載せる重要なものを取捨選択する。

行数が多いテーブルを優先しているのは、おそらく重要なテーブルが多いから。

(履歴テーブルとかあるかもしれないのでその辺は各自調整してください)

上のクエリでもし、間違いあればツッコミお願いします。

キャッシュウォームについて参考にしたのは奥さんの以下のブログ記事です。

http://labs.cybozu.co.jp/blog/kazuho/archives/2007/10/innodb_warmup.php

追記

Workbench で表示した場合、1カラムに表示される文字列数が制限されている。

そのため、途中でクエリが切れているような場合は、

Edit >Preferences...を選択し、表示文字列長を変更すると良いです。

更に追記

大きなテーブルだとselect * from xxxなんて結果表示されたらうっとおしい。

なので実行結果は捨てましょう。

SELECT 
    CONCAT('mysql -uユーザ名 -pパスワード -hDBのホスト -Pポート番号 スキーマ名 -e "select * from ',
            table_schema,
            '.',
            table_name,
            ';" > /dev/null')
FROM
    information_schema.statistics
WHERE
    table_schema = 'スキーマ名'
        AND index_name = 'PRIMARY'
;

できたコマンドをmysqlクライアントがインストールされたプロンプトから実行すればOK.