X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id DF6919DCA58 for ; Tue, 20 Dec 2005 08:51:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68653-08 for ; Tue, 20 Dec 2005 08:51:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.194]) by postgresql.org (Postfix) with ESMTP id 774329DCA22 for ; Tue, 20 Dec 2005 08:51:37 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 13so1600209nzn for ; Tue, 20 Dec 2005 04:51:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ZhK/dM1VCRecePkZGSPKehajPO3P87Gu7sarVJoCRUcwxZaMly9vq1dYGFhYjNHehMjfJvJXWVQ6E8V+s0OyJb2TeaesA28Ta1u+nyGsFPyGQRxokcMTNRtvYeYdETPPWBY1sCEadkuA8JwyUAwqCjUyIoZeX2S8SU0gOOCW1kE= Received: by 10.36.222.69 with SMTP id u69mr943884nzg; Tue, 20 Dec 2005 04:51:40 -0800 (PST) Received: by 10.36.72.7 with HTTP; Tue, 20 Dec 2005 04:51:40 -0800 (PST) Message-ID: <674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com> Date: Tue, 20 Dec 2005 18:21:40 +0530 From: Gourish Singbal To: Boguk Maxim Subject: Re: Some strange issues with VACUUM on one table. Cc: "pgsql-admin@postgresql.org" In-Reply-To: <291ABF6CBE162F45B006AFF7AB770B8806F8A7C1@office.rambler.stack.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4565_7990159.1135083100922" References: <291ABF6CBE162F45B006AFF7AB770B8806F8A7C1@office.rambler.stack.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.316 required=5 tests=[AWL=0.315, HTML_MESSAGE=0.001] X-Spam-Score: 0.316 X-Spam-Level: X-Archive-Number: 200512/217 X-Sequence-Number: 19937 ------=_Part_4565_7990159.1135083100922 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The table events_extra needs to be full vacuumed to free the disk space wit= h respect to this table and vacuum the table more frequently . You need to reindex the table once in a week ... (it depends on growth of the index size ) 12/20/05, Boguk Maxim wrote: > I have one small table (like 35000 records) in my database. > Table updated once per day (1%-5% records updated 0.1%-2% new records > inserted). > (parent table updating at same time... no other updates/deletes on tables > events and events_extra ever doing). > For such usage mode i choose 'vacuum analyze' table once per day after > update is enough for keep table clean and fast. > After like 3 month use i found table become hella slow and use like 5Gb o= n > HDD (and still have only ~35000 records). > I looked my cron mails about vacuum and found no errors at all. > > Then i start research situation: > > Here collected data: > (sorry for long post i tried add all info what i collected): > No server crashes/reboots last year. > > postmaster --version > postmaster (PostgreSQL) 8.0.3 > > afisha2=3D# \d events_extra > Table "public.events_extra" > Column | Type | Modifiers > --------+---------+----------- > id | integer | not null > data | text | > Indexes: > "events_extra_id" UNIQUE, btree (id) > Foreign-key constraints: > "$1" FOREIGN KEY (id) REFERENCES events(id) ON DELETE CASCADE > > afisha2=3D# SELECT avg(bit_length(data)) from events_extra; > avg > ----------------------- > 5188.2716594952901500 > (1 row) > > afisha2=3D# select count(*) from events_extra; > count > ------- > 34396 > (1 row) > > Now i doing usual vacuum analyze events_extra: > > afisha2=3D# VACUUM VERBOSE ANALYZE events_extra; > INFO: vacuuming "public.events_extra" > INFO: index "events_extra_id" now contains 35562 row versions in 859 > pages > DETAIL: 121759 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.05s/0.20u sec elapsed 6.33 sec. > INFO: "events_extra": removed 121759 row versions in 12005 pages > DETAIL: CPU 0.63s/0.67u sec elapsed 97.62 sec. > INFO: "events_extra": found 121759 removable, 34575 nonremovable row > versions in 325150 pages > DETAIL: 1100 dead row versions cannot be removed yet. > There were 3475521 unused item pointers. > 0 pages are entirely empty. > CPU 7.81s/2.83u sec elapsed 616.91 sec. > INFO: vacuuming "pg_toast.pg_toast_50664" > INFO: index "pg_toast_50664_index" now contains 1985 row versions in 169= 4 > pages > DETAIL: 35157 index row versions were removed. > 1627 index pages have been deleted, 1496 are currently reusable. > CPU 0.04s/0.03u sec elapsed 10.41 sec. > INFO: "pg_toast_50664": removed 35157 row versions in 6721 pages > DETAIL: CPU 0.33s/0.36u sec elapsed 55.81 sec. > INFO: "pg_toast_50664": found 35157 removable, 1752 nonremovable row > versions in 158420 pages > DETAIL: 252 dead row versions cannot be removed yet. > There were 829348 unused item pointers. > 0 pages are entirely empty. > CPU 4.04s/1.42u sec elapsed 494.67 sec. > INFO: analyzing "public.events_extra" > INFO: "events_extra": scanned 3000 of 325330 pages, containing 349 live > rows and 7 dead rows; 349 rows in sample, 37847 estimated total rows > VACUUM > > Hella strange DB growth like 100x normal size and still vacuum see '0 > pages > are entirely empty.' > And table use huge disk space. > > but 'vacuum full analyze' help: > > > afisha2=3D# VACUUM FULL VERBOSE ANALYZE events_extra; > INFO: vacuuming "public.events_extra" > INFO: "events_extra": found 4430 removable, 34396 nonremovable row > versions > in 325330 pages > DETAIL: 0 dead row versions cannot be removed yet. > Nonremovable row versions range from 56 to 2036 bytes long. > There were 3595534 unused item pointers. > Total free space (including removable row versions) is 2623671076 bytes. > 320774 pages are or will become empty, including 0 at the end of the > table. > 325220 pages containing 2623668204 free bytes are potential move > destinations. > CPU 6.11s/1.75u sec elapsed 243.67 sec. > INFO: index "events_extra_id" now contains 34396 row versions in 859 > pages > DETAIL: 4430 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.07s/0.07u sec elapsed 5.07 sec. > INFO: "events_extra": moved 26037 row versions, truncated 325330 to 2574 > pages > DETAIL: CPU 20.88s/20.47u sec elapsed 1873.01 sec. > INFO: index "events_extra_id" now contains 34396 row versions in 859 > pages > DETAIL: 26037 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.01s/0.08u sec elapsed 0.11 sec. > INFO: vacuuming "pg_toast.pg_toast_50664" > INFO: "pg_toast_50664": found 733 removable, 1733 nonremovable row > versions > in 158468 pages > DETAIL: 0 dead row versions cannot be removed yet. > Nonremovable row versions range from 43 to 2034 bytes long. > There were 864070 unused item pointers. > Total free space (including removable row versions) is 1289157700 bytes. > 158004 pages are or will become empty, including 0 at the end of the > table. > 158463 pages containing 1289157632 free bytes are potential move > destinations. > CPU 3.26s/1.03u sec elapsed 180.28 sec. > INFO: index "pg_toast_50664_index" now contains 1733 row versions in 861 > pages > DETAIL: 733 index row versions were removed. > 796 index pages have been deleted, 796 are currently reusable. > CPU 0.03s/0.01u sec elapsed 3.73 sec. > INFO: "pg_toast_50664": moved 1595 row versions, truncated 158468 to 315 > pages > DETAIL: CPU 8.74s/9.15u sec elapsed 763.01 sec. > INFO: index "pg_toast_50664_index" now contains 1733 row versions in 861 > pages > DETAIL: 1595 index row versions were removed. > 793 index pages have been deleted, 793 are currently reusable. > CPU 0.01s/0.01u sec elapsed 2.55 sec. > INFO: analyzing "public.events_extra" > INFO: "events_extra": scanned 2574 of 2574 pages, containing 34396 live > rows and 0 dead rows; 3000 rows in sample, 34396 estimated total rows > VACUUM > > Ok here question: > What i doing wrong? Or better why vacuum analyze dont work? > Bug? Some unknow for me feature on such tables? FOREIGN KEY issues? > Parent table 'event' staying clear with vacuum anylyze all time. > > PS: sorry for bad english. > > astar@rambler-co.ru ICQ: 99-312-438 > (910) 405-47-18 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Best, Gourish Singbal ------=_Part_4565_7990159.1135083100922 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
 
The table events_extra needs to be full vacuumed to free the disk= space with respect to this table and vacuum the table more frequently .
 
You need to reindex the table once in a week ... (it depends on growth= of the index size )
 
 
 
 12/20/05, Boguk Maxim <astar@ramble= r-co.ru> wrote:
I have one small table (like 350= 00 records) in my database.
Table updated once per day (1%-5% records up= dated=20 0.1%-2% new records
inserted).
(parent table updating at same time...= no other updates/deletes on tables
events and events_extra ever doing).=
For such usage mode i choose 'vacuum analyze' table once per day after
update is enough for keep table clean and fast.
After like 3 month u= se i found table become hella slow and use like 5Gb on
HDD (and still ha= ve only ~35000 records).
I looked my cron mails about vacuum and found n= o errors at all.

Then i start research situation:

Here collected data:
(so= rry for long post i tried add all info what i collected):
No server cras= hes/reboots last year.

postmaster --version
postmaster (PostgreSQ= L)=20 8.0.3

afisha2=3D# \d events_extra
Table "public.events_extra= "
Column |  Type   | Modifiers
--------+----= -----+-----------
id     | integer | not null
dat= a   | text    |
Indexes:
   &= quot;events_extra_id" UNIQUE, btree (id)
Foreign-key constraints:
   "$1" FOREIGN KEY (id= ) REFERENCES events(id) ON DELETE CASCADE

