Hi Scott,
On Sat, Nov 28, 2009 at 3:12 PM, Irene Barg <ibarg@xxxxxxxx> wrote:
> 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?
>
By the time I saw your last post, the 'update' had finished, but it took
8 hours. I loaded a dump of the same database on our test system, then
ran the same 'update' statement. Below is all of the stats plus a few
others. The test system is 2xAMD Athlon(tm) 64 X2 Dual Core Processor
5600+, 6GB RAM, 2xSATA drives.
##### pg_locks #####
metadata=# select * from pg_locks;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+------------------+---------
transactionid | | | | | 841483 | | | | 841483 | 12742 | ExclusiveLock | t
relation | 21800 | 21849 | | | | | | | 841478 | 12753 | RowShareLock | t
relation | 21800 | 22086 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22054 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 21847 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22064 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22088 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22090 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22134 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 21873 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 10328 | | | | | | | 841483 | 12742 | AccessShareLock | t
relation | 21800 | 22092 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22094 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22136 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22062 | | | | | | | 841478 | 12753 | AccessShareLock | t
transactionid | | | | | 841478 | | | | 841478 | 12753 | ExclusiveLock | t
relation | 21800 | 21851 | | | | | | | 841478 | 12753 | RowExclusiveLock | t
relation | 21800 | 22066 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 21892 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 21892 | | | | | | | 841478 | 12753 | RowExclusiveLock | t
relation | 21800 | 22050 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 21915 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22056 | | | | | | | 841478 | 12753 | RowExclusiveLock | t
relation | 21800 | 21837 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22048 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22135 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22060 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22058 | | | | | | | 841478 | 12753 | RowExclusiveLock | t
relation | 21800 | 22070 | | | | | | | 841478 | 12753 | RowExclusiveLock | t
relation | 21800 | 21890 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22072 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 21840 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22138 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22137 | | | | | | | 841478 | 12753 | AccessShareLock | t
relation | 21800 | 22068 | | | | | | | 841478 | 12753 | RowExclusiveLock | t
(35 rows)
##### pg_stat_activity #####
metadata=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port
-------+----------+---------+----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+---------+-------------------------------+-------------------------------+---------------+-------------
21800 | metadata | 12742 | 19472 | arcsoft | select * from pg_stat_activity; | f | 2009-11-29 03:55:59.918524-07 | 2009-11-29 03:52:40.954479-07 | | -1
21800 | metadata | 12753 | 19472 | arcsoft | update edu_noao_nsa_security.user_credentials set distinguished_name = 'kelson@xxxxxxxxx', status = 4 where credential_id = 1540815; | f | 2009-11-29 03:54:23.823678-07 | 2009-11-29 03:54:23.818717-07 | 140.252.26.34 | 37863
21800 | metadata | 12610 | 17193 | system_admin | <IDLE> | f | 2009-11-29 03:53:46.120621-07 | 2009-11-29 03:43:44.954155-07 | 140.252.6.54 | 34038
21800 | metadata | 12611 | 17193 | system_admin | <IDLE> | f | 2009-11-29 03:43:46.81295-07 | 2009-11-29 03:43:46.807336-07 | 140.252.6.54 | 38141
(4 rows)
##### vmstat #####
-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
1 0 148 38504 118212 5419140 0 0 1 11 5 4 1 0 99 0 0
1 0 148 38504 118240 5419288 0 0 1 19 1025 188 50 0 50 0 0
1 0 148 38504 118264 5419320 0 0 0 21 1025 186 50 0 50 0 0
1 0 148 38504 118276 5419376 0 0 0 4 1024 189 50 0 50 0 0
1 0 148 38372 118308 5419424 0 0 0 25 1032 199 50 0 50 0 0
1 0 148 38372 118320 5419488 0 0 0 12 1024 184 50 0 50 0 0
1 0 148 38248 118372 5419568 0 0 0 18 1025 189 50 0 50 0 0
1 0 148 37876 118408 5419944 0 0 0 20 1025 188 50 0 50 0 0
1 0 148 37876 118420 5419968 0 0 0 7 1024 184 50 0 50 0 0
1 0 148 37876 118444 5420036 0 0 0 19 1025 189 50 0 50 0 0
1 0 148 36644 118480 5420100 0 0 0 20 1028 206 50 0 50 0 0
1 0 148 36768 118504 5420140 0 0 0 5 1025 188 50 0 50 0 0
##### iostat #####
-bash-3.2$ iostat -x 10
Linux 2.6.18-92.1.13.el5 (somboo.tuc.noao.edu) 11/29/2009
avg-cpu: %user %nice %system %iowait %steal %idle
0.71 0.00 0.26 0.03 0.00 99.00
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.01 4.34 0.05 1.25 3.47 44.72 37.18 0.03 20.12 0.66 0.09
sdb 0.00 0.00 0.00 0.00 0.00 0.00 14.08 0.00 2.77 2.36 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
50.20 0.00 0.05 0.05 0.00 49.70
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 2.50 0.00 2.70 0.00 41.60 15.41 0.00 1.41 0.74 0.20
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
49.95 0.00 0.10 0.00 0.00 49.95
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 1.90 0.00 1.90 0.00 30.40 16.00 0.00 0.74 0.21 0.04
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
###### pgstat ######
-bash-3.2$ pgstat -d metadata -u arcsoft -p Royrkval
time commits rollbks blksrd blkshit bkends seqscan seqtprd idxscn idxtrd ins upd del locks hit load active
29-11-2009.04:03:38 18 1 0 0 4 35 2891 0 0 0 0 0 54 100.0 40 1
29-11-2009.04:03:48 6 0 0 0 4 7 0 0 0 0 0 0 54 100.0 40 1
29-11-2009.04:03:58 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 40 1
29-11-2009.04:04:08 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 40 1
29-11-2009.04:04:18 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 41 1
29-11-2009.04:04:28 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 41 1
29-11-2009.04:04:38 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 41 1
29-11-2009.04:04:48 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 41 1
29-11-2009.04:04:58 3 0 0 0 4 7 2891 0 0 0 0 0 54 100.0 41 1
29-11-2009.04:05:08 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 41 1
29-11-2009.04:05:18 1 0 0 0 4 0 0 0 0 0 0 0 54 100.0 42 1
#### pg_top ####
last pid: 14085; load avg: 1.00, 1.00, 0.82; up 39+15:57:38 04:20:20
3 processes: 2 running, 1 sleeping
CPU states: 50.0% user, 0.0% nice, 0.1% system, 49.9% idle, 0.0% iowait
Memory: 5886M used, 77M free, 130M buffers, 5233M cached
Swap: 148K used, 4095M free
PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
12753 postgres 25 0 1508M 218M run 25:54 17.37% 99.91% postgres: arcsoft metadata 140.252.26.34(37863) UPDATE
12610 postgres 15 0 1507M 6512K sleep 0:00 0.00% 0.00% postgres: system_admin metadata 140.252.6.54(34038) idle
14086 postgres 17 0 1505M 4288K run 0:00 0.00% 0.00% postgres: postgres metadata [local] idle
--
---------------------------------------------------------------------
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
