Hi all,
I have a relation where a tuple typically undergoes a lifecycle
something like:
1) Created
2) Updated maybe thousands of times (no updates to indexed columns though)
3) Rarely or never modified again
The following query takes about 100 minutes (3 seconds per tuple):
SELECT count(id) from T
(2) is causing a lot of auto vacuum/analyze activity (which is OK). HOT
seems to get used for about 90% of the updates, but there are enough
updates that don't use it to cause issues. I'm using pg version 9.0.3
on a 32-bit windows xp machine with 3GB of RAM. The .conf file is using
default settings.
Table Info:
n_live_tup 1799
n_dead_tup 191
pg_relation_size 2343mb
indexsize 10mb
toastsize 552kb
toastindexsize 16kb
This reports under 10kb for most tuples:
psql -A -c "select * from T where id=123" | wc -c
auto-vacuum and auto-analyze both ran yesterday with default settings.
There are only one or two new tuples since yesterday. The database is
fairly old (was probably created using pg_restore about when 9.0.3 came
out).
Here is the output from VACUUM VERBOSE:
INFO: vacuuming "public.T"
INFO: scanned index "idx1" to remove 249 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.00 sec.
INFO: scanned index "idx2" to remove 249 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.12 sec.
INFO: scanned index "idx3" to remove 249 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.90 sec.
INFO: scanned index "idx4" to remove 249 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.32 sec.
INFO: scanned index "idx5" to remove 249 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.42 sec.
INFO: "T": removed 249 row versions in 249 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO: index "idx1" now contains 1976 row versions in 252 pages
DETAIL: 249 index row versions were removed.
210 index pages have been deleted, 210 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: index "idx2" now contains 1976 row versions in 258 pages
DETAIL: 249 index row versions were removed.
209 index pages have been deleted, 209 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO: index "idx3" now contains 1976 row versions in 259 pages
DETAIL: 249 index row versions were removed.
217 index pages have been deleted, 217 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO: index "idx4" now contains 1976 row versions in 250 pages
DETAIL: 249 index row versions were removed.
206 index pages have been deleted, 206 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.07 sec.
INFO: index "idx5" now contains 1976 row versions in 267 pages
DETAIL: 249 index row versions were removed.
217 index pages have been deleted, 217 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO: "T": found 0 removable, 1444 nonremovable row versions in 1522
out of 299964 pages
DETAIL: 30 dead row versions cannot be removed yet.
There were 10035 unused item pointers.
0 pages are entirely empty.
CPU 0.01s/0.00u sec elapsed 17.24 sec.
INFO: vacuuming "pg_toast.pg_toast_17132"
INFO: index "pg_toast_17132_index" now contains 279 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: "pg_toast_17132": found 0 removable, 279 nonremovable row
versions in 69 out of 69 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.56 sec.
VACUUM
I imagine CLUSTERing the table would make things happier, but I'm hoping
for a permanent solution that avoids periodic downtime.
One thought was to partition so rows that are still changing live in a
separate table from the more stable rows. I imagine the cardinality of
(2) rarely exceeds 10. Can I still get into performance trouble with a
table that small after enough updates? Anyone have other ideas?
- Dave
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
