| From: | Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp> |
|---|---|
| To: | Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Amit Langote <amitlangote09(at)gmail(dot)com>, tatsuhito(dot)kasahara(dot)rd(at)hco(dot)ntt(dot)co(dot)jp, keisuke(dot)kuroda(dot)3862(at)gmail(dot)com |
| Subject: | Huge memory consumption on partitioned table with FKs |
| Date: | 2020-11-24 07:46:28 |
| Message-ID: | cab4b85d-9292-967d-adf2-be0d803c3e23@nttcom.co.jp_1 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi Hackers,
My company (NTT Comware) and NTT OSS Center did verification of
partitioned table on PG14dev, and we faced a problem that consumed
huge memory when we created a Foreign key constraint on a partitioned
table including 500 partitioning tables and inserted some data.
We investigated it a little to use the "pg_backend_memory_contextes"
command and realized a "CachedPlan" of backends increased dramatically.
See below:
Without FKs
==============================
CachedPlan 0kB
With FKs (the problem is here)
==============================
CachedPlan 710MB
Please find the attached file to reproduce the problem.
We know two things as following:
- Each backend uses the size of CachedPlan
- The more increasing partitioning tables, the more CachedPlan
consuming
If there are many backends, it consumes about the size of CachedPlan x
the number of backends. It may occur a shortage of memory and OOM killer.
We think the affected version are PG12 or later. I believe it would be
better to fix the problem. Any thoughts?
Regards,
Tatsuro Yamada
| Attachment | Content-Type | Size |
|---|---|---|
| repro.sql | text/plain | 1.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Surafel Temesgen | 2020-11-24 07:47:25 | Re: Evaluate expression at planning time for two more cases |
| Previous Message | Fujii Masao | 2020-11-24 07:35:14 | Re: walsender bug: stuck during shutdown |