Hello.
Couple of questions:
- Q1: Today I decided to do a vacuum full verbose
analyze on a large table that has been giving me slow performance. And
then I did it again. I noticed that after each run the values in my
indexes and estimate row version changed. What really got me
wondering is the fact my indexes report more rows than are in the table and then
the estimated rows is less than the actual amount.
The table is a read-only table that is updated
1/wk. After updating it is vacuumed full. I've also tried reindexing
but the numbers still change.
Is this normal? Below is a partial output for
4 consecutive vacuum full analyzes. No data was added nor was there anyone
in the table.
- Q2: I have about a dozen 5M plus row
tables. I currently have my max_fsm_pages set to 300,000. As you can
see in vacuum full output I supplied, one table is already over this
amount. Is there a limit on the size of max_fsm_pages?
CONF settings:
# - Memory -
shared_buffers =
2000 # min 16, at
least max_connections*2, 8KB each
sort_mem = 12288 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map -
max_fsm_pages =
300000 # min
max_fsm_relations*16, 6 bytes each
max_fsm_relations = 500 # min 100, ~50 bytes each Vacuum full information
#after second vacuum full
INFO: index "emaildat_fkey" now contains
8053743 row versions in 25764 pages
DETAIL: 1895 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 2.38s/0.42u sec elapsed 11.11 sec. INFO: analyzing "cdm.cdm_email_data" INFO: "cdm_email_data": 65882 pages, 3000 rows sampled, 392410 estimated total rows #after third vacuum full
INFO: index "emaildat_fkey" now contains
8052738 row versions in 25769 pages
DETAIL: 890 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 2.08s/0.32u sec elapsed 4.36 sec. INFO: analyzing "cdm.cdm_email_data" INFO: "cdm_email_data": 65874 pages, 3000 rows sampled, 392363 estimated total rows #after REINDEX and vacuum full
INFO: index "emaildat_fkey" now contains
8052369 row versions in 25771 pages
DETAIL: 521 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 1.37s/0.35u sec elapsed 4.79 sec. INFO: analyzing "cdm.cdm_email_data" INFO: "cdm_email_data": 65869 pages, 3000 rows sampled, 392333 estimated total rows #After vacuum full(s)
mdc_oz=# select count(*) from
cdm.cdm_email_data;
count --------- 5433358 (1 row) TIA
Patrick
|
- Re: [PERFORM] vacuum full & max_fsm_pages question Patrick Hatcher
- Re: [PERFORM] vacuum full & max_fsm_pages questio... Robert Treat
- Re: [PERFORM] vacuum full & max_fsm_pages que... Patrick Hatcher
- Re: [PERFORM] vacuum full & max_fsm_pages... Josh Berkus
- Re: [PERFORM] vacuum full & max_fsm_p... Patrick Hatcher
- Re: [PERFORM] vacuum full & max_fsm_p... Patrick Hatcher