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: <50B8BC7F.4000005@optionshouse.com>
Date: Fri, 30 Nov 2012 15:48:57 +0100
Cc: Kevin Grittner <kgrittn@mail.com>,
	Willem Leenen <willem_leenen@hotmail.com>,
	<pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <5F52712E-C339-4DF2-9EE5-A26643CEA2C2@autouncle.com>
References: <20121130012426.69300@gmx.com>
	<C5E296A1-F820-4B04-9DBD-E78380BD3300@autouncle.com>
	<855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
	<50B8BC7F.4000005@optionshouse.com>
To: <sthomas@optionshouse.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

> On 11/29/2012 08:32 PM, Niels Kristian Schj=F8dt wrote:
>=20
>> If I do a "sudo iostat -k 1"
>> I get a lot of output like this:
>> Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
>> sda               0.00         0.00         0.00          0          0
>> sdb               0.00         0.00         0.00          0          0
>> sdc             546.00      2296.00      6808.00       2296       6808
>> sdd             593.00      1040.00      7416.00       1040       7416
>> md1               0.00         0.00         0.00          0          0
>> md0               0.00         0.00         0.00          0          0
>> md2            1398.00      3328.00     13064.00       3328      13064
>> md3               0.00         0.00         0.00          0          0
>>=20
>=20
>> The storage thing is, that the sda and sdb is the SSD drives and the
>> sdc and sdd is the HDD drives. The md0, md1 and md2 is the raid
>> arrays on the HDD's and the md3 is the raid on the SSD's. Neither of
>> the md3 or the SSD's are getting utilized - and I should expect that
>> since they are serving my pg_xlog right?
>=20
> No, that's right. They are, but it would appear that the majority of your=
 traffic actually isn't due to transaction logs like I'd suspected. If you =
get a chance, could you monitor the contents of:
>=20
> /var/lib/postgresql/9.2/main/base/pgsql_tmp
>=20
> Your main drives are getting way, way more writes than they should. 13MB =
per second is ridiculous even under heavy write loads. Based on the TPS cou=
nt, you're basically saturating the ability of those two 3TB drives. Those =
writes have to be coming from somewhere.
>=20
>> #   sudo mkdir -p /ssd/pg_xlog
>=20
> This is going to sound stupid, but are you *sure* the SSD is mounted at /=
ssd ?
>=20
>> #   sudo chown -R  postgres.postgres /ssd/pg_xlog
>> #   sudo chmod 700 /ssd/pg_xlog
>> #   sudo cp -rf /var/lib/postgresql/9.2/main/pg_xlog/* /ssd/pg_xlog
>> #   sudo mv /var/lib/postgresql/9.2/main/pg_xlog /var/lib/postgresql/9.2=
/main/pg_xlog_old
>> #   sudo ln -s /ssd/pg_xlog /var/lib/postgresql/9.2/main/pg_xlog
>> #   sudo /etc/init.d/postgresql-9.2 start
>=20
> The rest of this is fine, except that you probably should have added:
>=20
> sudo chown -R postgres:postgres /ssd/pg_xlog/*
>=20
>=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

Oh my, Shaun once again you nailed it! That's what you get from working too=
 late in the night - I forgot to run 'sudo mount -a' I feel so embarrassed =
now :-( - In other words no the drive was not mounted to the /ssd dir.=20
So now it is, and this has gained me a performance increase of roughly arou=
nd 20% - a little less than what I would have hoped for but still better - =
but anyways yes that's right.
I still see a lot of CPU I/O when doing a lot of writes, so the question is=
, what's next. Should I try and go' for the connection pooling thing or mon=
itor that /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly=
 do you mean by monitor - size?)

PS. comment on the "Why not move the data to the SSDs" you are exactly righ=
t. i don't think the SSD's will be big enough for the data within a not too=
 long timeframe, so that is exactly why I want to keep my data on the "big"=
 drives.
PPS. I talked with New Relic and it turns out there is something wrong with=
 the disk monitoring tool, so that's why there was nothing in the disk char=
ts but iostat showed a lot of activity.


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