Re: [GENERAL] Increasing the shared memory

Dear all,

Thanks for your advices. I'd like to ask you where can I download the
pg_buffercache add-on and also where can I find some documentation about how
can I install it?

Thank you
Sorin
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bill Moran
Sent: Thursday, April 12, 2007 4:14 PM
To: Sorin N. Ciolofan
Cc: 'Shoaib Mir'; pgsql-general(at)postgresql(dot)org; pgsql-admin(at)postgresql(dot)org;
'Dimitris Kotzinos'
Subject: Re: [GENERAL] [ADMIN] Increasing the shared memory

In response to "Sorin N. Ciolofan" <ciolofan(at)ics(dot)forth(dot)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.
>