時には機動警察パトレーバーのように、あるいは、600 万件のデータ加工を

投稿日: 2011/05/31 作成者: masuda

たまぁーに、１巻だけ取り出して読み進めて、最後の熊耳巡査が出てくるところ（手元にあるのは文庫本）で、毎度思うところがある。

泉巡査がイングラムの能力を十分引き出せないのは何故か？格闘技術に疎いから、という結論になるのだけれど、イングラムというハードウェアに、イングラムを動かすソフトウェア（学習機能付き）が乗り、更に人の動かし方が最終的にイングラムの能力を決定づける（「個性」ともいうし、「個体差」ともいう）、という流れになる。

ハードウェアというのは、パソコンだったり、CPU だったり、メモリだったりするわけで、OS というのは、Windows だったり、Mac だったりする。いや、OSの取り方をもうちょっと広げれば、SQL Server だったり、Oracle だったり、.NET Framework だたり、するわけです。で、これを使うのが人なわけで、この場合はプログラマなのですね。

最近のハードウェアは 10 年前よりもぐんと良くなっているわけで、メモリが 2GB なんてのは当たり前で、HDD も 500GB という形で桁が違います。なので、同じ大量のデータを扱うにしても、昔のデータベースの扱い方と、最近の扱い方では全く違う…と言いますか、ハードウェアが違うところに人が関わって、でてくる性能に大きな違いがでます…と言いますか、作り方によって全く違うってのを実感しています。

たまたま、600 万件のアクセスログを解析しようと思ったわけですが、この 600 万件というデータ、10 年前だったら Oracle でもひいこらいう程度のものなので、ちょっと躊躇しました。テキストデータで 2GB 弱あるわけです。
で、ひとまず、手元の SQL Server 2008 Experss Edition にテキストデータを入れて、データ解析をしてみると、これが早い早い。最初のインポート（C# で作りました）はツールの作りが悪いのか、2 時間程データ挿入に掛かるのですが、その後データを加工する場合は、クエリを使うと結構のスピードで動きます。

インデックスが無い状態でも、全検索で 3 分位で結果がでるし、適当なインデックスをつければ 2,3 秒かからずに結果がでてきます。データの加工ですら、1 分位で済みます。

データ加工するところは、

25/May/2011:23:58:39 +0900

のようなアクセスログの日付データを、データベースの Datetime 型に直します。
なので、update 文で使えるように、

2011-5-25 23:58:39

な風に変えないと駄目なわけです。

最初は、C# のツールを使ってやろうと思ったのですが、そもそも SqlCommand を使って insert 文を使ってデータ挿入をすると、600 万件挿入するのに 2 時間かかるわけです。ということは、同じように SqlCommand を使って update 文を使ってデータを変更すると、2 時間掛かるのではないか？という予想が経ちますね。
データ加工のたびに、こんなに時間が掛かってしまってはろくなデータ解析ができません。

で、この遅い理由としては、

  1. SqlCommand の呼び出しで、SQL Server との通信が入っている。
  2. SqlCommand の呼び出しで、.NET Framework とネイティブデータの変換が入っている。
  3. SqlCommand の呼び出しで、insert 文の解析が Sql Server で行われている。

が考えられるわけです。

1 の場合は、データ通信がなくなるように直接 SQL Server 上で行います。今回は、同じパソコン内でデータベースを動かしているので、あまり関係がないでしょう。
2 の場合は、C# でツールを作る限り駄目です。.NET Framework(実は Java も同じ)の場合は、.NET とネイティブデータの変換が必ず入ってしまうので、C/C++ で直接データを書き込むよりも遅くなるのは当たり前なのです。これは、SQL Server 自体が C/C++ で書かれている（内部データ自体は単なるバイナリ形式）であろうことから予想ができます。
3. SQL 文を解析しないようにして、bulk データを挿入できればよいのですが、あいにく SQL Server には bulk 以外にデータを挿入する方法がありません。SQLite みたいに SQL 文ではない形でデータ加工ができればよいんですけどね。

という訳で、2 時間の作業を短縮させるためには、2 のように .NET で書かないという方法を取らないと駄目なのです。

さて、ツールを作るならば C/C++ を使ってもよいのですが、結構面倒です（ADOを扱えばいいんですが、まぁ、面倒と言えば面倒）。なので、SQL Server で直接クエリを実行するようにします。

直接クエリを実行する場合は、SQL Server Management Studio でクエリ文を作るか、最終的にファンクションあるいはストアドプロシージャにしてしまうかです。

で、ざっくりと作ったの以下です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
CREATE function [dbo].[getltime]
( @d as varchar(50) )
returns datetime
as
begin
declare @dt varchar(50)
declare @i int
declare @day   varchar(10)
declare @month varchar(10)
declare @year  varchar(10)
declare @time  varchar(20)
declare @gmt   varchar(20)
declare @ltime datetime
declare @gtime datetime
 
if @d is null
  return null
 
set @dt = @d
set @i = CHARINDEX( '/', @dt )
if @i = 0
 return @d
 
set @day = SUBSTRING( @dt,0,@i )
set @dt = RIGHT(@dt,LEN(@dt)-@i)
set @i = CHARINDEX( '/', @dt )
if @i = 0
 return @d
set @month = SUBSTRING( @dt,0,@i )
set @dt = RIGHT(@dt,LEN(@dt)-@i)
set @i = CHARINDEX( ':', @dt )
if @i = 0
 return @d
set @year = SUBSTRING( @dt,0,@i )
set @dt = RIGHT(@dt,LEN(@dt)-@i)
set @i = CHARINDEX( ' ', @dt )
if @i = 0
 return @d
set @time = SUBSTRING( @dt,0,@i )
set @gmt = RIGHT( @dt, len(@dt)-@i )
set @month = REPLACE(@month,'May','5')
 
set @gtime = CAST( @year+'-'+@month+'-'+@day+' '+@time as datetime )
if SUBSTRING(@gmt,0,1) = '+'
begin
    set @gmt = (SUBSTRING(@gmt,2,2)+':'+SUBSTRING(@gmt,4,2))
    set @ltime = @gtime - @gmt
end
else
begin
    set @gmt = (SUBSTRING(@gmt,2,2)+':'+SUBSTRING(@gmt,4,2))
    set @ltime = @gtime + @gmt
end
 
-- select @year, @month, @day, @time, @gmt, @ltime, @gtime , @gmt
return @ltime
end

月の表示(May)が、いい加減ですが、まぁ汎用的に作るつもりはないので、これで良しとします。

これをあらかじめ作成しておいて、

1
update log set ltime = getltime([date])

で動かせばよいわけです。

これがどのくらいのスピードで動くのかというと、600 万件のデータを加工するに 3 分程度なんですね。
その、なんといいますか、ADO.NET が如何に遅いか、というのが分かるのです。いや、こんなに違うとは思わなかったのですが…ちょっと、.NET でデータベースを扱うときは、加工の仕方を考え直したほうがいいですね。

という話でした。

カテゴリー: 開発, C# パーマリンク

時には機動警察パトレーバーのように、あるいは、600 万件のデータ加工を への5件のフィードバック

  1. konica のコメント:
    2011/05/31 9:51 am

    最初からMySQLにぶち込めばよかったと今理解ｗ
    そうすれば、独自の解析をＷＥＢ上で見れたと・・・
    どんまい私ｗ

  2. masuda のコメント:
    2011/05/31 10:07 am

    いやぁ、MySQL だと 600 万件はきついかも。Web 上で PHP 経由でアクセスしようとすると、途中で止めれなくなるので、MySQL が暴走しかねないです。。。１ヶ月前、この wordpress でつかっている MySQL も無駄にホスト会社経由でリセットしたし。
    データ量が多い場合は、手元のローカルマシンを使うほうがベターです。

    ちなみに、先の解析は、2GHz 2GB の３年前ほどのパソコン。

    MySQL で 600 万件の場合は、実体験したほうがいいなぁと思うけど、どうなんでしょうねぇ？

    • masuda のコメント:
      2011/05/31 10:08 am

      追記
      考えてみたら twitter のバックエンドは MySQL だから、そこそこデータ量が多くてもいけるのかな。

  3. konica のコメント:
    2011/05/31 11:01 pm

    一応テストした（´・ω・）ｽ
    ・・・現実問題無理。

    インサート自体もPHPからは入れ込み設定で許容しても、
    止まるし、かつハングぎみ(XEON3.0)
    twitter やら他の場合、クエリ出すならどうにか分散でいけるかと思うけど、
    その場合でも入れ込む時に2G近いとbigdumpみたいに分けるし、
    分けるには一回のインサートを分割となると・・・・

    700MBまでですね・・・なんとなく。

    • masuda のコメント:
      2011/06/07 4:25 pm

      むしろ、.NET から MySQL に insert するとうまくいったりして、とか思ったり。
      アクセスリストを１ヶ月分ぐらいは手軽に扱いたいので、インサートを含めてちょっと調査中です。
      SqlBulkCopy を使うと、とんでもなく早いので、解析は SQL Server 上でも良いかと思ったり。.NET -> MySQL でインポートして、MySQL -> PHP で表示でも良いんだなぁと。

コメントは停止中です。