Re: vacuumdb -z do a reindex?

Hi Scott,

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?
>> >

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(at)us-vo(dot)org', 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(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
---------------------------------------------------------------------