Thread

1. Super Optimizing Postgres

   mlw <markw@mohawksoft.com> — 2001-11-16T13:45:27Z

   It is sort of discouraging that Postgres' default configuration is so
   conservative. A tweek here and there can make a big difference. It seems to me
   that the default postgresql.conf should not be used for a dedicated server. In
   fact, it can't because TCP/IP is disabled.
   
   In my projects I have done the default stuff, increase buffers, sort memory,
   and so on, however, some of the tunable parameters seem a bit arcane and are
   not completely clear what they do or the effect they may have. (some have no
   noticable effect, eventhough it looks as if they should.) I think most users,
   particularly those new to SQL databases in general, would find it difficult to
   tune Postgres.
   
   Does anyone think it is a good idea, to make a postgresql.conf cookbook sort of
   thing? Gather a number of tuned config files, annotated as to why the settings
   are set the way they are, and the machine on which they run.
   
   Particularly, I'd like to see if someone has been able to really understand and
   manipulate the planner COST options successfully.
   
   Alternatively, it should be possible to write a program that analyzes a target
   system, asks questions like: "Is this a dedicated server?" "How much ram do you
   have?" "On which volume will the database be installed?" Then perform some
   tests that mimic the cost values, and create a new postgresql.conf with the
   options tuned.
   
   

2. Re: Super Optimizing Postgres

   Alex Pilosov <alex@pilosoft.com> — 2001-11-16T16:26:11Z

   Sure, that'd be useful. Especially since we all can discuss what things
   work, what doesn't work. 
   
   
   On Fri, 16 Nov 2001, mlw wrote:
   
   > It is sort of discouraging that Postgres' default configuration is so
   > conservative. A tweek here and there can make a big difference. It seems to me
   > that the default postgresql.conf should not be used for a dedicated server. In
   > fact, it can't because TCP/IP is disabled.
   > 
   > In my projects I have done the default stuff, increase buffers, sort
   > memory, and so on, however, some of the tunable parameters seem a bit
   > arcane and are not completely clear what they do or the effect they
   > may have. (some have no noticable effect, eventhough it looks as if
   > they should.) I think most users, particularly those new to SQL
   > databases in general, would find it difficult to tune Postgres.
   > 
   > Does anyone think it is a good idea, to make a postgresql.conf
   > cookbook sort of thing? Gather a number of tuned config files,
   > annotated as to why the settings are set the way they are, and the
   > machine on which they run.
   > 
   > Particularly, I'd like to see if someone has been able to really
   > understand and manipulate the planner COST options successfully.
   > 
   > Alternatively, it should be possible to write a program that analyzes
   > a target system, asks questions like: "Is this a dedicated server?"
   > "How much ram do you have?" "On which volume will the database be
   > installed?" Then perform some tests that mimic the cost values, and
   > create a new postgresql.conf with the options tuned.
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   > 
   > 
   
   
   

3. Re: Super Optimizing Postgres

   Matthew T. O'Connor <matthew@zeut.net> — 2001-11-16T19:52:06Z

   Didn't Bruce do a document on this exact topic?  I'm not sure it's everything 
   you are looking for, but it might help you get started.
   
   > Sure, that'd be useful. Especially since we all can discuss what things
   > work, what doesn't work.
   >
   > On Fri, 16 Nov 2001, mlw wrote:
   > > It is sort of discouraging that Postgres' default configuration is so
   > > conservative. A tweek here and there can make a big difference. It seems
   > > to me that the default postgresql.conf should not be used for a dedicated
   > > server. In fact, it can't because TCP/IP is disabled.
   > >
   > > In my projects I have done the default stuff, increase buffers, sort
   > > memory, and so on, however, some of the tunable parameters seem a bit
   > > arcane and are not completely clear what they do or the effect they
   > > may have. (some have no noticable effect, eventhough it looks as if
   > > they should.) I think most users, particularly those new to SQL
   > > databases in general, would find it difficult to tune Postgres.
   > >
   > > Does anyone think it is a good idea, to make a postgresql.conf
   > > cookbook sort of thing? Gather a number of tuned config files,
   > > annotated as to why the settings are set the way they are, and the
   > > machine on which they run.
   > >
   > > Particularly, I'd like to see if someone has been able to really
   > > understand and manipulate the planner COST options successfully.
   > >
   > > Alternatively, it should be possible to write a program that analyzes
   > > a target system, asks questions like: "Is this a dedicated server?"
   > > "How much ram do you have?" "On which volume will the database be
   > > installed?" Then perform some tests that mimic the cost values, and
   > > create a new postgresql.conf with the options tuned.
   > >
   > > ---------------------------(end of broadcast)---------------------------
   > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   
   

4. Re: Super Optimizing Postgres

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-11-16T20:43:46Z

   > Didn't Bruce do a document on this exact topic?  I'm not sure it's everything 
   > you are looking for, but it might help you get started.
   
   Sure:
   
   	http://techdocs.postgresql.org
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

5. Re: Super Optimizing Postgres

   Serguei Mokhov <sa_mokho@alcor.concordia.ca> — 2001-11-16T21:03:53Z

   ----- Original Message ----- 
   From: mlw <markw@mohawksoft.com>
   Sent: Friday, November 16, 2001 8:45 AM
   
   > Does anyone think it is a good idea, to make a postgresql.conf cookbook sort of
   > thing? Gather a number of tuned config files, annotated as to why the settings
   > are set the way they are, and the machine on which they run.
   
   Sounds like a very good idea, but... who's gonna be in charge of it?
   
   > Alternatively, it should be possible to write a program that analyzes a target
   > system, asks questions like: "Is this a dedicated server?" "How much ram do you
   > have?" "On which volume will the database be installed?" Then perform some
   > tests that mimic the cost values, and create a new postgresql.conf with the
   > options tuned.
   
   This program sounds like a little rule-based expert system-like software :)
   "Tune your PostgreSQL server with a pgTune expert system!" an ad
   would be voicing out loud...
   
   --
   Serguei A. Mokhov
   
   
   

6. Re: Super Optimizing Postgres

   mlw <markw@mohawksoft.com> — 2001-11-16T22:06:38Z

   Bruce Momjian wrote:
   > 
   > > Didn't Bruce do a document on this exact topic?  I'm not sure it's everything
   > > you are looking for, but it might help you get started.
   > 
   > Sure:
   > 
   >         http://techdocs.postgresql.org
   
   Question:
   
   Does sort memory come out of shared? I don't think so (would it need too?), but
   "Cache Size and Sort Size " seems to imply that it does.
   
   Also, you don't go into the COST variables. If what is documented about them is
   correct, they are woefully incorrect with a modern machine.
   
   Would a 1.3 ghz Athlon really have a cpu_operator_cost of 0.0025? That would
   imply that that computer could process 2500 conditionals in the time it would
   take to make a sequential read. If Postgres is run on a 10K RPM disk vs a 5.4K
   RPM disk on two different machines with the same processor and speed, these
   numbers can't hope to be right, one should be about twice as high as the other.
   
   That said, do these numbers really affect the planner all that much?
   
   

7. Re: Super Optimizing Postgres

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-11-16T22:13:56Z

   > Does sort memory come out of shared? I don't think so (would it
   > need too?), but "Cache Size and Sort Size " seems to imply that
   > it does.
   
   Sort comes from per-backend memory, not shared.  Of course, both
   per-backend and shared memory come from the same pool of RAM, if that's
   what you mean.  Could it be made clearer?
   
   > Also, you don't go into the COST variables. If what is documented
   > about them is correct, they are woefully incorrect with a modern
   > machine.
   
   You mean:
   	
   	#random_page_cost = 4
   	#cpu_tuple_cost = 0.01
   	#cpu_index_tuple_cost = 0.001
   	#cpu_operator_cost = 0.0025
   
   Thos are relative, of course.  We are always looking for better numbers.
   
   > Would a 1.3 ghz Athlon really have a cpu_operator_cost of 0.0025?
   > That would imply that that computer could process 2500 conditionals
   > in the time it would take to make a sequential read. If Postgres
   > is run on a 10K RPM disk vs a 5.4K RPM disk on two different
   > machines with the same processor and speed, these numbers can't
   > hope to be right, one should be about twice as high as the other.
   
   Again, are the correct relative to each other.
   
   > That said, do these numbers really affect the planner all that
   > much?
   
   Sure do effect the planner.  That is how index scan vs sequential and
   join type are determined.
   
   --
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

8. Re: Super Optimizing Postgres

   mlw <markw@mohawksoft.com> — 2001-11-16T23:36:11Z

   Bruce Momjian wrote:
   > 
   > > Does sort memory come out of shared? I don't think so (would it
   > > need too?), but "Cache Size and Sort Size " seems to imply that
   > > it does.
   > 
   > Sort comes from per-backend memory, not shared.  Of course, both
   > per-backend and shared memory come from the same pool of RAM, if that's
   > what you mean.  Could it be made clearer?
   
   Actually, in most cases, RAM is ram, and shared ram is just the same ram.
   However, on some cluster environments, shared ram is a different memory pool
   than process ram.
   
   In your section: "Cache Size and Sort Size" You talk about both and shared
   memory, but make no distinction about which uses what. I would suggest an
   explicit sentence about how Cache comes from the shared memory pool and Sort
   comes from the process memory pool.
   
   
   > 
   > > Also, you don't go into the COST variables. If what is documented
   > > about them is correct, they are woefully incorrect with a modern
   > > machine.
   > 
   > You mean:
   > 
   >         #random_page_cost = 4
   >         #cpu_tuple_cost = 0.01
   >         #cpu_index_tuple_cost = 0.001
   >         #cpu_operator_cost = 0.0025
   > 
   > Thos are relative, of course.  We are always looking for better numbers.
   > 
   > > Would a 1.3 ghz Athlon really have a cpu_operator_cost of 0.0025?
   > > That would imply that that computer could process 2500 conditionals
   > > in the time it would take to make a sequential read. If Postgres
   > > is run on a 10K RPM disk vs a 5.4K RPM disk on two different
   > > machines with the same processor and speed, these numbers can't
   > > hope to be right, one should be about twice as high as the other.
   > 
   > Again, are the correct relative to each other.
   
   They can't possibly be correct. If We have two identical machines where the
   only difference is the disk subsystem, one has a 10K RPM SCSI system, and the
   other is a 5.4K RPM IDE disk. There is no way these settings can be accurate.
   
   > 
   > > That said, do these numbers really affect the planner all that
   > > much?
   > 
   > Sure do effect the planner.  That is how index scan vs sequential and
   > join type are determined.
   
   OK, then it should be fairly straight forward to make a profiler for Postgres
   to set these parameters.
   
   Sequential and random read test, these are a no brainer.
   
   The cpu costs are not so easy. I don't have a very good idea about what they
   "really" mean. I have a guess, but not enough to make a benchmark routine.
   
   If someone who REALLY knows could detail a test routine for each of the cpu
   cost types. I could write a program that will spit out what the numbers should
   be.
   
   I envision:
   
   pgprofile /u01/postgres/test.file
   
   And that would output something like:
   
   random_page_cost = 2
   cpu_tuple_cost = 0.00344
   cpu_index_tuple_cost = 0.00234
   cpu_operator_cost = 0.00082
   
   

9. Re: Super Optimizing Postgres

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-11-16T23:45:44Z

   mlw <markw@mohawksoft.com> writes:
   > Also, you don't go into the COST variables. If what is documented
   > about them is correct, they are woefully incorrect with a modern
   > machine.
   
   The numbers seemed in the right ballpark when I experimented with them
   a year or two ago.  Keep in mind that all these things are quite fuzzy,
   given that we never know for sure whether a read() request to the kernel
   is going to cause actual I/O or be satisfied from kernel cache.  One
   should not mistake "operator" for "addition instruction", either ---
   at the very least, there are several levels of function call overhead
   involved.  And using one cost number for all Postgres operators is
   obviously a simplification of reality anyhow.
   
   > Would a 1.3 ghz Athlon really have a cpu_operator_cost of 0.0025? That
   > would imply that that computer could process 2500 conditionals in the
   > time it would take to make a sequential read. If Postgres is run on a
   > 10K RPM disk vs a 5.4K RPM disk on two different machines with the
   > same processor and speed, these numbers can't hope to be right, one
   > should be about twice as high as the other.
   
   We've talked in the past about autoconfiguring these numbers, but I have
   not seen any proposals for automatically deriving trustworthy numbers
   in a reasonable period of time.  There's too much uncertainty and noise
   in any simple test.  (I spent literally weeks convincing myself that the
   current numbers were reasonable.)
   
   But having said all that, it's true that CPU speed has been increasing
   much faster than disk speed over the last few years.  If you feel like
   reducing the CPU cost numbers, try it and see what happens.
   
   			regards, tom lane
   
   

10. Re: Super Optimizing Postgres

    Alex Pilosov <alex@pilosoft.com> — 2001-11-16T23:47:47Z

    On Fri, 16 Nov 2001, mlw wrote:
    
    > Sequential and random read test, these are a no brainer.
    > 
    > The cpu costs are not so easy. I don't have a very good idea about what they
    > "really" mean. I have a guess, but not enough to make a benchmark routine.
    > 
    > If someone who REALLY knows could detail a test routine for each of the cpu
    > cost types. I could write a program that will spit out what the numbers should
    > be.
    > 
    > I envision:
    > 
    > pgprofile /u01/postgres/test.file
    > 
    > And that would output something like:
    > 
    > random_page_cost = 2
    > cpu_tuple_cost = 0.00344
    > cpu_index_tuple_cost = 0.00234
    > cpu_operator_cost = 0.00082
    
    Actually, it could be done if the 'EXPLAIN EXACTLY' was implemented. Such
    a command would give you same output as explain plus precise timings each
    step took. Idea was floated in the list awhile ago. I think the problem
    with it was properly separating borders of queries, but still, it'd cool
    
    -alex
    
    
    

11. Re: Super Optimizing Postgres

    Justin Clift <justin@postgresql.org> — 2001-11-17T04:12:39Z

    Bruce Momjian wrote:
    > 
    <snip> 
    > You mean:
    > 
    >         #random_page_cost = 4
    >         #cpu_tuple_cost = 0.01
    >         #cpu_index_tuple_cost = 0.001
    >         #cpu_operator_cost = 0.0025
    > 
    > Thos are relative, of course.  We are always looking for better numbers.
    > 
    > > Would a 1.3 ghz Athlon really have a cpu_operator_cost of 0.0025?
    > > That would imply that that computer could process 2500 conditionals
    > > in the time it would take to make a sequential read. If Postgres
    > > is run on a 10K RPM disk vs a 5.4K RPM disk on two different
    > > machines with the same processor and speed, these numbers can't
    > > hope to be right, one should be about twice as high as the other.
    > 
    > Again, are the correct relative to each other.
    
    I think it's an interesting thought of having a program which will test
    a system and work out the Accurate and Correct values for this.
    
    It could become of enormous tuning help.  Another thought is to have
    PostgreSQL tune these parameters as it goes.
    
    + Justin
    
    <snip>
    > --
    >   Bruce Momjian                        |  http://candle.pha.pa.us
    >   pgman@candle.pha.pa.us               |  (610) 853-3000
    >   +  If your life is a hard drive,     |  830 Blythe Avenue
    >   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 4: Don't 'kill -9' the postmaster
    
    -- 
    "My grandfather once told me that there are two kinds of people: those
    who work and those who take the credit. He told me to try to be in the
    first group; there was less competition there."
       - Indira Gandhi
    
    

12. Re: Super Optimizing Postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-11-17T05:14:34Z

    Justin Clift <justin@postgresql.org> writes:
    > I think it's an interesting thought of having a program which will test
    > a system and work out the Accurate and Correct values for this.
    
    I think if you start out with the notion that there is an Accurate
    and Correct value for these parameters, you've already lost the game.
    They're inherently fuzzy numbers because they are parameters of an
    (over?) simplified model of reality.
    
    It would be interesting to try to fit the model to reality on a wide
    variety of queries, machines & operating environments, and see what
    numbers we come up with.  But there's always going to be a huge fuzz
    factor involved.  Because of that, I'd be *real* wary of any automated
    tuning procedure.  Without a good dollop of human judgement in the loop,
    an automated parameter-setter will likely go off into never-never land
    :-(
    
    			regards, tom lane
    
    

13. Re: Super Optimizing Postgres

    mlw <markw@mohawksoft.com> — 2001-11-17T14:24:58Z

    Tom Lane wrote:
    > 
    > Justin Clift <justin@postgresql.org> writes:
    > > I think it's an interesting thought of having a program which will test
    > > a system and work out the Accurate and Correct values for this.
    > 
    > I think if you start out with the notion that there is an Accurate
    > and Correct value for these parameters, you've already lost the game.
    
    Pardon me, but this is a very scary statement. If you believe that this is
    true, then the planner/optimizer is inherently flawed.
    
    If the numbers are meaningless, they should not be used. If the numbers are not
    meaningless, then they must be able to be tuned.
    
    In my example, two computers with exactly the same hardware, except one has a
    5400 RPM IDE drive, the other has a 10,000 RPM IDE drive. These machines should
    not use the same settings, it is obvious that a sequential scan block read on
    one will be faster than the other.
    
    > They're inherently fuzzy numbers because they are parameters of an
    > (over?) simplified model of reality.
    
    Very true, this also scares me. Relating processing time with disk I/O seems
    like a very questionable approach these days. Granted, this strategy was
    probably devised when computers systems were a lot simpler, but today with
    internal disk caching, cpu instruction caches, pipelining, L2 caching
    techniques, clock multiplication, RAID controllers, and so on, the picture is
    far more complicated.
    
    That being said, a running system should have a "consistent" performance which
    should be measurable.
    
    > 
    > It would be interesting to try to fit the model to reality on a wide
    > variety of queries, machines & operating environments, and see what
    > numbers we come up with.  But there's always going to be a huge fuzz
    > factor involved.  Because of that, I'd be *real* wary of any automated
    > tuning procedure.  Without a good dollop of human judgement in the loop,
    > an automated parameter-setter will likely go off into never-never land
    > :-(
    
    It is an interesting problem:
    
    It appears that "sequential scan" is the root measure for the system.
    Everything is biased off that. A good "usable" number for sequential scan would
    need to be created.
    
    Working on the assumption that a server will have multiple back-ends running,
    we will start (n) threads (Or forks). (n) will be tunable by the admin based on
    the expected concurrency of their system. There would two disk I/O test
    routines, one which performs a sequential scan, one which performs a series of
    random page reads.
    
    (n)/2 threads would perform sequential scans.
    (n)/2 threads would perform random page reads.
    
    (Perhaps we can even have the admin select the ratio between random and
    sequential? Or let the admin choose how many of each?)
    
    The result of the I/O profiling would be to get a reasonable average number of
    microseconds it takes to do a sequential scan and a ratio to random page reads.
    This will be done on files who's size is larger than the available memory of
    the machine to ensure the files do not stay in the OS cache. Each routine will
    make several iterations before quitting.
    
    This should produce a reasonable picture of the user's system in action.
    
    We could then take standard code profiling techniques against representative
    test routines to do each of the cpu_xxx modules and compare the profile of the
    routines against the evaluated time of a sequential scan.
    
    The real trick is the code profiling "test routines." What kind of processing
    do the cpu_xxx settings represent?
    
    

14. Re: Super Optimizing Postgres

    Justin Clift <justin@postgresql.org> — 2001-11-17T15:26:48Z

    mlw wrote:
    > 
    > Tom Lane wrote:
    > >
    > > Justin Clift <justin@postgresql.org> writes:
    > > > I think it's an interesting thought of having a program which will test
    > > > a system and work out the Accurate and Correct values for this.
    > >
    > > I think if you start out with the notion that there is an Accurate
    > > and Correct value for these parameters, you've already lost the game.
    
    I believe we can evolve and refine the model so it becomes more
    accurate.  We at least have to be willing to try otherwise I think THAT
    is where we lose the game.  :)
    
    <snip>
    > In my example, two computers with exactly the same hardware, except one has a
    > 5400 RPM IDE drive, the other has a 10,000 RPM IDE drive. These machines should
    > not use the same settings, it is obvious that a sequential scan block read on
    > one will be faster than the other.
    
    If we're going to do this bit properly, then we'll have to take into
    consideration many database objects will need their own individual
    statistics.  For example, lets say we have a database with a bunch of
    10k rpm SCSI drives which the tables are on, and the system also has one
    or more 15k rpm SCSI drives (lets say a Seagate Cheetah II drives) on
    which the indices have been placed.  With the 10k rpm drives, the tables
    needing the fastest throughput or having the highest usage are put on
    the outer edges of the disk media, and the rest of the tables are placed
    in the available space.
    
    On this theoretical system, we will be better off measuring the
    performance of each table and index in turn then generating and storing
    costs for each one which are as "accurate as possible at this point in
    time".  A model like this would probably have these costs re-calculated
    each time the ANALYZE command is run to ensure their accuracy through
    database growth and changes.
    
    I think this would be decently accurate, and RAID systems would be
    accurately analysed.  Don't know how to take into account large cache
    sizes though.  :)
    
    Regards and best wishes,
    
    Justin Clift
    
    -- 
    "My grandfather once told me that there are two kinds of people: those
    who work and those who take the credit. He told me to try to be in the
    first group; there was less competition there."
       - Indira Gandhi
    
    

15. Re: Super Optimizing Postgres

    Hannu Krosing <hannu@sid.tm.ee> — 2001-11-18T06:39:11Z

    
    Justin Clift wrote:
    
    >>In my example, two computers with exactly the same hardware, except one has a
    >>5400 RPM IDE drive, the other has a 10,000 RPM IDE drive. These machines should
    >>not use the same settings, it is obvious that a sequential scan block read on
    >>one will be faster than the other.
    >>
    >
    >If we're going to do this bit properly, then we'll have to take into
    >consideration many database objects will need their own individual
    >statistics.  For example, lets say we have a database with a bunch of
    >10k rpm SCSI drives which the tables are on, and the system also has one
    >or more 15k rpm SCSI drives (lets say a Seagate Cheetah II drives) on
    >which the indices have been placed.  With the 10k rpm drives, the tables
    >needing the fastest throughput or having the highest usage are put on
    >the outer edges of the disk media, and the rest of the tables are placed
    >in the available space.
    >
    >On this theoretical system, we will be better off measuring the
    >performance of each table and index in turn then generating and storing
    >costs for each one which are as "accurate as possible at this point in
    >time".
    >
    That would mean that these statistic values must be stored in pg_class 
    and not be SET
    variables at all.
    This will probably have the added benefit that some cacheing effects of 
    small/big tables
    will be accounted for automatically so you dont have to do that in 
    optimizer.
    
    >  A model like this would probably have these costs re-calculated
    >each time the ANALYZE command is run to ensure their accuracy through
    >database growth and changes.
    >
    Then the ANALYZE should be run on both tables and indexes. AFAIK we 
    currently
    analyze only real data.
    
    >
    >I think this would be decently accurate, and RAID systems would be
    >accurately analysed.  Don't know how to take into account large cache
    >sizes though.  :)
    >
    Maybe some volatile statistict on how much of table "may be" cached in 
    the disk/fs
    caches, assuming that we currently know how much of each is in shared 
    memory.
    
    -----------
    Hannu