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:dar...@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.
