Hello,
We found problem that a huge amount of memory was consumed when we created a foreign key on a partitioned table including a lots partitions and accessed them, as discussed in [1]. Kuroda-san's idea proposed in this thread is reducing cached SPI plans by combining several plans into one. But we are also considering another option to solve this problem, which makes users to release cached SPI plans for referential integrity as well as plain cached plans with DISCARD ALL. To do that, we added a new function, RI_DropAllPreparedPlan() which deletes all plans from ri_query_cache and modified DISCARD ALL to execute that function. I tested using a test case yamada-san attached in [2] as follows: [Before DISCARD ALL] =# SELECT name, sum(used_bytes) as bytes, pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts WHERE name LIKE 'Cached%' GROUP BY name; name | bytes | pg_size_pretty ------------------+-----------+---------------- CachedPlanQuery | 1326280 | 1295 kB CachedPlanSource | 1474616 | 1440 kB CachedPlan | 744009168 | 710 MB (3 rows) [After DISCARD ALL] =# DISCARD ALL; DISCARD ALL =# SELECT name, sum(used_bytes) as bytes, pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts WHERE name LIKE 'Cached%' GROUP BY name; name | bytes | pg_size_pretty ------------------+-------+---------------- CachedPlanQuery | 10280 | 10 kB CachedPlanSource | 14616 | 14 kB CachedPlan | 13168 | 13 kB (3 rows) In addition to that, a following case would be solved with this approach: When many processes are referencing many tables defined foreign key constraints thoroughly, a huge amount of memory will be consumed regardless of whether referenced tables are partitioned or not. Attached the patch. Any thoughts? [1] https://www.postgresql.org/message-id/flat/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1 [2]https://www.postgresql.org/message-id/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1 -- Best regards, Yuzuko Hosoya NTT Open Source Software Center |
In addition to that, a following case would be solved with this approach: Amit Langote has done some great work at eliminating SPI from INSERT/UPDATE triggers entirely, thus reducing the number of cached plans considerably. I think he was hoping to have a patch formalized this week, if time allowed. It doesn't have DELETE triggers in it, so this patch might still have good value for deletes on a commonly used enumeration table. However, our efforts might be better focused on eliminating SPI from delete triggers as well, an admittedly harder task. |
On Wed, Jan 13, 2021 at 1:03 PM Corey Huinker <[hidden email]> wrote:
Amit's patch is now available in this thread [1]. I'm curious if it has any effect on your memory pressure issue. |
Hi Corey,
Thank you for sharing. > Amit's patch is now available in this thread [1]. I'm curious if it has any effect on your memory pressure issue. > I just found that thread. I'll check the patch. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center |
In reply to this post by yuzuko
On 2021-01-13 09:47, yuzuko wrote:
> But we are also considering another option to solve this problem, which > makes users to release cached SPI plans for referential integrity as well as > plain cached plans with DISCARD ALL. To do that, we added a new > function, RI_DropAllPreparedPlan() which deletes all plans from > ri_query_cache and > modified DISCARD ALL to execute that function. I don't have a comment on the memory management issue, but I think the solution of dropping all cached plans as part of DISCARD ALL seems a bit too extreme of a solution. In the context of connection pooling, getting a new session with pre-cached plans seems like a good thing, and this change could potentially have a performance impact without a practical way to opt out. |
Free forum by Nabble | Edit this page |