Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1epEsd-0007IV-6x for pgsql-performance@arkaria.postgresql.org; Fri, 23 Feb 2018 15:03:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1epEsb-0008Gy-SZ for pgsql-performance@arkaria.postgresql.org; Fri, 23 Feb 2018 15:03:21 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1epEsb-0008Gk-Hu for pgsql-performance@lists.postgresql.org; Fri, 23 Feb 2018 15:03:21 +0000 Received: from mout.perfora.net ([74.208.4.197]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1epEsY-0002q2-QQ for pgsql-performance@lists.postgresql.org; Fri, 23 Feb 2018 15:03:20 +0000 Received: from [192.168.1.101] ([98.169.215.140]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MOyC9-1emmIe18zv-006OQz; Fri, 23 Feb 2018 16:03:15 +0100 Message-ID: <5A902D30.6070603@sqlexec.com> Date: Fri, 23 Feb 2018 10:03:12 -0500 From: MichaelDBA User-Agent: Postbox 5.0.22 (Windows/20171208) MIME-Version: 1.0 To: =?UTF-8?B?RGFyaXVzIFDEl8W+YQ==?= CC: pgsql-performance@lists.postgresql.org Subject: Re: need advice to tune postgresql References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------080203010303070705020604" X-Provags-ID: V03:K0:Sz3hh8l6kjtPHlUVtijNfaCcrfYzkUSX85Nl19WFBATxlL5q7CZ oJtjw709Fc3vNuCrmNdJEhDLtpeeQhOPsUXpGHOA6lSWaj7H14dp3fxUYjPh3A08qAXPevd TUAj0s4IgGHN2kKiy17lJrrxgQ+f74FV9kEKimLVfFujBuL3HrFfPCfoX1IKPIotMUcK/V9 MCHMamQpFgi+kc414eFGw== X-UI-Out-Filterresults: notjunk:1;V01:K0:4+weBSBDEDg=:Cs6rG/q/uvLfOFC+t9S9FE X+Rgj75OI9iRCAvtiBmtwNXF0ooIKwFN2oOb3wRoGfnA/1N2ywLyKrpEn2MKgEUWIGbshiWXh lH8tyWOj237Ahutp57z/EMGKxSiipXgYg/VmPk6L99N80pC2Kz/Y1a3WLJkg/ZXsMrpPGO44t vTp5yEraNEtrl1kfmCobAN1mCQahQMd1d+BJUmjkWbjHwokquh+pnitd6ftk6r0HEoUAjN412 tVd+tq3r6f2e1OW/SN1RCL16eV6+ksbu93vQ4Zvy1eVBj0kdsn2S2zqbdoZzon3+hE7dmM6t1 W6wOhX68u/0kJWspdDwp7Gs0miWEeMbnhJJOIZHfJB6fAjld2hgI7x0c9B5KWPGPrcFzYUPAP yi5CEgJG3Lg91jT24zh5RD82YicAoO6M6TgQqe9+LRZYMAa2vruXxoffLRRSigOqNcEUh+4rK mTinLGY3cuHJ13Nx95Szr1dOGnxhtPJsxfgDtNqF2ZNK/e32NRvwwmq6tiNXjlqDUfRJkGo25 nZHvnU95+vd9BUMJK/BenaFu6WSU4rDVkz5H32OIaIHIBLQ/PwYNW1UzNxi39DNwxKgqjg7UM wrT7cvA+pX/t+YuM7WN8Ds2APtdbcE8sEkfTr6XebkTMahB9EkhZmu9wqiesBlPeHNqM5dTdf cAGXRSiooq6bx7g9Apxa/k0PIu+Vsd1I47BOcCBl+ZvmDjcWZ0TpnKT1DMV7Lzdd5oY1fcByB OFBcXFs1lEIPfbLAh60m/MSvGQ/JgOBMNmG6ODwesAdgFR4mrAfeaLEarYw= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------080203010303070705020604 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. > --------------080203010303070705020604 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.


--------------080203010303070705020604--