Message-ID: <3F8819EF.7010004@chuckie.co.uk>
Date: Sat, 11 Oct 2003 15:55:43 +0100
From: Nick Barr <nicky@chuckie.co.uk>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.5b) Gecko/20030901 Thunderbird/0.2
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: go for a script! / ex:  PostgreSQL vs. MySQL
References: <007901c38f2b$0b585590$2802a8c0@webbased10>
	<200310101034.52073.josh@agliodbs.com>
	<3F86F5BB.90605@chuckie.co.uk>
	<200310101338.18167.josh@agliodbs.com>
In-Reply-To: <200310101338.18167.josh@agliodbs.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Josh Berkus wrote:

>>shared_buffers = 1/16th of total memory
>>effective_cache_size = 80% of the supposed kernel cache.
>>    
>>
>But only if it's a dedicated DB machine.   If it's not, all memory values 
>should be cut in half.
>  
>
>
>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