主页 / server / 问题 / 533578
Accepted
Steve Bennett
Asked: 2013-08-26 02:53:03 +0800 CST

为什么 Postgres 闲置 95%，没有文件 I/O？

  • 772

我有一个 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 秒。空间索引很重要！:)

performance

2 个回答

  1. Best Answer

    通过 explain.depesz.com运行您的Explain Anlayze 输出会突出显示大部分缓慢来自此操作：

    Seq Scan on planet_osm_polygon p

    以前有索引吗？你现在可以索引它吗？

    通过搜索该问题区域，我还在 Open Street Map 网站上找到了相关问答：

    • 4

  2. 对于任何给定的查询，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一些输出，看看它实际上在做什么。

    • 4

相关问题