Message-ID: <50B8CA01.5040405@optionshouse.com>
Date: Fri, 30 Nov 2012 09:00:17 -0600
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121028 Thunderbird/16.0.2
MIME-Version: 1.0
To: =?ISO-8859-1?Q?Niels_Kristian_Schj=F8dt?= <nielskristian@autouncle.com>
CC: Kevin Grittner <kgrittn@mail.com>, Willem Leenen
	<willem_leenen@hotmail.com>, <pgsql-performance@postgresql.org>
Subject: Re: Optimize update query
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>
In-Reply-To: <5F52712E-C339-4DF2-9EE5-A26643CEA2C2@autouncle.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Length: 2260
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 11/30/2012 08:48 AM, Niels Kristian Schj=F8dt 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


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