From: Nick Barr <nicky@chuckie.co.uk>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: go for a script! / ex: PostgreSQL vs. MySQL
Date: Sat, 11 Oct 2003 15:55:43 +0100
Message-ID: <3F8819EF.7010004@chuckie.co.uk> (raw)
In-Reply-To: <200310101338.18167.josh@agliodbs.com>
References: <007901c38f2b$0b585590$2802a8c0@webbased10>
<200310101034.52073.josh@agliodbs.com>
<3F86F5BB.90605@chuckie.co.uk>
<200310101338.18167.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nicky@chuckie.co.uk, josh@agliodbs.com
Subject: Re: go for a script! / ex: PostgreSQL vs. MySQL
In-Reply-To: <3F8819EF.7010004@chuckie.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox