Re: Simple postgresql.conf wizard

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.