配置PostgreSQL以获得读取性能

概述我们的系统编写了大量数据(大数据系统的种类).写入性能足以满足我们的需求,但读取性能实在太慢. 所有表的主键(约束)结构都类似： timestamp(Timestamp) ; index(smallint) ; key(integer). 一个表可以包含数百万行,甚至数十亿行,并且读取请求通常用于特定时间段(时间戳/索引)和标记.有一个返回大约200k行的查询是很常见的.目前,我们可以读取大约每秒 我们的系统编写了大量数据(大数据系统的种类).写入性能足以满足我们的需求,但读取性能实在太慢.

所有表的主键(约束)结构都类似：

timestamp(Timestamp) ; index(smallint) ; key(integer).

一个表可以包含数百万行,甚至数十亿行,并且读取请求通常用于特定时间段(时间戳/索引)和标记.有一个返回大约200k行的查询是很常见的.目前,我们可以读取大约每秒15k行,但我们需要快10倍.这是可能的,如果是的话,怎么样？

注意：Postgresql与我们的软件一起打包,因此不同客户端的硬件不同.

它是用于测试的VM. VM的主机是windows Server 2008 R2 x64,具有24.0 GB的RAM.

服务器规范(虚拟机VMWare)

Server 2008 R2 x642.00 GB of memoryIntel Xeon W3520 @ 2.67GHz (2 cores)

postgresql.conf优化

shared_buffers = 512MB (default: 32MB)effective_cache_size = 1024MB (default: 128MB)checkpoint_segment = 32 (default: 3)checkpoint_completion_target = 0.9 (default: 0.5)default_statistics_target = 1000 (default: 100)work_mem = 100MB (default: 1MB)maintainance_work_mem = 256MB (default: 16MB)

表定义

CREATE table "AnalogTransition"(  "KeyTag" integer NOT NulL,"Timestamp" timestamp with time zone NOT NulL,"TimestampQuality" smallint,"TimestampIndex" smallint NOT NulL,"Value" numeric,"Quality" boolean,"QualityFlags" smallint,"UpdateTimestamp" timestamp without time zone,-- (UTC)  CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp","TimestampIndex","KeyTag" ),CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag")      REFERENCES "Tag" ("Key") MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION)WITH (  OIDS=FALSE,autovacuum_enabled=true);

询问

查询在pgadmin3中执行大约需要30秒,但我们希望在5秒内获得相同的结果(如果可能).

SELECT     "AnalogTransition"."KeyTag","AnalogTransition"."Timestamp" AT TIME ZONE 'UTC',"AnalogTransition"."TimestampQuality","AnalogTransition"."TimestampIndex","AnalogTransition"."Value","AnalogTransition"."Quality","AnalogTransition"."QualityFlags","AnalogTransition"."UpdateTimestamp"FROM "AnalogTransition"WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60)ORDER BY "AnalogTransition"."Timestamp" DESC,"AnalogTransition"."TimestampIndex" DESCliMIT 500000;

解释1

"limit  (cost=0.00..125668.31 rows=500000 wIDth=33) (actual time=2.193..3241.319 rows=500000 loops=1)""  Buffers: shared hit=190147""  ->  Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..389244.53 rows=1548698 wIDth=33) (actual time=2.187..1893.283 rows=500000 loops=1)""        Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))""        Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))""        Buffers: shared hit=190147""Total runtime: 3863.028 ms"

解释2

在我的最新测试中,选择我的数据需要7分钟！见下文：

"limit  (cost=0.00..313554.08 rows=250001 wIDth=35) (actual time=0.040..410721.033 rows=250001 loops=1)""  ->  Index Scan using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..971400.46 rows=774511 wIDth=35) (actual time=0.037..410088.960 rows=250001 loops=1)""        Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))""Total runtime: 411044.175 ms"

数据对齐和存储大小

实际上,元组标头的每个元组的开销是24字节,项目指针的开销是4字节.
此相关答案中计算的更多细节：

> Use GIN to index bit strings

this related answer on SO中数据对齐和填充的基础知识：

> Calculating and saving space in PostgreSQL

我们有三列主键：

PRIMARY KEY ("Timestamp","KeyTag")"Timestamp"      timestamp (8 bytes)"TimestampIndex" smallint  (2 bytes)"KeyTag"         integer   (4 bytes)

结果是：

 4 bytes item pointer in the page header (not counting towards multiple of 8 bytes)---23 bytes for the tuple header 1 byte  @R_502_5095@ for data alignment (or NulL bitmap) 8 bytes "Timestamp" 2 bytes "TimestampIndex" 2 bytes @R_502_5095@ for data alignment 4 bytes "KeyTag"  0 @R_502_5095@ to the nearest multiple of 8 bytes-----44 bytes per tuple

有关在此相关答案中测量对象大小的更多信息

> Measure the size of a PostgreSQL table row

多列索引中的列顺序

阅读这两个问题和答案,了解：

> Is a composite index also good for queries on the first field?
> Working of indexes in PostgreSQL

您拥有索引(主键)的方式,您可以在没有排序步骤的情况下检索行,这很有吸引力,尤其是使用liMIT.但检索行似乎非常昂贵.

通常,在多列索引中,“相等”列应首先出现,“范围”列应最后：

> Multicolumn index and performance

因此,请尝试使用反向列顺序的其他索引：

CREATE INDEX analogransition_mult_IDx1   ON "AnalogTransition" ("KeyTag","Timestamp");

这取决于数据分布.但是,有数百万行,这可能要快得多.

由于数据对齐和放大,元组大小增加了8个字节.填充.如果您将此作为普通索引使用,则可能会尝试删除第三列“Timestamp”.可能有点快或不快(因为它可能有助于排序).

您可能希望保留两个索引.根据许多因素,您的原始索引可能更合适 – 特别是小liMIT.

autovacuum和表统计信息

您的表统计信息需要是最新的.我相信你有autovacuum跑.

由于您的表格似乎很大且统计信息对于正确的查询计划很重要,因此我会大幅增加相关列的statistics target：

ALTER table "AnalogTransition" ALTER "Timestamp" SET STATISTICS 1000;

…甚至更高的数十亿行.最大值为10000,默认值为100.

对WHERE或ORDER BY子句中涉及的所有列执行此 *** 作.然后运行ANALYZE.

表格布局

在此期间,如果您应用了解有关数据对齐和填充的知识,这种优化的表格布局应该节省一些磁盘空间并帮助提高性能(忽略pk& fk)：

CREATE table "AnalogTransition"(  "Timestamp" timestamp with time zone NOT NulL,"KeyTag" integer NOT NulL,-- (UTC)  "QualityFlags" smallint,"Value" numeric);

CLUSTER / pg_repack

要优化使用特定索引(无论是原始索引还是我建议的替代索引)的查询的读取性能,您可以按索引的物理顺序重写表. CLUSTER这样做,但它是相当侵入性的,并且在 *** 作期间需要独占锁. pg_repack是一种更复杂的替代方案,可以在没有桌面排他锁的情况下做同样的事情.
这可以大大有助于大型表,因为必须读取更少的表块.

内存

通常,2GB的物理RAM不足以快速处理数十亿行.更多RAM可能会有很长的路要走 – 伴随着适应的设置：显然是一个更大的effective_cache_size开始.

总结

以上是内存溢出为你收集整理的配置PostgreSQL以获得读取性能全部内容，希望文章能够帮你解决配置PostgreSQL以获得读取性能所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。