Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
Jason Hihn said: Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread that gathers statistics and periodically re-tunes the database parameters. Of course, be able to turn it off. People that actually take the time to run tune manually will turn it off as to not have the overhead or interruption. Those that don't care about pg_tune shouldn't care about having a thread around retuning. Those that will care will tune manually. This is related to my proposition, but trouble is, there is not such thing as 'well tuned database' that will suit all queries. You can tune the database to the hardware for example (still remember that old argument on random access and fast disks). It seems the system could 'self-tune' itself on minor choices. I believe it does this today for a number of things already. More significant changes require the DBA consent and choice - but they need to be well informed of the current usage statistics when making the choice. Daniel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
Josh Berkus said: How about we take this discussion to the Performance List, where it belongs? I believe the design and addition of code that collects and outputs the usage patterns of the database (statistics) belongs here. If we take the approach to providing information to tune PostgreSQL based on real-world usage, I guess we need at least the following: - Usage statistics on a per-database or even per-table level. I believe we already collect some; - Statistics analysis tool/routine/process to produce suggestions; - Of course lots of real-world data to justify the suggestions; - Can we provide more knobs for tunable parameters that can be applied on a per-database or even per-table level. One first candidate might be the FSM? - Can some of these parameters (when available) to auto-tune? Of course, this could move out of pgsql-hackers :) Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
On Fri, 2003-02-14 at 03:00, Daniel Kalchev wrote: Jason Hihn said: Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread that gathers statistics and periodically re-tunes the database parameters. Of course, be able to turn it off. People that actually take the time to run tune manually will turn it off as to not have the overhead or interruption. Those that don't care about pg_tune shouldn't care about having a thread around retuning. Those that will care will tune manually. This is related to my proposition, but trouble is, there is not such thing as 'well tuned database' that will suit all queries. You can tune the database to the hardware for example (still remember that old argument on random access and fast disks). It seems the system could 'self-tune' itself on minor choices. I believe it does this today for a number of things already. More significant changes require the DBA consent and choice - but they need to be well informed of the current usage statistics when making the choice. I agree. Given that we don't have solid explanations on telling people how to tune the different parameters, nor do we have enough mechanisms for actually giving people the information they need to determine the changes they need, a complete auto-tune seems premature. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
On Thu, 13 Feb 2003, Bruce Momjian wrote: To auto-tune, you would need to monitor swap usage and other stuff that may vary too much based on load from other systems. Only the admin knows how to answer some of those questions. No, to auto-tune many parameters that currently require manual configuration, you merely need to change things so that you work with the OS, rather than beside it. There are a lot of things we could mmap that would remove the need for tuning certain things altogether. The buffer cache is the biggest one; mmaping that would let the OS take care of it all, and a few hundred KB of shared memory would be all postgres would need. (Or none at all, if you used mmap'd memory regions where for that part of your IPC as well.) You could probably also make sort_mem need a lot less tuning if you sorted in mmap'd memory and let the OS deal with paging only when and if it needed it (as well as asking the OS not to page memory you're randomly accessing, since it really destroys your peformance when you do that). I'm not sure if you could get rid of semaphores or not, but if you could somehow do that, that would be another limited resource that you wouldn't have to deal with. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
HEY PEOPLE! How about we take this discussion to the Performance List, where it belongs? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
To auto-tune, you would need to monitor swap usage and other stuff that may vary too much based on load from other systems. Only the admin knows how to answer some of those questions. --- Jason Hihn wrote: Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread that gathers statistics and periodically re-tunes the database parameters. Of course, be able to turn it off. People that actually take the time to run tune manually will turn it off as to not have the overhead or interruption. Those that don't care about pg_tune shouldn't care about having a thread around retuning. Those that will care will tune manually. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian Sent: Thursday, February 13, 2003 2:22 PM To: Daniel Kalchev Cc: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED] Subject: Re: [pgsql-advocacy] [HACKERS] Changing the default configuration I imagined they could run pgtune anytime after install to update those performance parameters. It gives them a one-stop location to at least do minimal tuning, and as their load changes, they can run it again. --- Daniel Kalchev wrote: Bruce Momjian said: [...] For example, we can ask them how many rows and tables they will be changing, on average, between VACUUM runs. That will allow us set the FSM params. We can ask them about using 25% of their RAM for shared buffers. If they have other major apps running on the server or have small tables, we can make no changes. We can basically ask them questions and use that info to set values. Bruce, this is an very good idea and such tool would simplify setup for the me-too type of DBA - we should definitely try to attract them. However, how could one possibly answer the above question, if they setup their database for the first time? What is more, these settings are on a per-installation, not per-database - which means, that if you have several small, but active databases and one large database the requirements will be very different. Nobody likes answering such questions when installing new software. You might enjoy it the first few times, but then learn the 'answers' and don't even think what the question is. (we all know the answer :) Perhaps indeed a better idea is to have PostgreSQL itself collect usage statistics, and from time to time print 'suggestions' to the log file (best in my opinion), or have these available via some query. These suggestions should best reflect the of course require minimal intervention to the database system, such as restart etc. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-advocacy] [HACKERS] Changing the default configuration
Pardon my ignorance, but there's no way to auto-tune? Ship it with a thread that gathers statistics and periodically re-tunes the database parameters. Of course, be able to turn it off. People that actually take the time to run tune manually will turn it off as to not have the overhead or interruption. Those that don't care about pg_tune shouldn't care about having a thread around retuning. Those that will care will tune manually. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian Sent: Thursday, February 13, 2003 2:22 PM To: Daniel Kalchev Cc: PostgresSQL Hackers Mailing List; [EMAIL PROTECTED] Subject: Re: [pgsql-advocacy] [HACKERS] Changing the default configuration I imagined they could run pgtune anytime after install to update those performance parameters. It gives them a one-stop location to at least do minimal tuning, and as their load changes, they can run it again. --- Daniel Kalchev wrote: Bruce Momjian said: [...] For example, we can ask them how many rows and tables they will be changing, on average, between VACUUM runs. That will allow us set the FSM params. We can ask them about using 25% of their RAM for shared buffers. If they have other major apps running on the server or have small tables, we can make no changes. We can basically ask them questions and use that info to set values. Bruce, this is an very good idea and such tool would simplify setup for the me-too type of DBA - we should definitely try to attract them. However, how could one possibly answer the above question, if they setup their database for the first time? What is more, these settings are on a per-installation, not per-database - which means, that if you have several small, but active databases and one large database the requirements will be very different. Nobody likes answering such questions when installing new software. You might enjoy it the first few times, but then learn the 'answers' and don't even think what the question is. (we all know the answer :) Perhaps indeed a better idea is to have PostgreSQL itself collect usage statistics, and from time to time print 'suggestions' to the log file (best in my opinion), or have these available via some query. These suggestions should best reflect the of course require minimal intervention to the database system, such as restart etc. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
