我们试图了解Oracle和PostgreSQL的原始性能。我们有丰富的oracle经验,但对PostgreSQL是新手。我们将对我们的数据运行大量查询,等等。但首先我们想看看它们在基本的内核任务上是如何执行的,比如数学和分支,因为SQL是建立在这些基础上的。
在AWS RDS中,我们创建了两个db.m3.2xlarge实例,其中一个使用oracle 11.2.0.4。包含v1版本的许可证,另一个使用PostgreSQL (9.3.3)我们都运行了100万次平方根(从1到1 mill)的代码。然后在If..Then语句中执行相同的操作。
结果有点令人不安:
Oracle 4.8 seconds
PostgreSQL 21.803 seconds
添加if语句:
Oracle 4.78 seconds
PostgreSQL 24.4 seconds
代码Oracle平方根
SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
n NUMBER := 0;
BEGIN
FOR f IN 1..10000000
LOOP
n := SQRT (f);
END LOOP;
END;
PostgreSQL
DO LANGUAGE plpgsql $$ DECLARE n real;
BEGIN
FOR f IN 1..10000000 LOOP
n = SQRT (f);
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
n NUMBER := 0;
BEGIN
FOR f IN 1..10000000
LOOP
if 0 =0 then
n := SQRT (f);
end if;
END LOOP;
postgres添加if
DO LANGUAGE plpgsql $$ DECLARE n real;
BEGIN
FOR f IN 1..10000000 LOOP
if 0=0 then
n = SQRT (f);
end if;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
我在PostgreSQL中使用了一个匿名块。我也把它作为一个函数,得到了相同的结果
CREATE OR REPLACE FUNCTION testpostgrescpu()
RETURNS real AS
$BODY$
declare
n real;
BEGIN
FOR f IN 1..10000000 LOOP
n = SQRT (f);
END LOOP;
RETURN n;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION testpostgrescpu()
OWNER TO xxx
基于我们对PostgreSQL的了解以及它在很多方面与Oracle的可比性,我们被结果吓了一跳。我们的PostgreSQL代码写错了吗?我们错过了什么,或者这就是它的方式。
注意:一旦我们开始在Oracle和PostgreSQL中对完全相同的数据运行查询,我们就会看到类似的模式。在基本查询上差别不大,但是当查询变得越来越复杂时,Oracle的速度大约是3-5。
同样,这是在相同的AWS RDS实例上运行的,我们在不同的日子运行了很多次,结果总是相同的
这是一点猜测。我希望Oracle在这样的计算上比Postgres慢。然而,我认为你可能有一个性能问题,在文档中:
numeric类型可以存储具有非常大位数的数字精确地进行计算。特别推荐给精确的储存货币数量和其他数量的必需的。然而,与此相比,对数值的算术运算非常缓慢类型中描述的浮点类型下一部分。
你的代码没有为f
声明数据类型。根据上下文,它将被赋值为一个整数。然而,sqrt()
函数要么采用浮点数,要么采用numeric
常数。它们是不相等的(我猜当一个numeric
时,函数会变慢)。我的猜测是,整数f
被转换为number
,而不是real
的操作。
尝试通过显式地将f
声明为real
或在函数调用之前强制转换它来运行测试。这可能会提高性能。
我看不出这将是一个有用的指标,除非你碰巧在pl/sql或pg pl/sql中做大量的计算。无论如何,我们并不推荐这样做,它可以在C中本地完成,也可以调用Java类。Oracle可以在某些平台/版本上将pl/sql原生编译为c语言,因此这可能是您在速度上看到巨大差异的原因之一。
数据库的速度最好取决于其执行查询(可能包括使用正确的统计信息进行连接)或写入和更新数据的能力。对于像Oracle和Postgres sql这样的数据库,在多用户和事务环境中做这个测试会是一个更好的测试,假设您有一个OLTP应用程序。据我所知,Postgres在与Oracle的竞争中做得很好,但这取决于你的应用程序。
为了更好地描述和分析Oracle,我建议查看asktom https://asktom.oracle.com/论坛。我不确定postgres是否有类似的东西。
老实说,你的基准测试完全没有意义。
你正在计算100万个平方根,而立即扔掉结果;根据你的优化设置,我希望编译器完全摆脱你的循环。
你应该至少将结果存储在某个地方,或者将它们用于另一个计算(例如计算总和)。
同样,我不同意你的说法,即。数学和分支,因为SQL是建立在之上的。RDBMS可以做很多事情,但是有效地计算平方根肯定不是它的优点之一。如果你真的,真的需要这种计算,将它从数据库中移出并使用某种专门的软件来完成它会更有意义,例如R。
正如其他人所说,您的示例测试相当无意义。
我认为你所遇到的基本问题是你对PostgreSQL一无所知,并且正在尝试与Oracle相同的基本技巧。
我们在试着了解Oracle和PostgreSQL的原始性能
其实并不意味着什么,不是吗?除非您试图测量原始磁盘读取或诸如此类的。
我们已经尽可能地调整了它们(检查了所有参数,改变了随机页面成本,将seq扫描设置为关闭等)
嗯,将seq_scan设置为off不太可能是您想要做的事情,而不是在探索测试用例时强制计划器。你为什么要那样做?手册上哪里提到了?你不能说 你如何改变了random-page-cost,也不能说你如何确定你得到了正确的值。
我们发现,在PostgreSQL中,如果表的内存大于共享内存的25%,那么它的表数据就不会被缓存。
嗯,那显然是不可能的。缓存发生在PostgreSQL和OS级别,磁盘块将被缓存。你是如何衡量的?
(在我们的示例中,AWS 30gig实例的共享内存为7gb,一旦我们将表大小降到2gb以下,它就开始再次被缓存)
那么如何调整shared_mem的大小呢?我试着想象一个场景,其中2G和7G都是合理的值,我遇到了麻烦。你没有提供任何内存使用信息,所以没有人能知道发生了什么。
我认为你需要做的是:
- 喝一杯热茶/咖啡。
- 通读说明书
- 查看wiki,例如:Tuning Your PostgreSQL Server.
- 一旦你对work-mem和shared-mem如何操作有了一个合理的把握,在服务器上放置一些测量,这样你就可以看到内存使用情况,磁盘I/O等。
- 确保你对如何解释分析你的查询有一个基本的了解。 订阅一个postgresql.org邮件列表(性能似乎是合理的),这样你就有地方可以举行讨论。
- 然后开始考虑测量性能。
有 的情况下,Oracle将比PostgreSQL更聪明,但一个普遍的全面的大减速并不是你期望看到的。
我对这些基准测试有点惊讶,但我倾向于理论上同意Frank Schmitt的观点。虽然我不会说它"完全没有意义",但如果你要比较两个DBMS系统,我认为你要看的不仅仅是每个系统如何进行数学运算。
不管怎样,我几乎只在我的前雇主那里使用Oracle。在我的新角色中,我们的主要DBMS是Sybase ASE,它缺少许多我已经习惯使用的工具,我们使用PostgreSQL作为权宜之计。毫无疑问,有比我要提供的更好的文章,但从新手的角度来看:
我对Oracle的怀念:
- 操作系统认证(允许用户基于其Windows/Unix凭据登录的能力),没有混乱的密码问题
- "merge"语句
- 通过OCI (ODP.net, DBD::Oracle)批量插入和更新
- 通过过程部分提交的能力
- 优秀的ide的可用性(如All Around Automation PL/SQL Developer) <
- 位图索引/gh>
- 更无缝的DBlinks
PostgreSQL的优点:
- 价格标签
- "copy"比SQL*Loader更容易使用
- ODBC和Npgsql.dll等驱动程序的可用性 SQL内部的自定义函数不会拖低查询性能
- 使用PL(如Perl)以外的语言创建自定义函数的能力
- 更容易使用数据类型,如日期,时间,时间戳和间隔
- 一个近乎直观的更新语法(并且不需要额外的存在包装器)
—edit—
我应该补充一下,直到今天我还没有弄清楚如何在Oracle中做到这一点:
select * from pg_views
where definition like '%inventory.turns%'
这里的问题是类型转换。PostgreSQL sqrt
函数被定义为接受双精度(float)或数字。因此,在您的代码中发生的事情是,整数被强制转换为float(这是快速的)sqrt
的float版本,结果是从float类型强制转换为real(这是缓慢的)。
要明白我在说什么,试着比较这两个代码示例的运行时间:
DO LANGUAGE plpgsql $$
DECLARE n real;
BEGIN
FOR f IN 1..10000000 LOOP
n = f::float;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
DO LANGUAGE plpgsql $$
DECLARE n float;
BEGIN
FOR f IN 1..10000000 LOOP
n = f::float;
END LOOP;
RAISE NOTICE 'Result => %',n;
END $$;
在我的机器上,第一个需要16秒,第二个只需要3秒。
这个故事的寓意是,您需要小心使用的数据类型。
您没有执行任何PostgreSQL基准测试。
你真正做的是一个pl/pgsqlbenchmark。
您可以使用任何这些PostgreSQL语言扩展来进行此测试,并且您可能会得到相当不同的结果。
有一个pl/pgsql解释器来执行你的代码。默认情况下,它与PostgreSQL一起安装。查看更多信息:
- pl/pgsql实施
- pl/pgsql概述gh>
使用pl/java可以运行JVM, pl/sh可以运行shell。
plpgsql
不是一种针对性能进行广泛优化的语言。
我不知道为什么我要在数据库内的显式循环中计算1000万个平方根,但如果我这样做,我会使用plperl
来完成。