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, 查询。。。 1 <!-- 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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |