Re: REINDEX takes half a day (and still not complete!)

On Mon, Apr 18, 2011 at 1:26 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Sun, Apr 17, 2011 at 11:19 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>> Btw, hardware is not an issue. My db has been working fine for a
>> while. Smaller poorer systems around the web run InnoDB databases. I
>> wouldn't touch that with a barge pole.
>
> Did you or someone in an earlier post say that you didn't have
> problems with table bloat?  I can't remember for sure.
>
> Anyway if it's not hardware then it's drivers or your OS.  The output
> of iostat is abysmally bad.  100% utilization but actual throughput is
> pretty low.  Have you used the CLI utility for your RAID card to check
> for possible problems or errors?  Maybe your battery is dead or
> non-functioning?  Don't just rule out hardware until you're sure yours
> is working well.

For instance, here is what I get from iostat on my very CPU bound 8
core opteron machine with a battery backed caching controller:

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda 0.00 9.50 0.30 11.20 2.40 1826.40 159.03
0.01 0.54 0.50 0.58
sdb 42.40 219.80 114.60 41.10 27982.40 2088.80
193.14 0.26 1.67 1.42 22.16

Note that sda is the system / pg_xlog drive, and sdb is the /data/base
dir, minus pg_xlog. I'm reading ~19MB/s and writing ~1MB/s on sdb and
that's using 22% of the IO approximately. My CPUs are all pegged at
100% and I'm getting ~2500 tps.

I'm betting pgbench on your system will get something really low like
200 tps and be maxing out your %util.