473,326 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Repeated VACUUM reports same number of deleted rows

Postgresql 7.4.5

The following VACUUMs were run within a couple of minutes of each other completing.
This table concerned is a table that has not been changed in a long time. (a month)

There were open transactions at the time the vacuum was run. These were created by having PHP running with
postgresql. At least that is what I have managed to find so far. and disabling PHP in apache removes them.

postgres 7588 0.0 0.4 50324 3168 ? S Sep28 0:00 postgres: postgres sqlfilter 10.0.0.5 idle in transaction
postgres 7589 0.0 0.4 50324 3172 ? S Sep28 0:00 postgres: postgres sqlfilter 10.0.0.5 idle in transaction

Should VACUUM report the rows as deleted or say they could not be deleted?
Why does it report the same information for three runs in a row?

Or is something else going on that I don't understand.

Regards

Russell Smith
sqlfilter=# vacuum verbose filter.access_log_big
sqlfilter-# ;
INFO: vacuuming "filter.access_log_big"
INFO: index "access_log_url" now contains 5159204 row versions in 74984 pages
DETAIL: 21455 index pages have been deleted, 20000 are currently reusable.
CPU 5.16s/0.69u sec elapsed 650.85 sec.
INFO: index "access_log_whenwho" now contains 5159204 row versions in 58292 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 5.39s/0.68u sec elapsed 615.43 sec.
INFO: index "access_log_time" now contains 5159204 row versions in 38063 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 3.34s/0.46u sec elapsed 259.59 sec.
INFO: index "accesslogbig_domain" now contains 5159204 row versions in 25501 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 2.17s/0.24u sec elapsed 56.53 sec.
INFO: "access_log_big": found 0 removable, 5159204 nonremovable row versions in 175418 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 19262 unused item pointers.
0 pages are entirely empty.
CPU 21.06s/2.60u sec elapsed 1662.62 sec.
VACUUM
Time: 1662627.077 ms
sqlfilter=# vacuum verbose filter.access_log_big;
INFO: vacuuming "filter.access_log_big"
INFO: index "access_log_url" now contains 5159204 row versions in 74984 pages
DETAIL: 21455 index pages have been deleted, 20000 are currently reusable.
CPU 5.57s/0.64u sec elapsed 655.85 sec.
INFO: index "access_log_whenwho" now contains 5159204 row versions in 58292 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 5.54s/0.52u sec elapsed 589.95 sec.
INFO: index "access_log_time" now contains 5159204 row versions in 38063 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 3.30s/0.38u sec elapsed 262.43 sec.
INFO: index "accesslogbig_domain" now contains 5159204 row versions in 25501 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 2.37s/0.20u sec elapsed 54.98 sec.
INFO: "access_log_big": found 0 removable, 5159204 nonremovable row versions in 175418 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 19262 unused item pointers.
0 pages are entirely empty.
CPU 21.20s/2.36u sec elapsed 1647.25 sec.
VACUUM
Time: 1647292.681 ms
sqlfilter=# commit;
WARNING: there is no transaction in progress
COMMIT
Time: 47.537 ms
sqlfilter=# vacuum verbose filter.access_log_big;
INFO: vacuuming "filter.access_log_big"
INFO: index "access_log_url" now contains 5159204 row versions in 74984 pages
DETAIL: 21455 index pages have been deleted, 20000 are currently reusable.
CPU 4.95s/0.68u sec elapsed 648.90 sec.
INFO: index "access_log_whenwho" now contains 5159204 row versions in 58292 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 5.29s/0.68u sec elapsed 605.39 sec.
INFO: index "access_log_time" now contains 5159204 row versions in 38063 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 3.58s/0.42u sec elapsed 250.08 sec.
INFO: index "accesslogbig_domain" now contains 5159204 row versions in 25501 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 2.13s/0.29u sec elapsed 55.05 sec.
INFO: "access_log_big": found 0 removable, 5159204 nonremovable row versions in 175418 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 19262 unused item pointers.
0 pages are entirely empty.
CPU 20.89s/2.58u sec elapsed 1658.31 sec.
VACUUM
Time: 1658431.580 ms

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

Nov 23 '05 #1
2 2505
Russell Smith <mr*****@pws.com.au> writes:
Should VACUUM report the rows as deleted or say they could not be deleted?
Why does it report the same information for three runs in a row?
I see no pending deletions in that vacuum output:
DETAIL: 0 dead row versions cannot be removed yet.


so I'm not sure why you would expect the output to change.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
On Sat, 2 Oct 2004 12:42 am, Tom Lane wrote:
Russell Smith <mr*****@pws.com.au> writes:
Should VACUUM report the rows as deleted or say they could not be deleted?
Why does it report the same information for three runs in a row?
I see no pending deletions in that vacuum output:
DETAIL: 0 dead row versions cannot be removed yet.


INFO: *vacuuming "filter.access_log_big"
INFO: *index "access_log_url" now contains 5159204 row versions in 74984 pages
DETAIL: *21455 index pages have been deleted, 20000 are currently reusable.

I expect that index info not to be the same each vacuum run. Why are 21455index pages deleted
on runs 1, 2 and 3.
so I'm not sure why you would expect the output to change.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Stephen | last post by:
Hello, Is it normal for plain VACUUM on large table to degrade performance by over 9 times? My database becomes unusable when VACUUM runs. From reading newsgroups, I thought VACUUM should only...
8
by: Sean Shanny | last post by:
To all, The facts: PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI drives in hardware RAID 0 configuration. Database size with indexes is currently 122GB. DB size...
5
by: Gavin Scott | last post by:
Hi, I'm having a performance problem with a large database table we use with postgres 7.3.4. The table is: db=> \d log Table "public.log" Column | Type | Modifiers...
0
by: Jim Seymour | last post by:
Hi, Environment: PostgreSQL 7.4.2 Locally built with GCC 3.3.1 Solaris 8 (Sparc) I have a relatively simple database created with...
15
by: Ed L. | last post by:
If I see VACUUM ANALYZE VERBOSE output like this... INFO: --Relation public.foo-- INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434. CPU 17.05s/4.58u sec elapsed 3227.62 sec. ...
4
by: Ed L. | last post by:
I am trying to better understand diskspace leakage and the relationship to vacuum, max_fsm_pages, and max_fsm_relations. Below are 3 snippets from 3 successive vacuums on a table with ~284K rows...
1
by: Klint Gore | last post by:
query is select t2.field4, t1.* from t1 left outer join t2 on t2.field1 = t1.field1 and t2.field2 = t1.field2 There are 55k rows in t1 (103 fields) and 10k in t2 (4 fields, 4 is text)....
1
by: yateen joshi | last post by:
Hi , I am developing a script that will delete old data from various tables in a database periodically. The script deletes data from all the tables, one by one, in single go. I have two options...
1
by: G. Thomas Yagel, Jr. | last post by:
I have a postgresql database into which I inserted about 40 million records over a 12 hour period. I did not perform any updates or deleletes on the records. After 12 hours I stopped inserting...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.