PostgreSQL Configuration Tool for Dummies

Now I am at the difficult part, what parameters to calculate and how to
calculate them. Everything below has to do with PostgreSQL version 8.2:

The parameters I would think we should calculate are:

max_connections

shared_buffers

work_mem

maintenance_work_mem

effective_cache_size

random_page_cost

Any other variables? I am open to suggestions.

Calculations based on values supplied in the questions at the top of the
page:

max_connection= question #3 or a minimum of 8

effective_cache_size={question #2}MB

maintenance_work_mem= ({question #2} * .1) MB

Any thoughts on the other variables based on the questions found at the
top of the below web page?

http://www.webservices.uiuc.edu/postgresql/

Thanks,

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

Lance,

> The parameters I would think we should calculate are:
>
> max_connections
>
> shared_buffers
>
> work_mem
>
> maintenance_work_mem
>
> effective_cache_size
>
> random_page_cost

Actually, I'm going to argue against messing with random_page_cost. It's a
cannon being used when a slingshot is called for. Instead (and this was
the reason for the "What kind of CPU?" question) you want to reduce the
cpu_* costs. I generally find that if cpu_* are reduced as appropriate to
modern faster cpus, and effective_cache_size is set appropriately, a
random_page_cost of 3.5 seems to work for appropriate choice of index
scans.

If you check out my spreadsheet version of this:
http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc
... you'll see that the approach I found most effective was to create
profiles for each of the types of db applications, and then adjust the
numbers based on those.

Other things to adjust:
wal_buffers
checkpoint_segments
commit_delay
vacuum_delay
autovacuum

Anyway, do you have a pgfoundry ID? I should add you to the project.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Campbell, Lance writes:

> max_connections

Shouldn't that come straight from the user?

At 4:35p -0400 on 19 Jun 2007, Lance Campbell wrote:
> The parameters I would think we should calculate are:
> max_connections
> shared_buffers
> work_mem
> maintenance_work_mem
> effective_cache_size
> random_page_cost

From an educational/newb standpoint, I notice that the page
currently spews out a configuration file completely in line with
what's currently there, comments and all. May I suggest highlighting
what has been altered, perhaps above or below the textbox? It would
make it immediately obvious, and easier to add an explanation of the
thought process involved. Something like

What's changed from the default:

<li>
<p><strong>max_connections = 5</strong></p>
<p>This follows directly from you put above. It is the maximum
number of concurrent connections Postgres will allow.</p>
</li>
<li>
<p><strong>shared_buffers = 10000</strong></p>
<p>This setting will take some time to get exactly right for your
needs. Postgres uses this for ...</p>
</li>

Not something that necessarily needs to be spelled out in the .conf
file, but would, IMVHO, help minimally educate.

Kevin

Campbell, Lance wrote:
> Now I am at the difficult part, what parameters to calculate and how to
> calculate them. Everything below has to do with PostgreSQL version 8.2:
>
>
>
> The parameters I would think we should calculate are:
>
> max_connections
>
> shared_buffers
>
> work_mem
>
> maintenance_work_mem
>
> effective_cache_size
>
> random_page_cost
>
>
>
> Any other variables? I am open to suggestions.

we also should scale max_fsm_pages according to the database size and
workload answers - I also note that the configuration file it generates
seems to look like on for PostgreSQL 7.x or something - I think we
should just include the specific parameters to change.

Stefan

> ""Campbell, Lance"" <lance(at)uiuc(dot)edu> wrote in message
> news:A3AC4FA47DC0B1458C3E5396E685E63302395E67(at)SAB-DC1(dot)sab(dot)uiuc(dot)edu(dot)(dot)(dot)
> Now I am at the difficult part, what parameters to calculate and how to
> calculate them. Everything below has to do with PostgreSQL version 8.2:
>
>
> The parameters I would think we should calculate are:
> max_connections
> shared_buffers
> work_mem
> maintenance_work_mem
> effective_cache_size
> random_page_cost
>
> Any other variables? I am open to suggestions.

I know this is mainly about tuning for performance but I do think you ought
to give the option to change at least 'listen_address'. Something like:

Accept connections on: - Local connections (Unix sockets/localhost)
- All TCP/IP interfaces
- Specific IP addresses:
___________ (comma-seperated list)

and maybe a pointer to the pg_hba.conf docs for further info.

Regards,

Ben