DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=jKrznYTI1MRTdiqlyJC0C4dCajD/b4rzzwuJ9DLvKJ3MntcgvY9xq10S/xoj1AcU0Y
	EOwRbELV4y9Fwr1m4ecqR3LP4cvvjWccfnlpL/o21/2UAdS7ZWM1RwXT5GDW+OTueuB5
	nSFKOHk3TLLvcwOJMJssBTCI1b4bYgw0E7VRg=
MIME-Version: 1.0
In-Reply-To: <BANLkTi=Yr7QBdU-_Gc_9S+gUi0VO6s9u+A@mail.gmail.com>
References: <AANLkTim1aO2hG+D4h_VFXas8GTL6TQMLj7aDc7tJ0vok@mail.gmail.com>
	<AANLkTinjsS7Aj0ZoMVq3ZDhKepBsZ_HOpBqEjRrabk_J@mail.gmail.com>
	<BANLkTinq7KSstviqjGfnaKh7YOfan7sjig@mail.gmail.com>
	<BANLkTin9bB+LAScMMBnuJn9uROXOkPsQLw@mail.gmail.com>
	<BANLkTi=j-+Cauczk-q+pJuASp_PPOjXgEQ@mail.gmail.com>
	<BANLkTi=Yr7QBdU-_Gc_9S+gUi0VO6s9u+A@mail.gmail.com>
Date: Mon, 18 Apr 2011 13:14:34 +0800
Message-ID: <BANLkTim1EoDEmdrOSfMqVkVe9Pa4KP_TYQ@mail.gmail.com>
Subject: Re: REINDEX takes half a day (and still not complete!)
From: Phoenix Kiula <phoenix.kiula@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks for these suggestions.

I am beginning to wonder if the issue is deeper.

I set autovacuum to off, then turned off all the connections to the
database, and did a manual vacuum just to see how long it takes.

This was last night my time. I woke up this morning and it has still
not finished.

The maintenance_men given to the DB for this process was 2GB.

There is nothing else going on on the server! Now, even REINDEX is
just failing in the middle:


# REINDEX INDEX new_idx_userid;
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.


What else could be wrong?


On Mon, Apr 18, 2011 at 2:38 AM, Scott Marlowe <scott.marlowe@gmail.com> wr=
ote:
> On Sun, Apr 17, 2011 at 10:59 AM, Phoenix <phoenix.kiula@gmail.com> wrote=
:
>> TOP does not show much beyond "postmaster". How should I use TOP and
>> what info can I give you? This is what it looks like:
>
> We're basically looking to see if the postmaster process doing the
> vacuuming or reindexing is stuck in a D state, which means it's
> waiting on IO.
> hot the c key while it's running and you should get a little more info
> on which processes are what.
>
>> =A04799 postgres =A015 =A0 0 =A0532m =A094m =A093m D =A00.7 =A01.2 =A0 0=
:00.14
>> postmaster
>
> That is likely the postmaster that is waiting on IO.
>
>> VMSTAT 10 shows this:
>>
>> =A0r =A0b =A0 swpd =A0 free =A0 buff =A0cache =A0 si =A0 so =A0 =A0bi =
=A0 =A0bo =A0 in =A0 =A0cs us sy id wa
>> =A03 14 =A099552 =A017900 =A041108 7201712 =A0 =A00 =A0 =A00 =A0 =A042 =
=A0 =A011 =A0 =A00 =A0 =A0 0 =A08 34 41 16
>> =A02 17 =A099552 =A016468 =A041628 7203012 =A0 =A00 =A0 =A00 =A01326 =A0=
 =A084 1437 154810 =A07 66 12 15
>> =A03 =A07 =A099476 =A016796 =A041056 7198976 =A0 =A00 =A0 =A00 =A01398 =
=A0 =A096 1453 156211 =A07 66 21 =A06
>> =A03 17 =A099476 =A017228 =A039132 7177240 =A0 =A00 =A0 =A00 =A01325 =A0=
 =A068 1529 156111 =A08 65 16 11
>
> So, we're at 11 to 15% io wait. =A0I'm gonna guess you have 8 cores /
> threads in your CPUs, and 1/8th ot 100% is 12% so looks like you're
> probably IO bound here. =A0iostat tells us more:
>
>> The results of "iostat -xd 10" is:
>> Device: =A0 =A0rrqm/s wrqm/s =A0 r/s =A0 w/s =A0rsec/s =A0wsec/s =A0 =A0=
rkB/s =A0 =A0wkB/s
>> avgrq-sz avgqu-sz =A0 await =A0svctm =A0%util
>> sda =A0 =A0 =A0 =A0 =A00.00 =A0 7.41 =A00.30 =A03.50 =A0 =A02.40 =A0 87.=
29 =A0 =A0 1.20 =A0 =A043.64
>> =A0 23.58 =A0 =A0 0.13 =A0 32.92 =A010.03 =A0 3.81
>> sdb =A0 =A0 =A0 =A0 =A00.00 =A0 0.00 =A00.00 =A00.00 =A0 =A00.00 =A0 =A0=
0.00 =A0 =A0 0.00 =A0 =A0 0.00
>> =A0 =A00.00 =A0 =A0 0.00 =A0 =A00.00 =A0 0.00 =A0 0.00
>> sdc =A0 =A0 =A0 =A0 =A00.00 =A018.32 158.26 =A04.10 2519.32 =A0180.98 =
=A01259.66
>> 90.49 =A0 =A016.63 =A0 =A013.04 =A0 79.91 =A0 6.17 100.11
>
> 100% IO utilization, so yea, it's likely that your sdc drive is your
> bottleneck. =A0Given our little data is actually moving through the sdc
> drive, it's not very fast.
>
>> Device: =A0 =A0rrqm/s wrqm/s =A0 r/s =A0 w/s =A0rsec/s =A0wsec/s =A0 =A0=
rkB/s =A0 =A0wkB/s
>
>> 8GB memory in total. 1GB devoted to PGSQL during these operations.
>> Otherwise, my settings are as follows (and yes I did make the vacuum
>> settings more aggressive based on your email, which has had no
>> apparent impact) --
>
> Yeah, as it gets more aggressive it can use more of your IO bandwidth.
> =A0Since you
>
>> What else can I share?
>
> That's a lot of help. =A0I'm assuming you're running software or
> motherboard fake-raid on this RAID-1 set? =A0I'd suggest buying a $500
> or so battery backed caching RAID controller first, =A0the improvements
> in performance are huge with such a card. =A0You might wanna try testing
> the current RAID-1 set with bonnie++ to get an idea of how fast it is.
>