From: Ron <rjpeace@earthlink.net>
To: Guillaume Smet <guillaume.smet@openwide.fr>
To: pgsql-performance@postgresql.org
Subject: Re: weird performances problem
Date: Tue, 22 Nov 2005 12:19:57 -0500
Message-ID: <6.2.5.6.0.20051122120225.040f8658@earthlink.net> (raw)
In-Reply-To: <43833899.5080408@openwide.fr>
References: <437CC21D.8070402@openwide.fr>
<dlugsf$25o5$1@news.hub.org>
<4382EB58.3020804@openwide.fr>
<6.2.5.6.0.20051122074454.040fae60@earthlink.net>
<43832AA3.6080606@openwide.fr>
<6.2.5.6.0.20051122093227.040fd900@earthlink.net>
<43833899.5080408@openwide.fr>
At 10:26 AM 11/22/2005, Guillaume Smet wrote:
>Ron,
>
>First of all, thanks for your time.
Happy to help.
>>As has been noted many times around here, put the WAL on its own
>>dedicated HD's. You don't want any head movement on those HD's.
>
>Yep, I know that. That's just we supposed it was not so important if
>it was nearly a readonly database which is wrong according to you.
It's just good practice with pg that pg-xlog should always get it's
own dedicated HD set. OTOH, I'm not at all convinced given the scant
evidence so far that it is the primary problem here; particularly
since if I understand you correctly, px-xlog is not on sdb or sdb1
where you are having the write storm.
>>_Something_ is doing long bursts of write IO on sdb and sdb1 every
>>30 minutes or so according to your previous posts.
>
>It's not every 30 minutes. It's a 20-30 minutes slow down 3-4 times
>a day when we have a high load.
Thanks for the correction and I apologize for the misunderstanding.
Clearly the first step is to instrument sdb and sdb1 so that you
understand exactly what is being accessed and written on them.
Possibilities that come to mind:
a) Are some of your sorts requiring more than 32MB during high
load? If sorts do not usually require HD temp files and suddenly do,
you could get behavior like yours.
b) Are you doing too many 32MB sorts during high load? Same comment as above.
c) Are you doing some sort of data updates or appends during high
load that do not normally occur?
d) Are you constantly doing "a little" write IO that turns into a
write storm under high load because of queuing issues?
Put the scaffolding in needed to trace _exactly_ what's happening on
sdb and sdb1 throughout the day and then examine the traces over a
day, a few days, and a week. I'll bet you will notice some patterns
that will be helpful in identifying what's going on.
Ron
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: rjpeace@earthlink.net, guillaume.smet@openwide.fr
Subject: Re: weird performances problem
In-Reply-To: <6.2.5.6.0.20051122120225.040f8658@earthlink.net>
References: <437CC21D.8070402@openwide.fr> <dlugsf$25o5$1@news.hub.org> <4382EB58.3020804@openwide.fr> <6.2.5.6.0.20051122074454.040fae60@earthlink.net> <43832AA3.6080606@openwide.fr> <6.2.5.6.0.20051122093227.040fd900@earthlink.net> <43833899.5080408@openwide.fr> <6.2.5.6.0.20051122120225.040f8658@earthlink.net>
* 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