Re: [ADMIN] Increasing the shared memory - Mailing list pgsql-general

In response to "Sorin N. Ciolofan" <ciolofan@ics.forth.gr>:

>                 I've tried first to increase the number of shared buffers, I
> doubled it, from 1000 to 2000 (16Mb)
>
>                 Unfortunately this had no effect.

The difference between 8M and and 16M of shared buffers is pretty minor.
Try bumping it up to 250M or so and see if that helps.

You could install the pg_buffercache addon and monitor your buffer usage
to see how much is actually being used.

However, if the problem is write performance (which I'm inferring from your
message that it is) then increasing shared_buffers isn't liable to make a
significant improvement, unless the inserts are doing a lot of querying as
well.  With inserts, the speed is going to (most likely) be limited by the
speed of your disks.  I may have missed this information in earlier posts,
did you provide details of you hardware configuration?  Have you done tests
to find out what speed your disks are running?  Have you monitored IO
during your inserts to see if the IO subsystem is maxed out?

Also, the original problem you were trying to solve has been trimmed from
this thread, which makes me wonder if any of my advice is relevant.

>
>                  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
> <http://www.postgresql.org/docs/8.2/interactive/runtime-config-connection.ht
> ml#GUC-MAX-CONNECTIONS>  + max_prepared_transactions
> <http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html
> #GUC-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@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Shoaib Mir
> Sent: Monday, April 02, 2007 6:02 PM
> To: Sorin N. Ciolofan
> Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
> 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)
>
>


--
Bill Moran
http://www.potentialtech.com