我在OpenStack云上的8核心Ubuntu 12.04虚拟机上运行了TileMill / PostGIS堆栈。 这是一个非常相似的系统的重build,上周在非常相似的硬件(相同的云,但不同的物理硬件,我相信)上运行良好。 我试图重build堆栈完全一样(使用我已经build立的一些脚本)。
一切都在运行,但是数据库正在慢慢地进行查询,这最终performance为非常缓慢的瓦片生成。 一个示例查询(计算澳大利亚每个城镇半径内的酒吧数量),之前花费了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,我已经configuration了Postgres如下(在网上find的build议):
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---- rb 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
抓着吸pipe,我把Postgres数据目录从vda移到了vdb,但是当然没有什么区别。
所以我很茫然 为什么Postgres在不等待任何I / O时只使用5%的可用CPU? 我欢迎任何进一步调查的build议,其他工具,随机的东西尝试。
我对服务器进行了快照,并在同一个云的不同部分(不同的可用区域)启动了服务器。 结果有点奇怪。 虽然实际的查询执行时间实际上是相同的(630秒比623),但此服务器上的vmstat
报告CPU使用率为12%(现在我理解为8核心虚拟机上单个Postgres查询的预期值)。
我现在意识到,这个特定的查询可能不是一个好的例子,因为这个原因:它只能使用一个核心,它是一个update
(而瓦片渲染只是select
)。
我也没有注意到explain
,显然planet_osm_polygon
没有使用索引。 这可能是事业的原因,所以我会在下一步追逐。
问题肯定是planet_osm_polygon索引(es)没有被使用。 有两个(一个是由osm2pgsql创build的,一个是我按照随机指南创build的):
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已经search了1574次planet_osm_polygon,但从来没有发现任何东西,所以做了大量的蛮力search。
新的问题:为什么?
感谢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
那索引过了吗? 你现在可以索引吗?
通过search这个问题领域,我还在一个开放的街道地图网站上find了相关的问答:
对于任何给定的查询,PostgreSQL只能使用一个核心。 它实现了很多并发查询的良好并行性能,但是对于只是几个非常大的查询的工作负载而言,并不能从大的核心数量中受益。 所以如果你只运行一个单一的查询,5%并不是那么令人惊讶,但我认为它在8核心系统上是12%。
Iowait的缺乏表明,它可能不会受到磁盘I / O的影响。
所以 – 它在CPU或I / O上似乎不是瓶颈。
这个查询是否可能被一个锁简单地阻塞了一段时间? 检查pg_stat_activity
,并与pg_locks
连接,查看是否有未授权的锁。 (有关于Pg锁监视的jar头查询)。
接下来要做的是运行一些较低级别的系统testing。 运行pg_test_fsync
,使用sysbench的CPU和I / Otesting等等。如果这些performance很糟糕,那么就用你的托pipe服务提供商来提高它。
你也应该收集一些输出,看看它实际上在做什么。