PostgreSQL MySQL 的一次速度测试

sql= """ INSERT INTO t_node (id,scan_node,md5,crc32,batch_id,sequence_id) VALUES(
    2893396,'PREP','0766A312E548F238B7D6866712800176','4C721F9C','20130201_101456_929692_test','1');"""

单进程 INSERT 10w 数据，

<!-- lang: shell -->
 python2.7  insert_time.py 
~~     ===== MysqL  test=====  ~~
~~    => elasped lpush: 14.9068849087 s ~~
=====pg  test====
time.struct_time(tm_year=2014,tm_mon=10,tm_mday=24,tm_hour=1,tm_min=10,tm_sec=14,tm_wday=4,tm_yday=297,tm_isdst=0)
time.struct_time(tm_year=2014,tm_sec=34,tm_isdst=0)
=> elasped lpush: 19.3326239586 s

INSERT 100w 数据: (没有 autocommit,postgres 进程中出现 IDLE in translation ) ~~[root@localhost htt]# python2.7 insert_time.py ~~ ~~===== MysqL test===== ~~

~~ => elasped lpush: 137.065089941 s ~~ =====pg test==== time.struct_time(tm_year=2014,tm_min=16,tm_sec=8,tm_isdst=0) time.struct_time(tm_year=2014,tm_min=19,tm_sec=20,tm_isdst=0) => elasped lpush: 192.175278902 s

插入完成后,发现上面的都没有提交 %_%

INSERT 10w: 开启 Autocommit ， Postgresql 单个进程速度不高，但磁盘一直繁忙： %util == 89% ~ 96% (约) Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 2.00 0.00 157.00 0.00 1.26 16.41 1.01 6.45 6.36 99.90 [root@localhost htt]# python2.7 insert_time.py ===== MysqL test===== ~~ => elasped lpush: 13.9316670895 s ~~ =====pg test==== time.struct_time(tm_year=2014,tm_min=24,tm_sec=28,tm_min=35,tm_sec=32,tm_isdst=0) => elasped lpush: 663.937125921 s

以为自己出错了呢？好吧，果然出错了。。。补测 PG 10k 条数据： =====pg test==== => elasped lpush: 65.9787540436 s

MysqL  10k 数据：
<!-- lang: shell -->
===== MysqL  test=====
=> elasped lpush: 266.817662001 s

MysqL 的都没提交？？哎！

INSERT 10k 条数据，一次 commit(),===== MysqL test===== => elasped lpush: 1.37652492523 s =====pg test==== => elasped lpush: 1.98603391647 s 最后总提交 100w insert 。

===== MysqL  test=====
time.struct_time(tm_year=2014,tm_hour=2,tm_sec=25,tm_min=18,tm_sec=43,tm_isdst=0)
=> elasped lpush: 138.524701118 s
=====pg  test====
time.struct_time(tm_year=2014,tm_min=21,tm_sec=57,tm_isdst=0)
=> elasped lpush: 193.538767099 s

好像测试 INSERT 是没用的，都是刷磁盘，好吧，下次测试 UPDATE，查询。。。

<!-- lang: python -->
import time

class Timer(object):
    def __init__(self,verbose=False):
        self.verbose = verbose

    def __enter__(self):
        self.start = time.time()
        return self

    def __exit__(self,*args):
        self.end = time.time()
        self.secs = self.end - self.start
        self.msecs = self.secs * 1000  # millisecs
        if self.verbose:
            print 'elapsed time: %f ms' % self.msecs

PostgreSQL MySQL 的一次速度测试

猜你在找的Postgre SQL相关文章