MongoDB,CouchDB,MySQL,memcacheデータをPHPから読み込み&書き込み速度を検証してみました!

前回、前前回とMongoDB,CouchDBのインストール方法やphpからの使用方法を記事にしました。
CentOS5.4にCouchDBをインストールする方法! - @camelmasaの開発日記
CouchDBをphpから操作する方法。 - @camelmasaの開発日記
CentOS5.4にMongoDBをインストールする方法! - @camelmasaの開発日記
MongoDBをphpから操作する方法。 - @camelmasaの開発日記

MongoDBとCouchDBMySQL(MyISAM)とmemcacheを加えて読み込み(SELECT)と書き込み(INSERT)の速度を検証してみたいと思います。


環境

OS:CentOS5.4
PHP:5.3.1
MongoDB:1.2.2
CouchDB:0.10.1
MySQL:5.1.43
memcache:1.2.8

CPU:AMD(の何か)
メモリ:4GB
(ざっくりと検証です。)


読み取り時のソース

簡単に、"test"データベースの"test"テーブル(コレクション)の"test"データをphpから読み込みするプログラムです。
こんなソースで良いのか。


couchdb.php

<?php

require_once "HTTP/Request.php";

$http = new HTTP_Request('http://192.168.80.139:5984/test/93e4b62c166489b5ff94600e3003db88');
$response = $http->sendRequest();

mongodb.php

<?php

$mongo = new Mongo();

$db = $mongo->selectDB("test");
$col = $db->selectCollection("test");
$cursor = $col->findOne();

mysql.php

<?php
$link = mysql_pconnect('localhost', 'user', 'pass');
mysql_select_db('mytest', $link);
$result = mysql_query('SELECT `test` FROM `test`;');

memcache.php

<?php

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");
$memcache->get('test');

読み込み結果

couchdb.php

ab -c 100 -n 1000 'http://192.168.0.30/couchdb.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /couchdb.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   9.587989 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      190000 bytes
HTML transferred:       0 bytes
Requests per second:    104.30 [#/sec] (mean)
Time per request:       958.799 [ms] (mean)
Time per request:       9.588 [ms] (mean, across all concurrent requests)
Transfer rate:          19.29 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   1.5      0       9
Processing:    22  926 923.1    650    9554
Waiting:       21  860 568.4    650    5877
Total:         22  927 923.5    650    9555

Percentage of the requests served within a certain time (ms)
  50%    650
  66%    662
  75%    783
  80%   1099
  90%   1565
  95%   1735
  98%   3220
  99%   5656
 100%   9555 (longest request)


mongodb.php

ab -c 100 -n 1000 'http://192.168.0.30/mongodb.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /mongodb.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   1.939626 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      190000 bytes
HTML transferred:       0 bytes
Requests per second:    515.56 [#/sec] (mean)
Time per request:       193.963 [ms] (mean)
Time per request:       1.940 [ms] (mean, across all concurrent requests)
Transfer rate:          95.38 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   1.5      0       8
Processing:    35  187  35.4    201     236
Waiting:       35  187  35.4    201     235
Total:         35  187  35.2    201     244

Percentage of the requests served within a certain time (ms)
  50%    201
  66%    202
  75%    204
  80%    204
  90%    207
  95%    208
  98%    209
  99%    226
 100%    244 (longest request)


mysql.php

ab -c 100 -n 1000 'http://192.168.0.30/mysql.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /mysql.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   1.421236 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      190000 bytes
HTML transferred:       0 bytes
Requests per second:    703.61 [#/sec] (mean)
Time per request:       142.124 [ms] (mean)
Time per request:       1.421 [ms] (mean, across all concurrent requests)
Transfer rate:          130.17 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    1   5.1      0      22
Processing:     7  131 142.4    129    1404
Waiting:        7  131 142.3    129    1403
Total:         19  133 142.6    129    1416

Percentage of the requests served within a certain time (ms)
  50%    129
  66%    130
  75%    136
  80%    138
  90%    141
  95%    144
  98%    173
  99%   1339
 100%   1416 (longest request)


memcache.php

ab -c 100 -n 1000 'http://192.168.0.30/memcache.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /memcache.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   1.453866 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      191900 bytes
HTML transferred:       0 bytes
Requests per second:    687.82 [#/sec] (mean)
Time per request:       145.387 [ms] (mean)
Time per request:       1.454 [ms] (mean, across all concurrent requests)
Transfer rate:          128.62 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0   15  10.2     14      45
Processing:    20  124  36.9    138     479
Waiting:       16  109  35.7    119     462
Total:         27  139  41.8    147     496

Percentage of the requests served within a certain time (ms)
  50%    147
  66%    163
  75%    172
  80%    176
  90%    186
  95%    191
  98%    198
  99%    203
 100%    496 (longest request)

読み込み結果検証

Requests per secondの順位下記。
MySQL(MyISAM)>memcache>MongoDB>>>CouchDB

MySQLが一番早い結果になるとは思わなかったです。(なって欲しくなかった)
でもFacebookでのMySQL運用術はKVSの様にシンプルなのかな。
(http://hyuki.com/yukiwiki/wiki.cgi?HowFriendFeedUsesMySqlToStoreSchemaLessData)

CouchDBはRESTでのデータ取得をPEARでやってしまってるので遅いのかな?
毎度毎度その処理部分のPHPコンパイルする分遅くなっているのでしょう。

MongoDBもmemcache並に早いですね。
MySQLがmemcacheより早いのは意外です。解せぬ〜。

次は書き込み時の速さを見てみます。


書き込み時のソース

couchdbはinsertの方法が調べれず…><
誰か教えて!ヘルプ!


mongodb_write.php

<?php

$mongo = new Mongo();

$db = $mongo->selectDB("test");
$col = $db->selectCollection("test");
$col->insert(array("test" => "test"));


mysql_write.php

<?php
        $link = mysql_pconnect('localhost', 'root', '');
        mysql_select_db('mytest', $link);
        $result = mysql_query("INSERT INTO `test` VALUES ('test');");


memcache_write.php

<?php

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");
$memcache->set("test", "test");

書き込み結果

mongodb_write.php

ab -c 100 -n 1000 'http://192.168.0.30/mongodb_write.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /mongodb_write.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   9.325888 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      190190 bytes
HTML transferred:       0 bytes
Requests per second:    107.23 [#/sec] (mean)
Time per request:       932.589 [ms] (mean)
Time per request:       9.326 [ms] (mean, across all concurrent requests)
Transfer rate:          19.84 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    4   9.7      0      36
Processing:    30  728 1111.7    176    9092
Waiting:        3  725 1112.6    176    9091
Total:         31  732 1111.0    177    9108

Percentage of the requests served within a certain time (ms)
  50%    177
  66%    910
  75%    937
  80%    938
  90%   1030
  95%   3103
  98%   3148
  99%   4285
 100%   9108 (longest request)


mysql_write.php

ab -c 100 -n 1000 'http://192.168.0.30/mysql_write.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /mysql_write.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   1.484070 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      190000 bytes
HTML transferred:       0 bytes
Requests per second:    673.82 [#/sec] (mean)
Time per request:       148.407 [ms] (mean)
Time per request:       1.484 [ms] (mean, across all concurrent requests)
Transfer rate:          124.66 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   1.6      0       8
Processing:    41  142 165.8    129    1478
Waiting:       40  142 165.8    129    1478
Total:         41  143 165.8    129    1479

Percentage of the requests served within a certain time (ms)
  50%    129
  66%    131
  75%    132
  80%    133
  90%    139
  95%    144
  98%    161
  99%   1476
 100%   1479 (longest request)


memcache_write.php

ab -c 100 -n 1000 'http://192.168.0.30/memcache_write.php'

This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking 192.168.0.30 (be patient)


Server Software:        Apache/2.2.3
Server Hostname:        192.168.0.30
Server Port:            80

Document Path:          /memcache_write.php
Document Length:        0 bytes

Concurrency Level:      100
Time taken for tests:   1.701421 seconds
Complete requests:      1000
Failed requests:        0
Write errors:           0
Total transferred:      190380 bytes
HTML transferred:       0 bytes
Requests per second:    587.74 [#/sec] (mean)
Time per request:       170.142 [ms] (mean)
Time per request:       1.701 [ms] (mean, across all concurrent requests)
Transfer rate:          108.73 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0   17  11.0     18      44
Processing:    34  143  24.8    148     267
Waiting:        3  126  25.6    132     261
Total:         34  160  28.3    163     301

Percentage of the requests served within a certain time (ms)
  50%    163
  66%    175
  75%    181
  80%    185
  90%    192
  95%    195
  98%    201
  99%    205
 100%    301 (longest request)

全体を通したまとめ

大分ざっくりとした検証で納得がいくのかわかりませんが個人的には概ね満足。
インストール〜設定〜動作等で見えた部分は勉強になりました。

検証前は圧倒的にmemcacheが一番早いだろうと思ったのですが、読み書きともMySQLとほぼ同じ位で驚きました。
その後僅差でMongoDB。
CouchDBはhttp通信をphpでしてしまってるので良い結果が得られなかった感じですか。
(couchdb.so作って!)

恐らく皆さんが書いているように、RDBMSも単体では速度的に問題なくて、複雑なSQLを処理しようとするとボトルネックになるのでしょうか。
スケーリングを意識したSQLを書く事で、KVS並に高速化になるのかもしれません。
参考:http://hyuki.com/yukiwiki/wiki.cgi?HowFriendFeedUsesMySqlToStoreSchemaLessData


あとがき

うーん…。
こうしたらMongoDBが早くなるよ!的なアドバイス欲しいな〜。

上記のテストサーバーは家でそのままにしてあるので、こんなテストして!っていう場合コメント下さい。
テストしてブログに書きます。


[PR]Spreeの情報を集めています。

ECを持ちたい方、仕事でECを使いたい方向けのコミュニティサイトです。
このサイトでは世界で最も使用されているECの1つであるSpreeについての情報を提供しています。
http://spreecommerce.jp/