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

我正在使用 Ubuntu 16.04 以及 PostgreSQL 9.5 和 Django 1.11

我的网站一直遭受超长 ajax 调用的困扰（在某些情况下长达 30 秒）。相同的 ajax 调用大约需要 500 毫秒的开发时间。

该问题与磁盘读取 I/O 相关。在生产中执行单个查询会驱动磁盘读取 I/O高达 25MB/秒 https://i.gyazo.com/e212d736db195831b5dd0e422db62a14.png; an 完全相同的开发中的查询导致磁盘读取 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)

该查询没有产生任何磁盘 I/O – 所有块都是从共享缓冲区读取的。但由于查询读取了 73424 个块（约 574 MB），因此当表未缓存时，它将产生大量 I/O 负载。

但有两件事可以改进。

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

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

• 190108 行被丢弃，因为它们与位图堆扫描中的附加过滤条件不匹配。这可能是大部分时间花在的地方。如果你能减少这个数额，你就会赢。

  此查询的理想索引是：

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

  If transaction_type不是很有选择性（即大多数行都有值Sale)，您可以省略该列。

EDIT:

考试vmstat and iostat显示 CPU 和 I/O 子系统都遭受了巨大的过载：所有 CPU 资源都消耗在 I/O 等待和 VM 窃取时间上。您需要更好的 I/O 系统和具有更多可用 CPU 资源的主机系统。增加 RAM 可能会缓解 I/O 问题，但仅限于磁盘读取。