afisha2=3D# SELECT avg(bit= _length(data)) from events_extra;
      &n= bsp;  avg
-----------------------
5188.2716594952901500
(1 row)

afisha2=3D# select count(*) from events_extra;
count<= br>-------
34396
(1 row)

Now i doing usual vacuum analyze even= ts_extra:

afisha2=3D# VACUUM VERBOSE ANALYZE events_extra;
INFO:&= nbsp; vacuuming " public.events_extra"
INFO:  index "events_extra_id&q= uot; now contains 35562 row versions in 859 pages
DETAIL:  121= 759 index row versions were removed.
0 index pages have been deleted, 0 = are currently reusable.
CPU 0.05s/0.20u sec elapsed 6.33 sec.
INFO:  "events_= extra": removed 121759 row versions in 12005 pages
DETAIL: &nb= sp;CPU 0.63s/0.67u sec elapsed 97.62 sec.
INFO:  "events_= extra": found 121759 removable, 34575 nonremovable row
versions in 325150 pages
DETAIL:  1100 dead row versions c= annot be removed yet.
There were 3475521 unused item pointers.
0 page= s are entirely empty.
CPU 7.81s/2.83u sec elapsed 616.91 sec.
INFO:&n= bsp; vacuuming "pg_toast.pg_toast_50664"
INFO:  index "pg_toast_50664_index" now contains 19= 85 row versions in 1694
pages
DETAIL:  35157 index row vers= ions were removed.
1627 index pages have been deleted, 1496 are currentl= y reusable.
CPU=20 0.04s/0.03u sec elapsed 10.41 sec.
INFO:  "pg_toast_50664= ": removed 35157 row versions in 6721 pages
DETAIL:  CPU = 0.33s/0.36u sec elapsed 55.81 sec.
INFO:  "pg_toast_50664= ": found 35157 removable, 1752 nonremovable row
versions in 158420 pages
DETAIL:  252 dead row versions ca= nnot be removed yet.
There were 829348 unused item pointers.
0 pages = are entirely empty.
CPU 4.04s/1.42u sec elapsed 494.67 sec.
INFO:&nbs= p; analyzing " public.events_extra"
INFO:  "events_extra": sca= nned 3000 of 325330 pages, containing 349 live
rows and 7 dead rows; 349= rows in sample, 37847 estimated total rows
VACUUM

