MySQLの全テーブルを最適化(OPTIMIZE TABLE)する

パッケージソフトの開発会社や、業務系システムの開発会社にいた頃はデータベースといえばSQL ServerやOracleでした。

独立してからはオープンソース化の流れとライセンスの都合上でPostgreSQLを触る機会が多くなりましたが、MySQLを本格的に触り始めたのはわりと最近になります。MariaDBで興味を持ったのがきっかけですね。

Webサイトで使うデータベースを決めたら、まずメンテナンス用のスクリプトを組むのですが、はて、MySQLの場合はvacuumコマンドがないのかな、と不思議に思いました。

PostgreSQLもバージョンアップによってvacuumしなくて良くなった!と聞いたときには、え、そんなバカな、と思いつつも一応調べてみるとauto vacuumできるようになったというだけでしたし。

MySQLのInnoDBは追記型データベースではないと言ってもメンテナンスコマンドはあるハズだろうと少し調べてみました。

そもそもMySQLでvacuumは必要なのか

MySQLのInnoDBはPostgreSQLの追記型データベースとは違い、vacuumは不要………………と思いきや、UPDATEはともかくDELETE実行時はやはり大量の断片化が起こるのだとか。

実際、WordPressをインストールしてしばらく使っていたら多少の断片化が出来ていたのでそういうものなのでしょう。もしかしたらデータベースエンジンが自動的に最適化してくれるのかも知れませんが、通常のWeb運営においては朝方とかアクセスの少ない時間帯の予想がつきますから、その時間に合わせて自分で最適化したほうが良いかなぁー。

ほら、WindowsでもHDDのデフラグは自動化されてるけど、明らかにしばらく使わないときはとりあえず手動でデフラグ実行したりしませんか。あるいは24時間稼働させてるPCなら深夜のPC使っていない時間帯に自動実行させたり。

ま、そんな感じ(?)でMySQLテーブルの最適化も実装してみましょう。

MySQLでのテーブル最適化方法

コマンドラインで実行するかOPTIMIZE TABLEのSQLを投げます。

コマンドラインで最適化

mysqlcheck -o [データベース名]

もしくは全データベースを対象にこんな感じ。

mysqlcheck -o –all-databases

レンタルサーバー上でmysqlcheckコマンドが使えるかわからなかったので、うちはPHP上でOPTIMIZE TABLEを実行することにしました。

OPTIMIZE TABELを全テーブルに実行

PostgreSQLでは「vacuum」とSQLクエリに投げるだけで全テーブル最適化してくれたのですが、OPTIMIZE TABLEの場合はひとつひとつテーブル名を指定しなければならないのでPHPで作りました。

$cfg ['PDO_DB'] = '[データベース名]';
$cfg ['PDO_DSN'] = 'mysql:host=localhost;charset=utf8;dbname='.$cfg['PDO_DB'];
$cfg ['PDO_USER'] = '[ユーザー名]';
$cfg ['PDO_PASS'] = '[パスワード]';
//DB接続
$pdo_option = array(
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_STRINGIFY_FETCHES => false
);
$conn = new PDO($cfg['PDO_DSN'], $cfg['PDO_USER'], $cfg['PDO_PASS'], $pdo_option);
//テーブル最適化
$result = $conn->query("SHOW TABLE STATUS");
while ($row = $result->fetch()) {
	if ($row['Data_free'] > 0) {
		$conn->query("OPTIMIZE TABLE ".$row['Name']);
	}
}

まぁ何のことはなく、SHOW TABLE STATUSでテーブル一覧を取得し、1つ1つOPTIMIZE TABLEを実行しているだけです。

ちなみに、HeidiSQLやphpmyadmin等でOPTIMIZE TABLEを実行するとわかりますが、下記のようなメッセージが返ってきます。

Table does not support optimize, doing recreate + analyze instead

(InnoDBはMyISAMと違い)最適化はサポートしてないよ、だからテーブルを作り直したよ、ってことです。 すげーな、テーブル再作成かよ、と最初は思いましたが、よく考えたらぼくも開発中、テーブルのシーケンス番号をリセットするためによくやるわ…と思い直しました。

ですので、当然のようにOPTIMIZE TABLE中はテーブルにロックがかかります。

最初はデイリータスクで組み込むつもりでしたが、運営サイトのアクセス数が増えたら週に1回とか、頻度下げたほうが良いかなぁーと思いました。

まとめ

  • MySQLも一応最適化はしたほうが良さそう
  • 最適化方法はコマンドラインでmysqlcheckを実行か、OPTIMIZE TABLEクエリを投げる
  • OPTIMIZE TABLEは実際には最適化じゃなくてテーブルの再作成を行っている
  • …なので、SHOW TABLE STATUSでData_freeが一定以上の場合だけ実行したほうが良いかも