免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
楼主: osdba

PostgreSQL与MySQL比较 [复制链接]

论坛徽章:
0
发表于 2010-11-25 15:02 |显示全部楼层
不错。收下了。

论坛徽章:
0
发表于 2011-01-26 11:54 |显示全部楼层
好,顶,学习了、

论坛徽章:
1
2015年辞旧岁徽章
日期:2015-03-03 16:54:15
发表于 2011-01-30 17:05 |显示全部楼层
今天用了MYSQL 5.5,插入主表10万条,明细表30万条,更新流水号表10万次,
用了2分34秒,比POSTGRESQL快很多倍。

论坛徽章:
3
数据库技术版块每日发帖之星
日期:2015-06-18 22:20:00数据库技术版块每日发帖之星
日期:2015-06-21 22:20:00数据库技术版块每日发帖之星
日期:2015-08-27 06:20:00
发表于 2011-02-11 16:55 |显示全部楼层
本帖最后由 osdba 于 2011-02-11 17:08 编辑

回复 44# tony_trh


你是如何比较出MySQL5.5比PostgreSQL快了很多倍?

能否贴出来具体的操作过程?MySQL使用的是什么存储引擎?如果没有具体的说明,你的这个结论是没有任何意义的。

如果你测试时,PostgreSQL数据库的没有优化,参数配置也有问题,而MySQL是经过优化的,这样比较出来可能会差几倍, 但这样的结果没有什么意义。

还有就是你总共就操作了几 十万条数据,就花了2分34秒,好象比较慢啊,怎么还说性能很好?有可能是机器很差或每行的数据量比较大?或提交的太频繁?

我在我的笔记本上的测试:

osdba=# create table test (id int primary key ,name varchar(64));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
Time: 214.084 ms
osdba=# insert into test select generate_series(1,1000000),'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
INSERT 0 1000000
Time: 14856.029 ms

插入1000000条记录,只花了15秒左右。但实际上这个速度说明不了 任何问题,做插入时,数据实际上并不马上写到硬盘中,实际上是在内存中的,只有WAL日志写到磁盘中了,而WAL日志是顺序写入的,所以一般也不会存在性能问题。 对于OLTP的数据库,批量更新一般说明不了数据库的性能,主要还是要看随机更新的性能和随机读的性能。

论坛徽章:
1
2015年辞旧岁徽章
日期:2015-03-03 16:54:15
发表于 2011-02-19 21:56 |显示全部楼层
我是这样测试的:
先建设三个表:

CREATE TABLE stocks
(
  partno character varying(16) NOT NULL,
  descr character varying(50),
  onhand numeric(14,4),
  price numeric(14,4),
  CONSTRAINT pk_partno PRIMARY KEY (partno)
);


CREATE TABLE trans
(
  trans_no character varying(20) NOT NULL,
  partno  character varying(20) NOT NULL,

  qty numeric(14,4),

  CONSTRAINT pk_trans PRIMARY KEY (trans_no, partno)
);

CREATE TABLE sno
(

  sno integer

);

论坛徽章:
1
2015年辞旧岁徽章
日期:2015-03-03 16:54:15
发表于 2011-02-19 21:57 |显示全部楼层
然后用下列的存储过程,在MYSQL中执行,


DELIMITER //  

drop procedure test;

create  procedure test()
begin

/*
--stocks   partno,descr,onhand,price
--trans    trans_no,partno,qty
--sno      sno
*/

