Separate memory contexts for relcache and catcache - Mailing list pgsql-hackers

From Melih Mutlu
Subject Separate memory contexts for relcache and catcache
Date
Msg-id CAGPVpCTJWEQLt2eOSDGTDtRbQPUQ9b9JtZWro9osJubTyWAEMA@mail.gmail.com
Whole thread Raw
Responses Re: Separate memory contexts for relcache and catcache
Re: Separate memory contexts for relcache and catcache
Re: Separate memory contexts for relcache and catcache
List pgsql-hackers
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
Attachment

pgsql-hackers by date:

Previous
From: David Rowley
Date:
Subject: Re: [PoC] Reducing planning time when tables have many partitions
Next
From: Andy Fan
Date:
Subject: Re: Separate memory contexts for relcache and catcache