pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: kurt@roeckx.be
Subject: BUG #16443: Too much memory usage on insert query
Date: Sun, 17 May 2020 14:42:09 +0000
Message-ID: <16443-030629aae2859708@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16443
Logged by:          Kurt Roeckx
Email address:      kurt@roeckx.be
PostgreSQL version: 12.3
Operating system:   Debian
Description:        

Hi,

I have this in my config file:
shared_buffers = 2048MB                 # min 128kB
work_mem = 1024MB                       # min 64kB

I was executing this query:
insert into ct_entry_chain (ct_entry_id, certificate_id) select id,
unnest(certificate_chain_id) from ct_entry;

The process was using at least 14 GB, of the 8 GB of RAM that's available.
Swap was also using around the 8GB maximum, while normally around 200 MB is
swapped out. I didn't expect this process to use more than around 3 GB.

This has resulted in an out of memory condition.

I was converting my schema, ct_entry_chain is a new table that looks like:
                                Table "public.ct_entry_chain"
     Column     |  Type  | Collation | Nullable |                  Default
----------------+--------+-----------+----------+--------------------------------------------
 id             | bigint |           | not null |
nextval('ct_entry_chain_id_seq'::regclass)
 ct_entry_id    | bigint |           | not null |
 certificate_id | bigint |           | not null |
Indexes:
    "ct_entry_chain_pkey" PRIMARY KEY, btree (id)
    "ct_entry_chain_ct_entry_id_certificate_id_key" UNIQUE CONSTRAINT, btree
(ct_entry_id, certificate_id)
Foreign-key constraints:
    "ct_entry_chain_certificate_id_fkey" FOREIGN KEY (certificate_id)
REFERENCES raw_certificates(id)
    "ct_entry_chain_ct_entry_id_fkey" FOREIGN KEY (ct_entry_id) REFERENCES
ct_entry(id)

ct_entry itself looks like:
                                            Table "public.ct_entry"
        Column        |           Type           | Collation | Nullable |   
           Default
----------------------+--------------------------+-----------+----------+--------------------------------------
 id                   | bigint                   |           | not null |
nextval('ct_entry_id_seq'::regclass)
 ct_log_id            | integer                  |           | not null |
 raw_certificate_id   | bigint                   |           | not null |
 log_entry            | bigint                   |           | not null |
 timestamp            | timestamp with time zone |           | not null |
 version              | integer                  |           | not null |
 entry_type           | integer                  |           | not null |
 leaf_type            | integer                  |           | not null |
 extensions           | bytea                    |           |          |
 issuer_key_hash      | bytea                    |           |          |
 pre_certificate_id   | bigint                   |           |          |
 certificate_chain_id | bigint[]                 |           |          |
Indexes:
    "ct_entry_pkey" PRIMARY KEY, btree (id)
    "ct_entry_raw_certificate_id_idx" btree (raw_certificate_id)
Foreign-key constraints:
    "ct_entry_ct_log_id_fkey" FOREIGN KEY (ct_log_id) REFERENCES
ct_logs(id)
    "ct_entry_pre_certificate_id_fkey" FOREIGN KEY (pre_certificate_id)
REFERENCES raw_certificates(id)
    "ct_entry_raw_certificate_id_fkey" FOREIGN KEY (raw_certificate_id)
REFERENCES raw_certificates(id)
Referenced by:
    TABLE "ct_entry_chain" CONSTRAINT "ct_entry_chain_ct_entry_id_fkey"
FOREIGN KEY (ct_entry_id) REFERENCES ct_entry(id)



view thread (4+ messages)  latest in thread

Message-ID: <16443-030629aae2859708@postgresql.org>
Permalink:  ../16443-030629aae2859708@postgresql.org/
Also on:    postgresql.org/message-id/16443-030629aae2859708@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, kurt@roeckx.be
  Subject: Re: BUG #16443: Too much memory usage on insert query
  In-Reply-To: <16443-030629aae2859708@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox