Re: Ouch .. VACUUM *is* killer ... - Mailing list pgsql-hackers

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@hub.org>
To: <pgsql-hackers@postgresql.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@hub.org           secondary:
scrappy@{freebsd|postgresql}.org
> >
>
> Marc G. Fournier                   ICQ#7615664               IRC Nick:
Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary:
scrappy@{freebsd|postgresql}.org
>