MovableTypeの迷惑コメント・迷惑pingを削除してMySQLを整理整頓

MovableTypeは世界中に普及しているおかげで、当然スパムの攻撃対象にもなる。このブログの場合は迷惑コメントが月に約2500件、迷惑トラックバックが月に1000件来る。毎月数件は残念ながらフィルターをすり抜けてブログに表示されてしまうが、99.9%程度はMovableTypeのスパムフィルター（SpamLookupプラグイン、BanASCIIプラグイン）で排除しているのでブログには表示されない。BanASCIIプラグインは英数字しか含まないスパムを判定するフィルターで、これで海外からの大抵のスパムを排除できる。SpamLookupプラグインでブラックリストの参照先を登録しておくとこのブラックリストに掲載されているIPからの投稿をスパムと判定できる（アドレスチェックに「bsb.spamlookup.net, niku.2ch.net,」、ドメインチェックに「bsb.spamlookup.net, sc.surbl.org, rbl.bulkfeeds.jp」などを登録しておくと良い）。

これでスパムは迷惑フォルダーに放り込まれるし、迷惑フォルダーはワンクリックで中身を空に出来るのだが、中身を空にしたところでMovableTypeのコンソールパネルで表示されないだけでMySQL内にはデータとして保存されている（phpMyAdminで確認できる）。これは、「データ領域の解放操作は、レコードの削除のたびに実施するには重すぎる処理で、再利用した方が大抵の場合都合が良い。そのため1度使われたデータ領域は解放されずに再利用の為に保持される。」ということと「データレコードの再編成、削除などを繰り返し行うと、データベース内に無駄な未使用領域ができる（オーバーヘッド）」というデータベースの仕様によるものである。

そこで、MovableTypeの迷惑コメント、迷惑pingをコンソールパネルで「空にする」で削除してみてから、phpMyAdminにログインしてみよう。オーバーヘッド領域に何MBのデータ領域が使われているかを確認することが出来る（図のオレンジ枠部分）。特に、mt_commentとmt_tbpingのオーバーヘッド領域は過去に削除されたスパムがため込まれている部分だ（図の紫枠部分）。オーバーヘッド領域を無くし最適化するために、最適化したいものにチェックを入れて、「チェックしたものを」の「テーブルを最適化する」を選択しよう（図の緑枠部分）。おそらく数秒も待たずにオーバーヘッド領域が無くなり、同時にデータベースの容量も小さくなることだろう。MovableTypeのログも不必要であれば削除しても動作には問題ない。

ちなみにこのMovableType 3.37では2004年MovableType 2.661からの4年間の運用のうちにデータベースの容量は肥大を続け、2008年9月のSQLiteのサイズは31MB。これをMySQLに変更したところ17MBまで減少したが、さらに貯まりに貯まっていた数万件のスパムをMovableTypeの迷惑コメントフォルダから一括削除したところ7.5MBまで容量を圧縮。さらにさらに、オーバーヘッド領域の最適化を行って容量は5.8MBまでに抑えることが出来た。

- Movable Typeが利用するデータベースをメンテナンスする
- 「MySQL」データベースの容量削減

You should take part in a contest for one of the best blogs on the web. I will recommend this site!

Pretty good article. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.

I’d want to thank you for the efforts you have created written this post.

hello there and thank you for your info – I’ve certainly picked up something new from right here. I did however expertise several technical issues using this web site, since I experienced to reload the website a lot of times previous to I could get it to load properly. I had been wondering if your web host is OK? Not that I am complaining, but slow loading instances times will very frequently affect your placement in google and could damage your high quality score if advertising and marketing with Adwords. Well I am adding this RSS to my email and could look out for a lot more of your respective intriguing content. Ensure that you update this again soon..

I’d have to check with you here. Which is not something I usually do! I enjoy reading a post that will make people think. Also, thanks for allowing me to comment!

I discovered your blog site on google and check a few of your early posts. Continue to keep up the very good operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading more from you later on!…

Thanks for the useful information! My partner and i wouldn’t have found this on my own!

Have a excellent day!. I feel like I’m always looking for interesting things to read about a variety of niches, but I manage to include your blog among my reads every day because you have honest entries that I look forward to. Here’s hoping there’s a lot more great material coming! cellulite treatment

I found your entry interesting do I’ve added a Trackback to it on my weblog and my seotons :)……

What i do not realize is actually how you're not actually much more well-liked than you may be right now. You are very intelligent. You realize thus considerably relating to this subject, produced me personally consider it from a lot of varied angles. wos Its like men and women aren't fascinated unless it’s one thing to accomplish with Lady gaga! Your own stuffs nice. Always maintain it up!

This is really interesting, You are a very skilled blogger. I've joined your rss feed and look forward to seeking more of your wonderful post. meble drewniane Also, I have shared your web site in my social networks!

What’s Happening i'm new to this, I stumbled upon this I have found It positively helpful and it has aided me out loads. Overnight banners I hope to contribute & help other users like its helped me. Good job.

Have a fantastic day!. Thought I would comment and say cool theme, did you code it for yourself? It’s really really good! Cellulite Treatment

Have a good day!. Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful for me. hgh supplements

Have a good day!. Do you mind if I quote you on my blog if I link back to your website? Dress Up Games

Just a fast hello and also to thank you for discussing your ideas on this page. I wound up in your blog right after researching physical fitness connected issues on Yahoo… guess I lost track of what I had been performing! Anyway I’ll be back as soon as again within the potential to examine out your blogposts down the road. Thanks!

Hey There. I found your blog using msn. This is a really well written article. I will be sure to bookmark it and come back to read more of your useful info. Thanks for the post. I’ll certainly return.

This was a genuinely extremely excellent post. In theory I’d prefer to publish like this also - getting time and actual effort to make a great piece of writing… but what can I say… I procrastinate alot and by no means appear to obtain a little something done.

Hi, i think that i saw you visited my site thus i came to “return the favor”.I am attempting to find things to enhance my site!I suppose its ok to use some of your ideas!!

After study a few of the blog posts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.

Have a fantastic day!. I have read a few of the articles on your website now, and I really like your style of blogging. I added it to my favorites blog site list and will be checking back soon. Please check out my site as well and let me know what you think. Colon Cleansing

This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!

i want to subscribe to your site,please tell me how?

Have a good day!. There are some interesting points in that clause but I don’t know if I see all of them middle to center . There is some validity but I will take hold opinion until I look into it further. Good clause, thanks and we want more! Added to FeedBurner too. quit smoking aids