Re: vacuumdb -z do a reindex?

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.

>
>> 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.

>
> 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

-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

I did a reindexdb today, and it took less than 2 minutes. So I don't
think it had anything to do with a bloated db or index.

I need some utilities and training to be able to convince myself when a
problem is with the 'system' (PostgreSQL+hw+config) vs design of the db.

Thanks to all who responded.
Cheers,
--irene

>
>
>> 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
>>
>
>
>

--
---------------------------------------------------------------------
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
---------------------------------------------------------------------