小野マトペの業務日誌(アニメ制作してない篇)

はてなダイアリーの閉鎖をうけ、旧ブログ http://d.hatena.ne.jp/ono_matope/ から移行しました。続きは→ http://matope.hatenablog.com/

ふぁぼったーの負荷分散の計画をあれこれ考えてみた。

Programming

今日一日調べたこととかまとめてみる。

問題点

  • 次回リリースの新機能はDB select負荷がかなり掛かるはずである。
  • 現状でもデータLOADによる負荷が10に達し、selectにスロークエリ、接続エラーが出ている。DBの負荷分散が急務である。

参考: Load Average

参考: Slow Query

  • あと、STATUSテーブルが2GBに達しようとしているので、デフォルトのサイズ制限4GBが見えてきた。再定義できるけど。

垂直分割対応案

日本語版DBと英語版DBのデータベースとクローラを別ホストに格納する
レプリケーションする
  • Webが参照するスレーブDBから、クローラの実行負荷を分離する事が出来る。
  • しかし、INSERT処理はマスタにもスレーブにも等しく走るので、このDBへの挿入負荷をスレーブDBから分離する事はできない。
  • 問題はINSERT時のテーブルロックだと思われるので、クローラ負荷が分離したところで効果はやや疑問。

水平分割対応案

MERGEテーブルを利用する
  • 複数のテーブルをマージするMERGEテーブルを利用する
  • INSERTのロック範囲を狭められるので効果はありそう。
  • 欠点
    • INSERT時の主キー同一性チェックがINSERT_METHODで指定した挿入テーブルに対してしか行われないので、挿入する対象テーブルはロジック側で完全に制御する必要がある。ぶっちゃけ面倒。
    • TritonnがMERGEテーブルに非対応。ふぁぼったー検索をMySQL以外で実装する必要がある。
パーティショニングを利用する
  • MySQL5.1の新機能。一つのテーブルを複数のパーティションに分割する。
  • MERGEテーブルと比べて、主キー同一性チェックの問題がないらしくてうれしい。より透過的？
  • 欠点
    • そもそもMySQL5.1なので、今はTritonnが使えない。
    • Groonga(単品)に移行するか、Groonga MySQLストレージのリリースを待つしかない

結論

こうして考えると、水平分割はふぁぼったー検索がTritonnに依存している関係上、一筋縄ではいかない。
ふぁぼったー検索をTritonn以外の方法(例えばGroonga単体)で実装すれば、ふぁぼったーはTritonn(=MySQL5.0.45)の呪縛から解き放たれ、MySQL5.1にアップグレードしモダンなパーティショニングを使う事ができる(MERGEテーブルは使いたくない)。
しかし、ふぁぼったー検索は、通常の全文検索だけでなく

  • 動的に増加するfav数によるフィルタリング
  • ユーザーによるしぼりこみ
  • ふぁぼりユーザー(fav_by)によるしぼりこみ
  • ユーザーのprotect状態変更への追従
  • 発言削除への追従

と、DBから分離して管理するには面倒な要件が盛りだくさんなので、片手間で独自実装するのは現実的とは思えない。つまり、Groonga MySQLストレージを待ちたい。


一方垂直分割に関しては、レプリケーションとDB分離、どちらがメリットがあるかすぐには分からないが、どちらも仕組みはとても簡単なので、どちらとも評価して決めればよい。総論として、ふぁぼったーDB負荷分散戦略としては、


「Groonga MySQLストレージエンジンを待望つつ、レプリケーションまたは日本・英語DBの分離を行う」


で決まりかと思われる。と結論が出たところで寝ます。おやすみなさい。

MySQLのクエリキャッシュヒット率を計算するワンライナー書いた

Programming

MySQLのパフォーマンスチューニングをしていて避けては通れない要素、key_buffer_size設定。
参考サイトによると、MyISAMインデックスに割り当てられるキャッシュサイズであるkey_buffer_size値は、以下のように、インデックスを利用するリクエストにおける、ディスクread数の割合から求められる"キャッシュヒット率"によって増減を決定されるらしいですが、

キーキャッシュのヒット率 = 100 - ( key_reads / key_read_requests × 100 )

DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！

この値は特に起動直後はかなり変動し、毎回SHOW STATUSして手動で計算するのが面倒くさいので、これを一発で求めるワンライナーを作りました。

キャッシュヒット率(単位:％)

mysqladmin -u root -p extended-status | grep -e Key_read_requests -e Key_reads |sort -f|paste -s|awk '{print 100-100*$4/$9}'

出力結果

94.3086

キャッシュミス率(単位:％)

mysqladmin -u root -p extended-status | grep -e Key_read_requests -e Key_reads |sort -f|paste -s|awk '{print 100*$4/$9}'

出力結果

5.6928

うむ。キャッシュミス率は0.3%以内に収めるのが理想的だそうなので、まだまだヒット率が小さいですね。key_buffer_sizeを増やした方がいいのかもしれません。

参考サイト

DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！
[ThinkIT] 第5回：key_buffer_sizeの違いによるパフォーマンス比較 (2/3)

ふぁぼったーで使ってるPHPのプロファイリング用の関数

Programming

PHPでパフォーマンスチューニングをする時、特定のロジックの実行にどれくらい時間がかかっているのか知りたいことがよくある。よくあるというか、チューニングにプロファイリングは必須だ。世の中には高度なプロファイリングツールがあれこれあるみたいだけど、高機能すぎて僕にはよく分かんないです。
という訳で多分みなさんそれぞれ自前でパフォーマンスを計測してると思うんだけど、自分が普段ふぁぼったーのWeb開発で使っている、PHPの任意の部分の処理時間を計測するスニペットを公開してみます。やっつけですが。

使い方

getMS()関数は、前回getMS()が呼ばれた時刻からの経過時刻をミリ秒で返します。
すなわち、処理時間を計測したい部分の開始地点と終了地点でそれぞれgetMS()を呼び、終了地点での戻り値をprintするなりすれば任意の部分の処理時間を計測できます。

<?php
function getMS(){
    static $oldTime=0;
    list( $micro, $time ) = explode(' ',microtime());
    $newTime=$time+=$micro;
    $diffTime=$newTime-$oldTime;
    $oldTime=$newTime;
    return $diffTime;
}

getMS(); //計測開始

//
// 処理step 1
//

print ("step 1. ".getMS()."s<br>");  //step 1処理時間出力

//
// 処理step 2
//

print ("step 2. ".getMS()."s<br>");  //step 2処理時間出力
?>

結果

step 1. 0.0012s
step 2. 0.0003s

static変数を利用している関係上、入れ子構造で利用できないのが難点ですが、そんなときはgetMS2()を定義して(ry

あわせて読みたい

http://labs.unoh.net/2006/10/php_print_debug.htmlにインスパイヤされて作った、デバッグモードでのみ表示されるprintみたいな関数も使ってます。みたまんま。DEBUG定数の状態で出力を制御します。

<?php
define('DEBUG',true);

function preprint($var){
    if(defined('DEBUG') && DEBUG){
        print $var;
    }
}
?>

さらに横着版

<?php
function preprint($var){
    if(true){
        print $var;
    }
}
?>

PDO::MySQLでプレースホルダにバインドしたinteger変数がexecute後にstring化した

Programming

整数の$count変数をSQLのプレースホルダにバインドしてexecuteして、結果行数と$count値を比較する、こういうコードを書いた

  1 <?php
  2 
  3   $pdo = new PDO("mysql:host=localhost;dbname=xxx",xxx,xxx);
  4   $count = 10;
  5   $sql = "select * from SEEDUSER limit :count;";
  6 
  7   echo "count is $count\n";
  8   echo "before prepare ".gettype($count)."\n";
  9   $stmt = $pdo->prepare($sql);
 10   echo "after prepare ".gettype($count)."\n";
 11   $stmt->bindParam(':count',$count,PDO::PARAM_INT);
 12   echo "after bind ".gettype($count)."\n";
 13   $stmt->execute();
 14   echo "after execute ".gettype($count)."\n";
 15   $result = $stmt->fetchAll();
 16 
 17   echo "result length is ".count($result)."\n";
 18   if(count($result) === $count){
 19     echo "count(result) === count\n";
 20   }else{
 21     echo "count(result) !== count\n";
 22   }
 23 ?>

出力はこうなった

count is 10
before prepare integer
after prepare integer
after bind integer
after execute string
result length is 10
count(result) !== count

SQL文のプレースホルダにバインドした整数型の$count変数が、$stmt->execute()後にstring型に変化していることがわかるだろうか。
このため、18行目の条件文では結果行数が$countと同じ時に真を期待していたが、実際には偽となっている。


なにこれこわい

実行環境

  1. CentOS 5
  2. php 5.2.6
  3. pdo_mysql 5.0.67

ふぁぼったーv3リリースのお知らせ

ふぁぼったー 作ったモノ

先ほど、「みんながFavoriteした発言でつくる、全自動Twitterまとめサイトもどき」ふぁぼったーの新バージョンをリリースしました*1。詳細は以下に。

http://favotter.matope.com

検索ができるようになりました！

ふぁぼったーが収集したつぶやきが、全文検索できるようになりました！

  1. 検索フォーム
  2. 各ユーザーのふぁぼられページ

から検索できます。
自分のふぁぼられた発言やFavoriteした発言の中から検索できるので、Favoriteをブックマーク検索のように使えます。例えば「中目黒のおいしいお店の情報をふぁぼったんだけど、どこだっけ…」という時でも、このように検索したり……

↓↓↓

もちろん、モバイルからも利用可能です。
(RSS対応検討中)

発言の前後が読めるようになりました！

各発言の前後の発言が読める、アラウンドビュー機能を搭載しました。これにより、発言の個別ページから

  • 「その発言の前後50件」
  • 「そのユーザーの発言の前後50件」
  • 「その発言でリプライを飛ばしているユーザーを含んだ発言の前後50件」
  • 「その日のBESTふぁぼられ」

にジャンプする事ができるようになります。
例：このように単体では意味が取れない発言も

↓↓↓　　
アラウンドビューで流れを掴むことができます

ふぁぼられ/ふぁぼりページの"まとめて表示"に対応！

右上のユーザー検索窓にカンマ(,)区切りでユーザー名を入力することで、複数のユーザーをまとめて表示することが出来るようになりました。
例えばこのように、botとその作者のふぁぼられを表示することができます。

　　　↓↓↓
ふぁぼったー fuba,fuba_recorder のふぁぼられ

自分だけのふぁぼったーを！

同様に「ふぁぼり」も複数ユーザーに対応したので、自分の好みのユーザーを追加すれば、お気に入りのユーザーのふぁぼりによる「自分だけのふぁぼったー」を構築することが出来ます(256人まで*2 )。
ふぁぼったー irons,Shimpei Makimoto,Issei Naruta,makoto kataigi,小野マトペ,とはえ2.0,梅蔵,よこち / Hayato YOKOTA のふぁぼり

クロール範囲の拡大(英語ユーザー)

今まで、英語ユーザーを10万人程度しか認識していませんでしたが、クローラの改良で150万人ほど認識するようになりました。
これにより、より正確なふぁぼられ集計が可能となりました。

参考：現在のFavoriteクロール範囲

日本語ユーザーは2.0時代からかわらず5000人ですが、英語ユーザーは倍の10,000人のクロールを実施中です。

日本語
5,000人
英語
10,000人

高速化

すっごい速いよ！

その他バグフィックス

おまけ：フル自宅サーバー化

今までWeb→さくらプレミアム、DB→自宅という変な構成でしたが、さすがに限界が来たみたいなのでWebを自宅に持ってきました。今まで管理コストがこれ以上増加するのを嫌ってレンタルサーバーを使っていましたが、レンタルでは出来なかったことは非常に大きかったので今後は色々遊んでみようと思います。

以上

どうぞご利用ください

*1:バージョン番号に深い意味はないけど、『バージョン3』や『3.0』ではなくコードネームで『v3』のつもり

*2:告知無く変更される可能性があります

Safari3のリロードは強制リロード

Programming


この一週間、Webページのブラウザキャッシュについて少し調べてた。
Webコンテンツを出来るだけブラウザキャッシュさせて転送量を削減しようと目論んでいて、PHPアプリケーション側でIf-Modified-SinceヘッダとDB最終更新時間を照らし合わせて、更新がなければ304を返す、という一般的なブラウザキャッシュ利用実装を行ったんだけど、テストしてみたらSafariが全然キャッシュを使ってくれない。と言うわけでApacheのログを取って色々調べた結果、表題のことが分かった。


Safariのリロードは、いわゆる強制リロード相当である


一応簡単に説明すると、IEなどの一般的なブラウザはページの読み込みシチュエーションによって、ブラウザ内のキャッシュを使うかどうかのポリシーを変化させている。IEやFxでは、

  • 通常ロード
    • ブックマークやロケーションバーからページにアクセスした場合
    • リンクや「戻る」によるページ遷移の場合
  • 通常リロード
    • リロードボタンやいわゆるF5ボタンによる再読込の場合
  • 強制リロード
    • ブラウザによって異なるがShift+F5やControl+F5などで、キャッシュを利用せずに強制的に再読込する場合

の三つの読み込みパターンがあるらしく、これらの種類によって、ブラウザはサーバーに対して主にCache-Control、If-None-Match、If-Modified-Sinceなどのキャッシュ利用ポリシー関係のヘッダを出し分け、キャッシュの使い方をサーバーに申告する。


で、その出し分け方がブラウザによって少しずつ違う。この辺りはhttp://blog.as-is.net/2007/02/modcache.htmlの記事が分かりやすいです。
このOgawaさんの作成された表に、自分が調査したSafariの振る舞いを勝手に追加させてもらった。

ヘッダ If-Modified-Since If-None-Match Cache-Control Pragma
Firefox 通常ロード ○ ○ ― ―
Firefox リロード ○ ○ max-age=0 ―
Firefox 強制リロード ― ― no-cache no-cache
IE7 通常ロード ○ ○ ― ―
IE7 リロード ○ ○ ― ―
IE7 強制リロード ― ― no-cache no-cache
Safari 通常ロード ○ ○ ― ―
Safari リロード ― ― max-age=0 ―
Safari 強制リロード


Safariに強制リロードインターフェイスは存在しないっぽいので空白。
このように、Safariにおけるリロードは、If-Modified-Since無し、If-None-Match無し、Cache-Controlはmax-age=0と、全方位スキ無しでブラウザキャッシュ利用を拒否していることが分かる。これはFx、IEにおける強制リロードと同等のレベルである。よって、Safariでリロードしたときに、ブラウザキャッシュを利用させることは出来ないと結論できる(通常ロード時には他のブラウザと同様にブラウザキャッシュが利用される)。ちなみに、If-*系ヘッダが無いのに強引に304 headerと空白のレスポンスを返したところ、Safariは白紙をレンダリングした。

補遺

ちなみに上記のSafariは3.2.1を用いたが、Safari 4 Public Beta(5528.17)では、リクエストしたページからリンクされている各種画像やcssファイルに関してはきちんとIf-Modified-Sinceヘッダを送信し、304レスポンスを受け入れるようになったようだ。いいことだと思う。

関連エントリー

http://blog.as-is.net/2007/02/modcache.html
http://homepage.mac.com/yuji_okamura/iSawIt/archives/2008/10/entry_1215.html
っていうかこの下のエントリーに答え書いてあった…

MySQLで50個のIDからレコードを取得したいときに、プレースホルダで50回叩くのとINで一回で取るの、どっちが速いか。

Programming

今日はふぁぼったーのフロントエンドのDB処理まわりをリファクタリングしました。この辺りも、もう1年半も開発・拡張を続けている部分なのでかなり汚くなっており、今後のためにできるだけソースを短くし、保守性の高いコードに書き直すなどの作業です。
そのなかで、以前から気になっていた点があったので、せっかくなので検証してみました。

前提

ふぁぼったーでは、発言をデータベースから取得する際、最初に発言とそのユーザーをSTATUSテーブルとUSERテーブルからSELECTして、そこで得た発言のIDを用いて、右下に出る各発言のふぁぼりユーザーをFAVORITEテーブルとUSERテーブルから別途SELECTしています。二つに分けたのはその方が速かったからですが、今回検証したのは、後半のふぁぼりユーザー取得部分の書き方です。


FAVORITEテーブルから複数の既知のステータスIDのレコードをSELECTしてきたい訳ですが、大体以下の二つの取得方法が考えられます。

A. プレースホルダつきプリペアドステートメントを用いて複数回叩く。

$favSQL="SELECT SQL_NO_CACHE * from FAVORITE,USER where FAVORITE.id=? and FAVORITE.user_id=USER.id;";

というSQLをprepareし、FAVORITE.idのプレースホルダにステータスIDをつっこみつつステータスの数だけexecuteする。

B. IN句に全てのIDをつっこんで一回で叩く。

$favSQL="SELECT SQL_NO_CACHE * from FAVORITE,USER where FAVOIRTE.id IN (".implode(',',$id_array).") and FAVORITE.user_id=USER.id;";

なSQLを一発叩く。でもどのレコードがどのステータスIDに対応するのかという対照性はレコードの中身を見ないと分からないので、自力で各ステータスにつっこむという一手間を加える必要がある。

比較

人間が書きやすいのは多分Aなのですが、パフォーマンスの面ではどの程度差が出るのか気になったので、ベンチマークを取ってみました。

条件

  1. 2008/11/22の人気ふぁぼられページでテスト
  2. SQLキャッシュは無効
  3. プレースホルダを利用する関係上、SQL単体での比較は難しい。よって、各方式でふぁぼりユーザーを取得する関数を作成し、関数全体の実行時間を比較
A方式.
SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id=? and f.user_id=u.id;

/*以下のプレースホルダで逐次実行。
1424797395, 1429571494, 1425533647, 1424770030, 1424798850, 1429457857, 1427719403, 1424996482, 1427697348, 1425186009, 1425883861, 1425883445, 1424981533, 1424930159, 1430825300, 1431058634, 1425179905, 1424813902, 1430527920, 1425768797, 1424796665, 1430748456, 1424828035, 1430514385, 1430474389, 1427714952, 1424987539, 1424839614, 1431577764, 1427783898, 1425414359, 1427950164, 1425191835, 1424916362, 1430541056, 1429964773, 1428692993, 1427342901, 1425160690, 1425081143, 1430872949, 1425871198, 1424959392, 1430738299, 1430707134, 1430345316, 1430150316, 1427859488, 1427784934, 1427701659, 1424995649 
*/
B方式.

以下のSQL文で実行

SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id IN ( 1424797395, 1429571494, 1425533647, 1424770030, 1424798850, 1429457857, 1427719403, 1424996482, 1427697348, 1425186009, 1425883861, 1425883445, 1424981533, 1424930159, 1430825300, 1431058634, 1425179905, 1424813902, 1430527920, 1425768797, 1424796665, 1430748456, 1424828035, 1430514385, 1430474389, 1427714952, 1424987539, 1424839614, 1431577764, 1427783898, 1425414359, 1427950164, 1425191835, 1424916362, 1430541056, 1429964773, 1428692993, 1427342901, 1425160690, 1425081143, 1430872949, 1425871198, 1424959392, 1430738299, 1430707134, 1430345316, 1430150316, 1427859488, 1427784934, 1427701659, 1424995649 ) and f.user_id=u.id;

結果

参考：ページあたりのfav数の少ない「新着fav」ページでテストした結果

結論

2倍から6倍ほどIN句の方が高速でした。なんとなく予想はしていましたが、思った以上に一回のクエリで処理できるというアドバンテージは大きいみたいですね。
出来るだけ高速にしたい局面では、複数の既知のIDからレコードを取得する際にはIN句を使った方がよさそうです。

参考

実際に使った関数を置いておきます。

350   private function getStatusesFavotters(){
351     $favSQL="SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id=? and f.user_id=u.id;";
352     preprint($favSQL);
353     $favStmt=$this->pdo->prepare($favSQL);
354     foreach ($this->statuses as $key => $rev) {
355       $favotters = array();
356       $favStmt->execute( array( $rev['status_id'] ) );
357       
358       while($favotter=$favStmt->fetch(PDO::FETCH_ASSOC)){
359         $url=$favotter['profile_image_url'];
360         $favotter['profile_image_url_mini']=preg_replace("/(bigger|normal).(jpg|jpeg|gif|png)/i","mini.$2",$url,1);
361         array_push($favotters,$favotter);
362       }
363       $this->statuses[$key]['favotters'] = $favotters;
364         
365     }   
366   }     
367   private function getStatusesFavotters2(){
368     $sids = array();
369     $favotters = array();
370     foreach($this->statuses as $st){
371       array_push($sids,$st['status_id']);
372       $favotters[$st['status_id']] = array();
373     }   
374         
375     $favSQL="SELECT SQL_NO_CACHE f.id as status_id,screen_name,profile_image_url from FAVORITE f,USER u where f.id IN (".implode(',',$sids).") and f.user_id=u.id;";
376     preprint($favSQL);
377     $favStmt = $this->pdo->query($favSQL);
378     while($favotter = $favStmt->fetch(PDO::FETCH_ASSOC)){
379       $url=$favotter['profile_image_url'];
380       $favotter['profile_image_url_mini']=preg_replace("/(bigger|normal).(jpg|jpeg|gif|png)/i","mini.$2",$url,1);
381       array_push($favotters[ $favotter['status_id'] ],$favotter);
382     }
383     foreach( $this->statuses as $key => $rev ){
384       $this->statuses[$key]['favotters'] = $favotters[ $rev['status_id'] ];
385     } 
386   }