Re: [PERFORM] vacuum full max_fsm_pages question

2004-09-23 Thread Patrick Hatcher 

I upgraded to 7.4.3 this morning and
did a vacuum full analyze on the problem table and now the indexes show
the correct number of records


Patrick Hatcher
Macys.Com






Josh Berkus [EMAIL PROTECTED]

Sent by: [EMAIL PROTECTED]
09/21/04 10:49 AM




To
Patrick Hatcher
[EMAIL PROTECTED]


cc
Robert Treat
[EMAIL PROTECTED], [EMAIL PROTECTED]


Subject
Re: [PERFORM] vacuum full
 max_fsm_pages question








Patrick,

 Sorry. I wrote PG 7.4.2 and then I erased it to write something
else and
 then forgot to add it back.

Odd. You shouldn't be having to re-vacuum on 7.4.

 And thanks for the Page info. I was getting frustrated and looked
in the
 wrong place.

 So it's probably best to drop and readd the indexes then?

Well, I have to wonder if you've not run afoul of the known 7.4.2 bug 
regarding indexes.  This system hasn't had an improper database shutdown
or 
power-out in the last few weeks, has it?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(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

Re: [PERFORM] vacuum full max_fsm_pages question

2004-09-21 Thread Robert Treat 
On Tuesday 21 September 2004 00:01, Patrick Hatcher wrote:
 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.


This looks normal to me for a pre 7.4 database, if I am right your running on 
7.2? Basically your indexes are overgrown, so each time you run vacuum you 
are shrinking the number of pages involved, which will change the row counts, 
and correspondingly change the count on the table as the sampled pages 
change. 


 - 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?


The limit is based on your memory... each page = 6 bytes.  But according to 
the output below you are not over 30 pages yet on that table (though you 
might be on some other tables.)


 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 = 30  # 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)


I do think the count(*) seems a bit off based on the vacuum output above. I'm 
guessing you either have blocking transactions in the way or your not giving 
us a complete copy/paste of the session involved. 

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] vacuum full max_fsm_pages question

2004-09-21 Thread Patrick Hatcher 
Sorry.  I wrote PG 7.4.2 and then I erased it to write something else and
then forgot to add it back.

And thanks for the Page info.  I was getting frustrated and looked in the
wrong place.

So it's probably best to drop and readd the indexes then?


- Original Message - 
From: Robert Treat [EMAIL PROTECTED]
To: Patrick Hatcher [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, September 20, 2004 11:12 PM
Subject: Re: [PERFORM] vacuum full  max_fsm_pages question


 On Tuesday 21 September 2004 00:01, Patrick Hatcher wrote:
  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.
 

 This looks normal to me for a pre 7.4 database, if I am right your running
on
 7.2? Basically your indexes are overgrown, so each time you run vacuum you
 are shrinking the number of pages involved, which will change the row
counts,
 and correspondingly change the count on the table as the sampled pages
 change.


  - 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?
 

 The limit is based on your memory... each page = 6 bytes.  But according
to
 the output below you are not over 30 pages yet on that table (though
you
 might be on some other tables.)

 
  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 = 30  # 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)
 

 I do think the count(*) seems a bit off based on the vacuum output above.
I'm
 guessing you either have blocking transactions in the way or your not
giving
 us a complete copy/paste of the session involved.

 -- 
 Robert Treat
 Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] vacuum full max_fsm_pages question

2004-09-21 Thread Josh Berkus 
Patrick,

 Sorry.  I wrote PG 7.4.2 and then I erased it to write something else and
 then forgot to add it back.

Odd.  You shouldn't be having to re-vacuum on 7.4.

 And thanks for the Page info.  I was getting frustrated and looked in the
 wrong place.

 So it's probably best to drop and readd the indexes then?

Well, I have to wonder if you've not run afoul of the known 7.4.2 bug 
regarding indexes.   This system hasn't had an improper database shutdown or 
power-out in the last few weeks, has it?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(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

[PERFORM] vacuum full max_fsm_pages question

2004-09-20 Thread Patrick Hatcher 




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 itagain. I noticed that after each run the values in my 
indexes and estimate row versionchanged. 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 eachsort_mem = 
12288 
# min 64, size in KB#vacuum_mem = 
8192 
# min 1024, size in KB

# - Free Space Map -

max_fsm_pages = 
30 # min 
max_fsm_relations*16, 6 bytes eachmax_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 pagesDETAIL: 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 pagesDETAIL: 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 pagesDETAIL: 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