Slow Query 対策 - シーケンシャルリードを行いたい
背景
数百万行のデータを保持するMySQLから、定時でSolrのインデックス構築用にMySQLから全行の取得を行っていた。
ただし、サービスインしているためクエリ一発で取得するのは負荷のためNGであり、繰り返し一定行数データを取得する必要があった。
今回、既存クエリでSlow Queryが発生していたため、対応が必要になった。
今回の説明のため、下記の単純なテーブルを例に挙げる。 また、ベンチマークの取得に時間がかかりすぎたのでレコード数は20万に抑えた。
CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
既存クエリ
LIMIT句でoffsetを指定しながら1000件ずつ取得していた。
SELECT * FROM user ORDER BY id ASC LIMIT 10000,1000
EXPLAINの結果は次の通り。
mysql> EXPLAIN SELECT * FROM user ORDER BY id ASC LIMIT 10000,1000; +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 4 | NULL | 11000 | | +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+
offsetで取ってくる順序を保証しないと、繰り返し実行時に重複してデータを取得する可能性があるので、ORDER句は必須。
上記の通りフルインデックススキャンになっている。
offset位置までのシーケンシャルリード＋取得件数分のリードが発生するため、offsetが大きくなればなるほど遅くなる。
改善案1 - BETWEENを使って範囲検索を狙う
BETWEENを使った範囲検索であれば、ソートも不要になりかなり高速になる。
SELECT * FROM user WHERE id BETWEEN 10001 AND 11000
EXPLAIN結果
mysql> EXPLAIN SELECT * FROM user WHERE id BETWEEN 10001 AND 11000; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | user | range | PRIMARY | PRIMARY | 4 | NULL | 999 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
改善案2 - WHERE句で開始位置を限定する
サービス上で削除が頻繁に行われる場合はデータが虫食いになるので、BETWEENだと毎回期待した件数分を取得できるとは限らない。
そのため、繰り返し数が多くなる傾向があり、これを嫌う開発者も存在する。
そういった場合を考慮するのであれば、WHERE句で開始位置を指定してORDER、LIMITを使うと範囲検索になるので、既存よりは高速化できる。
SELECT * FROM user WHERE id > 10000 ORDER BY id ASC LIMIT 1000
EXPLAIN結果
mysql> EXPLAIN SELECT * FROM user WHERE id > 10000 ORDER BY id ASC LIMIT 1000; +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | user | range | PRIMARY | PRIMARY | 4 | NULL | 100155 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
ベンチマーク
下記コードで上記3パターンについてベンチマークを行い比較した。
use strict;
use warnings;
use DBI;
use Benchmark qw(:all);
my $dbh = DBI->connect('dbi:mysql:sota', 'root', '');
my $sth1 = $dbh->prepare(q{
SELECT * FROM user ORDER BY id ASC LIMIT ?, ?
});
my $sth2 = $dbh->prepare(q{
SELECT * FROM user WHERE id BETWEEN ? AND ?
});
my $sth3 = $dbh->prepare(q{
SELECT * FROM user WHERE id > ? ORDER BY id ASC LIMIT ?
});
cmpthese(100, {
before => sub {
foreach my $i ( 0 .. 199 ) {
my $start = $i * 1000;
$sth1->execute($start, 1000);
}
},
after1 => sub {
foreach my $i ( 0 .. 199 ) {
my $start = $i * 1000 + 1;
my $end = ( $i + 1 ) * 1000;
$sth2->execute($start, $end);
}
},
after2 => sub {
foreach my $i ( 0 .. 199 ) {
my $start = $i * 1000;
$sth3->execute($start, 1000);
}
},
});
結果を以下に示す。
Rate 1 2 3 before 20.6/s -- -34% -34% after1 31.2/s 51% -- -0% after2 31.2/s 51% 0% --
既存クエリが最も遅く、実行中にSlow Queryが多発していた。
改善案1、改善案2は今回はそれほど差は出なかった。 もっとレコード数が多い場合は改善案1と改善案2に差が見られるかもしれない。
Jogport Ariake & お台場ラン
今日はお台場でランニングしてきた。
利用したランステは「Jogport Ariake」。
りんかい線の国際展示場駅からだとちょっと歩くけど、ゆりかもめの国際展示場正門前駅からだと目の前にある。
丁度ランニングイベントがあったのか、M高史さんがいた。
他のランステと同様、ロッカーとシャワーが使えます。ボディソープなども備え付け。
ビジター利用だと700円でこれも一緒。
アシックスのウェアや靴のレンタルもしてるとか。
トイレは店舗内にはないけどビル内のが使えるので問題なし。
コースはお台場内にいくつかあるよう。
(引用元：http://jogport.com/course/index.php)
とりあえずJogportの真ん前からスタートする5kmのコースを周回することに。
コースの所々に標識やマーカーが置いてあって迷うこともなく、ちゃんと距離が測れるのが良い。
海や観覧車、ガンダムなど景観がよいので走ってて単調に感じることはあまりなかった。
海風がそれなりに吹いているので、今日みたいな30度近い気温の場合は涼しくてよい。
アップダウンもあっていい練習になりそう。
注意点としては3カ所ほど車道を横断するので車に気をつけることと。
また、Diver Cityを過ぎてからの直線の折り返しは結構人通りが多いので歩行者との接触に気をつけること。
サーバー上のユーザーのオペレーションをお手軽に記録する
Linux/Unixサーバー上で、ユーザーにオペレーション権限を与えたいが無制限に開放するとセキュリティ上好ましくないので、ユーザーに改竄されない形でオペレーションを記録しておいて後で追跡できるようにしたい。
ただし、以下の制約が存在する。
- パッケージ増やすとそれに応じて管理コストも増えるのでなるべく避けたい
- パフォーマンスの低下は確実に避けたい
そのため、以下の手段を取るのが難しい。
これらを満たすお手軽なロギング環境の構築についてbash、zshのときで考えた。
(bash、zshの理由は単に自分の置かれてる環境でメインに使われてるのがこの2つだけだったから)
bash
下記の記述を.bashrcに記述する。
export PROMPT_COMMAND='¥logger `¥history | ¥tail -n 1 | ¥sed "s/^[ ]*[0-9]+[ ]*//"`' readonly PROMPT_COMMAND
PROMPT_COMMANDでhistoryの最新1件をloggerに与えてsyslogに記録する。
history | tail -n 1 のところは単に history -n 1でも良い場合もある。
sedの部分は実行履歴の番号を消しているだけ。
各実行コマンドに¥をつけているのはaliasなどで各コマンド自体を改竄されるのを防ぐため。
zsh
PROMPT_COMMAND相当のことをadd-zsh-hookで実現する。
以下の内容を.zshrcに記述する。
autoload -Uz add-zsh-hook
add-zsh-hook precmd log_command
log_command() {
¥logger `¥history | ¥tail -n 1 | ¥sed "s/^[ ]*[0-9]+[ ]*//"`
}
ただ、add-zsh-hook -d precmd log_commandとするとキャンセルできてしまう…。
参考
ASICS STORE原宿店で足形測定をした
ニューバランスをやめた
ニューバランスが昔から好きで、初任給でNB Zipのランニングシューズを買って以降、ランニングシューズは全部ニューバランスでした。
しかし、ここ最近のシューズは合うまでに2ヶ月かかる、もしくは合わないということが続きました。
特に今年2月に買ったRC1300は足の小指の付け根の締め付けが強くとても痛むようになり、同じタイプで縦0.5cm、横2サイズ大きめのを一度買い直したのですが、あまり状況は改善されませんでした。
海外メーカーのシューズは日本人には合いにくいという話もあり、そろそろ別のシューズも試してみるか、ということで国内メーカーのどれにしようかと悩んでいました。
結局、近くで足形測定を行っているasicsにしました。
今日はASICS STORE原宿店に行って足形測定をしてシューズを買ってきました。
足形測定
親指の付け根、小指の付け根、かかとの内側の3点にマーカーをつけ、測定用の3Dスキャナーに足を突っ込みます。
これ自体は1−2分で終わりました。
ニューバランス東京でも足長・足囲は計測していて、これは今回も同様に足囲がかなり細いという結果が出ました。
ただ、asicsではかかと幅、足高、アーチ高、かかとの傾斜角度、第一趾側角度も計測されました。
結果としては自分の足は標準より平べったく、かかとが外反に傾いていると出ました。
走行測定
2000円払う、もしくは5000円以上の買い物をするのであれば走行測定もできるということでした。
ランニングシューズを買いにきている以上、5000円以上の買い物をすることは確定してたのでお願いしました。
レンタルシューズでトレッドミルの上を走り、それをビデオで取りました。
自分のランニングフォームは、ジムで鏡に写ってるのを見ることはありましたが後ろから見るのは初めてで、その分収穫がありました。
両足とも足先が外に向いており、着地がかかとの外側に寄っていました。
特に左足は顕著でした。
左足の腸脛靭帯を痛めて走れなかった頃、足先を外側に向けるように意識したところ痛みなく走れるようになってから自然とそうなっていました。
しかし、今回後ろから見てると特に左足の足運びに無駄な動作が多く、この辺を改善できればもっと効率よく走れるように見えました。
シューズの選択
自分の細い足形だと合うタイプが「Tarther Japan-slim」と「Tartherzeal」の2つしかありませんでした。
機能性やデザインもほとんど変わらず結構悩みましたが、結局足の締め付けがなんとなく軽かった前者を選択しました。
早速ジムに行って走ってみました。
店員さんがクッション性が高いと言っていた通り弾む感覚がありました。
それもすぐに慣れて普通に走ることができました。
また、足先の向きをいつもより内側に向けて足を移動方向に平行になるように意識してみました。
とはいえ、長年の習慣で身に付いたものなので一朝一夕ではそうそう変えられそうもありません。
それにまたランナーズニーを発症しても嫌ですしね。
足先の向きについては時間をかけて研究していきます。
サハラマラソンに出てリタイアしてきた話
はじめに
4/3-4/16に有給をもらい、第29回サハラマラソンに参加してきました。 結果をだけ書いておきますと、自分は2日目の第2チェックポイントでリタイアしました。
今回は主に失敗した原因について書いています。
サハラマラソンとは
サハラマラソンとはその名の通り、サハラ砂漠でマラソンをします。 ただ通常のマラソンとは違いステージ制のレースで１日ごとに全く別のコースを走ります。
サハラマラソンでは6ステージを7日かけて、総計240km走ります。 今年のステージ構成は以下の通りです。
また、レース中の食料や寝袋などは全て自分で用意し、全て背負ってレースを行います。
失敗した原因
失敗した原因を思いつく限り挙げています。 ただ、いくつも挙げてはいますが、今回の失敗の原因の9割はゲイターの不備が占めています。
ゲイターの不備
ゲイターは公式サイトで販売している靴全体を覆うものにするべきでした。これは海外からの輸入の面倒さに億劫になって、近場のスポーツショップで済ませようとしたのが間違いでした。スポーツショップで売ってるものは足首部分しか覆われないので本物の砂砂漠には不適切でした。
スポーツショップで売ってるものを使う場合でも、トレッキングシューズにするべきでした。ランニングシューズは通気性重視のためアッパーがメッシュ構造になっていますが、トレッキングシューズは泥を入れない構造になっているので、これであれば足首部分だけのゲイターでも十分機能しているようでした。
まとめると、
のどちらかである必要だったでしょう。
ハイドローションの不適切さ
過去の参加者のブログでペットボトルのみを取り替えられるのがよいと読み、バックパックの前にペットボトルをつける装備で臨んだのですが、これは水をチェックポイントでほとんど飲みきってしまって、チェックポイント間を1時間程度で走りきるようなかなり上位狙いをする人の発想で、自分のような初心者にとっては細めに水を取っていくことになるので如何に1回当たりの給水の手間がかからないかを考えるべきでした。
さらに、オンロードのフルの経験のみで、トレイルやトレッキングの経験がなかったので給水の重要性が理解できていませんでした。
自分の場合、オンロードのフルだと3時間程度で走りきってしまうので、1回のレースでそれほど水を必要とせず、トータルでも1Lいくかいかないか程度しか給水しません。
なので、ハイドローションを使ったことがなく、それを必要とするような状況、その使い方など、自前で細めに給水するということに全然ピンときていませんでした。
オフロードの経験のなさ
トレイルやトレッキングの経験がないと述べましたが、足下の技術についてもオンロードのものしか持っておらず、様々な場面での対応に意外と困りました。
オンロードの場合、足下が安定しているので、求められる足場への対応はせいぜいフラット・アップ・ダウンなので、とにかく速く足を運ぶことだけを考えていればいいです。
乾燥した滑りやすい土の上を走ることはありましたが、砂漠に比べれば全然走りやすい環境でした。 これに対しオフロードの場合、足下がとにかく不安定なので、如何に足にダメージを蓄積させず歩を進めるかが要求されました。
砂の上だと一歩先の足場の固さは踏んでみるまで分からないですし、岩場だと足の裏へのダメージが大きく、これらに対してロードでの膝や足首の使い方では対応できませんでした。
パッキングの軽視
アルファ米などは買ったそのままで、その他の食料も1日分ごとにスーパーのビニール袋で小分けするようにしていましたが、これが結構かさ張って荷物全体が膨らんでしまい、重心を保つのを難しくしていました。
また、財布やパスポートは防水対策をしていなかったので、上から水をかぶることも容易ではなかったのダメでした。 Ziplockで防水性を考慮しつつ、同一の食料はまとめてパッキングして圧縮することをもっと考えるべきでした。
食料が多かった
食料は意識して少なめにするようにして、14000kcal必須のところを16000kcal程度にしましたが、実際走ってみると少し多い気がしました。 おそらく14000kcalギリギリでも十分持つと思います。 あと、味噌汁は飲みたいと思って持っていきましたが、これは要りませんでした。本当に必要なもののみに絞った方がよいです。
よかったこと
練習の過程で荷物の重さが10kgを超えると肩に内出血ができたり筋肉痛になるなど体への負担が大きいことに気づき、できるだけ荷物を軽くする方向に考えがシフトできたのは幸いでした。 不安に駆られて色々と物を詰め込みがちですが、そこをぐっと堪えて「本当に必要なの？」と自問しつつ不要な物を切っていけたのは良かったです。
まとめ
物事の成否を分けるものとして、成功要因と失敗要因があります。 成功させるためには「より多くの成功要因を積み重ね、失敗要因を全て排除する」必要があると自分は考えます。 言い換えれば「失敗要因を一つでも抱え込むと成功要因をいくら積み上げても失敗する」と思ってます。 今回、自分は致命的な失敗要因を踏んでしまい、リタイアすることになりました。 今回書いた失敗談が、今後参加される方の参考になると幸いです。
Redisでランキングを作る
「Redisでランキングを作る」という手垢の付きまくったネタをPerlでやります。 Redis::Clientを使いました。
データの準備
Redisサーバーをインストール・起動した後、ランキング用のデータを準備します。 100万件のデータをスクリプトで入れました。 1件1件いれていくと時間がかかるので、1000件まとめて1000回入れました。
init.pl
#!/usr/bin/perl
use strict;
use warnings;
use Redis::Client;
my $redis = Redis::Client->new;
foreach my $repeat (1 .. 1000) {
my @data = map {
my $score = int rand (1000000);
my $num = ($repeat - 1) * 1000 + $_;
+( $score => $num );
} ( 1 .. 1000 );
$redis->zadd( 'ranking', @data );
}
zaddメソッドでソート済みセットを作っています。 データを入れるときは、$redis->zadd( ソート済みセット名, score, id)の形で入れます。 score, idの組は1度に複数入れることができます。
ランキングの取得
sample.pl
#!/usr/bin/perl
use strict;
use warnings;
use Redis::Client;
my $redis = Redis::Client->new;
my $count = $redis->zcard('ranking');
print "count: $count\n";
my @result = $redis->zrange('ranking', $count - 3, $count - 1);
foreach my $num (@result) {
print join ',', ($num, $count - $redis->zrank('ranking', $num), $redis->zscore('ranking', $num));
print "\n";
}
投入済みデータからランキングを作ります。
zcardメソッドはソート済みセットが保持するデータ数を返します。
zrangeメソッドではソート済みセットから指定した範囲のデータを取得します。 $redis->zrange('ranking', n, m)のように呼べば、rankingという名前のソート済みセットでscoreで昇順に並べられたデータの中からn番目からm番目のデータを取得します。 n, mに負の値を指定すると、最後尾から1,2,…の用に逆から取り出していきます。 scoreの上位3件が欲しいので、$count -3 , $count - 1と指定してますが、-3, -1も同じです。
zrankでは昇順に並んだランキング内の順位を返します。同率などは考慮しません。 zrevrankを使うと降順に並んだランキング内の順位を返します。
zscoreは実際にランキングの基となるscoreを返します。
C言語とLuaの連携 -2次元配列のやりとり-
やりたいこと
昨日の続きで、今回はCからLuaに2次元配列を渡し、Luaで加工した後、LuaからCに値を返します。
C言語のコード
matrix.c
#include <stdio.h>
#include <stdlib.h>
#include "include/lua.h"
#include "include/lualib.h"
#include "include/lauxlib.h"
#include "luadebug.h"
int main () {
const char *fn = "double_matrix.lua";
lua_State *lua = luaL_newstate();
luaL_openlibs(lua);
if ( luaL_dofile(lua, fn) != 0 ) {
printf("failed execute %s\n", fn);
exit(1);
}
lua_getglobal(lua, "double");
lua_newtable(lua);
int arr[][5] = {
{ 1, 2, 3, 4, 5 },
{ 6, 7, 8, 9, 10 }
};
int i, j;
for ( i = 0; i < 2; i++ ) {
lua_newtable(lua);
for ( j = 0; j < 5; j++ ) {
lua_pushnumber(lua, arr[i][j]);
lua_rawseti(lua, 3, j + 1);
}
lua_rawseti(lua, 2, i + 1);
}
lua_call(lua, 1, 1);
int value;
for ( i = 0; i < 2; i++ ) {
lua_rawgeti(lua, -1, i + 1);
for ( j = 0; j < 5; j++ ) {
lua_rawgeti(lua, -1, j + 1);
if ( j == 0 ) printStack(lua);
value = lua_tonumber(lua, -1);
printf("[%d][%d] : %d\n", i, j, value);
lua_pop(lua, 1);
}
lua_pop(lua, 1);
}
lua_close(lua);
return 0;
}
Luaのコード
double_matrix.lua
function double (matrix)
for i = 1, #matrix do
local array = matrix[i]
for j = 1, #array do
array[j] = array[j] * 2
end
end
return matrix;
end
解説
Luaの関数に2次元配列を渡す
基本的には昨日とやってることは同じです。 ただ2次元配列を扱うので、2種類の配列を扱うことになります。
L19とL28でlua_newtableを呼び出しています。L28の時点でのスタックは下記のようになります。
-----------------------------
003(-001): TABLE
002(-002): TABLE
001(-003): FUNCTION
-----------------------------
003(-001): TABLE が数値を格納する配列、002(-002): TABLEが003(-001): TABLEを格納する配列になります。
L29-32のループで003(-001): TABLEに数値を追加していきます。
L33のlua_rawsetiを呼び出すと、003(-001): TABLEがスタックから取り出され、002(-002): TABLEに追加されるためスタックの状態は次のようになります。
-----------------------------
002(-001): TABLE
001(-002): FUNCTION
-----------------------------
関数を実行して2次元配列を受け取る
lua_callを実行した際のスタックの状態は次のようになります。
-----------------------------
001(-001): TABLE
-----------------------------
このテーブルは2次元配列です。なので配列を格納する配列になります。
このテーブルに対してlua_rawgetiを呼び出し、配列を取り出してスタックに載せます。
-----------------------------
002(-001): TABLE
001(-002): TABLE
-----------------------------
さらに、002(-001): TABLEに対してlua_rawgetiを呼び出し、この配列に格納されている数値を取り出します。
-----------------------------
003(-001): NUMBER 2.000000
002(-002): TABLE
001(-003): TABLE
-----------------------------
あとは昨日と同様、スタックを操作しながら値を取り出していきます。
実行結果
[0][0] : 2
[0][1] : 4
[0][2] : 6
[0][3] : 8
[0][4] : 10
[1][0] : 12
[1][1] : 14
[1][2] : 16
[1][3] : 18
[1][4] : 20
