From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | ibarg(at)noao(dot)edu |
Cc: | pgsql-general(at)postgresql(dot)org, guillaume(at)lelarge(dot)info |
Subject: | Re: vacuumdb -z do a reindex? |
Date: | 2009-11-28 23:49:47 |
Message-ID: | dcc563d10911281549w67e03eb8n6793fe39c830e87d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2009-11-28 23:55:43 | Re: Date with time zone |
Previous Message | Eduardo Piombino | 2009-11-28 23:41:42 | Re: Date with time zone |