Re: vacuumdb -z do a reindex?

On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg <ibarg(at)noao(dot)edu> wrote:
> I've had a simple update running for over 4 hours now (see results from
> pg_top below). The sql is:

Have you looked in pg_locks and pg_stat_activity?

> The database has 1016789 records, vacuumdb -z is ran once a day. I have not
> ran 'reindexdb' in weeks. The system is a:
>
> 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB
> SAS drives configured with software RAID10

So do you have autovacuum disabled? What pg version are you running?

an 8 drive RAID array is usually pretty fast, unless it's on a bad
RAID controller or something. What do "vmstat 10" and "iostat -x 10"
say about your io activity?

>
> Your comments are appreciated.
> --irene
>
>>> last pid:  1185;  load avg:  2.17,  2.21,  1.60;       up 38+01:36:40
>>>                                                              13:52:27
>>> 14 processes: 2 running, 12 sleeping
>>> CPU states: 14.0% user,  0.0% nice, 10.5% system, 75.4% idle,  0.0%
>>> iowait
>>> Memory: 11G used, 20G free, 456M buffers, 8724M cached
>>> Swap:
>>>  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
>>> 28508 postgres  17    0   93M   38M run   265:53 58.42% 99.08% postgres:
>>> postgres metadata 140.252.26.34(34717) UPDATE
>>> 31609 postgres  16    0   91M   36M run     7:05 57.85% 98.09% postgres:
>>> system_admin metadata 140.252.26.34(43303) SELECT
>>> 25156 postgres  16    0  102M   46M sleep   7:28  0.00%  0.00% postgres:
>>> system_admin metadata 140.252.6.51(40350) idle
>>> 25363 postgres  18    0   93M   37M sleep   5:08  0.00%  0.00% postgres:
>>> system_admin metadata 140.252.6.51(35951) idle
>>> 31622 postgres  15    0   95M   38M sleep   1:45  0.00%  0.00% postgres:
>>> system_admin metadata 140.252.6.51(51917) idle
>>> 31624 postgres  15    0   95M   38M sleep   0:14  0.00%  0.00% postgres:
>>> system_admin metadata 140.252.6.51(53908) idle
>>> 28755 postgres  15    0   91M   10M sleep   0:02  0.00%  0.00% postgres:
>>> postgres keyword 140.252.26.33(41270) idle
>>> 28757 postgres  15    0   91M   10M sleep   0:02  0.00%  0.00% postgres:
>>> postgres keyword 140.252.26.33(41272) idle
>>> 28756 postgres  15    0   91M   10M sleep   0:02  0.00%  0.00% postgres:
>>> postgres keyword 140.252.26.33(41271) idle
>>> 28758 postgres  15    0   91M   10M sleep   0:02  0.00%  0.00% postgres:
>>> postgres keyword 140.252.26.33(41273) idle
>>> 28754 postgres  15    0   92M 9724K sleep   0:02  0.00%  0.00% postgres:
>>> postgres keyword 140.252.26.33(41269) idle
>>> 25180 postgres  15    0   91M 7016K sleep   0:00  0.00%  0.00% postgres:
>>> postgres metadata 140.252.6.51(33997) idle
>>> 25179 postgres  15    0   91M 6956K sleep   0:00  0.00%  0.00% postgres:
>>> postgres metadata 140.252.6.51(47331) idle
>>>  1186 postgres  16    0   90M 4808K sleep   0:00  0.00%  0.00% postgres:
>>> arcsoft metadata [local] idle    [arcsoft(at)archdbn1 ~]$ date
>>> Fri Nov 27 13:53:28 MST 2009
>
> --
> ---------------------------------------------------------------------
> Irene Barg                    Email:  ibarg(at)noao(dot)edu
> NOAO/AURA Inc.                 http://www.noao.edu/noao/staff/ibarg
> 950 N. Cherry Ave.            Voice:  520-318-8273
> Tucson, AZ  85726 USA           FAX:  520-318-8360
> ---------------------------------------------------------------------
>
> --
> 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
>

--
When fascism comes to America, it will be intolerance sold as diversity.