PostgreSQL MySQL 的一次速度测试-白红宇

PostgreSQL MySQL 的一次速度测试

阅读量：6766 次

发布时间：2019-06-26

本文共 3236 字，大约阅读时间需要 10 分钟。

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 数据，


     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_mon=10, tm_mday=24, tm_hour=1, tm_min=10, tm_sec=34, tm_wday=4, tm_yday=297, tm_isdst=0)=> elasped lpush: 19.3326239586 s

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

~~ => elasped lpush: 137.065089941 s ~~ =====pg test==== time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=1, tm_min=16, tm_sec=8, tm_wday=4, tm_yday=297, tm_isdst=0) time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=1, tm_min=19, tm_sec=20, tm_wday=4, tm_yday=297, 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 htt]# python2.7 insert_time.py ===== mysql test===== ~~ => elasped lpush: 13.9316670895 s ~~ =====pg test==== time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=1, tm_min=24, tm_sec=28, tm_wday=4, tm_yday=297, tm_isdst=0) time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=1, tm_min=35, tm_sec=32, tm_wday=4, tm_yday=297, tm_isdst=0) => elasped lpush: 663.937125921 s

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

mysql  10k 数据：
    ===== 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_mon=10, tm_mday=24, tm_hour=2, tm_min=16, tm_sec=25, tm_wday=4, tm_yday=297, tm_isdst=0)time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=2, tm_min=18, tm_sec=43, tm_wday=4, tm_yday=297, tm_isdst=0)=> elasped lpush: 138.524701118 s=====pg  test====time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=2, tm_min=18, tm_sec=43, tm_wday=4, tm_yday=297, tm_isdst=0)time.struct_time(tm_year=2014, tm_mon=10, tm_mday=24, tm_hour=2, tm_min=21, tm_sec=57, tm_wday=4, tm_yday=297, tm_isdst=0)=> elasped lpush: 193.538767099 s

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


    import timeclass 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

转载于:https://my.oschina.net/innovation/blog/336818

你可能感兴趣的文章