Re: need advice to tune postgresql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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.



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux