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 09:00:17 -0600
Message-ID: <50B8CA01.5040405@optionshouse.com> (raw)
In-Reply-To: <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>
<5F52712E-C339-4DF2-9EE5-A26643CEA2C2@autouncle.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 11/30/2012 08:48 AM, Niels Kristian Schjødt wrote:
> 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.
Yeah, that'll get ya.
> 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 monitor that
> /var/lib/postgresql/9.2/main/base/pgsql_tmp dir (and what exactly do
> you mean by monitor - size?)
Well, like Keven said, if you have more than a couple dozen connections
on your hardware, you're losing TPS. It's probably a good idea to
install pgbouncer or pgpool and let your clients connect to those
instead. You should see a good performance boost from that.
But what concerns me is that your previous CPU charts showed a lot of
iowait. Even with the SSD taking some of the load off your write stream,
something else is going on, there. That's why you need to monitor the
"size" in MB, or number of files, for the pgsql_tmp directory. That's
where PG puts temp files when sorts are too big for your work_mem. If
that's getting a ton of activity, that would explain some of your write
overhead.
> 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 charts but iostat showed a lot of activity.
Yeah. Next time you need to check IO, use iostat. It's not as pretty,
but it tells everything. ;) Just to help out with that, use:
iostat -dmx
That will give you extended information, including the % utilization of
your drives. TPS stats are nice, but I was just guessing your drives
were stalling out based on experience. Getting an outright percentage is
better. You should also use sar. Just a plain:
sar 1 100
Will give you a lot of info on what the CPU is doing. You want that
%iowait column to be as low as possible.
Keep us updated.
--
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: <50B8CA01.5040405@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