Thread: 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.

Darius Pėža wrote:
> 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.

If the number of updated lines is that big, you should try to
get HOT updates as much as possible.

For that, make sure that there is *no* index on the column,
and that the fillfactor for the table is suitably low (perhaps 50).

During a HOT update, when the new row version fits into the same
page as the old one, the indexes don't have to be updated.
That will speed up the UPDATE considerably.

On the other hand, an UPDATE like yours would then always use a
sequential scan, but that may still be a net win.

Other than that, setting checkpoint_segments high enough that
you don't get too many checkpoints can help.

Of course, more RAM and fast storage are always good.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com