very high load in winery postgresql master

added environment: production platform::CEA winery labels

changed the description

Looks like some shards are not correctly cleaned, their tables are cleaned but not removed, don't know if it's expected or not

swh@gloin002:~$ sudo -u postgres psql winery -c "\d" | grep shard_ | wc -l
494

                                                              List of relations
 Schema |                  Name                  |   Type   | Owner  | Persistence | Access method |    Size    |          Description
--------+----------------------------------------+----------+--------+-------------+---------------+------------+-------------------------------
 public | dbmodule                               | table    | winery | permanent   | heap          | 48 kB      | Database module storage
 public | dbversion                              | table    | winery | permanent   | heap          | 48 kB      | Details of current db version
 public | shard_i0051e53bd89496abc4b8c680ac00e33 | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i005c26be6c64b85b9c52092db24d0b9 | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i0076fcf1c6d4f9da74382a34474cfda | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i0089526a9174468b8ed5eeec67fd5bd | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i00e1da287aa41bf9035130dd4d23e95 | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i019531f82374112808126a86d01cfc8 | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i02567c2bcba440da9a32ac438545d5e | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i03e724367f940df8febeb2b95230d3c | table    | winery | permanent   | heap          | 8192 bytes |
 public | shard_i050790f369a41c186841d2eec0eb682 | table    | winery | permanent   | heap          | 8192 bytes |

It seems most of the running request are related to the throttler.

FTR:

winery=# SELECT COUNT(*), SUM(bytes) FROM t_read WHERE bytes > 0 AND updated > NOW() - INTERVAL '5 minutes';
 count |   sum
-------+----------
 33387 | 55119867
(1 row)

Time: 614,885 ms

winery=# select count(*) from t_read;
  count
---------
 2967089
(1 row)

winery=# explain  SELECT COUNT(*), SUM(bytes) FROM t_read WHERE bytes > 0 AND updated > NOW() - INTERVAL '5 minutes';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=52236.16..52236.17 rows=1 width=16)
   ->  Gather  (cost=52235.94..52236.15 rows=2 width=16)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=51235.94..51235.95 rows=1 width=16)
               ->  Parallel Seq Scan on t_read  (cost=0.00..51195.28 rows=8133 width=4)
                     Filter: ((bytes > 0) AND (updated > (now() - '00:05:00'::interval)))
(6 rows)

To try and lessen the load, I've done the following:

create index on t_read using brin (updated);
create index on t_write using brin (updated);

This creates block range indexes on the updated column, which should allow reducing the number of blocks scanned for queries filtering by range of the updated column.

Before/after comparison:

winery=# explain analyze SELECT COUNT(*), SUM(bytes) FROM t_read WHERE bytes > 0 AND updated > NOW() - INTERVAL '5 minutes';
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=53358.01..53358.02 rows=1 width=16) (actual time=1055.497..1055.602 rows=1 loops=1)
   ->  Gather  (cost=53357.79..53358.00 rows=2 width=16) (actual time=1055.488..1055.594 rows=1 loops=1)
         Workers Planned: 2
         Workers Launched: 0
         ->  Partial Aggregate  (cost=52357.79..52357.80 rows=1 width=16) (actual time=1055.206..1055.207 rows=1 loops=1)
               ->  Parallel Seq Scan on t_read  (cost=0.00..52346.21 rows=2317 width=4) (actual time=0.057..1053.411 rows=19119 loops=1)
                     Filter: ((bytes > 0) AND (updated > (now() - '00:05:00'::interval)))
                     Rows Removed by Filter: 2922741
 Planning Time: 0.163 ms
 Execution Time: 1055.658 ms
(10 rows)

winery=# create index on t_read using brin (updated);
CREATE INDEX
winery=# explain analyze SELECT COUNT(*), SUM(bytes) FROM t_read WHERE bytes > 0 AND updated > NOW() - INTERVAL '5 minutes';
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18891.89..18891.90 rows=1 width=16) (actual time=76.220..76.221 rows=1 loops=1)
   ->  Bitmap Heap Scan on t_read  (cost=5.94..18864.29 rows=5520 width=4) (actual time=0.258..75.239 rows=19091 loops=1)
         Recheck Cond: (updated > (now() - '00:05:00'::interval))
         Rows Removed by Index Recheck: 348421
         Filter: (bytes > 0)
         Rows Removed by Filter: 289
         Heap Blocks: lossy=3840
         ->  Bitmap Index Scan on t_read_updated_idx  (cost=0.00..4.56 rows=23452 width=0) (actual time=0.168..0.169 rows=38400 loops=1)
               Index Cond: (updated > (now() - '00:05:00'::interval))
 Planning Time: 0.285 ms
 Execution Time: 76.275 ms
(11 rows)

It seems that we're missing a cleanup procedure for outdated rows in these tables...

mentioned in commit olasd/swh-objstorage@b0e554cc

mentioned in merge request swh/devel/swh-objstorage!202 (merged)

mentioned in commit olasd/swh-objstorage@3229f292

winery seems stable since the fixes.

closed

very high load in winery postgresql master

Designs

Child items ...

Activity