Re: vacuumdb -z do a reindex?

On Sat, Nov 28, 2009 at 3:12 PM, Irene Barg <ibarg(at)noao(dot)edu> wrote:
> Hi Scott,
>
> Scott Marlowe wrote:
>>
>> 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?
>
> Yes, I did look at pg_stat_activity and did not see anything alarming. What
> would have been indicators of something bad? The runtime was the only
> alarming thing I saw.

In pg_stat_activity anything that's waiting and has been for any length of time.

>>> 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?
>
> Yes. It seems simpler than trying to configure the many options.

Bad idea usually unless you're sure it's a problem.

>> 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?
>
> -bash-3.2$ vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
> wa st
>  0  0      0 21143944 471304 8928016    0    0     0     4    0    1  2  1
> 97  0  0

The first line is since the machine started up, the lines AFTER that
are what's important really.

> -bash-3.2$ iostat -x 10
> Linux 2.6.18-128.1.10.el5 (archdbn1)    11/28/09
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>           1.91    0.00    1.42    0.00    0.00   96.67
>
> Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda               0.01     5.19  0.03  3.27     2.71    67.69    21.37
> 0.02    5.71   0.12   0.04
> sda1              0.00     0.00  0.00  0.00     0.00     0.00    11.66
> 0.00    1.84   1.27   0.00
> sda2              0.01     4.07  0.02  3.21     2.69    58.24    18.84
> 0.02    5.69   0.12   0.04
> sda3              0.00     0.00  0.00  0.00     0.01     0.04    11.15
> 0.00    0.83   0.80   0.00
> sda4              0.00     0.00  0.00  0.00     0.00     0.00     2.00
> 0.00    8.75   8.75   0.00
> sda5              0.00     0.00  0.00  0.00     0.00     0.01    15.44
> 0.00    0.82   0.70   0.00
> sda6              0.00     1.12  0.00  0.05     0.01     9.41   171.06
> 0.00    7.70   0.13   0.00
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>           0.00    0.00    0.01    0.00    0.00   99.99
>
> Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda               0.00     0.60  0.00  1.00     0.00    12.80    12.80
> 0.00    0.00   0.00   0.00
> sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> 0.00    0.00   0.00   0.00
> sda2              0.00     0.60  0.00  1.00     0.00    12.80    12.80
> 0.00    0.00   0.00   0.00
> sda3              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> 0.00    0.00   0.00   0.00
> sda4              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> 0.00    0.00   0.00   0.00
> sda5              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> 0.00    0.00   0.00   0.00
> sda6              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> 0.00    0.00   0.00   0.00

This machine looks idle, was this WHILE the bad queries etc were
running? The rest of the time it'll tell us nothing.