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@hogranch.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general