Search Postgresql Archives

Re: vacuumdb -z do a reindex?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Scott,

Scott Marlowe wrote:
On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg <ibarg@xxxxxxxx> 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@archdbn1 ~]$ date
Fri Nov 27 13:53:28 MST 2009
--
---------------------------------------------------------------------
Irene Barg                    Email:  ibarg@xxxxxxxx
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





--
---------------------------------------------------------------------
Irene Barg                    Email:  ibarg@xxxxxxxx
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux