pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedneed advice to tune postgresql
3+ messages / 3 participants
[nested] [flat]
* need advice to tune postgresql
@ 2018-02-23 14:42 Darius Pėža <darius@onrails.lt>
2018-02-23 15:03 ` Re: need advice to tune postgresql MichaelDBA <MichaelDBA@sqlexec.com>
2018-02-23 15:26 ` Re: need advice to tune postgresql Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 3+ messages in thread
From: Darius Pėža @ 2018-02-23 14:42 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: need advice to tune postgresql
2018-02-23 14:42 need advice to tune postgresql Darius Pėža <darius@onrails.lt>
@ 2018-02-23 15:03 ` MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 0 replies; 3+ messages in thread
From: MichaelDBA @ 2018-02-23 15:03 UTC (permalink / raw)
To: Darius Pėža <darius@onrails.lt>; +Cc: pgsql-performance@lists.postgresql.org
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@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.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: need advice to tune postgresql
2018-02-23 14:42 need advice to tune postgresql Darius Pėža <darius@onrails.lt>
@ 2018-02-23 15:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2018-02-23 15:26 UTC (permalink / raw)
To: Darius Pėža <darius@onrails.lt>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-02-23 15:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-23 14:42 need advice to tune postgresql Darius Pėža <darius@onrails.lt>
2018-02-23 15:03 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-02-23 15:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox