Re: Ouch .. VACUUM *is* killer ...

I had one start on Monday, it just finished an hour ago.

I had added 33 million rows of varchar(255) (paying with the full text
indexing) :-)

Now I have to create indexes and all kinds of neat stuff, I wish I had a
quad Xeon for this!

- Mitch

"The only real failure is quitting."

----- Original Message -----
From: The Hermit Hacker <scrappy(at)hub(dot)org>
To: <pgsql-hackers(at)postgresql(dot)org>
Sent: Wednesday, May 17, 2000 12:47 PM
Subject: Re: [HACKERS] Ouch .. VACUUM *is* killer ...

>
> Wow, it finally came back:
>
> NOTICE: Rel ndict: Pages: 65736 --> 59893; Tuple(s) moved: 993304. CPU
1924.28s/0.00u sec.
>
> Still processing, mind you, but at least I know its not hung up :)
>
>
> On Wed, 17 May 2000, The Hermit Hacker wrote:
>
> >
> > Wow, I thought the last guy that brought this up might be just unlucky
> > (slower hardware then I've been running, etc) ... but, so far, my vacuum
> > has been running for ~2.5hrs and *looks* stuck as far as 'vacuum
verbose'
> > shows, but files keep getting updated ...
> >
> > ... I don't know if this gives you any more information then we've had
in
> > the past, or if this has been pretty much talked out, but figured one
more
> > report, that is still running live, can't hurt ...
> >
> > Tom, its on the machine you have access to ... if there is anything you
> > might want to look at that might help, please feel free ... its the
> > udmsearch database, and from what I can tell, its PID 44718 ...
> >
> > VACUUM VERBOSE has been hung at:
> >
> > NOTICE: --Relation url--
> > NOTICE: Pages 8607: Changed 0, reaped 8391, Empty 0, New 0; Tup 88821:
Vac 65416, Keep/VTL 0/0, Crash 0, UnUsed 98291, MinLen 125, MaxLen 914;
Re-using: Free/Avail. Space 21878184/21835520; EndEmpty/Avail. Pages 0/7059.
CPU 1.92s/0.00u sec.
> > NOTICE: Index url_crc: Pages 1385; Tuples 88821: Deleted 65416. CPU
2.23s/0.00u sec.
> > NOTICE: Index url_url: Pages 4199; Tuples 88821: Deleted 38844. CPU
2.25s/0.00u sec.
> > NOTICE: Index url_pkey: Pages 1001; Tuples 88821: Deleted 38844. CPU
1.41s/0.00u sec.
> > NOTICE: Rel url: Pages: 8607 --> 6091; Tuple(s) moved: 16669. CPU
10.10s/0.00u sec.
> > NOTICE: Index url_crc: Pages 1387; Tuples 88821: Deleted 16669. CPU
0.75s/0.00u sec.
> > NOTICE: Index url_url: Pages 4202; Tuples 88821: Deleted 16669. CPU
1.49s/0.00u sec.
> > NOTICE: Index url_pkey: Pages 1002; Tuples 88821: Deleted 16669. CPU
0.65s/0.00u sec.
> > NOTICE: --Relation ndict--
> > NOTICE: Pages 65736: Changed 5857, reaped 5913, Empty 0, New 0; Tup
10181746: Vac 993304, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 42, MaxLen 42;
Re-using: Free/Avail. Space 43847288/43847288; EndEmpty/Avail. Pages 0/5913.
CPU 19.83s/0.00u sec.
> > NOTICE: Index n_word: Pages 26102; Tuples 10181746: Deleted 993304. CPU
66.69s/0.00u sec.
> > NOTICE: Index n_url: Pages 50739; Tuples 10181746: Deleted 993304. CPU
69.34s/0.00u sec.
> >
> > Uptime on the machine is normal/low:
> >
> > pgsql% uptime
> > 12:27PM up 7 days, 5:55, 3 users, load averages: 3.23, 3.27, 3.33
> >
> > Its a Dual-PIII 450, 512Meg of RAM, the pgsql databases are on their own
> > dedicated drive, seperate from the system drive, and not even swapping:
> >
> > pgsql% pstat -s
> > Device 512-blocks Used Avail Capacity Type
> > /dev/rda0s1b 1048320 0 1048320 0% Interleaved
> > /dev/rda1s1b 1048320 0 1048320 0% Interleaved
> > Total 2096640 0 2096640 0%
> >
> > lsof shows (if it matters):
> >
> > pgsql% lsof -p 44718
> > COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
> > postgres 44718 pgsql cwd VDIR 13,131084 2048 1253933
/pgsql/data/base/udmsearch
> > postgres 44718 pgsql rtd VDIR 13,131072 512 2 /
> > postgres 44718 pgsql txt VREG 13,131084 4669647 103175
/pgsql/bin/postgres
> > postgres 44718 pgsql txt VREG 13,131076 76648 212707
/usr/libexec/ld-elf.so.1
> > postgres 44718 pgsql txt VREG 13,131076 11128 56672
/usr/lib/libdescrypt.so.2
> > postgres 44718 pgsql txt VREG 13,131076 118044 56673
/usr/lib/libm.so.2
> > postgres 44718 pgsql txt VREG 13,131076 33476 56680
/usr/lib/libutil.so.3
> > postgres 44718 pgsql txt VREG 13,131076 148316 56532
/usr/lib/libreadline.so.4
> > postgres 44718 pgsql txt VREG 13,131076 251416 56674
/usr/lib/libncurses.so.5
> > postgres 44718 pgsql txt VREG 13,131076 550996 56746
/usr/lib/libc.so.4
> > postgres 44718 pgsql 0r VCHR 2,2 0t0 7967 /dev/null
> > postgres 44718 pgsql 1w VREG 13,131084 233611833 761881
/pgsql/logs/postmaster.5432.93622
> > postgres 44718 pgsql 2w VREG 13,131084 19771345 761882
/pgsql/logs/5432.93622
> > postgres 44718 pgsql 3r VREG 13,131084 1752 1253891
/pgsql/data/base/udmsearch/pg_internal.init
> > postgres 44718 pgsql 4u VREG 13,131084 3547136 15922
/pgsql/data/pg_log
> > postgres 44718 pgsql 5u IPv4 0xd46352e0 0t0 TCP
pgsql.tht.net:5432->hub.org:2135 (ESTABLISHED)
> > postgres 44718 pgsql 6u VREG 13,131084 8192 15874
/pgsql/data/pg_variable
> > postgres 44718 pgsql 7u VREG 13,131084 16384 1253978
/pgsql/data/base/udmsearch/pg_class
> > postgres 44718 pgsql 8u VREG 13,131084 16384 1253977
/pgsql/data/base/udmsearch/pg_class_oid_index
> > postgres 44718 pgsql 9u VREG 13,131084 57344 1253981
/pgsql/data/base/udmsearch/pg_attribute
> > postgres 44718 pgsql 10u VREG 13,131084 32768 1253979
/pgsql/data/base/udmsearch/pg_attribute_relid_attnum_index
> > postgres 44718 pgsql 11u VREG 13,131084 8192 1253989
/pgsql/data/base/udmsearch/pg_am
> > postgres 44718 pgsql 12u VREG 13,131084 8192 1253949
/pgsql/data/base/udmsearch/pg_rewrite
> > postgres 44718 pgsql 13u VREG 13,131084 8192 1253973
/pgsql/data/base/udmsearch/pg_index
> > postgres 44718 pgsql 14u VREG 13,131084 8192 1253984
/pgsql/data/base/udmsearch/pg_amproc
> > postgres 44718 pgsql 15u VREG 13,131084 16384 1253987
/pgsql/data/base/udmsearch/pg_amop
> > postgres 44718 pgsql 16u VREG 13,131084 73728 1253957
/pgsql/data/base/udmsearch/pg_operator
> > postgres 44718 pgsql 17u VREG 13,131084 16384 1253972
/pgsql/data/base/udmsearch/pg_index_indexrelid_index
> > postgres 44718 pgsql 18u VREG 13,131084 32768 1253956
/pgsql/data/base/udmsearch/pg_operator_oid_index
> > postgres 44718 pgsql 19u VREG 13,131084 16384 1253972
/pgsql/data/base/udmsearch/pg_index_indexrelid_index
> > postgres 44718 pgsql 20u VREG 13,131084 16384 1253938
/pgsql/data/base/udmsearch/pg_type
> > postgres 44718 pgsql 21u VREG 13,131084 16384 1253937
/pgsql/data/base/udmsearch/pg_type_oid_index
> > postgres 44718 pgsql 22u VREG 13,131084 8192 1253983
/pgsql/data/base/udmsearch/pg_attrdef
> > postgres 44718 pgsql 23u VREG 13,131084 16384 1253976
/pgsql/data/base/udmsearch/pg_class_relname_index
> > postgres 44718 pgsql 24u VREG 13,131084 8192 1253942
/pgsql/data/base/udmsearch/pg_trigger
> > postgres 44718 pgsql 25u VREG 13,131084 16384 1253939
/pgsql/data/base/udmsearch/pg_trigger_tgrelid_index
> > postgres 44718 pgsql 26u VREG 13,131084 8192 15921
/pgsql/data/pg_shadow
> > postgres 44718 pgsql 27u VREG 13,131084 16384 1253982
/pgsql/data/base/udmsearch/pg_attrdef_adrelid_index
> > postgres 44718 pgsql 28u VREG 13,131084 538509312 1254004
/pgsql/data/base/udmsearch/ndict
> > postgres 44718 pgsql 29u VREG 13,131084 213909504 1254006
/pgsql/data/base/udmsearch/n_word
> > postgres 44718 pgsql 30u VREG 13,131084 416432128 1254005
/pgsql/data/base/udmsearch/n_url
> >
> >
> >
> > Marc G. Fournier ICQ#7615664 IRC Nick:
Scrappy
> > Systems Administrator @ hub.org
> > primary: scrappy(at)hub(dot)org secondary:
scrappy(at){freebsd|postgresql}.org
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick:
Scrappy
> Systems Administrator @ hub.org
> primary: scrappy(at)hub(dot)org secondary:
scrappy(at){freebsd|postgresql}.org
>