shared_buffers = 1/16th of total memoryBut only if it's a dedicated DB machine. If it's not, all memory values should be cut in half.
effective_cache_size = 80% of the supposed kernel cache.
What I would prefer would be an interactive script which would, by asking the user simple questions and system scanning, collect all the information necessary to set:
max_connections shared_buffers sort_mem vacuum_mem effective_cache_size random_page_cost max_fsm_pages checkpoint_segments & checkpoint_timeout tcp_ip
and on the OS, it should set:
shmmax & shmmall
and should offer to create a chron job which does appropriate frequency VACUUM ANALYZE.
I reckon do a system scan first, and parse the current PostgreSQL conf file to figure out what the settings are. Also back it up with a date and time appended to the end to make sure there is a backup before overwriting the real conf file. Then a bunch of questions. What sort of questions would need to be asked and which parameters would these questions affect? So far, and from my limited understanding of the .conf file, I reckon there should be the following
Here is your config of your hardware as detected. Is this correct ?
This could potentially be several questions, i.e. one for proc, mem, os, hdd etc
Would affect shared_buffers, sort_mem, effective_cache_size, random_page_cost
How was PostgreSQL compiled?
This would be parameters such as the block size and a few other compile time parameters. If we can get to some of these read-only parameters than that would make this step easier, certainly for the new recruits amongst us.
Is PostgreSQL the only thing being run on this computer?
Then my previous assumptions about shared_buffers and effective_cache_size would be true.
If shmmax and shmmall are too small, then:
PostgreSQL requires some more shared memory to cache some tables, x Mb, do you want to increase your OS kernel parameters?
Tweak shmmax and shmmall
How are the clients going to connect?
i.e. TCP or Unix sockets
How many clients can connect to this database at once?
Affects max_connections
How many databases and how many tables in each database are going to be present?
Affects max_fsm_pages, checkpoint_segments, checkpoint_timeout
Do you want to vacuum you database regularly?
Initial question for cron job
It is recomended that you vacuum analyze every night, do you want to do this?
It is also recomended that you vacuum full every month, do you want to do this?
Thoughts?
Nick
---------------------------(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
