more about pg_toast growth 
Author Message
 more about pg_toast growth

Quote:



> > > > I have increased the free space map and will be able to restart the
> > > > postmaster today at around midnight GMT.

> > >     Any news?

> > I couldn't work it in to the last maintenance window.  I'll give it
> > another run tonight.

> I increased the FSM and restarted postgres several weeks ago, and the
> toast tables continue to just grow and grow.  I set it to:

> max_fsm_relations = 1000    # min 10, fsm is free space map
> max_fsm_pages = 100000      # min 1000, fsm is free space map

> Now, my table with only 107490 rows takes 20GB on disk.  I dumped and
> reloaded the database and got 18.5GB free space back.

    Dump  and  reload  is  a  bit  drastic.  A  "VACUUM FULL" and
    "REINDEX" should've done that as well.

Quote:
> I really think this is a bug in the implementation.

    Come on, Jeffrey, do a little math - it's not *that* hard  to
    understand.  A  free  space  map  of 100,000 entries can hold
    information about 100,000  pages  where  new  tuples  can  be
    stored.  that's  100,000  maybe partially filled 8K pages, so
    we're talking about 800MB here.

    As soon as your table once has more  than  100,000  partially
    filled  or entirely free pages due to deleted rows, you start
    loosing pages. Now, how much data where you  pumping  through
    that table by the hour?

    I  really  think this time the bug is in front of the screen,
    not behind it :-) Give it a chance and increase max_fsm_pages
    to 10 million.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 25 Sep 2004 21:14:30 GMT
 more about pg_toast growth

Quote:

> > I doubled that, and it still doesn't work.  You are suggesting I
> > increase your previous estimate by a factor of 200.  Your email of
> > 2002-03-13 at 15:16 -0500 suggests a FSM of 50,000 pages allocates "some
> > more shared memory.  It's  surely  in  the range of a few megabytes..."
> > Will a FSM map 200 times larger require 200 times more memory, or is the
> > growth nonlinear?  How can I calculate this requirement?  Without some
> > documentation this database is inoperable.

> > I stand behind my previous statement: if PostgreSQL's unchecked table
> > growth can only be prevented by changing an undocumented configuration
> > key using an undocumented formula producing undocumented system impact,
> > the implementation is flawed.

> This does bring up a point that VACUUM alone does not handle all cases
> of reusing tuple space.  VACUUM FULL is needed occasionally.

    I still believe it's due to the massive amount of data pumped
    through that table between vacuums and inappropriate settings
    for the freespace map size for this particular case.

    Initially  I suggested an FSM size of 50,000 "to start with".
    That was meant as an introduction to play around  with  these
    parameters a little, figuring out what the right settings are
    in his case, and reporting back the result. What we got  back
    after  a  week  or longer, was a lax "still doesn't work". It
    seemed to me he had not spent alot of time to understand  the
    underlying  concepts,  nor has he ever taken a single look at
    the code. Pumping multiple  gigabytes  every  day  through  a
    database is not the occational DB usage, where you can expect
    default settings to be appropriate. This is  clearly  a  case
    where  someone has to "learn" the finer details about tuning.

    This is an open source project.  Getting that pi**ed about my
    response, and asking that snobby for URL's to the appropriate
    documentation, finally telling "this database is inoperable",
    well,  maybe  he's  better  off  with  a support contract for
    Oracle or SQL-Server.  At  least  he'll  not  get  any  picky
    comments from those people.

    I  will  look  into  it another day, but without someone else
    running into the same problem, I  don't  feel  much  pressure
    doing so right now.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sun, 26 Sep 2004 03:02:30 GMT
 more about pg_toast growth

Quote:


> > > I doubled that, and it still doesn't work.  You are suggesting I
> > > increase your previous estimate by a factor of 200.  Your email of
> > > 2002-03-13 at 15:16 -0500 suggests a FSM of 50,000 pages allocates "some
> > > more shared memory.  It's  surely  in  the range of a few megabytes..."
> > > Will a FSM map 200 times larger require 200 times more memory, or is the
> > > growth nonlinear?  How can I calculate this requirement?  Without some
> > > documentation this database is inoperable.

