Início / dba / Perguntas / 42290
Accepted
JPelletier
Asked: 2013-05-16 10:32:11 +0800 CST

Configurando o PostgreSQL para desempenho de leitura

  • 772

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

2 respostas

  1. Best Answer

    Alinhamento de dados e tamanho de armazenamento

    Na verdade, a sobrecarga por tupla de índice é de 8 bytes para o cabeçalho da tupla mais 4 bytes para o identificador de item.

    Relacionado:

    Temos três colunas para a chave primária:

    PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag")

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

    Resulta em:

    4 bytes para identificador de item no cabeçalho da página (sem contar para múltiplos de 8 bytes)

 8 bytes para o cabeçalho da tupla de índice
 8 bytes "Timestamp"
 2 bytes "TimestampIndex"
 2 bytes de preenchimento para alinhamento de dados
 4 bytes "KeyTag"
 0 preenchimento para o múltiplo mais próximo de 8 bytes
-----
28 bytes por tupla de índice; além de alguns bytes de sobrecarga.

    Sobre medir o tamanho do objeto nesta resposta relacionada:

    Ordem das colunas em um índice de várias colunas

    Leia estas duas perguntas e respostas para entender:

    A maneira como você tem seu índice (chave primária), pode recuperar linhas sem uma etapa de classificação, o que é atraente, especialmente com LIMIT. Mas recuperar as linhas parece extremamente caro.

    Geralmente, em um índice de várias colunas, as colunas de "igualdade" devem vir primeiro e as colunas de "intervalo" por último:

    Portanto, tente um índice adicional com a ordem das colunas invertida :

    CREATE INDEX analogransition_mult_idx1
   ON "AnalogTransition" ("KeyTag", "TimestampIndex", "Timestamp");

    Depende da distribuição dos dados. Mas com millions of row, even billion of rowsisso pode ser substancialmente mais rápido.

    O tamanho da tupla é 8 bytes maior, devido ao alinhamento e preenchimento de dados. Se você estiver usando isso como índice simples, tente descartar a terceira coluna "Timestamp". Pode ser um pouco mais rápido ou não (já que pode ajudar na classificação).

    Você pode querer manter ambos os índices. Dependendo de vários fatores, seu índice original pode ser preferível - em particular com um pequeno LIMIT.

    autovacuum e estatísticas de mesa

    As estatísticas da sua tabela precisam estar atualizadas. Tenho certeza que você tem autovacuum funcionando.

    Como sua tabela parece ser enorme e as estatísticas são importantes para o plano de consulta correto, eu aumentaria substancialmente a meta de estatísticas para colunas relevantes:

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

    ... ou ainda maior com bilhões de linhas. O máximo é 10.000, o padrão é 100.

    Faça isso para todas as colunas envolvidas em WHEREou ORDER BYcláusulas. Então corra ANALYZE.

    Layout da tabela

    Enquanto estiver nisso, se você aplicar o que aprendeu sobre alinhamento e preenchimento de dados, esse layout de tabela otimizado deve economizar algum espaço em disco e ajudar um pouco o desempenho (ignorando pk & fk):

    CREATE TABLE "AnalogTransition"(
  "Timestamp" timestamp with time zone NOT NULL,
  "KeyTag" integer NOT NULL,
  "TimestampIndex" smallint NOT NULL,
  "TimestampQuality" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  "QualityFlags" smallint,
  "Quality" boolean,
  "Value" numeric
);

    CLUSTER/ pg_repack / pg_squeeze

    Para otimizar o desempenho de leitura para consultas que usam um determinado índice (seja o original ou minha alternativa sugerida), você pode reescrever a tabela na ordem física do índice. CLUSTERfaz isso, mas é bastante invasivo e requer um bloqueio exclusivo para a duração da operação.
    pg_repacké uma alternativa mais sofisticada que pode fazer o mesmo sem trava exclusiva na mesa.
    pg_squeezeé uma ferramenta posterior semelhante (ainda não a usei).

    Isso pode ajudar substancialmente com tabelas enormes, já que muito menos blocos da tabela precisam ser lidos.

    BATER

    Geralmente, 2 GB de RAM física não é suficiente para lidar com bilhões de linhas rapidamente. Mais RAM pode percorrer um longo caminho - acompanhado de configuração adaptada: obviamente, maior effective_cache_sizepara começar.

    • 63

  2. Portanto, dos planos, vejo uma coisa: seu índice está inchado (e junto com a tabela subjacente) ou simplesmente não é realmente bom para esse tipo de consulta (tentei abordar isso no meu último comentário acima).

    Uma linha do índice contém 14 bytes de dados (e alguns para o cabeçalho). Agora, calculando a partir dos números dados no plano: você obteve 500.000 linhas de 190147 páginas - isso significa, em média, menos de 3 linhas úteis por página, ou seja, cerca de 37 bytes por página de 8 kb. Esta é uma proporção muito ruim, não é? Como a primeira coluna do índice é o Timestampcampo e é usada na consulta como um intervalo, o planejador pode - e faz - escolher o índice para encontrar as linhas correspondentes. Mas não há TimestampIndexmenção nas WHEREcondições, então a filtragem KeyTagnão é muito eficaz, pois esses valores supostamente aparecem aleatoriamente nas páginas de índice.

    Portanto, uma possibilidade é alterar a definição do índice para

    CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp", "KeyTag", "TimestampIndex")

    (ou, dada a carga do seu sistema, crie este índice como um novo:

    CREATE INDEX CONCURRENTLY "idx_AnalogTransition" 
    ON "AnalogTransition" ("Timestamp", "KeyTag", "TimestampIndex");
    • isso vai demorar um pouco com certeza, mas você ainda pode trabalhar enquanto isso.)

    A outra possibilidade é que uma grande proporção das páginas de índice esteja ocupada por linhas mortas, que podem ser removidas por aspiração. Você criou a tabela com configuração autovacuum_enabled=true- mas você já começou a autoaspirar? Ou executar VACUUMmanualmente?

    • 13

relate perguntas