postgresql - 生产中的 Postgres 查询导致异常高的磁盘读取 I/O

Question

我将 Ubuntu 16.04 与 PostgreSQL 9.5 和 Django 1.11 一起使用

我的网站一直受到超长 ajax 调用的困扰(在某些情况下超过 30 秒)。相同的 ajax 调用在开发中大约需要 500 毫秒。

该问题与磁盘读取 I/O 相关。在生产中执行单个查询会驱动磁盘读取 I/O up to 25MB/s；开发中的相同查询导致磁盘读取 I/O 低于 0.01 MB/s。代码和查询在生产/开发中是相同的。

所以生产中的 postgres 导致异常高的磁盘读取 I/O。会是什么？

这是一个示例查询，在生产环境中耗时约 25 秒，而在开发环境中仅需 500 毫秒:

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) AS "__count" FROM "map_listing" 
WHERE ("map_listing"."lo" <  -79.32516245458987 AND "map_listing"."la" > 43.640279060122346
AND "map_listing"."lo" >  -79.60531382177737 AND "map_listing"."transaction_type" = 'Sale'
AND "map_listing"."la" < 43.774544561921296 
AND NOT ("map_listing"."status" = 'Sld' AND "map_listing"."sold_date" < '2018-01-21'::date
AND "map_listing"."sold_date" IS NOT NULL)
AND NOT (("map_listing"."status" = 'Ter' OR "map_listing"."status" = 'Exp'))
AND NOT (("map_listing"."property_type" = 'Parking Space' OR "map_listing"."property_type" = 'Locker')));

对上述语句(生产)执行EXPLAIN(ANALYZE，BUFFERS)的结果:

 Aggregate  (cost=89924.55..89924.56 rows=1 width=0) (actual time=27318.859..27318.860 rows=1 loops=1)
   Buffers: shared read=73424
   ->  Bitmap Heap Scan on map_listing  (cost=4873.96..89836.85 rows=35079 width=0) (actual time=6061.214..27315.183 rows=3228 loops=1)
         Recheck Cond: ((la > 43.640279060122346) AND (la < 43.774544561921296))
         Rows Removed by Index Recheck: 86733
         Filter: ((lo < '-79.32516245458987'::numeric) AND (lo > '-79.60531382177737'::numeric) AND ((status)::text <> 'Ter'::text) AND ((status)::text <> 'Exp'::text) AND ((property_type)::text <> 'Parking Space'::text) AND ((property_type)::text <> 'Locker'::text) AND ((transaction_type)::text = 'Sale'::text) AND (((status)::text <> 'Sld'::text) OR (sold_date >= '2018-01-21'::date) OR (sold_date IS NULL)))
         Rows Removed by Filter: 190108
         Heap Blocks: exact=46091 lossy=26592
         Buffers: shared read=73424
         ->  Bitmap Index Scan on map_listing_la_88ca396c  (cost=0.00..4865.19 rows=192477 width=0) (actual time=156.964..156.964 rows=194434 loops=1)
               Index Cond: ((la > 43.640279060122346) AND (la < 43.774544561921296))
               Buffers: shared read=741
 Planning time: 0.546 ms
 Execution time: 27318.926 ms
(14 rows)

---

EXPLAIN (ANALYZE, BUFFERS) 结果 (开发):

 Aggregate  (cost=95326.23..95326.24 rows=1 width=8) (actual time=495.373..495.373 rows=1 loops=1)
   Buffers: shared read=77281
   ->  Bitmap Heap Scan on map_listing  (cost=5211.98..95225.57 rows=40265 width=0) (actual time=80.929..495.140 rows=4565 loops=1)
         Recheck Cond: ((la > 43.640279060122346) AND (la < 43.774544561921296))
         Rows Removed by Index Recheck: 85958
         Filter: ((lo < '-79.32516245458987'::numeric) AND (lo > '-79.60531382177737'::numeric) AND ((status)::text <> 'Ter'::text) AND ((status)::text <> 'Exp'::text) AND ((property_type)::text <> 'P
arking Space'::text) AND ((property_type)::text <> 'Locker'::text) AND ((transaction_type)::text = 'Sale'::text) AND (((status)::text <> 'Sld'::text) OR (sold_date >= '2018-01-21'::date) OR (sold_date
 IS NULL)))
         Rows Removed by Filter: 198033
         Heap Blocks: exact=49858 lossy=26639
         Buffers: shared read=77281
         ->  Bitmap Index Scan on map_listing_la_88ca396c  (cost=0.00..5201.91 rows=205749 width=0) (actual time=73.070..73.070 rows=205569 loops=1)
               Index Cond: ((la > 43.640279060122346) AND (la < 43.774544561921296))
               Buffers: shared read=784
 Planning time: 0.962 ms
 Execution time: 495.822 ms
(14 rows)

Answer 1

这个查询没有产生任何磁盘 I/O——所有的 block 都是从共享缓冲区中读取的。但由于查询读取了 73424 个 block (约 574 MB)，因此在表未缓存时会产生大量的 I/O 负载。

但有两点可以改进。

• 您在堆扫描中有有损 block 匹配。这意味着 work_mem 不够大，无法包含每个表行一个位的位图，而是 26592 位映射一个表 block 。必须重新检查所有行，并丢弃 86733 行，其中大部分是有损 block 匹配的误报。

  如果增加 work_mem，每个表行一个位的位图将适合内存，并且这个数字会缩小，从而减少堆扫描期间的工作。

• 190108 行被丢弃，因为它们不符合位图堆扫描中的附加过滤条件。这可能是大部分时间花费的地方。如果您可以减少该金额，您将获胜。

  这个查询的理想索引是:

    CREATE INDEX ON map_listing(transaction_type, la);
    CREATE INDEX ON map_listing(transaction_type, lo);
  

  如果 transaction_type 不是很有选择性(即，大多数行的值为 Sale)，您可以忽略该列。

编辑:

vmstat 和 iostat 的检查表明，CPU 和 I/O 子系统都在遭受大量过载:所有 CPU 资源都花在了 I/O 等待和虚拟机窃取时间。您需要更好的 I/O 系统和具有更多可用 CPU 资源的主机系统。增加 RAM 可能会缓解 I/O 问题，但仅限于磁盘读取。