Re: Reindex taking forever, and 99% CPU

Thanks John.

So what're the right settings? Anyway, right now Postgresql is
servicing only one main connection, which is the REINDEX. All other
stuff is switched off, no one else is connecting to the DB.

My issue with this table was the vaccum process would stop at this
table, and take hours. So I thought something was wrong with this
table. My version of PG was 9.0.11, and googling for similar issues
brought up an old post by Tom Lane that suggested to the poster of
that thread to upgrade. So now I have, and am at 9.0.17 -- I recognize
this is not 9.3.5, but not sure we have the appetite right now for a
massive upgrade.

So what I'm trying to do is reindex this specific table.

> iostat

Linux 2.6.18-238.9.1.el5 (coco.MYDOMAIN.com) 08/02/2014

avg-cpu: %user %nice %system %iowait %steal %idle
10.63 0.10 3.11 13.42 0.00 72.74

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 284.10 43828.59 5729.27 84628259628 11062603842
sda1 0.00 0.00 0.00 2272 10
sda2 3.08 44.97 989.21 86838949 1910058506
sda3 7.65 193.04 84.34 372745356 162860428
sda4 0.00 0.00 0.00 6 0
sda5 1.58 31.15 6.84 60140845 13208874
sda6 0.82 20.28 0.88 39161138 1693104
sda7 1.10 15.59 24.32 30101692 46962204
sda8 2.77 44.88 20.07 86661146 38754800
sda9 267.11 43478.67 4603.61 83952607992 8889065916

On Sun, Aug 3, 2014 at 9:56 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 8/2/2014 6:20 PM, Phoenix Kiula wrote:
>>
>> PS: CentOS 6 64 bit, 4 GB of RAM, Raid 1 Raptor disks. Postgresql.conf
>> and TOP output during the running of the REINDEX are below..
>>
>>
>> ----POSTGRESQL.CONF-----
>>
>> max_connections = 180
>> superuser_reserved_connections = 5
>> shared_buffers = 512MB
>> effective_cache_size = 1200MB
>> temp_buffers = 32MB
>> maintenance_work_mem = 320MB
>> work_mem = 128MB
>
>
> with 4GB of ram, and 180 connections, if you actually had all 180
> connections busy at once, you could use over 180 times work_mem, 180*128MB
> in 4GB would be fatal.
>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general