半个PostgreSQL DBA,热衷于数据库相关的技术。我的ppt分享https://pan.baidu.com/s/1eRQsdAa https://github.com/chenhuajun https://chenhuajun.github.io
分类: NOSQL
2015-05-01 00:33:30
对于我之前的关于Mongodb的插入速度的问题,有网友jasbling2提出了质疑。
http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4981629
这么导入70000多行,平均每秒才800多个插入,我用你这种方式导入也很慢,但是我用php的mongo驱动插入就可以达到每秒4w到5w的插入
关于这个800有两点需要解释 1)虽然最终插入的是7w多条记录,但执行的插入操作确是10w次(有2w多条失败了),所以每秒的插入操作应该是1000出头。 2)使用mongo控制台插入存在客户端瓶颈,我在比较的时候特意转成了服务端的CPU资源占用(20.39%),如果能把服务端CPU撑满的话,每秒的插入就能达到5000了。
但是这个5000和jasbling2的5w还是差别满大的,为了解开其中原因,我在和之前一样的测试环境里,按照下面的链接,以jasbling2相同的测试方法做了一次插入测试。
http://blog.csdn.net/e421083458/article/details/8849247
参考 进行安装,但我的机子执行“pecl install mongo”老是失败,只好从源码安装。
wget phpize ./configure make install
编辑/etc/php.ini,加入下面一句 extension=mongo.so
-bash-4.1$ cat test.php admin; $collection = $db->members; for($i=1;$i<1000000;$i++){ $user = array('uname' => 'chuchuchu_'.$i, 'name' => '褚褚褚', 'password' => 'e10adc3949ba59abbe56e057f20f883e', 'email' => 'dhaig@yahoo.com.cn'); $collection->insert($user); } $conn->close(); ?> -bash-4.1$ time php test.php real 1m41.267s user 0m22.615s sys 0m12.188s
这样算下来,每秒大约插入1w条,和 的测试结果(87秒)基本差不多。(但是jasbling2为什么能达到4w到5w,估计是他测试用的CPU太强悍了。)
再看看CPU资源利用率。
[root@hanode1 ~]# top top - 02:33:56 up 2 days, 23:41, 7 users, load average: 0.52, 0.14, 0.04 Tasks: 168 total, 3 running, 165 sleeping, 0 stopped, 0 zombie Cpu(s): 16.6%us, 6.8%sy, 0.0%ni, 74.8%id, 0.0%wa, 0.0%hi, 1.8%si, 0.0%st Mem: 1019320k total, 958176k used,61144k free, 131124k buffers Swap: 2064376k total, 152k used, 2064224k free, 576196k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 23571 postgres 20 0 356m 71m 11m S 64.5 7.2 0:20.91 mongod 24545 postgres 20 0 208m 7332 4796 S 34.9 0.7 0:05.00 php
CPU资源利用率是64.5%,折算下来,插入100w记录使用CPU 46s,而之前用mongo插入10w记录使用CPU 20.39s。这样就有将近5倍的差距。 但是这两次测试插入的数据不一样,一个记录大,一个记录小,不具可比性
之前测试插入的10w条记录,有43M
db.jsontables.stats() { "ns" : "benchmark.jsontables", "count" : 100001, "size" : 266284846, "avgObjSize" : 2662, "storageSize" : 43167744,
现在插入的100多w条记录,只有22M
db.members.stats() { "ns" : "admin.members", "count" : 1137596, "size" : 167004402, "avgObjSize" : 146, "storageSize" : 22216704,
现在用php驱动,测试一下和之前mongo测试差不多大小的记录,插入10w条。
-bash-4.1$ cat test2.php admin2; $collection = $db->members; for($i=1;$i<100000;$i++){ //$user = array('uname' => 'chuchuchu_'.$i, 'name' => '褚褚褚', 'password' => 'e10adc3949ba59abbe56e057f20f883e', 'email' => 'dhaig@yahoo.com.cn'); $user = array('uname' => 'chuchuchu_'.$i, 'name' => '褚褚褚', 'password' => 'e10adc3949ba59abbe56e057f20f883e', 'email' => 'dhaig@yahoo.com.cn','description' => 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin eget elit ut nulla tempor viverra vel eu nulla. Sed luctus porttitor urna, ac dapibus velit fringilla et. Donec iaculis, dolor a vehicula dictum, augue neque suscipit augue, nec mollis massa neque in libero. Donec sed dapibus magna. Pellentesque at condimentum dolor. In nunc nibh, dignissim in risus a, blandit tincidunt velit. Vestibulum rutrum tempus sem eget tempus. Mauris sollicitudin purus auctor dolor vestibulum, vitae pulvinar neque suscipit. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Phasellus lacus turpis, vulputate at adipiscing viverra, ultricies at lectus. Pellentesque ut porta leo, vel eleifend neque. Nunc sagittis metus at ante pellentesque, ut condimentum libero semper. In hac habitasse platea dictumst. In dapibus posuere posuere. Fusce vulputate augue eget tellus molestie, vitae egestas ante malesuada. Phasellus nunc mi, faucibus at elementum pharetra, aliquet a enim. In purus est, vulputate in nibh quis, faucibus dapibus magna. In accumsan libero velit, eu accumsan sem commodo id. In fringilla tempor augue, et feugiat erat convallis et. Sed aliquet eget ipsum eu vestibulum.Curabitur blandit leo nec condimentum semper. Mauris lectus sapien, rutrum a tincidunt id, euismod ac elit. Mauris suscipit et arcu et auctor. Quisque mollis magna vel mi viverra rutrum. Nulla non pretium magna. Cras sed tortor non tellus rutrum gravida eu at odio. Aliquam cursus fermentum erat, nec ullamcorper sem gravida sit amet. Donec viverra, erat vel ornare pulvinar, est ipsum accumsan massa, eu tristique lorem ante nec tortor. Sed suscipit iaculis faucibus. Maecenas a suscipit ligula, vitae faucibus turpis.Cras sed tellus auctor, tempor leo eu, molestie leo. Suspendisse ipsum tellus, egestas et ultricies eu, tempus a arcu. Cras laoreet, est dapibus consequat varius, nisi nisi placerat leo, et dictum ante tortor vitae est. Duis eu urna ac felis ullamcorper rutrum. Quisque iaculis, enim eget sodales vehicula, magna orci dignissim eros, nec volutpat massa urna in elit. In interdum pellentesque risus, feugiat pulvinar odio eleifend sit amet. Quisque congue libero quis dolor faucibus, a mollis nisl tempus.'); $collection->insert($user); } $conn->close(); ?> -bash-4.1$ time php test2.php real 0m13.574s user 0m2.930s sys 0m1.357s
再看看CPU资源利用率。
[root@hanode1 ~]# top top - 02:59:13 up 3 days, 6 min, 7 users, load average: 0.16, 0.03, 0.01 Tasks: 168 total, 1 running, 167 sleeping, 0 stopped, 0 zombie Cpu(s): 9.8%us, 14.8%sy, 0.0%ni, 74.2%id, 0.0%wa, 0.1%hi, 1.0%si, 0.0%st Mem: 1019320k total, 948556k used,70764k free, 127372k buffers Swap: 2064376k total, 152k used, 2064224k free, 565232k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 23571 postgres 20 0 380m 86m 11m S 72.5 8.7 1:26.44 mongod 27145 postgres 20 0 208m 7332 4796 S 30.2 0.7 0:01.73 php 59 root 20 0 000 S 0.7 0.0 0:00.16 kswapd0
现在的CPU耗是 13.574*72.5%=9.8s。 这和之前用mongo插入10w记录(20.39s)相比。性能提升了一倍。
下面再看看通过mongo插入小数据的性能(insert_mongo.sh会生成指定数目的插入语句)
-bash-4.1$ sh insert_mongo.sh 100000|time -p mongo benchmark >/dev/null real 75.04 user 33.68 sys 23.34 [root@hanode1 ~]# top top - 04:09:08 up 3 days, 1:16, 5 users, load average: 0.08, 0.02, 0.01 Tasks: 162 total, 2 running, 160 sleeping, 0 stopped, 0 zombie Cpu(s): 16.4%us, 1.4%sy, 0.0%ni, 75.9%id, 0.0%wa, 0.0%hi, 6.2%si, 0.0%st Mem: 1019320k total, 915384k used, 103936k free, 128780k buffers Swap: 2064376k total, 152k used, 2064224k free, 477816k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2231 postgres 20 0 748m 66m 9208 R 75.8 6.7 0:06.54 mongo 23571 postgres 20 0 382m 87m 11m S 21.6 8.8 2:50.43 mongod 2229 postgres 20 0 103m 1216 1060 S 7.0 0.1 0:00.65 sh
这样这算下来插入10w记录,消耗CPU 16.2s,是用php驱动(100w,46s)时的差不多4倍。
之前用mongo控制台测试mongoDB的插入性能有点对不住mongoDB了。