我需要一个真正的DBA意见。 Postgres 8.3在Macbook Pro上执行此查询需要200毫秒，而Java和Python在20毫秒(350,000行)内执行相同的计算：

SELECT count(id), avg(a), avg(b), avg(c), avg(d) FROM tuples;

使用SQL数据库时，这是正常现象吗？

模式(该表包含对调查的响应)：

CREATE TABLE tuples (id integer primary key, a integer, b integer, c integer, d integer);

\copy tuples from '350,000 responses.csv' delimiter as ','

我用Java和Python针对上下文编写了一些测试，它们粉碎了SQL(纯python除外)：

java   1.5 threads ~ 7 ms    
java   1.5         ~ 10 ms    
python 2.5 numpy   ~ 18 ms  
python 2.5         ~ 370 ms

即使sqlite3假定所有列都是字符串，它甚至也与Postgres竞争(对比：即使在Postgres中仅切换到数字列而不是整数也会导致速度降低10倍)

我尝试过但没有成功的调整包括(盲目遵循一些网络建议)：

increased the shared memory available to Postgres to 256MB    
increased the working memory to 2MB
disabled connection and statement logging
used a stored procedure via CREATE FUNCTION ... LANGUAGE SQL

所以我的问题是，我的经验是否正常，这是使用SQL数据库时可以期望的吗？我可以理解ACID必须要付费，但这在我看来有点疯狂。我并不是在要求实时游戏速度，但是由于Java可以在20毫秒内处理数百万个双打，所以我感到有些嫉妒。

是否有更好的方法以便宜的价格(在金钱和服务器复杂性方面)进行简单的OLAP？我研究了Mondrian和Pig + Hadoop，但对维护另一个服务器应用程序并不感到兴奋，并且不确定它们是否会有所帮助。

可以这么说，没有Python代码和Java代码可以完成内部所有工作。我只是生成4个数组，每个数组具有350,000个随机值，然后取平均值。我没有在时序中包括生成，仅在平均步骤中。 Java线程计时使用4个线程(每个数组平均1个线程)，虽然过大，但绝对是最快的。

sqlite3计时由Python程序驱动，并从磁盘运行(不是：memory :)

我意识到Postgres在幕后做得更多，但是对于我来说，大多数工作并不重要，因为这是只读数据。

Postgres查询不会更改后续运行的时间。

我已经重新运行Python测试，包括将其从磁盘后台处理。计时速度大大降低到将近4秒。但是我猜想Python的文件处理代码几乎都是用C语言编写的(虽然可能不是csv lib吗？)，所以这向我表明Postgres也没有从磁盘中流式传输(或者我们是正确的，我应该低头看一下)在任何人写他们的存储层之前！)

解决方案

回答

我认为结果并不令人惊讶-如果说Postgres如此之快，那无非是。

一旦有机会缓存​​数据，Postgres查询第二次运行会更快吗？为了公平起见，我们对Java和Python的测试应首先涵盖获取数据的成本(最好是从磁盘上加载数据)。

如果此性能水平实际上是应用程序遇到的问题，但由于其他原因需要RDBMS，则可以查看memcached。这样我们便可以更快地缓存对原始数据的访问，并可以用代码进行计算。

回答

我想说测试方案不是真的有用。为了完成数据库查询，数据库服务器需要执行以下几个步骤：

• 解析SQL
• 制定查询计划，即e。确定要使用的索引(如果有)，优化等。
• 如果使用了索引，请在其中搜索指向实际数据的指针，然后转到数据中的适当位置，或者
• 如果不使用索引，则扫描整个表以确定需要哪些行
• 将数据从磁盘加载到临时位置(希望但不一定是内存)
• 执行count()和avg()计算

因此，在Python中创建一个数组并获取平均值基本上会跳过所有这些步骤，并保存最后一个步骤。由于磁盘I / O是程序必须执行的最昂贵的操作之一，因此这是测试中的主要缺陷(另请参见我之前在此处提出的该问题的答案)。即使我们在其他测试中从磁盘读取数据，该过程也完全不同，并且很难说出结果的相关性。

为了获得有关Postgres花费时间的更多信息，我建议进行以下测试：

• 将查询的执行时间与没有聚合功能的SELECT进行比较(即剪切第5步)
• 如果发现聚合导致显着减慢，请尝试使用Python来加快聚合速度，并从比较中通过普通SELECT获取原始数据。

为了加快查询速度，请首先减少磁盘访问。我非常怀疑是耗时的汇总。

有几种方法可以做到这一点：

• 通过数据库引擎自身的功能或者使用诸如memcached之类的工具将数据缓存在内存中以供后续访问
• 减少存储数据的大小
• 优化索引的使用。有时，这可能意味着完全跳过索引使用(毕竟，这也是磁盘访问)。对于MySQL，我似乎还记得如果假设查询获取表中所有数据的10％以上，则建议跳过索引。
• 如果查询充分利用了索引，那么我知道对于MySQL数据库，将索引和数据放在单独的物理磁盘上是有帮助的。但是，我不知道这是否适用于Postgres。
• 如果由于某种原因无法在内存中完全处理结果集，则还可能存在更复杂的问题，例如将行交换到磁盘。但是，在我遇到严重的性能问题(我找不到其他解决方法)之前，我将不进行此类研究，因为它需要我们了解过程中许多底层细节的知识。

更新：

我只是意识到，我们似乎没有使用上述查询的索引，而且很可能也没有使用索引，因此我对索引的建议可能无济于事。对不起。不过，我会说聚合不是问题，但磁盘访问是问题。无论如何，我都会保留索引的内容，它可能仍然有一些用处。

回答

RDBMS通常为我们做的另一件事是通过保护我们避免被另一个进程同时访问来提供并发性。这是通过放置锁来完成的，因此会有一些开销。

如果我们要处理的是永远不变的完全静态数据，特别是如果我们处于基本"单一用户"的情况下，那么使用关系数据库并不一定会带来很多好处。

回答

这些是非常详细的答案，但是它们主要是在问一个问题：鉴于数据很容易装入内存，需要并发读取但无需写操作并且一遍又一遍地查询相同的查询，我如何在不离开Postgres的情况下获得这些好处。

是否可以预编译查询和优化计划？我本以为存储过程可以做到这一点，但这并没有真正的帮助。

为了避免磁盘访问，有必要将整个表缓存在内存中，我可以强制Postgres这样做吗？我认为它已经在这样做了，因为查询在重复运行后仅200毫秒内就执行了。

我可以告诉Postgres该表是只读的，以便它可以优化任何锁定代码吗？

我认为可以用一个空表来估算查询的构建成本(时间范围为20-60毫秒)

我仍然看不到为什么Java / Python测试无效。 Postgres只是没有做更多的工作(尽管我仍然没有解决并发方面，只是缓存和查询构造)

更新：
我认为比较SELECTS的方法是不公平的，因为通过在驱动程序和序列化步骤中输入350,000到Python中来运行聚合来建议SELECTS，甚至由于聚合在格式化和显示方面的开销很难与时间分开而忽略聚合。如果两个引擎都在内存数据上运行，那应该是一个苹果对一个苹果的比较，但是我不确定如何保证这种情况已经发生。

我不知道如何添加评论，也许我没有足够的声誉？

回答

我们需要将postgres的缓存增加到整个工作集都适合内存的程度，然后才能看到性能与在程序中的内存性能相当。

回答

Postgres所做的工作远远超出其预期(保持数据一致性一开始！)。

如果这些值不必一定是100％固定，或者该表很少更新，但是我们经常运行此计算，则可能需要查看物化视图以加快速度。

(请注意，我没有在Postgres中使用物化视图，它们看起来有点怪异，但可能适合情况)。

物化视图

还应考虑实际连接到服务器的开销以及将请求发送到服务器并返回所需的往返行程。

我认为200毫秒对于这样的事情是非常好的，在我的oracle服务器上进行快速测试，相同的表结构具有约500k行并且没有索引，大约需要1 1.5秒，这几乎就是oracle吸收数据的原因磁盘。

真正的问题是200ms是否足够快？

• • • • • • -更多的 - - - - - - - - - -

我对使用实体化视图解决此问题很感兴趣，因为我从未真正与他们一起玩过。这是在oracle中。

首先，我创建了一个每分钟刷新一次的MV。

create materialized view mv_so_x 
build immediate 
refresh complete 
START WITH SYSDATE NEXT SYSDATE + 1/24/60
 as select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

