From: Shaun Thomas <sthomas@optionshouse.com>
To: Niels Kristian Schjødt <nielskristian@autouncle.com>
Cc: Kevin Grittner <kgrittn@mail.com>
Cc: Willem Leenen <willem_leenen@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize update query
Date: Fri, 30 Nov 2012 08:02:39 -0600
Message-ID: <50B8BC7F.4000005@optionshouse.com> (raw)
In-Reply-To: <855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
References: <20121130012426.69300@gmx.com>
<C5E296A1-F820-4B04-9DBD-E78380BD3300@autouncle.com>
<855F8817-6C9A-4EFB-BB80-752BF7B73B56@autouncle.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 11/29/2012 08:32 PM, Niels Kristian Schjødt wrote:
> 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
>
> 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?
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:
/var/lib/postgresql/9.2/main/base/pgsql_tmp
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
count, you're basically saturating the ability of those two 3TB drives.
Those writes have to be coming from somewhere.
> # sudo mkdir -p /ssd/pg_xlog
This is going to sound stupid, but are you *sure* the SSD is mounted at
/ssd ?
> # 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
The rest of this is fine, except that you probably should have added:
sudo chown -R postgres:postgres /ssd/pg_xlog/*
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@optionshouse.com, nielskristian@autouncle.com, kgrittn@mail.com, willem_leenen@hotmail.com
Subject: Re: Optimize update query
In-Reply-To: <50B8BC7F.4000005@optionshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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