Hella strange = DB growth like 100x normal size and still vacuum see '0 pages
are entirely empty.'
And table use huge disk space.

but 'vacu= um full analyze' help:


afisha2=3D# VACUUM FULL VERBOSE ANALYZE e= vents_extra;
INFO:  vacuuming "public.events_extra"<= br>INFO:  "events_extra": found 4430 removable, 34396 n= onremovable row versions
in 325330 pages
DETAIL:  0 dead row versions cannot be rem= oved yet.
Nonremovable row versions range from 56 to 2036 bytes long.There were 3595534 unused item pointers.
Total free space (including re= movable row versions) is 2623671076 bytes.
320774 pages are or will become empty, including 0 at the end of the ta= ble.
325220 pages containing 2623668204 free bytes are potential movedestinations.
CPU 6.11s/1.75u sec elapsed 243.67 sec.
INFO: &nb= sp;index "events_extra_id" now contains 34396 row versions in 859= pages
DETAIL:  4430 index row versions were removed.
0 index pag= es have been deleted, 0 are currently reusable.
CPU 0.07s/0.07u sec elap= sed 5.07 sec.
INFO:  "events_extra": moved 26037 row= versions, truncated 325330 to 2574
pages
DETAIL:  CPU 20.88s/20.47u sec elapsed 1873.01 sec.<= br>INFO:  index "events_extra_id" now contains 34396 ro= w versions in 859 pages
DETAIL:  26037 index row versions were= removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.08u sec elapsed 0.11 sec.
INFO:  vacuuming &qu= ot;pg_toast.pg_toast_50664"
INFO:  "pg_toast_50664&q= uot;: found 733 removable, 1733 nonremovable row versions
in 158468 page= s
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 43 to 2034 bytes long.
There we= re 864070 unused item pointers.
Total free space (including removable ro= w versions) is 1289157700 bytes.
158004 pages are or will become empty, = including 0 at the end of the table.
158463 pages containing 1289157632 free bytes are potential move
des= tinations.
CPU 3.26s/1.03u sec elapsed 180.28 sec.
INFO:  i= ndex "pg_toast_50664_index" now contains 1733 row versions in 861=
pages
DETAIL:  733 index row versions were removed.
796 index pa= ges have been deleted, 796 are currently reusable.
CPU 0.03s/0.01u sec e= lapsed 3.73 sec.
INFO:  "pg_toast_50664": moved 1595= row versions, truncated 158468 to 315
pages
DETAIL:  CPU 8.74s/9.15u sec elapsed 763.01 sec.
= INFO:  index "pg_toast_50664_index" now contains 1733 r= ow versions in 861
pages
DETAIL:  1595 index row versions w= ere removed.
793 index pages have been deleted, 793 are currently reusab= le.
CPU 0.01s/0.01u sec elapsed 2.55 sec.
INFO:  analyzing &qu= ot;public.events_extra"
INFO:  "events_extra": = scanned 2574 of 2574 pages, containing 34396 live
rows and 0 dead rows; = 3000 rows in sample, 34396 estimated total rows
VACUUM

Ok here question:
What i doing wrong? Or better why va= cuum analyze dont work?
Bug? Some unknow for me feature on such tables? = FOREIGN KEY issues?
Parent table 'event' staying clear with vacuum anyly= ze all time.

PS: sorry for bad english.

astar@rambler-co.ru   ICQ: 99-312-438
(910) 405-47-1= 8


---------------------------(end of broadcast)-----------------= ----------
TIP 2: Don't 'kill -9' the postmaster



--
Best,
Gourish Singbal=20 ------=_Part_4565_7990159.1135083100922--