MongoDB,CouchDB,MySQL,memcacheデータをPHPから読み込み&書き込み速度を検証してみました!
前回、前前回とMongoDB,CouchDBのインストール方法やphpからの使用方法を記事にしました。
CentOS5.4にCouchDBをインストールする方法! - @camelmasaの開発日記
CouchDBをphpから操作する方法。 - @camelmasaの開発日記
CentOS5.4にMongoDBをインストールする方法! - @camelmasaの開発日記
MongoDBをphpから操作する方法。 - @camelmasaの開発日記
MongoDBとCouchDBにMySQL(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から読み込みするプログラムです。
こんなソースで良いのか。
<?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();
<?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');
読み込み結果
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)
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"));
<?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)
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/