Início / user-23824

JPelletier's questions

Martin Hope
JPelletier
Asked: 2013-05-16 10:32:11 +0800 CST
  • 48

Nosso sistema grava muitos dados (tipo de sistema Big Data). O desempenho de gravação é bom o suficiente para nossas necessidades, mas o desempenho de leitura é realmente muito lento.

A estrutura de chave primária (restrição) é semelhante para todas as nossas tabelas:

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

Uma tabela pode ter milhões de linhas, até bilhões de linhas, e uma solicitação de leitura geralmente é para um período específico (timestamp/índice) e tag. É comum ter uma consulta que retorna em torno de 200 mil linhas. Atualmente, podemos ler cerca de 15 mil linhas por segundo, mas precisamos ser 10 vezes mais rápidos. Isso é possível e se sim, como?

Nota: O PostgreSQL é empacotado com nosso software, então o hardware é diferente de um cliente para outro.

É uma VM usada para testes. O host da VM é o Windows Server 2008 R2 x64 com 24,0 GB de RAM.

Especificação do servidor (VMWare de máquina virtual)

Server 2008 R2 x64
2.00 GB of memory
Intel Xeon W3520 @ 2.67GHz (2 cores)

postgresql.confotimizações

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)

Definição de tabela

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
);

Consulta

A consulta leva cerca de 30 segundos para ser executada no pgAdmin3, mas gostaríamos de ter o mesmo resultado em menos de 5 segundos, se possível.

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" DESC
LIMIT 500000;

Explique 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"

Explique 2

No meu último teste, levou 7 minutos para selecionar meus dados! Veja abaixo:

"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"
postgresql performance