Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4DF019FBABB; Thu, 12 Apr 2007 09:15:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 47225-05; Thu, 12 Apr 2007 09:14:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mailhost.ics.forth.gr (mailhost.ics.forth.gr [139.91.157.55]) by postgresql.org (Postfix) with ESMTP id B257C9FBAB3; Thu, 12 Apr 2007 09:15:01 -0300 (ADT) Received: from mailhost.ics.forth.gr (mailhost.ics.forth.gr [139.91.157.55] (may be forged)) by mailhost.ics.forth.gr (8.13.8/ICS-FORTH/V10-Server-2.0.0) with ESMTP id l3CCEv6l027906; Thu, 12 Apr 2007 15:14:57 +0300 Received: from sappho.ics.forth.gr (unknown [127.0.0.1]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id 7F95416C061; Thu, 12 Apr 2007 15:14:57 +0300 (EEST) X-AuditID: 7f000001-a8f9cbb00000050a-b0-461e22c0ed5c Received: from SHADOWFAX (unknown [139.91.183.33]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id B494C8E40FC; Thu, 12 Apr 2007 15:14:56 +0300 (EEST) From: "Sorin N. Ciolofan" To: "'Shoaib Mir'" Cc: , , "'Dimitris Kotzinos'" Subject: Re: Increasing the shared memory Date: Thu, 12 Apr 2007 15:15:45 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003D_01C77D15.71CF6170" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 thread-index: Acd1ODwVSJtO7VzhTDqnv8Tj3dp8RgHws/pQ X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 In-Reply-To: Message-Id: <20070412121456.B494C8E40FC@mailhost.ics.forth.gr> X-Brightmail-Tracker: AAAAAA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/119 X-Sequence-Number: 25183 This is a multi-part message in MIME format. ------=_NextPart_000_003D_01C77D15.71CF6170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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@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) ------=_NextPart_000_003D_01C77D15.71CF6170 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

   = ;            =     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=3D100 and max_prepared_transaction=3D5  (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<= /span>


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.powerpostgre= sql.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)=

------=_NextPart_000_003D_01C77D15.71CF6170--