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
