Performance

為什麼 Postgres 閒置 95%，沒有文件 I/O？

  • January 19, 2016

我有一個 TileMill/PostGIS 堆棧在 OpenStack 雲上的 8 核 Ubuntu 12.04 VM 上執行。這是一個非常相似的系統的重建，上週在非常相似的硬體（我相信相同的雲，但不同的物理硬體）上執行良好。我試圖重建與原來完全相同的堆棧（使用我建構的一些腳本）。

一切都在執行，但數據庫執行查詢的速度非常緩慢，這最終表現為非常緩慢的切片生成。一個範例查詢（計算澳大利亞每個城鎮半徑內的酒吧數量），以前需要大約 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

（我將此查詢作為症狀包括在內，而不是直接解決要解決的問題。這個特定的查詢僅每週執行一次左右。）

伺服器有 32 GB 的 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 時只使用 5% 的可用 CPU？我歡迎任何關於進一步調查、其他工具、隨機嘗試的建議。

更新

我對伺服器進行了快照並在同一云的不同部分（不同的可用區）上啟動它。結果有點奇怪。vmstat在此伺服器上報告 12% 的 CPU 使用率（我現在將其理解為 8 核 VM 上單個 Postgres 查詢的預期值） - 儘管實際查詢執行時間幾乎相同（630 秒與 623 秒）。

我現在意識到這個特定的查詢可能不是一個好的範例，因為這個原因：它只能使用一個核心，而且它是一個update（而圖塊渲染只是selects）。

我也沒有註意到explain顯然planet_osm_polygon沒有使用索引。這很可能是原因，所以我接下來會追究。

更新2

問題肯定似乎是planet_osm_polygon 索引（es）正在/沒有被使用。有兩個（一個由 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 的統計數據非常具有啟發性：

行星osm_polygon：

Sequential Scans    194204  
Sequential Tuples Read  60981018608 
Index Scans 1574    
Index Tuples Fetched    0

行星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 秒。空間索引很重要！:)

通過 explain.depesz.com執行您的Explain Anlayze 輸出會突出顯示大部分緩慢來自此操作：

Seq Scan on planet_osm_polygon p

以前有索引嗎？你現在可以索引它嗎？

通過搜尋該問題區域，我還在 Open Street Map 網站上找到了相關問答：

對於任何給定的查詢，PostgreSQL 只能使用一個核心。它通過許多並發查詢實現了良好的並行性能，但對於僅幾個非常大的查詢的工作負載並沒有受益於大核心數。因此，如果您只執行一個查詢，那麼 5% 就不足為奇了，儘管我希望它在 8 核系統上為 12%。

缺少 iowait 表明它可能不會受到磁碟 I/O 的影響。

所以 - 它似乎沒有在 CPU 或 I/O 上出現瓶頸。

查詢是否有可能被鎖簡單地阻塞了一段時間？檢查pg_stat_activity查詢，並加入pg_locks以查看是否有任何未授予的鎖。（有關於 Pg 鎖監控的罐頭查詢）。

接下來要做的是執行一些較低級別的系統測試。執行pg_test_fsync，使用 sysbench 的 CPU 和 I/O 測試等。如果這些測試也表現不佳，請向您的託管服務提供商提出。

您還應該收集perf top -a一些輸出，看看它實際上在做什麼。

引用自：https://serverfault.com/questions/533578

相關問答

Performance

如何使 pg_dump 減少資源貪婪

  • September 21, 2021
檢視問答
Postgresql

嘗試在 Centos 6.9 上安裝 postgis 並缺少依賴項 libgeotiff.so.2

  • September 4, 2021
檢視問答
Performance

辨識 CentOS 8 上的飽和磁碟

  • January 10, 2021
檢視問答
Performance

Postgres SELECT 非常慢，EXPLAIN ANALYZE SELECT 一點也不慢

  • November 10, 2020
檢視問答
Postgresql

使用 PostGIS 將 PostgreSQL 9.5 升級到 9.6 - pg_upgrade

  • October 16, 2020
檢視問答
Performance

postgres stats 收集器程序生成的 I/O 過多

  • March 27, 2020
檢視問答