postgresql - 如果瓶颈似乎是 I/O，如何提高删除查询的性能？

Question

我有一个（设计糟糕的）数据库表（postgres），我正在尝试清理它。该表大小约为 270 GB，38K 行（每行 +- 70 MB --> 列包含文件内容）。

在将设计更改为不再包含文件的同时，我想删除 80% 的数据以减少磁盘使用量。因此，我尝试了以下查询：

DELETE FROM table_name.dynamic_data 
WHERE table_name.env = 'AE'

其中应涵盖 +- 25% 的数据。此查询超时而没有任何警告，或者有时报告日志文件已满：PANIC: could not write to file "pg_xlog/xlogtemp.32455": No space left on device

我试过

DELETE FROM table_name 
WHERE ctid IN (
SELECT ctid 
FROM table_name
WHERE table_name.env = 'AE'
LIMIT 1000)

哪个有效，但速度非常慢（每行删除 200-250 毫秒）并且如果我超过 1000 次就会超时。

为了找到查询中的瓶颈，我在该查询explain (analyze,buffers,timing)的较小版本上运行了上面的查询（使用 LIMIT 1 而不是 LIMIT 1000），结果如下：

QUERY PLAN
Delete on dynamic_data  (cost=0.38..4410.47 rows=1 width=36) (actual time=338.913..338.913 rows=0 loops=1)
  Buffers: shared hit=7972 read=988 dirtied=975
  I/O Timings: read=312.160
  ->  Nested Loop  (cost=0.38..4410.47 rows=1 width=36) (actual time=3.919..13.700 rows=1 loops=1)
        Join Filter: (dynamic_data.ctid = "ANY_subquery".ctid)
        Rows Removed by Join Filter: 35938
        Buffers: shared hit=4013
        ->  Unique  (cost=0.38..0.39 rows=1 width=36) (actual time=2.786..2.788 rows=1 loops=1)
              Buffers: shared hit=479
              ->  Sort  (cost=0.38..0.39 rows=1 width=36) (actual time=2.786..2.787 rows=1 loops=1)
                    Sort Key: "ANY_subquery".ctid
                    Sort Method: quicksort  Memory: 25kB
                    Buffers: shared hit=479
                    ->  Subquery Scan on "ANY_subquery"  (cost=0.00..0.37 rows=1 width=36) (actual time=2.753..2.753 rows=1 loops=1)
                          Buffers: shared hit=474
                          ->  Limit  (cost=0.00..0.36 rows=1 width=6) (actual time=2.735..2.735 rows=1 loops=1)
                                Buffers: shared hit=474
                                ->  Seq Scan on dynamic_data dynamic_data_1  (cost=0.00..4020.71 rows=11093 width=6) (actual time=2.735..2.735 rows=1 loops=1)
                                      Filter: (env = 'AE'::text)
                                      Rows Removed by Filter: 5614
                                      Buffers: shared hit=474
        ->  Seq Scan on dynamic_data  (cost=0.00..3923.37 rows=38937 width=6) (actual time=0.005..8.130 rows=35939 loops=1)
              Buffers: shared hit=3534
Planning time: 0.354 ms
Execution time: 338.969 ms

我从查询计划中得到的主要结论是 I/O 时间占用了 312/338 = 92% 的时间：

actual time=338.913..338.913 rows=0 loops=1)
  Buffers: shared hit=7972 read=988 dirtied=975
  I/O Timings: read=312.160

我找不到任何关于如何在不更改数据库配置的情况下提高此查询的 I/O 性能的信息。这仅仅是大表行/数据库的不幸影响吗？还是我错过了一些重要的东西？如何加快此删除操作？

在没有任何解决方案的情况下，我默认让脚本一次删除 1 行，并使用单独的查询。远非理想，所以我希望你有建议。

请注意，我没有管理数据库，也没有授权对其配置进行任何更改，DBA 也不太可能更改配置以应对我设计不当的设置。

标签： postgresqlsql-deletequery-performance