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

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.