declare   i  int;
declare   j  int;
declare   rec int;
declare   vsno int;
declare   stime  int;
declare   etime  int;
declare   vpartno varchar(16);
declare   vonhand  int;
declare   vprice  numeric(10,2);


   start transaction;

   delete  from stocks;
   delete  from trans;
   update sno set sno = 1 ;   

   set rec = 1;
   set i = rec;
   while i <= rec+100000 do
      
        set vpartno = trim(cast(i as char(16)));
        set vonhand =  round(rand() *1000);
        set vprice  = rand() * 100;
      
        insert into stocks(partno,descr,onhand,price)
               values(vpartno,  'ABCDE',  vonhand, vprice);
      
        set j = 1;
        while j <= 3 do
                select sno into vsno from sno;
                set vsno = vsno + 1 ;

                update  sno  set sno = vsno ;
                       
                insert into trans(trans_no,partno,qty)
                       values(vsno,  vpartno, vonhand);
              set j = j  +  1;
         end while ;        
         
        set i = i  +  1;
   end while ;

   commit;

end;  //

论坛徽章:
1
2015年辞旧岁徽章
日期:2015-03-03 16:54:15
发表于 2011-02-19 22:00 |显示全部楼层
我的电脑是很落后,但测试POSTGRESQL,也是用这台电脑,

你可以把存储过程稍修改下,在PG里运行,看PG慢多少,

如果你能调优PG,速度跟MYSQL差不多,

请你告诉我,你设置了什么参数?

谢谢。

论坛徽章:
3
数据库技术版块每日发帖之星
日期:2015-06-18 22:20:00数据库技术版块每日发帖之星
日期:2015-06-21 22:20:00数据库技术版块每日发帖之星
日期:2015-08-27 06:20:00
发表于 2011-02-22 19:44 |显示全部楼层
本帖最后由 osdba 于 2011-02-22 19:57 编辑

我仔细看了你存储过程,发现慢的原因是你使用了一张表sno做记数器使用:
        while j <= 3 do
                select sno into vsno from sno;
                set vsno = vsno + 1 ;
                update  sno  set sno = vsno ;
                insert into trans(trans_no,partno,qty)
                       values(vsno,  vpartno, vonhand);
              set j = j  +  1;
         end while ;   

由于在PostgreSQL中,没有回滚段,多次对sno做更新,实际上,每更新一次,会生成一个新的版本行,同时由于你更新这个表sno是在一个事务中的,所以vacuum不会清理这个表,最后这个表中虽然只有一条记录,但这个表最终会变成3M大小,这样每次更新时找到这条记录会很慢,这导致了性能的大大下降。所以在PostgreSQL最好不要这样使用表做这样的记数器。可以使用sequence做记数器。
最终在PostgreSQL中的测试代码如下:
CREATE TABLE stocks
(
  partno character varying(16) NOT NULL,
  descr character varying(50),
  onhand numeric(14,4),
  price numeric(14,4),
  CONSTRAINT pk_partno PRIMARY KEY (partno)
);


CREATE TABLE trans
(
  trans_no character varying(20) NOT NULL,
  partno  character varying(20) NOT NULL,
  qty numeric(14,4),
  CONSTRAINT pk_trans PRIMARY KEY (trans_no, partno)
);


CREATE SEQUENCE seq_sno start with 1 CACHE 100;

create or replace function test() RETURNS VOID AS
$$
declare
i  int;
j  int;
rec int;
vsno int;
stime  int;
etime  int;
vpartno varchar(16);
vonhand  int;
vprice  numeric(10,2);
BEGIN
    delete  from stocks;
    delete  from trans;
    update sno set sno = 1 ;
    rec := 1;
    i := rec;
    while i <= rec+100000 loop
        vpartno := i;
        vonhand :=  round(random() *1000);
        vprice  := random() * 100;
      
        insert into stocks(partno,descr,onhand,price)
               values(vpartno,  'ABCDE',  vonhand, vprice);
      
        j := 1;
        while j <= 3 loop
            select nextval('seq_sno') into vsno;
            vsno := vsno + 1 ;
            insert into trans(trans_no,partno,qty) values(vsno,  vpartno, vonhand);
            j := j + 1;
        end loop;
         
        i := i + 1;
   end loop;
end;
$$
LANGUAGE plpgsql;


改成这样后,测试:
osdba=# select test();
LOG:  checkpoints are occurring too frequently (18 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
test
------

(1 row)

Time: 24059.938 ms

可以看到只需要24秒就完成了这个操作。

同样在MySQL中执行你那段代码所需要的时间为73秒:
mysql> call test();
Query OK, 0 rows affected, 65535 warnings (1 min 13.39 sec)
当然由于MySQL中没有sequence,所以只能使用表做记数器,所以慢了一些。



这个测试就是在我的笔记本上进行的,我的笔记本的CPU类型为:
model name        : Intel(R) Core(TM)2 Duo CPU     T7250  @ 2.00GHz
cpu MHz                : 800.000
cache size        : 2048 KB

论坛徽章:
1
2015年辞旧岁徽章
日期:2015-03-03 16:54:15
发表于 2011-02-22 23:13 |显示全部楼层
非常谢谢osdba兄对该问题的分析和探讨,如果象你那么改的话,速度确实上去了,在我的破烂机器上66秒左右也就执行完成了。

但我的本意,不是把SNO仅仅做为流水号,我的本意就是在插入的过程中,确实还要更新另外的表的很多数据(虽然这里只是个流水号)

如果你不修改我那个表,在你电脑上速度是多少? 我估计应该也会很慢吧,但同样,MYSQL,ORACLE,确很快。

有什么办法提高它的速度吗?

论坛徽章:
3
数据库技术版块每日发帖之星
日期:2015-06-18 22:20:00数据库技术版块每日发帖之星
日期:2015-06-21 22:20:00数据库技术版块每日发帖之星
日期:2015-08-27 06:20:00
发表于 2011-02-23 10:33 |显示全部楼层
本帖最后由 osdba 于 2011-02-23 10:36 编辑

这个更新慢的问题仅存在于你这种做测试的这个特殊场景中。一般的业务情况下,这个问题是不存在的,只要不在你这个特殊的测试场景中,PostgreSQL更新是很快的。

我前面写的原因,你可能没有仔细看,这里再做一个详细说明:

慢的原因是你在一个事务中多次对同一条数据进行更新,而对这同一条记录的更新,而你一直不提交,导致vacuum一直不能对这个表做清理,于是后面的更新时,需要找到当前最新版本的记录时花了很长时间。如果你更新100条就提交一次,这样就有可能让vacuum来整理这个表的多版本数据,那么后面更新也会快很多。当然如果你的表中是有很多条记录,如10000条记录,表上再有索引时,每次更新时都是选不同的记录时,这样找到要更新的记录会很快,那么这样的更新也是会很快的。

见我下面的测试:
我先建两个表sno1和sno2,表sno1中只有一条数据,而sno2中有10000条数据,对sno1更新100000次,而对sno2表也更新100000次,但由于sno2有10000条记录,所有每条记录只更新了10次,下面的测试可以看到对sno1的更新很慢,花了169秒,而对sno2的更新只花了4.5秒:

create table sno1(sno int );
insert into sno1 values(1);
create or replace function updatetest1() RETURNS VOID AS
$$
declare
i  int;
BEGIN
    i := 1;
    while i <= 100000 loop
        update  sno1  set sno = sno +1;
        i := i + 1;
    end loop;
end;
$$
LANGUAGE plpgsql;


create table sno2(id int primary key,sno int );

insert into sno2 select generate_series(1,10000),1;

create or replace function updatetest2() RETURNS VOID AS
$$
declare
i  int;
n  int;
BEGIN
    i := 1;
    n := 1;
    while i <= 100000 loop
        update  sno2  set sno = sno +1 where id=n;
        i := i + 1;
        n := n + 1;
        IF n> 10000 THEN
            n := 1;
        END IF;
    end loop;
end;
$$
LANGUAGE plpgsql;

osdba=# select updatetest1();
updatetest1
-------------

(1 row)

Time: 169260.575 ms
osdba=# select updatetest2();
updatetest2
-------------

(1 row)

Time: 4544.237 ms
osdba=#
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP