首页 > 代码库 > SQLite性能 - 意想不到,但又情理之中的测试结果。

SQLite性能 - 意想不到,但又情理之中的测试结果。

win7(64) sata2 希捷

技术分享
MINGW32_NT-6.1 1.0.17(0.48/3/2)
cat: /proc/cpuinfo: No such file or directory 
 
------ 1000 in disk ----
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.21s
commit: 1

real    0m0.484s
user    0m0.000s
sys    0m0.032s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.01s
 0.23s
commit: 1

real    0m0.440s
user    0m0.015s
sys    0m0.000s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
215.52s
215.52s
commit: 1000

real    3m35.756s
user    0m0.000s
sys    0m0.015s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
202.44s
202.44s
commit: 1000

real    3m22.741s
user    0m0.000s
sys    0m0.000s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.02s
 0.44s
commit: 1

real    0m0.885s
user    0m0.000s
sys    0m0.015s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.02s
 0.20s
commit: 1

real    0m0.468s
user    0m0.000s
sys    0m0.000s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
429.34s
429.34s
commit: 2000

real    7m9.628s
user    0m0.000s
sys    0m0.000s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
423.91s
423.91s
commit: 2000

real    7m4.186s
user    0m0.000s
sys    0m0.015s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.04s
 0.40s
commit: 1

real    0m0.780s
user    0m0.000s
sys    0m0.015s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.12s
 0.41s
commit: 1

real    0m0.786s
user    0m0.000s
sys    0m0.015s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.36s
 0.66s
commit: 1

real    0m0.938s
user    0m0.000s
sys    0m0.000s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 1.00s
 1.45s
commit: 1

real    0m1.713s
user    0m0.000s
sys    0m0.015s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 2.07s
 2.67s
commit: 1

real    0m2.887s
user    0m0.000s
sys    0m0.015s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 8.60s
 9.12s
commit: 1

real    0m9.376s
user    0m0.000s
sys    0m0.015s
win7(64) sata2 希捷

win7(64) usb-硬盘盒(sata2 西数)

技术分享
------ 1000 in disk ----
rm: cannot lstat `test.db: No such file or directory
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.03s
commit: 1

real    0m0.108s
user    0m0.015s
sys    0m0.000s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.01s
 0.03s
commit: 1

real    0m0.089s
user    0m0.015s
sys    0m0.000s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
 8.17s
 8.17s
commit: 1000

real    0m8.254s
user    0m0.000s
sys    0m0.015s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
 8.31s
 8.31s
commit: 1000

real    0m8.405s
user    0m0.000s
sys    0m0.015s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.01s
commit: 1

real    0m0.080s
user    0m0.000s
sys    0m0.031s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.02s
 0.03s
commit: 1

real    0m0.080s
user    0m0.000s
sys    0m0.000s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
16.07s
16.07s
commit: 2000

real    0m16.124s
user    0m0.015s
sys    0m0.000s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
17.50s
17.50s
commit: 2000

real    0m17.572s
user    0m0.000s
sys    0m0.031s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.02s
 0.04s
commit: 1

real    0m0.102s
user    0m0.000s
sys    0m0.015s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.12s
 0.14s
commit: 1

real    0m0.240s
user    0m0.015s
sys    0m0.031s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.17s
 0.36s
commit: 1

real    0m0.427s
user    0m0.015s
sys    0m0.000s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.83s
 1.03s
commit: 1

real    0m1.080s
user    0m0.000s
sys    0m0.015s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 1.79s
 3.32s
commit: 1

real    0m3.364s
user    0m0.000s
sys    0m0.015s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 8.40s
 9.81s
commit: 1

real    0m9.867s
user    0m0.000s
sys    0m0.000s
win7(64) usb-硬盘盒(sata2 西数)

win7(32) 笔记本硬盘

技术分享
------ 1000 in disk ----
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.09s
commit: 1

real    0m0.750s
user    0m0.000s
sys    0m0.031s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.04s
 0.12s
commit: 1

real    0m0.774s
user    0m0.000s
sys    0m0.015s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
103.46s
103.46s
commit: 1000

real    1m44.107s
user    0m0.000s
sys    0m0.015s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
90.21s
90.21s
commit: 1000

real    1m30.839s
user    0m0.000s
sys    0m0.031s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.18s
commit: 1

real    0m0.903s
user    0m0.000s
sys    0m0.000s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.06s
 0.30s
commit: 1

real    0m0.949s
user    0m0.000s
sys    0m0.015s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
179.64s
179.64s
commit: 2000

real    3m0.283s
user    0m0.015s
sys    0m0.015s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
183.82s
183.82s
commit: 2000

real    3m4.585s
user    0m0.000s
sys    0m0.031s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.05s
 0.22s
commit: 1

real    0m0.881s
user    0m0.000s
sys    0m0.015s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.21s
 0.34s
commit: 1

real    0m1.000s
user    0m0.000s
sys    0m0.000s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.45s
 0.71s
commit: 1

real    0m1.398s
user    0m0.000s
sys    0m0.015s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 2.21s
 2.38s
commit: 1

real    0m3.161s
user    0m0.000s
sys    0m0.016s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 3.93s
 4.66s
commit: 1

real    0m5.381s
user    0m0.015s
sys    0m0.000s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
20.62s
21.12s
commit: 1

real    0m21.750s
user    0m0.015s
sys    0m0.015s
win7(32) 笔记本硬盘

linux(vm11) vm虚拟硬盘

技术分享
nux 2.6.32-358.el6.x86_64
cpu MHz        : 3591.760
cpu MHz        : 3591.760

------ 1000 in disk ----
rm: cannot remove `test.db: No such file or directory
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.00s
commit: 1

real    0m0.032s
user    0m0.009s
sys    0m0.007s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.01s
 0.01s
commit: 1

real    0m0.038s
user    0m0.012s
sys    0m0.008s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
 0.95s
 0.95s
commit: 1000

real    0m3.372s
user    0m0.015s
sys    0m0.944s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
 0.89s
 0.89s
commit: 1000

real    0m3.047s
user    0m0.023s
sys    0m0.872s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.01s
commit: 1

real    0m0.049s
user    0m0.012s
sys    0m0.009s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.03s
 0.03s
commit: 1

real    0m0.057s
user    0m0.034s
sys    0m0.008s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
 1.55s
 1.55s
commit: 2000

real    0m5.661s
user    0m0.021s
sys    0m1.540s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
 1.90s
 1.90s
commit: 2000

real    0m7.214s
user    0m0.059s
sys    0m1.860s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.04s
 0.04s
commit: 1

real    0m0.071s
user    0m0.045s
sys    0m0.006s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.08s
 0.08s
commit: 1

real    0m0.112s
user    0m0.084s
sys    0m0.006s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.14s
 0.14s
commit: 1

real    0m0.216s
user    0m0.127s
sys    0m0.027s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.48s
 0.48s
commit: 1

real    0m0.533s
user    0m0.481s
sys    0m0.017s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 1.38s
 1.62s
commit: 1

real    0m2.284s
user    0m1.033s
sys    0m0.600s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 4.60s
 4.68s
commit: 1

real    0m5.013s
user    0m4.444s
sys    0m0.244s
linux(vm11) vm虚拟硬盘

linux(vm11) usb-硬盘盒(sata2 西数)

技术分享
Linux 2.6.32-358.el6.x86_64
cpu MHz        : 3591.760
cpu MHz        : 3591.760
 
------ 1000 in disk ----
rm: cannot remove `test.db: No such file or directory
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.00s
commit: 1

real    0m0.237s
user    0m0.008s
sys    0m0.010s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.02s
 0.03s
commit: 1

real    0m0.088s
user    0m0.023s
sys    0m0.011s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
 2.18s
 2.18s
commit: 1000

real    0m42.629s
user    0m0.089s
sys    0m2.105s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
 2.35s
 2.35s
commit: 1000

real    0m43.325s
user    0m0.084s
sys    0m2.281s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.01s
commit: 1

real    0m0.126s
user    0m0.015s
sys    0m0.012s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.03s
 0.03s
commit: 1

real    0m0.121s
user    0m0.035s
sys    0m0.006s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
 4.43s
 4.43s
commit: 2000

real    1m28.085s
user    0m0.107s
sys    0m4.333s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
 4.76s
 4.76s
commit: 2000

real    1m28.598s
user    0m0.207s
sys    0m4.565s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.03s
 0.03s
commit: 1

real    0m0.167s
user    0m0.040s
sys    0m0.007s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.08s
 0.08s
commit: 1

real    0m0.205s
user    0m0.089s
sys    0m0.010s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.18s
 0.21s
commit: 1

real    0m1.191s
user    0m0.196s
sys    0m0.029s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.55s
 0.56s
commit: 1

real    0m1.526s
user    0m0.558s
sys    0m0.021s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 1.38s
 1.46s
commit: 1

real    0m11.102s
user    0m1.337s
sys    0m0.140s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 4.61s
 4.71s
commit: 1

real    0m14.392s
user    0m4.511s
sys    0m0.217s
linux(vm11) usb-硬盘盒(sata2 西数)

xp(vm11) vm虚拟硬盘

技术分享
MINGW32_NT-5.1 1.0.17(0.48/3/2)
cat: /proc/cpuinfo: No such file or directory

------ 1000 in disk ----
rm: cannot lstat `test.db: No such file or directory
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.00s
commit: 1

real    0m0.047s
user    0m0.030s
sys    0m0.015s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.00s
 0.02s
commit: 1

real    0m0.047s
user    0m0.015s
sys    0m0.000s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
 6.39s
 6.39s
commit: 1000

real    0m6.422s
user    0m0.015s
sys    0m0.000s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
 5.14s
 5.14s
commit: 1000

real    0m5.156s
user    0m0.015s
sys    0m0.015s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.01s
commit: 1

real    0m0.031s
user    0m0.015s
sys    0m0.015s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.01s
 0.03s
commit: 1

real    0m0.062s
user    0m0.015s
sys    0m0.030s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
11.67s
11.67s
commit: 2000

real    0m11.703s
user    0m0.015s
sys    0m0.000s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
11.20s
11.20s
commit: 2000

real    0m11.219s
user    0m0.015s
sys    0m0.000s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.02s
 0.03s
commit: 1

real    0m0.063s
user    0m0.015s
sys    0m0.000s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.09s
 0.11s
commit: 1

real    0m0.140s
user    0m0.015s
sys    0m0.015s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.20s
 0.31s
commit: 1

real    0m0.343s
user    0m0.015s
sys    0m0.015s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.86s
 1.14s
commit: 1

real    0m1.172s
user    0m0.015s
sys    0m0.016s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 3.98s
 4.50s
commit: 1

real    0m4.531s
user    0m0.015s
sys    0m0.000s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 9.13s
 9.83s
commit: 1

real    0m9.859s
user    0m0.015s
sys    0m0.015s
xp(vm11) vm虚拟硬盘

xp(vm11) usb-硬盘盒(sata2 西数)

技术分享
MINGW32_NT-5.1 1.0.17(0.48/3/2)
cat: /proc/cpuinfo: No such file or directory
 
------ 1000 in disk ----
rm: cannot lstat `test.db: No such file or directory
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.05s
commit: 1

real    0m0.094s
user    0m0.015s
sys    0m0.015s
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.02s
 0.09s
commit: 1

real    0m0.156s
user    0m0.015s
sys    0m0.015s
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
36.84s
36.84s
commit: 1000

real    0m36.890s
user    0m0.015s
sys    0m0.015s
repeat insert 1000 times, in auto trans(s), with each stmt prepared
37.36s
37.36s
commit: 1000

real    0m37.407s
user    0m0.015s
sys    0m0.015s
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.06s
commit: 1

real    0m0.141s
user    0m0.015s
sys    0m0.030s
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.01s
 0.06s
commit: 1

real    0m0.125s
user    0m0.015s
sys    0m0.000s
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
77.27s
77.27s
commit: 2000

real    1m17.312s
user    0m0.015s
sys    0m0.015s
repeat insert 2000 times, in auto trans(s), with each stmt prepared
75.89s
75.89s
commit: 2000

real    1m15.954s
user    0m0.015s
sys    0m0.031s
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.09s
commit: 1

real    0m0.141s
user    0m0.015s
sys    0m0.031s
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.08s
 0.16s
commit: 1

real    0m0.203s
user    0m0.015s
sys    0m0.015s
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.27s
 0.73s
commit: 1

real    0m0.781s
user    0m0.015s
sys    0m0.015s
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.91s
 2.91s
commit: 1

real    0m2.969s
user    0m0.015s
sys    0m0.015s
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 4.75s
 5.55s
commit: 1

real    0m5.719s
user    0m0.015s
sys    0m0.078s
repeat insert 1000000 times, in 1 trans, with each stmt prepared
10.56s
11.66s
commit: 1

real    0m11.734s
user    0m0.015s
sys    0m0.031s
xp(vm11) usb-硬盘盒(sata2 西数)

磁盘不利于零散的读写。
vm虚拟硬盘,就是vm运行所在的操作系统的文件系统中一个或一组文件,频繁的IO操作并没有实时同步到物理扇区。
硬盘用硬盘盒转换成usb接口后,硬盘盒与操作系统之间的驱动方式是usb,不再是磁盘驱动,所以IO操作也是没有实时同步到物理扇区。
因为挂在sata(或其它类型主板上硬盘接口)上的物理磁盘,磁盘驱动会将所有IO同步请求实时同步到物理扇区,所以十分的慢。

 

上上一篇 《SQLite存储格式》

上一篇 《SQLite内存模式,但不是内存数据库》

SQLite性能 - 意想不到,但又情理之中的测试结果。