Re: Simple postgresql.conf wizard

[Josh Berkus <josh(at)agliodbs(dot)com>]

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:
	Syslog
	Analyze Performance
	Private 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 server will 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 / oltp
	listen_addresses = '*'
	max_connections = 200
	shared_buffers = 1/4 AvRAM
	effective_cache_size = 3/4 AvRAM
	work_mem = AvRAM / max_connections, round down
	maint_work_mem = AvRAM / 16, round up
	wal_buffers = 8mb
	autovacuum = on
	max_fsm_pages = DBsize / PageSize / 8
	checkpoint_segments = 8
	default_statistics_target = 10
	constraint_exclusion = off
	
DW:
	listen_addresses = '*'
	max_connections = 20
	shared_buffers = 1/4 AvRAM
	effective_cache_size = 3/4 AvRAM
	work_mem = AvRAM / max_connections / 2, round down
	maint_work_mem = AvRAM / 8, round up
	wal_buffers = 32mb
	autovacuum = off
	max_fsm_pages = DBsize / PageSize / 32*
		(unless LoadSize is known)
	checkpoint_segments = 64
	default_statistics_target = 400
	constraint_exclusion = on

Mixed:
	listen_addresses = '*'
	max_connections = 80
	shared_buffers = 1/4 AvRAM
	effective_cache_size = 3/4 AvRAM
	work_mem = AvRAM / max_connections / 2, round down
	maint_work_mem = AvRAM / 16, round up
	wal_buffers = 8mb
	autovacuum = on
	max_fsm_pages = DBsize / PageSize / 8
	checkpoint_segments = 16
	default_statistics_target = 100
	constraint_exclusion = on

Desktop:
	listen_addresses = 'localhost'
	max_connections = 5
	shared_buffers = 1/16 AvRAM
	effective_cache_size = 1/4 AvRAM
	work_mem = AvRAM / 32, round down
	maint_work_mem = AvRAM / 16, round up
	wal_buffers = 1mb
	autovacuum = on
	max_fsm_pages = DBsize / PageSize / 8
	checkpoint_segments = 3
	default_statistics_target = 10
	constraint_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.