Re: need advice to tune postgresql

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(at)onrails(dot)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.
>