Re: go for a script! / ex: PostgreSQL vs. MySQL

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