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