在刷新时，没有返回任何行

SQL> select * from mv_so_x;

no rows selected

Elapsed: 00:00:00.00

刷新后，其速度比原始查询快得多

SQL> select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:05.74
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL>

如果我们插入基表，则无法立即查看MV的结果。

SQL> insert into so_x values (1,2,3,4,5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL>

但是，请稍等片刻，MV将在幕后更新，并且结果将按我们希望的那样快速返回。

SQL> /

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899460 7495.35823 22.2905352 5.00276078 2.17647059

Elapsed: 00:00:00.00
SQL>

这不是理想的。首先，它不是实时的，插入/更新将不会立即可见。另外，无论是否需要，都会运行查询来更新MV(可以调整到任何时间范围，也可以根据需要进行调整)。但是，这确实表明，如果我们能够接受的误差值还不能达到秒精度，那么对于最终用户来说，MV可以使它看起来快多少。

回答

感谢Oracle的时间安排，这就是我正在寻找的东西(虽然令人失望:-)

物化视图可能值得考虑，因为我认为我可以为大多数用户预计算该查询的最有趣形式。

我不认为查询往返时间应该很高，因为我是在运行Postgres的同一台计算机上运行查询的，所以它不会增加太多延迟吗？

我还对缓存大小进行了一些检查，似乎Postgres依赖操作系统来处理缓存，他们特别提到BSD是实现此目的的理想操作系统，因此我认为Mac OS在将表放入表中应该非常聪明。记忆。除非有人想到了更具体的参数，否则我认为更具体的缓存是我无法控制的。

最后，我可以忍受200毫秒的响应时间，但是知道7毫秒是可能的目标，这让我感到不满意，因为即使20到50毫秒的时间也将使更多的用户拥有更多的最新查询并摆脱掉很多缓存和预先计算的黑客。

我只是使用MySQL 5检查了计时，它们比Postgres差一些。因此，除非有一些重要的缓存突破，否则我想这就是我期望的关系数据库路由。

我希望我可以对一些答案投赞成票，但我的观点还不够。

回答

我本人是MS-SQL专家，我们将使用DBCC PINTABLE来保持表的高速缓存，并使用SET STATISTICS IO来查看它是从高速缓存而不是从磁盘读取的。

我在Postgres上找不到任何可以模仿PINTABLE的东西，但是pg_buffercache似乎提供了有关我们可能要检查的缓存中内容的详细信息，并查看表是否已被缓存。

快速浏览信封计算使我怀疑我们正在从磁盘分页。假设Postgres使用4字节整数，则每行有(6 * 4)个字节，因此表最少为(24 * 350,000)字节〜8.4MB。假设HDD上的持续吞吐量为40 MB / s，我们正在寻找大约200ms的时间来读取数据(如所指出的，这几乎是所有时间所花费的时间)。

除非我在某个地方搞砸了数学，否则除非驱动器或者操作系统已经缓存了该文件，否则我看不到我们有可能在显示的时间内将8MB内容读入Java应用程序并对其进行处理。 。

回答

我用MySQL指定ENGINE = MEMORY进行了重新测试，但它并没有改变(仍然是200毫秒)。使用内存数据库的Sqlite3也提供了类似的计时(250毫秒)。

这里的数学看起来正确(至少是大小，因为那是sqlite db的大小:-)

我只是不购买disk-causes-slowness参数，因为有种种迹象表明表已在内存中(postgres伙计们警告不要过于努力地将表固定到内存，因为他们发誓操作系统会比程序员做得更好)

为了澄清时间，Java代码不是从磁盘读取，如果Postgres是从磁盘读取并计算复杂的查询，则这是完全不公平的比较，但这确实很重要，DB应该足够聪明，可以使磁盘小将表放入内存并预编译存储过程恕我直言。

更新(针对下面的第一条评论)：

我不确定如何在不使用聚合函数的情况下公平地测试查询，因为如果我选择所有行，将花费大量时间序列化和格式化所有内容。我并不是说速度慢是由于聚合功能引起的，它仍然可能仅仅是并发性，完整性和朋友性的开销。我只是不知道如何将聚合隔离为唯一的独立变量。

回答

我们是否正在使用TCP访问Postgres？在这种情况下，Nagle会弄乱时间安排。