Content-Type: text/plain; charset=iso-8859-1
Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\))
Subject: Re: Optimize update query
From: =?iso-8859-1?Q?Niels_Kristian_Schj=F8dt?= <nielskristian@autouncle.com>
In-Reply-To: <50B641AD.3020204@optionshouse.com>
Date: Thu, 29 Nov 2012 04:32:11 +0100
Cc: Willem Leenen <willem_leenen@hotmail.com>,
	<pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <F9939192-448B-41FF-9FB1-61343754D050@autouncle.com>
References: <1C199C0D-13DF-47B3-944A-B0560C6D2E5A@autouncle.com>
	<50B61ABF.4070000@optionshouse.com>, <50B6223B.6000100@mejor.pl>
	<DUB104-W48E83E17609D570C42B5AD8F5D0@phx.gbl>
	<CAC9DA03-64C2-45E2-9B88-703F9768F574@autouncle.com>
	<50B641AD.3020204@optionshouse.com>
To: <sthomas@optionshouse.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi, I have started to implement your suggestions . I have a small error so =
far though. The "vm.dirty_writeback_ratio =3D 1" command rerurns:=20
  error: "vm.dirty_writeback_ratio" is an unknown key
I'm on ubuntu 12.04


Den 28/11/2012 kl. 17.54 skrev Shaun Thomas <sthomas@optionshouse.com>:

> On 11/28/2012 10:19 AM, Niels Kristian Schj=F8dt wrote:
>=20
>> https://rpm.newrelic.com/public/charts/h2dtedghfsv
>=20
> Doesn't this answer your question?
>=20
> That iowait is crushing your server into the ground. It's no surprise upd=
ates are taking several seconds. That update you sent us *should* execute o=
n the order of only a few milliseconds.
>=20
> So I'll reiterate that you *must* move your pg_xlog location elsewhere. Y=
ou've got row lookup bandwidth conflicting with writes. There are a couple =
other changes you should probably make to your config:
>=20
>> checkpoint_segments =3D 16
>=20
> This is not enough for the workload you describe. Every time the database=
 checkpoints, all of those changes in pg_xlog are applied to the backend da=
ta files. You should set these values:
>=20
> checkpoint_segments =3D 100
> checkpoint_timeout =3D 10m
> checkpoint_completion_target =3D 0.9
>=20
> This will reduce your overall write workload, and make it less active. To=
o many checkpoints massively reduce write throughput. With the settings you=
 have, it's probably checkpointing constantly while your load runs. Start w=
ith this, but experiment with increasing checkpoint_segments further.
>=20
> If you check your logs now, you probably see a ton of "checkpoint startin=
g: xlog" in there. That's very bad. It should say "checkpoint starting: tim=
e" meaning it's keeping up with your writes naturally.
>=20
>> work_mem =3D 160MB
>=20
> This is probably way too high. work_mem is used every sort operation in a=
 query. So each connection could have several of these allocated, thus star=
ting your system of memory which will reduce that available for page cache.=
 Change it to 8mb, and increase it in small increments if necessary.
>=20
>> So correct me if I'm wrong here: my theory is, that I have too many
>> too slow update queries, that then often end up in a situation, where
>> they "wait" for each other to finish, hence the sometimes VERY long
>> execution times.
>=20
> Sometimes this is the case, but for you, you're running into IO contentio=
n, not lock contention. Your 3TB RAID-1 is simply insufficient for this wor=
kload.
>=20
> If you check your logs after making the changes I've suggested, take a lo=
ok at your checkpoint sync times. That will tell you how long it took the k=
ernel to physically commit those blocks to disk and get a confirmation back=
 from the controller. If those take longer than a second or two, you're pro=
bably running into controller buffer overflows. You have a large amount of =
RAM, so you should also make these two kernel changes to sysctl.conf:
>=20
> vm.dirty_ratio =3D 10
> vm.dirty_writeback_ratio =3D 1
>=20
> Then run this:
>=20
> sysctl -p
>=20
> This will help prevent large IO write spikes caused when the kernel decid=
es to write out dirty memory. That can make checkpoints take minutes to com=
mit in some cases, which basically stops all write traffic to your database=
 entirely.
>=20
> That should get you going, anyway. You still need more/better disks so yo=
u can move your pg_xlog directory. With your write load, that will make a h=
uge difference.
>=20
> --=20
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> sthomas@optionshouse.com
>=20
> ______________________________________________
>=20
> See http://www.peak6.com/email_disclaimer/ for terms and conditions relat=
ed to this email


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance