のんびり雑記帳

November 08(Wed), 2017

SQLite

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件程度がちょうど良いように思いました。


ここで設定した数以上のレコードは省略して、その他◯件と表示するようにしました。


SQLite3 ODBC Driver

上記スクリプトはSQLite3 ODBC Driverというドライバーを使っており、これをインストールする必要があります。以下よりダウンロード出来ます。


http://www.ch-werner.de/sqliteodbc/


On Error Resume Next

リビジョンのレコードがなかった時に、処理が止まってしまうのでOn Error Resume Nextで先に進むようにしました。この時とテーブル名が間違っていた時のエラー処理を書きましたが、もし他にも何か起こった場合、On Error Resume Nextなのでエラーが表示されません。


SQLite ポケットリファレンス

SQLite ポケットリファレンス

投稿したコメントは管理者が承認するまで公開されません。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/Shinez/20171108/p1