Skip to content

exclude temporary schemas from the statistics

Vincent Sellier requested to merge generated-differential-D4635-source into generated-differential-D4635-target

The requests gathering performance statistics of postgres are getting slow when the number of opened connection increase. This is due to the cache of temporary tables cleaned only when the connections are closed. Filtering on the public schema exclude the temporary statistics.

When there is a lot of connections, one request one pg_statio_user_tables can take ~40s and return ~1000 lines:

softwareheritage=#           SELECT
          current_database()::text AS datname,
          COALESCE(schemaname::text, 'null') AS schemaname,
          COALESCE(relname::text, 'null') AS relname,
          COALESCE(heap_blks_read::float, 0) AS heap_blks_read,
          COALESCE(heap_blks_hit::float, 0) AS heap_blks_hit,
          COALESCE(idx_blks_read::float, 0) AS idx_blks_read,
          COALESCE(idx_blks_hit::float, 0) AS idx_blks_hit
          FROM pg_statio_user_tables FULL JOIN (VALUES(0)) filler(i) ON TRUE;
SELECT
          current_database()::text AS datname,
          COALESCE(schemaname::text, 'null') AS schemaname,
          COALESCE(relname::text, 'null') AS relname,
          COALESCE(heap_blks_read::float, 0) AS heap_blks_read,
          COALESCE(heap_blks_hit::float, 0) AS heap_blks_hit,
          COALESCE(idx_blks_read::float, 0) AS idx_blks_read,
          COALESCE(idx_blks_hit::float, 0) AS idx_blks_hit
          FROM pg_statio_user_tables FULL JOIN (VALUES(0)) filler(i) ON TRUE;
...
 1561783992 | pg_temp_76  | tmp_content                 |              1 |             1 |               |              |               0 |              0 |              0 |             0
 1561784918 | pg_temp_187 | tmp_content                 |             23 |            23 |               |              |               0 |              0 |              0 |             0
 1561786123 | pg_temp_187 | tmp_directory               |              1 |             8 |               |              |               0 |              0 |              0 |             0
 1561786130 | pg_temp_187 | tmp_directory_entry_file    |              1 |             2 |               |              |               0 |              0 |              0 |             0
 1561786137 | pg_temp_187 | tmp_directory_entry_dir     |              1 |             2 |               |              |               0 |              0 |              0 |             0
 1561786144 | pg_temp_187 | tmp_directory_entry_rev     |              0 |             0 |               |              |               0 |              0 |              0 |             0
 1561786153 | pg_temp_187 | tmp_revision                |              2 |             6 |               |              |               2 |              4 |              4 |            12
 1898621340 | public      | metadata_authority          |           1497 |      97622321 |          2185 |    122252751 |               0 |              0 |              0 |             0
 1898621347 | public      | origin_metadata             |           3256 |            33 |             2 |            4 |               0 |              0 |              0 |             0
 1898621354 | public      | origin_metadata_translation |              0 |             0 |             0 |            0 |               0 |              0 |              0 |             0
 2119436598 | public      | metadata_fetcher            |            255 |      97623588 |          1660 |     24591505 |               0 |              0 |              0 |             0
...
(954 rows)
Time: 39803.734 ms (00:39.804)

It can also reduce the number of timeseries creation in prometheus.

Related to T2828

Test Plan

softwareheritage=#           SELECT
          current_database()::text AS datname,
          COALESCE(schemaname::text, 'null') AS schemaname,
          COALESCE(relname::text, 'null') AS relname,
          COALESCE(heap_blks_read::float, 0) AS heap_blks_read,
          COALESCE(heap_blks_hit::float, 0) AS heap_blks_hit,
          COALESCE(idx_blks_read::float, 0) AS idx_blks_read,
          COALESCE(idx_blks_hit::float, 0) AS idx_blks_hit
          FROM pg_statio_user_tables FULL JOIN (VALUES(0)) filler(i) ON TRUE;
SELECT
          current_database()::text AS datname,
          COALESCE(schemaname::text, 'null') AS schemaname,
          COALESCE(relname::text, 'null') AS relname,
          COALESCE(heap_blks_read::float, 0) AS heap_blks_read,
          COALESCE(heap_blks_hit::float, 0) AS heap_blks_hit,
          COALESCE(idx_blks_read::float, 0) AS idx_blks_read,
          COALESCE(idx_blks_hit::float, 0) AS idx_blks_hit
          FROM pg_statio_user_tables FULL JOIN (VALUES(0)) filler(i) ON TRUE;
...
(954 rows)
Time: 39803.734 ms (00:39.804)
softwareheritage-# ;
SELECT
          current_database()::text AS datname,
          COALESCE(schemaname::text, 'null') AS schemaname,
          COALESCE(relname::text, 'null') AS relname,
          COALESCE(heap_blks_read::float, 0) AS heap_blks_read,
          COALESCE(heap_blks_hit::float, 0) AS heap_blks_hit,
          COALESCE(idx_blks_read::float, 0) AS idx_blks_read,
          COALESCE(idx_blks_hit::float, 0) AS idx_blks_hit
          FROM pg_statio_user_tables FULL JOIN (VALUES(0)) filler(i) ON TRUE
          WHERE schemaname='public'
...
(25 rows)
Time: 235.463 ms

Migrated from D4635 (view on Phabricator)

Merge request reports