On Feb 24, 2008, at 1:18 PM, Stephen Denne wrote:
If you always get around a third of the rows in your table written
in the last day, you've got to be deleting about a third of the rows
in your table every day too. You might have a huge number of dead
rows in your table, slowing down the sequential scan.
(Likewise updating a third of the rows, changing an indexed field.)
What do you get from:
VACUUM VERBOSE u_counts;
This actually makes sense as we aggregate the production rows (but not
development), and here is the output of vacuum analyze. We have the
auto vacuum daemon on, but after we do our aggregation (we aggregate
rows down to a less granular time scale, i.e. similar to what rrdtool
does etc.), we should probably do a 'vacuum full analyze' moving
forward after each aggregation run, right?
I need to do one now it appears, but I am assuming it will take a
_long_ time...I might need to schedule some downtime if it will. Even
without a full vacuum, the query seems to have come down from 20-30s
to 5s.
db=> VACUUM VERBOSE u_counts;
INFO: vacuuming "public.u_counts"
INFO: index "u_counts_pkey" now contains 5569556 row versions in
73992 pages
DETAIL: 0 index row versions were removed.
57922 index pages have been deleted, 57922 are currently reusable.
CPU 0.59s/0.09u sec elapsed 3.73 sec.
INFO: index "u_counts_i1" now contains 5569556 row versions in 76820
pages
DETAIL: 0 index row versions were removed.
54860 index pages have been deleted, 54860 are currently reusable.
CPU 1.04s/0.16u sec elapsed 20.10 sec.
INFO: index "u_counts_i2" now contains 5569556 row versions in 77489
pages
DETAIL: 0 index row versions were removed.
53708 index pages have been deleted, 53708 are currently reusable.
CPU 0.70s/0.10u sec elapsed 5.41 sec.
INFO: index "u_counts_i3" now contains 5569556 row versions in 76900
pages
DETAIL: 0 index row versions were removed.
55564 index pages have been deleted, 55564 are currently reusable.
CPU 0.94s/0.13u sec elapsed 20.34 sec.
INFO: "u_counts": found 0 removable, 5569556 nonremovable row
versions in 382344 pages
DETAIL: 2085075 dead row versions cannot be removed yet.
There were 15567992 unused item pointers.
281727 pages contain useful free space.
0 pages are entirely empty.
CPU 5.24s/1.77u sec elapsed 53.69 sec.
WARNING: relation "public.u_counts" contains more than
"max_fsm_pages" pages with useful free space
HINT: Consider using VACUUM FULL on this relation or increasing the
configuration parameter "max_fsm_pages".
VACUUM
Time: 53758.329 ms
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