> > > I stand behind my previous statement: if PostgreSQL's unchecked table
> > > growth can only be prevented by changing an undocumented configuration
> > > key using an undocumented formula producing undocumented system impact,
> > > the implementation is flawed.

> > This does bring up a point that VACUUM alone does not handle all cases
> > of reusing tuple space.  VACUUM FULL is needed occasionally.

>     I still believe it's due to the massive amount of data pumped
>     through that table between vacuums and inappropriate settings
>     for the freespace map size for this particular case.

>     Initially  I suggested an FSM size of 50,000 "to start with".
>     That was meant as an introduction to play around  with  these
>     parameters a little, figuring out what the right settings are
>     in his case, and reporting back the result. What we got  back
>     after  a  week  or longer, was a lax "still doesn't work". It
>     seemed to me he had not spent alot of time to understand  the
>     underlying  concepts,  nor has he ever taken a single look at
>     the code.

I don't need this abuse.  I'm perfectly capable of reading the source
code for PostgreSQL.  I helped diagnose a spinlock contention problem in
the 7.2beta series and I maintain DBD::Pg.  And I've contributed source
code to several other projects which I need not list here.

Surely I'd be willing to learn how to tune Pg, since I've staked my
operation on it.  But, "max_fsm_pages" produces fewer pages on Google
than "deep fried orangutan pancreas", and I do not often have the time
to leaf through 460,000 lines of source.  Some documentation would be
swell.

-jwb

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Sun, 26 Sep 2004 03:39:41 GMT
 more about pg_toast growth

Quote:


> >     I still believe it's due to the massive amount of data pumped
> >     through that table between vacuums and inappropriate settings
> >     for the freespace map size for this particular case.

> >     Initially  I suggested an FSM size of 50,000 "to start with".
> >     That was meant as an introduction to play around  with  these
> >     parameters a little, figuring out what the right settings are
> >     in his case, and reporting back the result. What we got  back
> >     after  a  week  or longer, was a lax "still doesn't work". It
> >     seemed to me he had not spent alot of time to understand  the
> >     underlying  concepts,  nor has he ever taken a single look at
> >     the code.

> I don't need this abuse.  I'm perfectly capable of reading the source
> code for PostgreSQL.  I helped diagnose a spinlock contention problem in
> the 7.2beta series and I maintain DBD::Pg.  And I've contributed source
> code to several other projects which I need not list here.

    Abuse of what?

    I  did  not  mean  to  offend  you in any way. Maybe I wasn't
    carefull enough in choosing my words, maybe  you  missed  the
    smiley  in  my  earlier  posting  (that  about doing a little
    math), maybe whatever.

    Anyway,  seems  we  don't  get  along  well  enough  to  work
    together.   Thus,  it's  better  you  help  someone  else  to
    diagnose your problem.  Good luck.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Sun, 26 Sep 2004 04:59:42 GMT
 more about pg_toast growth

Quote:

> Surely I'd be willing to learn how to tune Pg, since I've staked my
> operation on it.  But, "max_fsm_pages" produces fewer pages on Google
> than "deep fried orangutan pancreas", and I do not often have the time
> to leaf through 460,000 lines of source.  Some documentation would be
> swell.

In case anything thinks he's kidding, he's not. max_fsm_pages produces a
total of 5 hits. 1 under runtime configuration for 7.3devel docs (which is
interesting since it's not runtime configurable). 1 in the postgreql.conf
sample file.

--

Quote:
> Ignorance continues to thrive when intelligent people choose to do
> nothing.  Speaking out against censorship and ignorance is the imperative
> of all intelligent people.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Sun, 26 Sep 2004 07:46:43 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. restricting file growth & 1105 errors

2. How to calculate Sales Growth

3. Login Connectivity and Memory Growth

4. File size and growth

5. Abnormal DB growth

6. Data Files Growth

7. Java and tempdb excessive growth

8. SQL TempDB growth/SQL 2000

9. Restricting a datafile growth disables all other options

10. Excessive Log File Growth With Simple Recovery

11. Auto/Manual growth for database file size

12. Q:Data file/log file growth


 
Powered by phpBB® Forum Software