Hello!
I’ve tried first to increase the number of shared buffers, I doubled it,
from 1000 to 2000 (16Mb)
Unfortunately this had no effect.
Then I increased the number of max_locks_per_transaction from 64 to
128 (these shoul assure about 12 800 lock slots) considering max_connections=100
and max_prepared_transaction=5 (Quote from the manual - The shared lock table is created to track locks on max_locks_per_transaction * (max_connections
+ max_prepared_transactions)
objects (e.g. tables);)
I’ve also restarted
This had also no effect. Because I can’t see any difference between the
maximum input accepted for our application with the old configuration and the
maximum input accepted now, with the new configuration. It looks like nothing
happened.
Thanks
Sorin
From:
pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Shoaib Mir
Sent: Monday, April 02, 2007 6:02
PM
To: Sorin N. Ciolofan
Cc: pgsql-general@xxxxxxxxxxxxxx;
pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] Increasing
the shared memory
An extract from --> http://www.powerpostgresql.com/PerfList/
might help you....
shared_buffers:
As a reminder: This figure is NOT the total memory PostgreSQL has to work with.
It is the block of dedicated memory PostgreSQL uses for active operations, and
should be a minority of your total RAM on the machine, since PostgreSQL uses
the OS disk cache as well. Unfortunately, the exact amount of shared buffers
required is a complex calculation of total RAM, database size, number of
connections, and query complexity. Thus it's better to go with some rules of
thumb in allocating, and monitor the server (particuarly pg_statio views) to
determine adjustments.
On dedicated servers, useful values seem to be between between 8MB and 400MB
(between 1000 and 50,000 for 8K page size). Factors which raise the desired
shared buffers are larger active portions of the database, large complex
queries, large numbers of simultaneous queries, long-running procedures or
transactions, more available RAM, and faster/more CPUs. And, of course, other
applications on the machine. Contrary to some expectations, allocating much too
much shared_buffers can actually lower peformance, due time required for
scanning. Here's some examples based on anecdotes and TPC tests on Linux
machines:
* Laptop, Celeron processor, 384MB RAM, 25MB database:
12MB/1500
* Athlon server, 1GB RAM, 10GB decision-support database:
120MB/15000
* Quad PIII server, 4GB RAM, 40GB, 150-connection heavy
transaction processing database: 240MB/30000
* Quad Xeon server, 8GB RAM, 200GB, 300-connection heavy
transaction processing database: 400MB/50000
Please note that increasing shared_buffers, and a few other memory parameters,
will require you to modify your operating system's System V memory parameters.
See the main PostgreSQL documentation for instructions on this.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
