我在 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
(我将这个查询作为症状,而不是直接作为需要解决的问题。这个特定的查询大约每周只运行一次。)
该服务器有 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
显示未读取任何内容,但写入了一些数据(不知道写入位置或原因),并且 CPU 空闲率为 95%:
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
这台服务器报告 CPU 使用率为 12%(我现在明白这是 8 核 VM 上单个 Postgres 查询的预期值)——尽管实际查询执行时间几乎相同(630 秒 vs 623 秒)。
我现在意识到这个特定的查询可能不是一个好的样本,原因如下:它只能使用一个核心,而且它是一个update
(而图块渲染只是select
s)。
我也没有注意到,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_多边形:
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 秒。空间索引很重要!:)
答案1
运行你的通过 explain.depesz.com 解释 Anlayze 输出强调大部分的缓慢来自于这个动作:
Seq Scan on planet_osm_polygon p
之前有索引过吗?现在可以索引吗?
通过搜索该问题区域,我还在 Open Street Map 网站上找到了相关的问答:
答案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
一些输出,看看它实际上在做什么。