Release SPI plans for referential integrity with DISCARD ALL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Release SPI plans for referential integrity with DISCARD ALL

yuzuko
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

v1_discard_ri_spi_plans.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Release SPI plans for referential integrity with DISCARD ALL

Corey Huinker
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?

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.
Reply | Threaded
Open this post in threaded view
|

Re: Release SPI plans for referential integrity with DISCARD ALL

Corey Huinker
On Wed, Jan 13, 2021 at 1:03 PM Corey Huinker <[hidden email]> wrote:
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?

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.

Amit's patch is now available in this thread [1]. I'm curious if it has any effect on your memory pressure issue.
Reply | Threaded
Open this post in threaded view
|

Re: Release SPI plans for referential integrity with DISCARD ALL

yuzuko
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


Reply | Threaded
Open this post in threaded view
|

Re: Release SPI plans for referential integrity with DISCARD ALL

Peter Eisentraut-7
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.



Reply | Threaded
Open this post in threaded view
|

Re: Release SPI plans for referential integrity with DISCARD ALL

yuzuko
Hello,

Thank you for your comments.
Following Corey's advice, I applied Amit's patches proposed in this
email [1], and confirmed our memory pressure problem was solved.
So dropping cached plan with DISCARD is not necessary anymore.

[1] https://www.postgresql.org/message-id/CA%2BHiwqG1qQuBwApueaUfA855UJ4TiSgFkPF34hQDWx3tOChV5w%40mail.gmail.com

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center


Reply | Threaded
Open this post in threaded view
|

Re: Release SPI plans for referential integrity with DISCARD ALL

yuzuko
Hello,

I thought about this suggestion again.

Amit's patch suggested in the thread [1] can eliminate SPI plans from
INSERT/UPDATE triggers, so our memory pressure issue would be solved.
But as far as I can see that thread, Amit's patch doesn't cover DELETE case.
It is not a common case, but there is a risk of pressing memory
capacity extremely.
Considering that, this suggestion might still have good value as Corey
said before.

I improved a patch according to Peter's following comment :
> 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.

In a new patch, I separated discarding SPI Plan caches for RI from DISCARD ALL
and added a new option "RIPLANS" to DISCARD command to do that.  Also a new
function, ResetRIPlanCache() which clears SPI plan caches is called by
DISCARD ALL
or DISCARD RIPLANS.  The amount of modification is small and this option can be
removed instantly when it is no longer needed, so I think we can use
this patch as
a provisional solution.

Any thoughts?

[1] https://www.postgresql.org/message-id/flat/CA%2BHiwqGkfJfYdeq5vHPh6eqPKjSbfpDDY%2Bj-kXYFePQedtSLeg%40mail.gmail.com

--
Best regards,
Yuzuko Hosoya
NTT Open Source Software Center

v2_discard_ri_spi_plans.patch (9K) Download Attachment