postgresql mysql速度_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 数据，

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@localhost 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@localhost 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， 查询。。。

1

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