Skip site navigation (1) Skip section navigation (2)

Re: vacuumdb -z do a reindex?

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: ibarg(at)noao(dot)edu
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: vacuumdb -z do a reindex?
Date: 2009-11-27 22:58:37
Message-ID: dcc563d10911271458s22a6e8abl444e38376bd1c8f5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

In response to

Responses

pgsql-general by date

Next:From: Stephan SzaboDate: 2009-11-27 23:07:43
Subject: Re: empty string causes planner to avoid index. Makes me sad.
Previous:From: Peter EricksonDate: 2009-11-27 22:55:30
Subject: Re: Cannot allocate memory for output buffer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group