Hi hackers, Most catcache and relcache entries (other than index info etc.) currently go straight into CacheMemoryContext. And I believe these two caches can be the ones with the largest contribution to the memory usage of CacheMemoryContext most of the time. For example, in cases where we have lots of database objects accessed in a long-lived connection, CacheMemoryContext tends to increase significantly.
While I've been working on another patch for pg_backend_memory_contexts
view, we thought that it would also be better to see the memory usages of
different kinds of caches broken down into their own contexts. The attached
patch implements this and aims to easily keep track of the memory used by
relcache and catcache
To quickly show how pg_backend_memory_contexts would look like, I did the
following:
-Create some tables:
SELECT 'BEGIN;' UNION ALL SELECT format('CREATE TABLE %1$s(id serial
primary key, data text not null unique)', 'test_'||g.i) FROM
generate_series(0, 1000) g(i) UNION ALL SELECT 'COMMIT;';\gexec
-Open a new connection and query pg_backend_memory_contexts [1]:
This is what you'll see before and after the patch.
-- HEAD:
name | used_bytes | free_bytes | total_bytes
--------------------+------------+------------+-------------
CacheMemoryContext | 467656 | 56632 | 524288
index info | 111760 | 46960 | 158720
relation rules | 4416 | 3776 | 8192
(3 rows)
-- Patch:
name | used_bytes | free_bytes | total_bytes
-----------------------+------------+------------+-------------
CatCacheMemoryContext | 217696 | 44448 | 262144
RelCacheMemoryContext | 248264 | 13880 | 262144
index info | 111760 | 46960 | 158720
CacheMemoryContext | 2336 | 5856 | 8192
relation rules | 4416 | 3776 | 8192
(5 rows)
- Run select on all tables
SELECT format('SELECT count(*) FROM %1$s', 'test_'||g.i) FROM
generate_series(0, 1000) g(i);\gexec
- Then check pg_backend_memory_contexts [1] again:
--HEAD
name | used_bytes | free_bytes | total_bytes
--------------------+------------+------------+-------------
CacheMemoryContext | 8197344 | 257056 | 8454400
index info | 2102160 | 113776 | 2215936
relation rules | 4416 | 3776 | 8192
(3 rows)
--Patch
name | used_bytes | free_bytes | total_bytes
-----------------------+------------+------------+-------------
RelCacheMemoryContext | 4706464 | 3682144 | 8388608
CatCacheMemoryContext | 3489384 | 770712 | 4260096
index info | 2102160 | 113776 | 2215936
CacheMemoryContext | 2336 | 5856 | 8192
relation rules | 4416 | 3776 | 8192
(5 rows)
You can see that CacheMemoryContext does not use much memory without
catcache and relcache (at least in cases similar to above), and it's easy
to bloat catcache and relcache. That's why I think it would be useful to
see their usage separately.
Any feedback would be appreciated.
[1]
SELECT
name,
sum(used_bytes) AS used_bytes,
sum(free_bytes) AS free_bytes,
sum(total_bytes) AS total_bytes
FROM pg_backend_memory_contexts
WHERE name LIKE '%CacheMemoryContext%' OR parent LIKE '%CacheMemoryContext%'
GROUP BY name
ORDER BY total_bytes DESC;
Thanks,
--
Melih Mutlu
Microsoft
0001-Separate-memory-contexts-for-relcache-and-catcache.patch
Description: Binary data
