pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gourish Singbal <gourish@gmail.com>
To: Boguk Maxim <astar@rambler-co.ru>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Some strange issues with VACUUM on one table.
Date: Tue, 20 Dec 2005 18:21:40 +0530
Message-ID: <674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com> (raw)
In-Reply-To: <291ABF6CBE162F45B006AFF7AB770B8806F8A7C1@office.rambler.stack.net>
References: <291ABF6CBE162F45B006AFF7AB770B8806F8A7C1@office.rambler.stack.net>

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@rambler-co.ru> 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 on
> 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=# \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=# SELECT avg(bit_length(data)) from events_extra;
>          avg
> -----------------------
> 5188.2716594952901500
> (1 row)
>
> afisha2=# select count(*) from events_extra;
> count
> -------
> 34396
> (1 row)
>
> Now i doing usual vacuum analyze events_extra:
>
> afisha2=# 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 1694
> 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=# 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

view thread (5+ messages)  latest in thread

Message-ID: <674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com>
Permalink:  ../674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com/
Also on:    postgresql.org/message-id/674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: gourish@gmail.com, astar@rambler-co.ru
  Subject: Re: Some strange issues with VACUUM on one table.
  In-Reply-To: <674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com>
  References: <291ABF6CBE162F45B006AFF7AB770B8806F8A7C1@office.rambler.stack.net> <674d1f8a0512200451j52940438rb3c4e67afb5ea919@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox