From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Darius Pėža <darius@onrails.lt>
To: pgsql-performance@lists.postgresql.org
Subject: Re: need advice to tune postgresql
Date: Fri, 23 Feb 2018 16:26:55 +0100
Message-ID: <1519399615.2530.14.camel@cybertec.at> (raw)
In-Reply-To: <CAGAb3f7sTq30vj1sDAH3BWBAh4CLV4OLa3GrdxW_iTSKBT3zfw@mail.gmail.com>
References: <CAGAb3f7sTq30vj1sDAH3BWBAh4CLV4OLa3GrdxW_iTSKBT3zfw@mail.gmail.com>
Darius Pėža wrote:
> I have issue that update queries is slow, I need some advice how improve speed. I don't have much control to change queries. But I can change postresql server configuration
>
> query example:
>
> UPDATE "project_work" SET "left" = ("project_work"."left" + 2) WHERE ("project_work"."left" >= 8366)
>
> sometimes updated lines count is up to 10k
>
> postgresql version 9.3
>
> postgresl.conf
> max_connections = 100
> shared_buffers = 6GB # min 128kB
> work_mem = 100MB # min 64kB
>
> all other values are default
>
> server hardware
> Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz
> 16GB RAM
> disk is HDD
>
> about half of resource I can dedicate for postgresql server.
If the number of updated lines is that big, you should try to
get HOT updates as much as possible.
For that, make sure that there is *no* index on the column,
and that the fillfactor for the table is suitably low (perhaps 50).
During a HOT update, when the new row version fits into the same
page as the old one, the indexes don't have to be updated.
That will speed up the UPDATE considerably.
On the other hand, an UPDATE like yours would then always use a
sequential scan, but that may still be a net win.
Other than that, setting checkpoint_segments high enough that
you don't get too many checkpoints can help.
Of course, more RAM and fast storage are always good.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, darius@onrails.lt, pgsql-performance@lists.postgresql.org
Subject: Re: need advice to tune postgresql
In-Reply-To: <1519399615.2530.14.camel@cybertec.at>
* 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