Re: Simple postgresql.conf wizard - Mailing list 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.