public inbox for [email protected]
help / color / mirror / Atom feedFrom: Muhammad Usman Khan <[email protected]>
To: Durgamahesh Manne <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Recommendations on improving the insert on conflict do nothing performance
Date: Thu, 12 Sep 2024 09:35:38 +0500
Message-ID: <CAPnRvGu_Zdf7rq+5EjRdcGZHwd711g=Cg2EpUZk6QwqKWKxzHw@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoKi5RqJ0Y+_D_D_yhdLXcCiiCG4uBdui1Mgd0wC=EM4oA@mail.gmail.com>
References: <CAJCZkoKi5RqJ0Y+_D_D_yhdLXcCiiCG4uBdui1Mgd0wC=EM4oA@mail.gmail.com>
Hi,
You can use the following approaches for optimization:
- Instead of inserting one row at a time, perform bulk inserts, which
will reduce the overhead of each individual transaction
- Partitioning can improve write performance by splitting the data into
smaller, more manageable chunks
- Tune postgres configuration like
work_mem = '16MB'
shared_buffers = '8GB'
effective_cache_size = '24GB'
On Wed, 11 Sept 2024 at 13:50, Durgamahesh Manne <[email protected]>
wrote:
> Hi
> insert into
> dictionary(lang,tid,sportid,brandid,translatedtext,objecttype,basetid)
> values ($1,$2,$3,$4,$5,$6,$7) on conflict do nothing
> *8vcpus and 32gb ram
> Number of calls per sec 1600 at this time 42% of cpu utilized
> Max in ms 33.62 per call
> Avg in ms 0.17 per call
> Table
> "dictionary.dictionary"
> Column | Type | Collation | Nullable |
> Default | Storage | Compression | Stats target | Description
>
> ----------------+--------------------------+-----------+----------+----------+----------+-------------+--------------+-------------
> lang | text | | not null |
> | extended | | |
> tid | text | | not null |
> | extended | | |
> basetid | text | | not null |
> | extended | | |
> sportid | text | | |
> | extended | | |
> brandid | text | | not null |
> | extended | | |
> translatedtext | text | | |
> | extended | | |
> objecttype | text | | |
> | extended | | |
> createdat | timestamp with time zone | | not null | now()
> | plain | | |
> modified | timestamp with time zone | | not null | now()
> | plain | | |
> modifiedby | text | | not null |
> ''::text | extended | | |
> version | integer | | not null | 0
> | plain | | |
> Indexes:
> "pk_dictionary" PRIMARY KEY, btree (lang, tid)
> "idx_dictionary_basetid" btree (basetid)
> "idx_dictionary_brandid" btree (brandid)
> "idx_dictionary_objecttype" btree (objecttype)
> "idx_dictionary_sportid" btree (sportid)
> Triggers:
> i_dictionary_createdat BEFORE INSERT ON dictionary FOR EACH ROW
> EXECUTE FUNCTION update_createdat_col()
> i_dictionary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH
> ROW EXECUTE FUNCTION update_modified_col()
> Access method: heap
> How do we improve this query performance without taking more cpu?
>
> Regards,
> Durga Mahesh
>
view thread (2+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Recommendations on improving the insert on conflict do nothing performance
In-Reply-To: <CAPnRvGu_Zdf7rq+5EjRdcGZHwd711g=Cg2EpUZk6QwqKWKxzHw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox