OpenStack 클라우드의 8 코어 Ubuntu 12.04 VM에서 실행되는 TileMill / PostGIS 스택이 있습니다. 지난주에 매우 유사한 하드웨어 (동일한 클라우드이지만 다른 물리적 하드웨어)에서 훌륭하게 실행되는 매우 유사한 시스템의 재구성입니다. 스택과 똑같이 스택을 다시 만들려고했습니다 (내가 작성한 일부 스크립트를 사용하여).

모든 것이 실행되지만 데이터베이스는 쿼리를 매우 느리게 수행하여 타일 생성 속도가 매우 느립니다. 이전에 10-20 초 정도 걸리던 예제 쿼리 (호주의 모든 도시 반경 내 펍 수 계산)는 이제 10 분 이상이 걸렸습니다.

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

(이 쿼리를 증상으로 포함시키고 있으며 직접 해결할 문제는 없습니다.이 특정 쿼리는 일주일에 한 번 정도만 실행됩니다.)

서버에는 32GB의 RAM이 있으며 Postgres를 다음과 같이 구성했습니다 (웹에서 찾은 조언에 따라).

shared_buffers = 8GB
autovacuum = on
effective_cache_size = 8GB
work_mem = 128MB
maintenance_work_mem = 64MB
wal_buffers = 1MB
checkpoint_segments = 10

iostat 읽은 내용 없음, 약간의 데이터 작성 중 (어디에서 또는 왜 모름) 및 95 % 유휴 CPU 표시 :

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

의 샘플 출력 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

빨대를 쥐고 ​​나는 Postgres 데이터 디렉토리를 vda에서 vdb로 옮겼지만 물론 아무런 차이가 없었습니다.

그래서 나는 길을 잃었다. Postgres가 I / O를 기다리지 않을 때 사용 가능한 CPU의 5 % 만 사용하는 이유는 무엇입니까? 추가 조사, 기타 도구, 무작위로 시도 할 수있는 제안을 환영합니다.

최신 정보

서버를 스냅 샷하여 동일한 클라우드의 다른 부분 (다른 가용 영역)에서 시작했습니다. 결과는 약간 이상했습니다. vmstat이 서버에서 12 %의 CPU 사용량 (이제 8 코어 VM에서 단일 Postgres 쿼리의 예상 값으로 이해 됨)을보고합니다. 실제 쿼리 실행 시간은 사실상 동일하지만 (630 초 대 623).

나는이 특정 쿼리가 아마도 이런 이유로 좋은 샘플이 아니라는 것을 깨달았습니다. 하나의 코어 만 사용할 수 update있으며 타일 렌더링은 단지 selects입니다.

또한 색인을 사용하지 않는 explain것으로 나타났습니다 planet_osm_polygon. 그 원인이 될 수 있으므로 다음에 추격하겠습니다.

업데이트 2

문제는 planet_osm_polygon 인덱스가 사용되지 않는 것 같습니다. 두 가지가 있습니다 (하나는 osm2pgsql에 의해 만들어졌고 하나는 임의의 가이드에 따라 나에 의해 만들어졌습니다).

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

planet_osm_polygon과 planet_osm_point의 통계는 꽤 밝습니다.

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

내가 그 권리를 읽으면 Postgres는 planet_osm_polygon을 1574 번 검색했지만 실제로는 아무것도 찾지 못했습니다.

새로운 질문 : 왜?

미스터리 해결

Frederik Ramm의 답변 덕분에 대답은 매우 간단합니다. 어떤 이유로 공간 인덱스가 없었습니다. 그것들을 재생시키는 것은 사소한 일이었습니다.

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

해당 쿼리를 실행하는 데 4.6 초가 걸립니다. 공간 인덱스가 중요합니다! 🙂

답변

Seq Scan on planet_osm_polygon p