Search Postgresql Archives

strategies for dealing with frequently updated tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux