Re: Simple postgresql.conf wizard
От | Josh Berkus |
---|---|
Тема | Re: Simple postgresql.conf wizard |
Дата | |
Msg-id | 491C6CDC.8090506@agliodbs.com обсуждение исходный текст |
Ответ на | Re: Simple postgresql.conf wizard (Greg Smith <gsmith@gregsmith.com>) |
Ответы |
Re: Simple postgresql.conf wizard
Re: Simple postgresql.conf wizard Re: Simple postgresql.conf wizard |
Список | pgsql-hackers |
Greg, > Attached version takes all its input via command line switches. If you > don't specify an explict number of connections, it also implements > setting max_connections via some of the logic from your calcfactors > spreadsheet. OK, I'll review. What follows is a review of the *previous* version, because I'm currently on the road and didn't see your message to -hackers. Some of the information in the review will still be relevant; for one thing, I've simplified the "what color is your application" logic to a few calculations. ---------------------------- Review of simple_config.py: 1) don't bother with os.sysconf, or make it optional and error-trap it. Instead, solicit the following information from the user: -- Available RAM -- Expected Database Size (to nearest 10x) -- Type of Application-- Web-- Data Warehouse-- Mixed-- Desktop -- Operating System [Linux/Windows/OSX/Solaris/FreeBSD/other] From the above, you can derive all necessary calculations for the basics. In the advanced version, we'll also want to ask: -- Memory used by other applications on the system? -- Analyze queries for performance? -- SSL? -- Production vs. Development status? -- How many connections? -- Logging setup:SyslogAnalyze PerformancePrivate log with weekly rotation 2) It's completely unnecessary to account for OS overhead. This can and should be taken into account as part of the calculations for other figures. For example, my 1/4 and 3/4 calculations ignore OS overhead. You only need to reduce Available RAM when the serverwill be running something else, like a J2EE server or multiple databases. 3) You need to provide a whole bunch more values. shared_buffers and effective_cache_size isn't nearly enough. We should also provide, based on these calculations, and by database type. (I'm happy to argue out the figures below. They are simply based on my direct turning experience with a variety of databases and could probably use more tweaking for the general case.) web / oltplisten_addresses = '*'max_connections = 200shared_buffers = 1/4 AvRAMeffective_cache_size = 3/4 AvRAMwork_mem =AvRAM / max_connections, round downmaint_work_mem = AvRAM / 16, round upwal_buffers = 8mbautovacuum = onmax_fsm_pages =DBsize / PageSize / 8checkpoint_segments = 8default_statistics_target = 10constraint_exclusion = off DW:listen_addresses = '*'max_connections = 20shared_buffers = 1/4 AvRAMeffective_cache_size = 3/4 AvRAMwork_mem = AvRAM /max_connections / 2, round downmaint_work_mem = AvRAM / 8, round upwal_buffers = 32mbautovacuum = offmax_fsm_pages = DBsize/ PageSize / 32* (unless LoadSize is known)checkpoint_segments = 64default_statistics_target = 400constraint_exclusion= on Mixed:listen_addresses = '*'max_connections = 80shared_buffers = 1/4 AvRAMeffective_cache_size = 3/4 AvRAMwork_mem = AvRAM/ max_connections / 2, round downmaint_work_mem = AvRAM / 16, round upwal_buffers = 8mbautovacuum = onmax_fsm_pages= DBsize / PageSize / 8checkpoint_segments = 16default_statistics_target = 100constraint_exclusion = on Desktop:listen_addresses = 'localhost'max_connections = 5shared_buffers = 1/16 AvRAMeffective_cache_size = 1/4 AvRAMwork_mem= AvRAM / 32, round downmaint_work_mem = AvRAM / 16, round upwal_buffers = 1mbautovacuum = onmax_fsm_pages =DBsize / PageSize / 8checkpoint_segments = 3default_statistics_target = 10constraint_exclusion = off 4) Because this comes up so often, we should output to a seperate file a set of sysctl.conf lines to support SysV memory, depending on OS.
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Sometimes pg_dump generates dump which is not restorable