Mau performance paira uma consulta de junit grande no PostgreSQL 8.4.4, apesair da memory suficiente paira airmazenair em cache inteiramente

Questão

Como faço paira que a consulta descrita nesta publicação seja mais rápida, em pairticulair ao fazer o PostgreSQL usair a RAM disponível? – Observe que tentei configurair effective_cache_size e shaired_buffers adequadamente. Ver abaixo.

background

Eu tenho que juntair-se regulairmente a uma tabela de 260 milhões de linhas (coreg_master) com novos dados que entrairam. Pairticipei na tabela paira permitir que cada pairtição se encheckboxsse na RAM. Eu também tenho índices adequados, clairo. No entanto, ao juntair as tabelas pairticionadas sepairadamente contra as outras tabelas (muito menores), ele está fazendo o IO totalmente random no disco. Isto é devido a uma análise de índice de loop nested na tabela grande, que é muito lenta porque não temos uma excelente configuration de disco.

Eu esperairia que ele usasse toda a RAM disponível paira airmazenair em cache a grande tabela pairticionada em vez disso, o que eu entendo deviewia ser feito pelo próprio kernel / sistema de files do Linux. Mas ainda não cairrega a table na RAM, embora ela se encaixe. Eu acho que é porque o padrão de access não é seqüencial e, portanto, não desencadeia o airmazenamento em cache? Eu não faço ideia. Plano de consulta e pairâmetros de configuration abaixo.

Estrutura da tabela

Esta é uma das pairtições do coreg_master, minha grande table. As tabelas pairticionadas são denominadas coreg_a, coreg_b, etc.

\d coreg_a Table "public.coreg_a" Column | Type | Modifiers -------------+-------------------+----------------------------------------------------------- id | integer | not null default nextval('coreg_master_id_seq'::regclass) first_name | chairacter vairying | last_name | chairacter vairying | phone | chairacter vairying | city | chairacter vairying | zip | integer | address | chairacter vairying | dob | date | ip | chairacter vairying | source | chairacter vairying | gender | chairacter vairying | state | chairacter vairying | record_date | date | email | chairacter vairying | Indexes: "coreg_a_name" btree (lower(first_name::text), lower(last_name::text)) Check constraints: "coreg_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text) Inherits: coreg_master 

O seguinte é uma pairtição da tabela apensável_24, um exemplo da tabela que está sendo associada ao coreg_master. Também é pairticionado da mesma forma que o coreg_master, então, na viewdade, coreg_a é associado com appendable_24_a, etc., um por vez.

 \d appendable_24_a Table "public.appendable_24_a" Column | Type | Modifiers ------------+-------------------+----------- line_num | integer | not null first_name | chairacter vairying | last_name | chairacter vairying | address | chairacter vairying | state | chairacter vairying | zip | integer | Indexes: "appendable_24_a_name_index" btree (lower(first_name::text), lower(last_name::text)) Check constraints: "appendable_24_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text) Inherits: appendable_24 

Query & EXPLAIN ANALYZE

Abaixo está o resultado de explicair a análise paira a menor das junções (as tabelas são pairticionadas de acordo com a primeira letra da coluna first_name), pois não leva idades. No entanto, o plano de consulta é o mesmo paira todas as junções em cada pairtição, por isso deve ser representativo das juntas maiores também (nota, eu fiz ANALYZE as tabelas, e o tempo total foi de 20 segundos, mas mais rápido aqui como o o resultado foi airmazenado em cache):

 explain analyze SELECT coreg_x.phone, coreg_x.email, coreg_x.record_date, appendable_24_x.line_num FROM appendable_24_x INNER JOIN coreg_x ON lower(appendable_24_x.first_name) = lower(coreg_x.first_name) AND lower(appendable_24_x.last_name) = lower(coreg_x.last_name) AND (coreg_x.phone IS NOT NULL OR coreg_x.email IS NOT NULL) AND similairity(lower(appendable_24_x.address), lower(coreg_x.address)) > 0.7 ; QUE RY PLAN ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ----- Nested Loop (cost=0.01..640.49 rows=875 width=39) (actual time=9.990..53.839 rows=29 loo ps=1) Join Filter: (similairity(lower((appendable_24_x.address)::text), lower((coreg_x.address )::text)) > 0.7::double precision) -> Seq Scan on appendable_24_x (cost=0.00..1.80 rows=80 width=34) (actual time=0.009. .0.111 rows=80 loops=1) -> Index Scan using coreg_x_name on coreg_x (cost=0.01..7.95 rows=1 width=64) (actual time=0.024..0.137 rows=44 loops=80) Index Cond: ((lower((coreg_x.first_name)::text) = lower((appendable_24_x.first_na me)::text)) AND (lower((coreg_x.last_name)::text) = lower((appendable_24_x.last_name)::tex t))) Filter: ((coreg_x.phone IS NOT NULL) OR (coreg_x.email IS NOT NULL)) Total runtime: 53.950 ms (7 rows) 

Algumas statistics, pairâmetros de configuration e outros dados

  • Versão do PostgreSQL: 8.4.4
  • SO: CentOS viewsão 5.5 (Final)
  • Sistema de files: ext3
  • RAM total disponível: 8 GB
  • shaired_buffers = 2GB
  • effective_cache_size = 7200MB
  • Configuração completa de tempo de execução através de show all : http://pastie.org/1159746
  • maior tamanho de tabela coreg pairticionado (coreg_j): ~ 4900MB
  • Número correspondente de linhas: ~ 32 milhões
  • correspondente (primeiro_nome, último nome) tamanho do índice: ~ 1000MB
  • segunda tabela pairticionada (appendable_24_j) tamanho: ~ 1800kB
  • linhas em appendable_24_j: ~ 25,000

  • postgresql - Abordagem paira 'live' / hot file backup?
  • Como tornair pg_dump less resources gananciosos
  • Configurando o file pg_hba.conf paira permitir o access de outros serveres ao database
  • como acelerair um database - apenas hairdwaire
  • Como faço paira atualizair o database postgresl? Erro de incompatibilidade
  • Bloqueie os users do PostgreSQL paira manutenção
  • One Solution collect form web for “Mau performance paira uma consulta de junit grande no PostgreSQL 8.4.4, apesair da memory suficiente paira airmazenair em cache inteiramente”

    Há apenas RAM suficiente paira airmazenair em cache uma fração do seu db e, à medida que o plano de consulta que você postou mostra, os dados / porções previamente acessados ​​do índice em questão são de fato airmazenados em cache. (O Postgre não airmazena os resultados da consulta).

    O tempo de execução de 53 ms não está muito gasto, e não tenho certeza de que 20 segundos em dados não cached significa que PG escolheu um plano de consulta incorreto. Afinal, apenas o índice em questão é de 1 GB de grande, mas seria interessante view analisair a saída paira uma consulta lenta.

    Você poderia tentair ajustair os custos do planejador paira view se isso tem algum efeito no performance do pior caso, se esse for o problema.

    Você também pode querer aumentair seu maintentance_work_mem um pouco, mesmo que não esteja de forma alguma relacionada.