MyNETSソーシャルマップ検索の高速化(Tritonn対応)

2009年9月にUsagi-projectに寄稿したもの。

現状のMyNETSでデータ量が増えて出てくるボトルネックとして、以前日記検索がlikeで行われている状況を改善するためTritonnを使った高速化を紹介しました。

これでしばらく高負荷が発生するのを防げていたのですが、次のボトルネックが発生してきました。それが個人TOPページのサムネ下「ソーシャルマップ」のページの表示です。

このページを開くと全日記本文+コメント及び全トピックコメントからGoogleMap用のcmdをlike検索するため、大きな負荷が発生することになりました。

そこでここもTritonnで対策したので、対応方法を紹介しておきます。

ーーー
■データベースにindex追加

[code]
ALTER TABLE c_diary_comment ADD FULLTEXT fullindex USING SENNA,NGRAM,NORMALIZE (body);
ALTER TABLE c_commu_topic_comment ADD FULLTEXT fullindex USING SENNA,NGRAM,NORMALIZE (body);
[/code]

■webapp/lib/db/read/gmaps.phpを修正

[code]
function p_h_gmaps_list_all_search_c_diary4c_diary($keyword, $page_size, $page)
{

//and検索を実装
//subject,body を検索
if ($keyword) {
//全角空白を半角に統一
$keyword = str_replace(‘ ’, ‘ ‘, $keyword);

$keyword_list = explode(‘ ‘, $keyword);
foreach ($keyword_list as $word) {
$word = check_search_word($word);
$where_d .= ‘ AND (‘. MYNETS_PREFIX_NAME .’c_diary.subject LIKE “%’.$word.’%” OR ‘. MYNETS_PREFIX_NAME .’c_diary.body LIKE “%’.$word.’%”)’;
$where_dc .= ‘ AND (‘. MYNETS_PREFIX_NAME .’c_diary.subject LIKE “%’.$word.’%” OR ‘. MYNETS_PREFIX_NAME .’c_diary_comment.body LIKE “%’.$word.’%”)’;
}
}
[/code]

の部分を以下に修正(MYNETS_PREFIX_NAMEは無しで仮定)

[code]
function p_h_gmaps_list_all_search_c_diary4c_diary($keyword, $page_size, $page)
{

//and検索を実装
//subject,body を検索
if ($keyword) {
//全角空白を半角に統一
$keyword = str_replace(‘ ’, ‘ ‘, $keyword);
//SQL Injection対策
$keyword = str_replace(‘\\’, ”, $keyword);
$keyword = str_replace(‘”‘, ”, $keyword);
$keyword = str_replace(‘;’, ”, $keyword);
$keyword = str_replace(‘)’, ”, $keyword);

$keyword_list = explode(‘ ‘, $keyword);
foreach ($keyword_list as $word) {
$word = check_search_word($word);
$where_d .= ‘ AND MATCH (c_diary.subject, c_diary.body) AGAINST (“‘ .$keyword. ‘” IN BOOLEAN MODE)’;
$where_dc .= ‘ AND (MATCH (c_diary.subject, c_diary.body) AGAINST (“‘ .$keyword. ‘” IN BOOLEAN MODE) OR MATCH (c_diary_comment.body) AGAINST (“‘ .$keyword. ‘” IN BOOLEAN MODE))’;
}
}
[/code]

以下の検索コードをそれぞれ置換する。全部で17カ所。
(MYNETS_PREFIX_NAMEは無しで仮定)

日記本文検索部分
[code]
” WHERE ” . MYNETS_PREFIX_NAME . “c_diary.body like ‘%%'” .
[/code]
 ↓
[code]
” WHERE match(c_diary.subject, c_diary.body) against (\”%'” .
[/code]
 ↓
[code]
” WHERE match(c_diary_comment.body) against (\”%'” .
[/code]
 ↓
[code]
” WHERE match(c_commu_topic_comment.body) against (\”

, ,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (まだ評価されていません)
Loading...

MyNETSの日記検索高速化(Tritonn導入)

2009年6月にUsagi-Projectに寄稿したもの。

日記データが膨大になってきて日記検索のI/Oがサーバ全体の足を引っ張るようになってきたので、検索の高速化をしてみました。
方法はmysqlを日本語全文検索対応のTritonnプロジェクト版に変更し、それに合わせて検索部分を修正します。

手元の日記データ37万件400Mくらいのc_diaryテーブルをPentiumM1.6GHzのマシンで検索したところ(検索クエリは後述)検索速度が15倍以上になりました。

 LIKE検索 平均5.3秒
 Tritonn検索 平均0.3秒

以下手順。

1)Tritonnインストール(CentOS5サーバの場合

まずは既存のDBを念のためバックアップ
[code]# mysqldump -u root -p mynets_db > dump.sql[/code]

mysqlを置き換えるためmysqlのインストールを確認
[code]# rpm -qa | grep -i mysql
mysql-5.0.45-7.el5[/code]

mysqlをremove
通常dovecotで利用されているので、dovecotを停止してから作業
[code]# service dovecot stop
# yum remove mysql[/code]

perl-DBIのインストールを確認
[code]# rpm -qa | grep -i perl-DBI
perl-DBI-1.52-1.fc6[/code]

最新のtritonn-1.0.12-mysql-5.0.67パッケージ群をダウンロード
[code]# mkdir download
# cd download/
# wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-client-5.0.67-tritonn.1.0.12.i386.rpm
# wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-devel-5.0.67-tritonn.1.0.12.i386.rpm
# wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-server-5.0.67-tritonn.1.0.12.i386.rpm
# wget http://iij.dl.sourceforge.jp/tritonn/36448/MySQL-shared-5.0.67-tritonn.1.0.12.i386.rpm
# wget http://iij.dl.sourceforge.jp/tritonn/36448/mecab-0.97-tritonn.1.0.12.i386.rpm
# wget http://iij.dl.sourceforge.jp/tritonn/36448/mecab-ipadic-2.7.0.20070801-tritonn.1.0.12.i386.rpm
# wget http://iij.dl.sourceforge.jp/tritonn/36448/senna-1.1.4-tritonn.1.0.12.i386.rpm[/code]

以下の順番でインストール
[code]# rpm -ivh mecab-0.97-tritonn.1.0.12.i386.rpm
# rpm -ivh mecab-ipadic-2.7.0.20070801-tritonn.1.0.12.i386.rpm
# rpm -ivh senna-1.1.4-tritonn.1.0.12.i386.rpm
# rpm -ivh MySQL-shared-5.0.67-tritonn.1.0.12.i386.rpm
# rpm -ivh MySQL-client-5.0.67-tritonn.1.0.12.i386.rpm
# rpm -ivh MySQL-server-5.0.67-tritonn.1.0.12.i386.rpm
# rpm -ivh MySQL-devel-5.0.67-tritonn.1.0.12.i386.rpm[/code]

MySQLはインストール後すぐに起動するのでrootのパスを設定
[code]# /usr/bin/mysqladmin -u root password ‘rootpassword'[/code]

MySQLへのログイン確認
[code]# mysql -u root -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.67-modified MySQL Community Server (GPL) (portions (c) Tritonn Project)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>[/code]

これでTritonnのインストール完了

入れ替え前のDBもたぶん大丈夫なはずだが、もし使えない時には新規にDB作成して、バックアップデータをリストアする
[code]# mysql -u root -p
mysql> CREATE DATABASE mynets_db DEFAULT CHARACTER SET utf8;
mysql> quit
# mysql -u root -p mynets_db < dump.sql[/code] 2)Tritonn用のindex作成
[code]# mysql -u root -p
mysql> use mynets_db;
mysql> ALTER TABLE c_diary ADD FULLTEXT fullindex USING NGRAM,SECTIONALIZE (subject, body);[/code]

件数が多いと相当時間がかかるのでじっと待つ
完了したらテスト検索してみる(testで検索)
[code]mysql> SELECT * FROM c_diary WHERE (public_flag = ‘public’ or public_flag = ‘open’) AND MATCH (subject, body) AGAINST (“test”) ORDER BY r_datetime DESC;[/code]

LIKE検索と時間を比較してみる
[code]mysql> SELECT * FROM c_diary WHERE (public_flag = ‘public’ or public_flag = ‘open’) AND (subject LIKE ‘%test%’ or body LIKE ‘%test%’) ORDER BY r_datetime DESC;[/code]

3)MyNETSの検索部分の修正

webapp/lib/db/read/diary.phpの新規日記検索の項目を以下のように修正

$s[code]elect = ‘SELECT *’;
$from = ‘ FROM ‘ . MYNETS_PREFIX_NAME . ‘c_diary’;
$where = ” WHERE (public_flag = ‘public’ or public_flag = ‘open’)”;

if ($keyword) {
//全角空白を半角に統一
$keyword = str_replace(‘ ’, ‘ ‘, $keyword);
//SQL Injection対策
$keyword = str_replace(‘\\’, ”, $keyword);
$keyword = str_replace(‘”‘, ”, $keyword);
$keyword = str_replace(‘;’, ”, $keyword);
$keyword = str_replace(‘)’, ”, $keyword);
$keyword = check_search_word($keyword);

$where .= ‘ AND MATCH (subject, body) AGAINST (“‘ .$keyword. ‘” IN BOOLEAN MODE)’;

}
$order = ” ORDER BY r_datetime DESC”;[/code]

, , , ,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (まだ評価されていません)
Loading...

無償SSL証明書の入手 【5/11追記】

最新のブラウザでないと対応してないので注意。
携帯等の最新のもの、もしくはCA証明書追加ができるタイプのものではないと利用は難しいと思われます。昨年10月末ごろは日本からの申請に対応していなかったのですが、現在はjapanを選択すると都道府県名が適切にリストされているようです。

https://www.startssl.com/

個人の住所や電話番号が必須で、登録内容についてはちゃんと確認しますって書いてるけど、どう確認するのか不明。一度入手してみるべきか…。SSL証明書の一部に登録内容が公開されるとも書いてあるんだけど、どこまで出るのか明示はない。

特定商取引法対応で個人情報開示している個人事業主さんには無償で使えるしブラウザを選ぶという点が問題なければいい選択肢かもしれません。

無償取得の手続きは以下のリンク先から
https://www.startssl.com/?app=12

2011/5/11 追記
 TLSの実験のため複数の正規証明書が利用したかったので、こちらのページを参照して実際にStartSSLで無償の証明書を取得してみました。以前懸念していたSSL証明書の一部に登録内容が公開されるというのは問合せ用メールアドレスのみでした。ドメインの所有を確認される際に利用するpostmasterやwebmasterのアドレスにしておけばあまり問題ないと思われます。また無償で発行されるのは1つだけではなく、所有さえしていれば異なるドメイン名やホスト名についても複数の証明書が取得可能ですので、数が多くなると1年更新管理は大変ですが、非常に嬉しい使い勝手を提供してくれています。

 StartSSLの証明書については既に多くのブラウザが対応しており、iPad/iPhoneやAndroid2.2以降(001HTやWhisperCoreの入ったNexusOneでも)でメールの同期やブラウザアクセスで問題なく利用できている(証明書の確認ダイアログ等がでない)ので、1年毎更新とはいえ無償で安心して利用できるという点では非常に嬉しい選択肢だと思います。国内携帯についても古いものは未対応になりますが、スマートフォンが増えてくれば結果的に対応している機種比率が上がっていくので、携帯アクセスがメインでない限りは今後利用検討の対象にしてもいいのではないかと思われます。

 90年代後半では取得手続きやコストでそれなりに大変だったSSL証明書取得がここまでシンプル且つ低コストになるというのは本当に感慨深いものがあります。StartSSLが後発でありながら新興CAとしてシェアを拡大するためには、無償の証明書で認知とシェアを獲得し標準とされるブラウザやOSに積極的にroot証明を組み込んでもらえるように働きかける必要があったということだと推測しますが、個人向け無償というのは大きな判断だったと思います。しばらくいろいろと試させてもらおうと思っています。

,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (まだ評価されていません)
Loading...

mysqlデータベース障害対応手順

問題のあるテーブルを特定
/var/log/mysqld.log を確認

# mysqlcheck -u root -p -c -q -A
念のためデータベース全チェック。

# cd /var/lib/mysql/dbname/
# mysqld_multi stop 1 (1番のmysql停止)

通常のmysql停止コマンドは
# service mysqld stop
ここでは複数起動設定のためmysqld_multiを使用

CPU100%張り付き等でプロセスが終了してくれない場合には
# ps aux | grep mysql
プロセス番号を確認して強制終了
# kill -9 プロセス番号

# mv tablename.TMD tabelname.TMD.org
# myisamchk -v -q tablename.MYI

-qがダメなら-rで。
# myisamchk -v -r tablename.MYI

修復が終わったら確認

# mysqld_multi start 1
# mysqlcheck -u root -p -c -q -A

データに問題がなければ、念のためデータベースのdump
# mysqlqump -u root -p -e dbname > dbname.sql

以下のやり方もあるけど時間かかるのでオススメしない。
# mysql -u root -p
> use databasename
> check table tablename quick;
> repair no_write_to_binlog table tablename quick;

だめなら

> repair no_write_to_binlog table tablename use_frm;

長時間かかる。

,
とりあえず付けておく無駄ではなかったなまぁまぁ読めたちょっと役に立ったかなかなり良かったかも (まだ評価されていません)
Loading...