From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Darius Pėža <darius@onrails.lt>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: need advice to tune postgresql
Date: Fri, 23 Feb 2018 10:03:12 -0500
Message-ID: <5A902D30.6070603@sqlexec.com> (raw)
In-Reply-To: <CAGAb3f7sTq30vj1sDAH3BWBAh4CLV4OLa3GrdxW_iTSKBT3zfw@mail.gmail.com>
References: <CAGAb3f7sTq30vj1sDAH3BWBAh4CLV4OLa3GrdxW_iTSKBT3zfw@mail.gmail.com>
What caught my eye is the update count can be up to 10K. That means if
autovacuum is not keeping up with this table, bloat may be increasing at
a high pace leading to more page I/O which causes degraded performance.
If the table has become bloated, you need to do a blocking VACUUM FULL
on it or a non-blocking VACUUM using pg_repack. Then tune autovacuum so
that it can keep up with the updates to this table or add manual vacuum
analyze on this table at certain times via a cron job. Manual vacuums
(user-initiated) will not be bumped as with autovacuums that can be
bumped due to user priority.
Regards,
Michael Vitale
> Darius Pėža <mailto:darius@onrails.lt>
> Friday, February 23, 2018 9:42 AM
> 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.
>
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: MichaelDBA@sqlexec.com, darius@onrails.lt, pgsql-performance@lists.postgresql.org
Subject: Re: need advice to tune postgresql
In-Reply-To: <5A902D30.6070603@sqlexec.com>
* 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