Eu tenho uma pilha TileMill / PostGIS rodando em uma VM Ubuntu 12.04 de 8 núcleos em uma nuvem OpenStack. É uma reconstrução de um sistema muito similar que estava rodando muito bem em hardware similar (na mesma nuvem, mas hardware físico diferente, eu acredito) na semana passada. Eu tentei reconstruir a pilha exatamente da mesma forma que era (usando alguns scripts que eu construí).
Tudo é executado, mas o banco de dados está realizando consultas de maneira extremamente lenta, o que se manifesta com a geração de blocos muito lenta. Uma consulta de exemplo (conte o número de pubs dentro de um raio de cada cidade na Austrália), que anteriormente levava algo como 10 a 20 segundos, agora leva mais de 10 minutos:
explain (analyze, buffers) update places set pubs =
(select count(*) from planet_osm_point p where p.amenity = 'pub' and st_dwithin(p.way,places.way,scope)) +
(select count(*) from planet_osm_polygon p where p.amenity = 'pub' and st_dwithin(p.way,places.way,scope)) ;
Update on places (cost=0.00..948254806.93 rows=9037 width=160) (actual time=623321.558..623321.558 rows=0 loops=1)
Buffers: shared hit=132126300
-> Seq Scan on places (cost=0.00..948254806.93 rows=9037 width=160) (actual time=68.130..622931.130 rows=9037 loops=1)
Buffers: shared hit=132107781
SubPlan 1
-> Aggregate (cost=12.95..12.96 rows=1 width=0) (actual time=0.187..0.188 rows=1 loops=9037)
Buffers: shared hit=158171
-> Index Scan using planet_osm_point_index on planet_osm_point p (cost=0.00..12.94 rows=1 width=0) (actual time=0.163..0.179 rows=0 loops=9037)
Index Cond: (way && st_expand(places.way, (places.scope)::double precision))
Filter: ((amenity = 'pub'::text) AND (places.way && st_expand(way, (places.scope)::double precision)) AND _st_dwithin(way, places.way, (places.scope)::double precision))
Buffers: shared hit=158171
SubPlan 2
-> Aggregate (cost=104917.24..104917.25 rows=1 width=0) (actual time=68.727..68.728 rows=1 loops=9037)
Buffers: shared hit=131949237
-> Seq Scan on planet_osm_polygon p (cost=0.00..104917.24 rows=1 width=0) (actual time=68.138..68.716 rows=0 loops=9037)
Filter: ((amenity = 'pub'::text) AND (way && st_expand(places.way, (places.scope)::double precision)) AND (places.way && st_expand(way, (places.scope)::double precision)) AND _st_dwithin(way, places.way, (places.scope)::double precision))
Buffers: shared hit=131949237
Total runtime: 623321.801 ms
(Estou incluindo essa consulta como um sintoma, não diretamente o problema a ser resolvido. Essa consulta específica só é executada uma vez por semana, aproximadamente.)
O servidor tem 32 GB de RAM e configurei o Postgres da seguinte forma (seguindo os conselhos encontrados na Web):
shared_buffers = 8GB
autovacuum = on
effective_cache_size = 8GB
work_mem = 128MB
maintenance_work_mem = 64MB
wal_buffers = 1MB
checkpoint_segments = 10
iostat não mostra nada sendo lido, um pouco de dados sendo gravados (não sei onde ou por que) e 95% de CPU ociosa:
avg-cpu: %user %nice %system %iowait %steal %idle
5.40 0.00 0.00 0.11 0.00 94.49
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
vda 0.20 0.00 0.80 0 8
vdb 2.30 0.00 17.58 0 176
Exemplo de saída de vmstat :
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
...
1 0 0 18329748 126108 12600436 0 0 0 18 148 140 5 0 95 0
2 0 0 18329400 126124 12600436 0 0 0 9 173 228 5 0 95 0
Agarrando palhas, mudei o diretório de dados do Postgres de vda para vdb, mas é claro que isso não fez diferença.
Então estou perdido. Por que o Postgres usa apenas 5% da CPU disponível quando não está esperando por nenhuma E / S? Eu gostaria de receber sugestões para investigações adicionais, outras ferramentas, coisas aleatórias para tentar.
Eu fiz um instantâneo do servidor e o iniciei em uma parte diferente da mesma nuvem (uma zona de disponibilidade diferente). Os resultados foram um pouco estranhos. vmstat neste servidor reporta 12% de uso da CPU (que agora entendo como o valor esperado para uma única consulta Postgres em uma VM de 8 núcleos) - embora o tempo real de execução da consulta seja virtualmente idêntico (630 segundos vs 623). p>
Agora percebo que essa consulta específica provavelmente não é uma boa amostra por esse motivo: ela só pode usar um núcleo e é um update (enquanto a renderização de bloco é apenas select s).
Eu também não notei no explain que aparentemente planet_osm_polygon não está usando um índice. Essa pode ser a causa, então eu vou perseguir isso em seguida.
O problema definitivamente parece ser que o (s) índice (s) planet_osm_polygon não está sendo usado. Há dois (um criado por osm2pgsql, um criado por mim seguindo um guia aleatório):
CREATE INDEX idx_planet_osm_polygon_tags
ON planet_osm_polygon
USING gist
(tags);
CREATE INDEX planet_osm_polygon_pkey
ON planet_osm_polygon
USING btree
(osm_id);
As estatísticas em planet_osm_polygon e planet_osm_point são bem reveladoras, eu acho:
planet_osm_polygon:
Sequential Scans 194204
Sequential Tuples Read 60981018608
Index Scans 1574
Index Tuples Fetched 0
planet_osm_point:
Sequential Scans 1142
Sequential Tuples Read 12960604
Index Scans 183454
Index Tuples Fetched 43427685
Se eu ler isso corretamente, o Postgres pesquisou o planet_osm_polygon 1574 vezes, mas nunca encontrou nada, então fez um número absurdamente grande de pesquisas de força bruta.
A nova pergunta: por quê?
Graças a a resposta de Frederik Ramm , a resposta se revela ser bastante simples: não havia índice espacial, por algum motivo. Foi trivial regenerá-los:
create index planet_osm_polygon_polygon on planet_osm_polygon using gist(way);
create index planet_osm_polygon_point on planet_osm_point using gist(way);
A execução dessa consulta agora leva 4,6 segundos. Os índices espaciais são importantes! :)
A exibição da sua saída Explain Anlayze através de explain.depesz.com destaca que a maior parte da lentidão vem dessa ação:
Seq Scan on planet_osm_polygon p
Isso foi indexado antes? Você pode indexá-lo agora?
Ao pesquisar por essa área do problema, também encontrei um Q & A relacionado em um site do Open Street Map:
O PostgreSQL só pode usar um núcleo para qualquer consulta. Ele alcança um bom desempenho paralelo com muitas consultas simultâneas, mas não se beneficia de grandes contagens de núcleo para cargas de trabalho de apenas algumas consultas muito grandes. Então, se você está apenas executando uma única consulta, 5% não é tão surpreendente, embora eu espere que seja 12% em um sistema de 8 núcleos.
A falta de iowait sugere que provavelmente não está sofrendo por E / S de disco.
Então - ele não parece estar afunilado na CPU ou na E / S.
É possível que a consulta seja simplesmente bloqueada por um tempo por um bloqueio? Verifique pg_stat_activity para a consulta e associe-se a pg_locks para ver se há bloqueios não concedidos. (Existem consultas predefinidas sobre monitoramento de bloqueio de Pg).
A próxima coisa a fazer é executar alguns testes de sistema de nível inferior. Execute pg_test_fsync , use a CPU do sysbench e os testes de E / S, etc. Se eles apresentarem um desempenho ruim, aumente-o com seu provedor de hospedagem.
Você também deve coletar perf top -a output por um tempo, ver o que realmente está fazendo.