Hello,
What would be reasonable settings for quite heavily used
but not large database?
Dabatase is under 1G in size and fits into server cache (server
has 2GB of memeory). Two of most used tables are ~100k rows each
but they get up to 50inserts/updates/deletes per second.
How to tweak fsm (?) and pg_auovacuum settings for such case?
What I do not like about one table is "unused item pointers" number.
Now I use max_fsm_relations=1000 and max_fsm_pages=200000.
pg_autovacuum ran with default settings.
Thanks,
Mindaugas
# VACUUM VERBOSE msq;
INFO: vacuuming "msq"
INFO: index "msq_next" now contains 74983 row versions in 537 pages
DETAIL: 75963 index row versions were removed.
123 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.13u sec elapsed 2.00 sec.
INFO: index "msq_recipient_idx" now contains 75014 row versions in 740
pages
DETAIL: 75963 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.16u sec elapsed 0.17 sec.
INFO: index "msq_id_pk" now contains 75065 row versions in 396 pages
DETAIL: 75963 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.15u sec elapsed 0.17 sec.
INFO: "msq": removed 75963 row versions in 6118 pages
DETAIL: CPU 0.62s/0.56u sec elapsed 17.02 sec.
INFO: "msq": found 75963 removable, 74553 nonremovable row versions in
49386 pages
DETAIL: 1221 dead row versions cannot be removed yet.
There were 1634616 unused item pointers.
0 pages are entirely empty.
CPU 1.36s/1.24u sec elapsed 33.23 sec.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
