SEO

I have a table that has only a few records in it at the time, and they
get deleted every few seconds and new records are inserted. Table never
has more than 5-10 records in it.

However, I noticed a deteriorating performance in deletes and inserts
on it. So I performed vacuum analyze on it three times (twice in a row,
and once two days later). In the statistics it says that the table size
is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even
though the table has only 5-10 rows in it it. I was wondering how can I
reclaim all this space and improve the performance?

Here are the outputs of my vacuum sessions:

----------------------02/24/06 4:30PM----------------------
INFO: vacuuming "incidents.php_sessions"
INFO: index "php_sessions_pkey" now contains 16 row versions in 17151
pages
DETAIL: 878643 index row versions were removed.
16967 index pages have been deleted, 8597 are currently reusable.
CPU 3.35s/3.67u sec elapsed 25.96 sec.
INFO: "php_sessions": removed 878689 row versions in 107418 pages
DETAIL: CPU 17.53s/11.23u sec elapsed 88.22 sec.
INFO: "php_sessions": found 878689 removable, 14 nonremovable row
versions in 110472 pages
DETAIL: 10 dead row versions cannot be removed yet.
There were 87817 unused item pointers.
0 pages are entirely empty.
CPU 23.87s/16.57u sec elapsed 124.54 sec.
INFO: vacuuming "pg_toast.pg_toast_47206"
INFO: index "pg_toast_47206_index" now contains 550 row versions in
11927 pages
DETAIL: 1415130 index row versions were removed.
11901 index pages have been deleted, 6522 are currently reusable.
CPU 1.45s/2.15u sec elapsed 20.62 sec.
INFO: "pg_toast_47206": removed 1415130 row versions in 353787 pages
DETAIL: CPU 56.92s/32.12u sec elapsed 592.18 sec.
INFO: "pg_toast_47206": found 1415130 removable, 114 nonremovable row
versions in 353815 pages
DETAIL: 114 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 87.42s/43.06u sec elapsed 939.62 sec.
INFO: analyzing "incidents.php_sessions"
INFO: "php_sessions": scanned 3000 of 110479 pages, containing 0 live
rows and 16 dead rows; 0 rows in sample, 0 estimated total rows

Total query runtime: 1064249 ms.


----------------------02/24/06 5:00PM----------------------
INFO: vacuuming "incidents.php_sessions"
INFO: index "php_sessions_pkey" now contains 4 row versions in 17151
pages
DETAIL: 783 index row versions were removed.
17137 index pages have been deleted, 17129 are currently reusable.
CPU 0.31s/0.20u sec elapsed 13.89 sec.
INFO: "php_sessions": removed 784 row versions in 87 pages
DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO: "php_sessions": found 784 removable, 3 nonremovable row versions
in 110479 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 966202 unused item pointers.
0 pages are entirely empty.
CPU 1.21s/0.79u sec elapsed 15.82 sec.
INFO: vacuuming "pg_toast.pg_toast_47206"
INFO: index "pg_toast_47206_index" now contains 310 row versions in
11927 pages
DETAIL: 1830 index row versions were removed.
11922 index pages have been deleted, 11915 are currently reusable.
CPU 0.18s/0.12u sec elapsed 11.39 sec.
INFO: "pg_toast_47206": removed 1830 row versions in 465 pages
DETAIL: CPU 0.12s/0.04u sec elapsed 0.25 sec.
INFO: "pg_toast_47206": found 1830 removable, 30 nonremovable row
versions in 354141 pages
DETAIL: 20 dead row versions cannot be removed yet.
There were 1414680 unused item pointers.
0 pages are entirely empty.
CPU 16.07s/4.46u sec elapsed 200.87 sec.
INFO: "pg_toast_47206": truncated 354141 to 312 pages
DETAIL: CPU 8.32s/2.57u sec elapsed 699.85 sec.
INFO: analyzing "incidents.php_sessions"
INFO: "php_sessions": scanned 3000 of 110479 pages, containing 0 live
rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

Total query runtime: 924084 ms.


----------------------02/26/06 9:30AM----------------------
INFO: vacuuming "incidents.php_sessions"
INFO: index "php_sessions_pkey" now contains 1 row versions in 17151
pages
DETAIL: 46336 index row versions were removed.
17140 index pages have been deleted, 16709 are currently reusable.
CPU 0.20s/0.18u sec elapsed 13.96 sec.
INFO: "php_sessions": removed 46343 row versions in 4492 pages
DETAIL: CPU 0.25s/0.31u sec elapsed 2.42 sec.
INFO: "php_sessions": found 46343 removable, 1 nonremovable row
versions in 110479 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 948998 unused item pointers.
0 pages are entirely empty.
CPU 1.07s/0.90u sec elapsed 17.45 sec.
INFO: vacuuming "pg_toast.pg_toast_47206"
INFO: index "pg_toast_47206_index" now contains 50 row versions in
11927 pages
DETAIL: 125250 index row versions were removed.
11923 index pages have been deleted, 11446 are currently reusable.
CPU 0.25s/0.12u sec elapsed 11.79 sec.
INFO: "pg_toast_47206": removed 125250 row versions in 31316 pages
DETAIL: CPU 2.35s/1.79u sec elapsed 15.68 sec.
INFO: "pg_toast_47206": found 125250 removable, 32 nonremovable row
versions in 31436 pages
DETAIL: 30 dead row versions cannot be removed yet.
There were 456 unused item pointers.
0 pages are entirely empty.
CPU 4.39s/2.20u sec elapsed 37.92 sec.
INFO: analyzing "incidents.php_sessions"
INFO: "php_sessions": scanned 3000 of 110479 pages, containing 0 live
rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

Total query runtime: 55517 ms.

"Nik" <XLPizza@gmail.com> writes:
> I have a table that has only a few records in it at the time, and they
> get deleted every few seconds and new records are inserted. Table never
> has more than 5-10 records in it.
>
> However, I noticed a deteriorating performance in deletes and inserts
> on it. So I performed vacuum analyze on it three times (twice in a row,
> and once two days later). In the statistics it says that the table size
> is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even
> though the table has only 5-10 rows in it it. I was wondering how can I
> reclaim all this space and improve the performance?

You need to run VACUUM ANALYZE on this table very frequently.

Based on what you describe, "very frequently" should be on the order
of at least once per minute.

Schedule a cron job specifically to vacuum this table, with a cron
entry like the following:

* * * * * /usr/local/bin/vacuumdb -z -t my_table -p 5432 my_database

Of course, you need to bring it back down to size, first.

You could run CLUSTER on the table to bring it back down to size;
that's probably the fastest way...

cluster my_table_pk on my_table;

VACUUM FULL would also do the job, but probably not as quickly.
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/sgml.html
"Now they can put you in jail if they *THINK* you're gonna commit a
crime. Let me say that again, because it sounds vaguely important"
--george carlin

On 27/02/06, Chris Browne <cbbrowne@acm.org> wrote:
>
> "Nik" <XLPizza@gmail.com> writes:
> > I have a table that has only a few records in it at the time, and they
> > get deleted every few seconds and new records are inserted. Table never
> > has more than 5-10 records in it.
> >
> > However, I noticed a deteriorating performance in deletes and inserts
> > on it. So I performed vacuum analyze on it three times (twice in a row,
> > and once two days later). In the statistics it says that the table size
> > is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even
> > though the table has only 5-10 rows in it it. I was wondering how can I
> > reclaim all this space and improve the performance?
>
> You need to run VACUUM ANALYZE on this table very frequently.
>
> Based on what you describe, "very frequently" should be on the order
> of at least once per minute.
>
> Schedule a cron job specifically to vacuum this table, with a cron
> entry like the following:
>
> * * * * * /usr/local/bin/vacuumdb -z -t my_table -p 5432 my_database
>
> Of course, you need to bring it back down to size, first.
>
> You could run CLUSTER on the table to bring it back down to size;
> that's probably the fastest way...
>
> cluster my_table_pk on my_table;
>
> VACUUM FULL would also do the job, but probably not as quickly.
> --
> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
> http://cbbrowne.com/info/sgml.html
> "Now they can put you in jail if they *THINK* you're gonna commit a
> crime. Let me say that again, because it sounds vaguely important"
> --george carlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

You probably want to do one or two other things.

1> Switch on autovacuum.

2> improve the setting of max_fsm_pages in your postgresql.conf a restart
will be required.

if you do a "vacuum verbose;" the last couple of lines should tell you how
much free space is about against how much free space the database can
actuall remember to use.

INFO: free space map contains 5464 pages in 303 relations
DETAIL: A total of 9760 page slots are in use (including overhead).
9760 page slots are required to track all free space.
Current limits are: 40000 page slots, 1000 relations, using 299 KB.

if the required page slots (9760 in my case) goes above the current limit
(40000 in my case) you will need to do a vacuum full to reclaim the free
space. (cluster of the relevent tables may work.

If you run Vacuum Verbose regullally you can check you are vacuuming often
enough and that your free space map is big enough to hold your free space.

Peter Childs

On Mon, Feb 27, 2006 at 06:48:02 -0800,
Nik <XLPizza@gmail.com> wrote:
> I have a table that has only a few records in it at the time, and they
> get deleted every few seconds and new records are inserted. Table never
> has more than 5-10 records in it.
>
> However, I noticed a deteriorating performance in deletes and inserts
> on it. So I performed vacuum analyze on it three times (twice in a row,
> and once two days later). In the statistics it says that the table size
> is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even
> though the table has only 5-10 rows in it it. I was wondering how can I
> reclaim all this space and improve the performance?

You can use VACUUM FULL to recover the space. You should be running normal
VACUUMs on that table every minute or two, not once a day.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly