WordPressのデータベース(SQLite)を最適化するVBScript
先日WordPress化した記事を書きました。その時は書いていなかったのですが、実は通常のMySQLは使用せず、SQLiteを使って環境を構築しました。
SQLiteにした理由とメリット
WordPressはMySQLを前提にしたシステムなのに、わざわざプラグイン(SQLite Integration)を使ってSQLiteを使う理由は、MySQLが使用不可のプランでもWordPressが使えるためです。当初はプラン変更して普通にMySQLで運用するつもりでしたが、ローカルでの作業中にSQLiteでもやろうと思えば出来ることを知って、SQLiteに移行しました。多くのアクセスがあったり、データベースが大きいサイトなら素直にMySQLが使えるプランに変更すれば良いと思うのですが、そうではないので費用対効果が悪く思いました。
SQLiteのデータベースは単体のファイルですので、バックアップや移植、差し替えが用意なのもメリットです。本番・テスト・ローカルの各環境でささっと移植が出来ます。
データベースの最適化
SQLiteをデータベースにすると、互換性がなく使えないプラグインがあるようです。特にデータベースを扱ったりキャッシュするようなものは、その可能性が高いようです。
ですので、プラグインなしで最適化出来ないかなぁと色々調べました。他にも方法はあるかもしれませんが、2つ見つけました。
リビジョンの削除
デフォルトでは履歴や自動保存が有効になっており、履歴が残る度にデータベースが肥大化します。
リビジョンは記事が保存されているテーブルのフィールド名がpost_typeの列に、「revision」という値で保存されおり判別出来ます。それをSQLコマンドで削除します。
因みにリビジョン機能や自動保存は以下をwp-config.phpやfunctions.phpに追加することでオフに出来ます。個人的にリビジョン機能は必要ないなと思い、最近オフにしました。潔癖症ではありませんが、いつの間にかどんどんIDが増えていってデータベースが大きくなるのが気になるのです。スクリプトを作ったけど、オフにしてしまったので、これに関してはあまり意味なかったかも(笑)
<?php // リビジョン停止 // (wp-config.phpの「require_once(ABSPATH . ‘wp-settings.php’);」より前に記述) define('WP_POST_REVISIONS', false); // 自動保存停止 (functions.php) function disable_autosave() { wp_deregister_script('autosave'); } add_action( 'wp_print_scripts', 'disable_autosave' ); ?>
reindex / vacuumコマンド
reindexとvacuumコマンドを実行して最適化します。SQLiteを採用しているFireFoxでも有効で、これでFireFoxが軽くなるといった情報がたくさんありました。
これらのコマンドはreindexは名前の通りインデックスの再構築、vacuumは空き領域の開放を行うようです。
上の2項目両方を実行することで大幅削減
自分のデータベースでは、リビジョンの削除、若しくはreindex / vacuumコマンドだけだと殆ど容量は減りませんでした。しかし両方を行うことで容量が60%程度になりました。
具体的なことは分かりませんが、データベースの仕様上データを削除しても容量は減らないけど、vacuumしたことで削除したリビジョン分の空き領域が開放されたからなのかなと思いました。
仮想OSの仕様にも似てますね。予め最大容量を確保しておくオプションもありますが、デフォルトでは最大容量を決めておいて、使用に応じて肥大化するようになってます。容量が増えてからデータを削除しても仮想OSの容量は減りません。クリーンアップをすることで容量が削減されます。
スクリプトの仕様・解説
以下スクリプトの内容です。
' wpdb-optimize.vbs Option Explicit '----------------------------------------------------- ' データベース名を設定。指定のファイル名以外は処理を行わない。 Const WP_DB_FILE_NAME = ".ht.sqlite" ' セキュリティのためにプレフィックスを変えてる場合は変更。 Const WP_DB_POSTS_TABLE_NAME = "wp_posts" ' 確認用のダイアログで表示するリビジョンレコードの数。 Const DISP_REVI_LIMIT_NUM = 50 '----------------------------------------------------- Dim dbPath, wssn, reviData Dim RS, con, fs, oArgs Dim done, doReviDel, doCmd Dim crNum, divPosNum, diffNum done = False doReviDel = False doCmd = False wssn = WScript.ScriptName Set fs = CreateObject("Scripting.FileSystemObject") Set oArgs = WScript.Arguments If oArgs.Count = 0 Then WScript.Quit dbPath = oArgs(0) Set oArgs = Nothing If fs.GetFileName(dbPath) <> WP_DB_FILE_NAME Then MsgBox "WP用のDB " & WP_DB_FILE_NAME & " をドラッグしてください。", vbExclamation, wssn WScript.Quit End If If MsgBox("リビジョンを削除しますか?", vbYesNo, wssn) = vbYes Then doReviDel = True If MsgBox("reindex & vacuum コマンドを実行しますか?", vbYesNo, wssn) = vbYes Then doCmd = True If Not doReviDel And Not doCmd Then WScript.Quit Set con = CreateObject("ADODB.Connection") con.Open "DRIVER=SQLite3 ODBC Driver;Database=" & dbPath & ";" ' リビジョン削除 If doReviDel Then On Error Resume Next Set RS = con.Execute("SELECT post_title FROM " & WP_DB_POSTS_TABLE_NAME &" WHERE post_type = 'revision';") If Err.Number = -2147467259 Then MsgBox "テーブルがありません。定数に正しいテーブル名を入力してください。", vbExclamation, wssn Else reviData = RS.GetString If Err.Number = 3021 Then MsgBox "リビジョンのレコードはありませんでした。", , wssn Else crNum = UBound(Split(reviData, vbCr)) If crNum > DISP_REVI_LIMIT_NUM Then divPosNum = InStrCnt(reviData, vbCr, DISP_REVI_LIMIT_NUM) diffNum = crNum - DISP_REVI_LIMIT_NUM reviData = Left(reviData, divPosNum) & vbCrLf & "その他 " & diffNum & " 件" End If If MsgBox("以下の post_title のリビジョンを削除します。よろしいですか?" & vbCrLf & vbCrLf & reviData, vbYesNo, wssn) = vbYes Then con.Execute("DELETE FROM " & WP_DB_POSTS_TABLE_NAME &" WHERE post_type = 'revision';") done = True End If End If End If RS.Close Err.Clear On Error Goto 0 Set RS = Nothing End If ' reindex & vacuum コマンド If doCmd Then con.Execute("vacuum;") con.Execute("reindex;") done = True End If con.Close Set con = Nothing Set fs = Nothing If done Then MsgBox "最適化が完了しました。", , wssn Function InStrCnt(ByVal strString, ByVal strSearch, ByVal lngCount) Dim num, i num = InStr(1, strString, strSearch) Do While num > 0 i = i + 1 If i = lngCount Then Exit Do num = InStr(num + 1, strString, strSearch) Loop InStrCnt = num End Function
データベースをVBSファイルにドラッグしたり、コンテキストメニューの「送る」に登録しておいて、そこに投げます。ファイルは1ファイルのみです。
リビジョン削除とreindex / vacuumコマンドをそれぞれリクエストに応じて行います。初めにダイアログで処理を行うか確認し、Yesだったものだけ処理します。両方Noなら何も処理しません。
もし使用される場合は、バックアップの上自己責任でお願いします。
定数について
定数で任意の値を設定するようにしています。特に、テーブル名はセキュリティの関係上プレフィックスを変えていることがままあると思うので、変えている場合は変更します。
データベースのファイル名は、一応一意の名前でないと動作しないようにしました。
確認用のダイアログで表示するリビジョンレコードの数は、スクリプトでダイアログでしか表示出来ない仕様上、表示するレコード数を制限することにしました。件数が多いと、ディスプレイをはみ出してボタンが押せなくなってしまうのです。
もっとも、マウス位置でウィンドウ移動のようなツールを使用していたら一応移動して押せますが(笑) 自分はテスト中ボタンが押せなくなって、これでウィンドウを上の方に移動してボタンを押しました。
改行を全角スペースに変換してもっと表示出来るようにしようとも考えましたが、見辛かったのでやめました。自分のWUXGAのディスプレイでは50〜60件程度がちょうど良いように思いました。
ここで設定した数以上のレコードは省略して、その他◯件と表示するようにしました。
On Error Resume Next
リビジョンのレコードがなかった時に、処理が止まってしまうのでOn Error Resume Nextで先に進むようにしました。この時とテーブル名が間違っていた時のエラー処理を書きましたが、もし他にも何か起こった場合、On Error Resume Nextなのでエラーが表示されません。