From ciolofan@ics.forth.gr Mon Apr 2 09:27:31 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4FAD59FB518; Mon, 2 Apr 2007 06:27:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50314-01; Mon, 2 Apr 2007 06:27:27 -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 1EDE39FB1D6; Mon, 2 Apr 2007 06:27:26 -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 l329ROSZ015736; Mon, 2 Apr 2007 12:27:24 +0300 Received: from sappho.ics.forth.gr (unknown [127.0.0.1]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id AEFCB16C061; Mon, 2 Apr 2007 12:27:24 +0300 (EEST) X-AuditID: 7f000001-ac8e3bb00000050a-4c-4610cc7b61ce Received: from SHADOWFAX (unknown [139.91.183.33]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id CB2578E40FC; Mon, 2 Apr 2007 12:27:23 +0300 (EEST) From: "Sorin N. Ciolofan" To: Cc: Subject: Increasing the shared memory Date: Mon, 2 Apr 2007 12:28:08 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0011_01C77522.5EC7D1C0" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 thread-index: Acd1CTlbmPXvwY5XStupk/N7NiXSrw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Message-Id: <20070402092723.CB2578E40FC@mailhost.ics.forth.gr> X-Brightmail-Tracker: AAAAAA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/5 X-Sequence-Number: 25069 This is a multi-part message in MIME format. ------=_NextPart_000_0011_01C77522.5EC7D1C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello! I'd like to ask you if there is any Postgre configuration parameter (like the ones defined in postgresql.conf file) that could be used for increasing the shared memory for Postgre? Thank you very much With best regards, Sorin ------=_NextPart_000_0011_01C77522.5EC7D1C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

 Hello!

 

 I’d like to ask you if there is = any Postgre configuration parameter (like the ones defined in postgresql.conf file) = that could be used for increasing the shared memory for = Postgre?

 

Thank you very = much

With best = regards,

Sorin

------=_NextPart_000_0011_01C77522.5EC7D1C0-- From marius.roman@gmail.com Mon Apr 2 09:32:25 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6E8809FB671 for ; Mon, 2 Apr 2007 06:32:25 -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 82799-03 for ; Mon, 2 Apr 2007 06:32:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.247]) by postgresql.org (Postfix) with ESMTP id E2E4E9FA47A for ; Mon, 2 Apr 2007 06:32:19 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1201119ana for ; Mon, 02 Apr 2007 02:32:18 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=sOUYDiPhEc7mr9wmZaPT3nMtMBtWYdmMpKaLSfQhYQ+W3XEA9N8SRZPdjZOtIuXTa6N0mKgVGEU2rUN/kI+vOkYbpPJidnvZxfl7iKXh7Vbyx3nue6J1j34BUJYPq6wi1N86M6kQhXCdZd6Jal/6F4r+oKU/HsTsrJeCtAvDOms= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=er3iMPt1v0rMxxauiZmzkdsLKrUnp5TL8luWI15SgbEPM1OWcjSeTqJh750JY2iF7AHapnh3usfQH8Eos6aWDLtSNEblNhLBNoLkKC3tmHC4KphMDPjnSQYxDoUihVwEcMsfb4/+BxQ8Df8J0xeWLnp2JMrgQIRf6AlMvcs8TWk= Received: by 10.100.132.16 with SMTP id f16mr3332676and.1175506338625; Mon, 02 Apr 2007 02:32:18 -0700 (PDT) Received: by 10.100.247.12 with HTTP; Mon, 2 Apr 2007 02:32:18 -0700 (PDT) Message-ID: <477ea2690704020232u6e6fc80ev15531de3221b3afb@mail.gmail.com> Date: Mon, 2 Apr 2007 12:32:18 +0300 From: "Marius ROMAN" To: "Sorin N. Ciolofan" , pgsql-admin@postgresql.org Subject: Re: Increasing the shared memory In-Reply-To: <20070402092723.CB2578E40FC@mailhost.ics.forth.gr> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070402092723.CB2578E40FC@mailhost.ics.forth.gr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/6 X-Sequence-Number: 25070 http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html On 4/2/07, Sorin N. Ciolofan wrote: > > > > > > > Hello! > > > > I'd like to ask you if there is any Postgre configuration parameter (like > the ones defined in postgresql.conf file) that could be used for increasing > the shared memory for Postgre? > > > > Thank you very much > > With best regards, > > Sorin -- marius@{bsd.com.ro, joy2share.com, vrajamarii.net, ispot.ro} From shoaibmir@gmail.com Mon Apr 2 10:00:46 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A31179FB5A0 for ; Mon, 2 Apr 2007 07:00:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 11000-05 for ; Mon, 2 Apr 2007 07:00:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.251]) by postgresql.org (Postfix) with ESMTP id A3C659FA5F7 for ; Mon, 2 Apr 2007 07:00:41 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1207347ana for ; Mon, 02 Apr 2007 03:00:40 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=SBaBieqjOtaeNH6mWEb+Urhj9AnbaCh58LmWFMsbLof/tLrKx6OKTLWXk7TIb+toJVMsmizMgTO3f91aNzRT3SlizLHu9JdvG34AVODU2GT8a4w0kM//q3Lfik5oNpRwPHVOC/v3emHSm07UBoVJcv4zVdE6Lv3+fyFTO4HvEvk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=EW9bLAdfLiezC8EVZex8Awq7waqgMgK7+zBk11ZedG3y0W8PA4DhMJyjfYFRN4t1L16HRdlnoHjzdKp2MwRoBLQw20vNNdeYjpK07FcOGxI/PoGYaWBAJ3BpJHzJ8D++I+SQ6zwPLIGRUtIbelhhYO9WxWUH3CZgooFCNqeWhpk= Received: by 10.100.32.1 with SMTP id f1mr3345602anf.1175508040400; Mon, 02 Apr 2007 03:00:40 -0700 (PDT) Received: by 10.100.14.1 with HTTP; Mon, 2 Apr 2007 03:00:40 -0700 (PDT) Message-ID: Date: Mon, 2 Apr 2007 15:00:40 +0500 From: "Shoaib Mir" To: "Sorin N. Ciolofan" Subject: Re: Increasing the shared memory Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <20070402092723.CB2578E40FC@mailhost.ics.forth.gr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_83619_21115058.1175508040355" References: <20070402092723.CB2578E40FC@mailhost.ics.forth.gr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/9 X-Sequence-Number: 25073 ------=_Part_83619_21115058.1175508040355 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I guess shared_buffers (in postgresql.conf file) will help you here if you have properly setup your kernel.SHMMAX value. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 4/2/07, Sorin N. Ciolofan wrote: > > > > Hello! > > > > I'd like to ask you if there is any Postgre configuration parameter (like > the ones defined in postgresql.conf file) that could be used for > increasing the shared memory for Postgre? > > > > Thank you very much > > With best regards, > > Sorin > ------=_Part_83619_21115058.1175508040355 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I guess shared_buffers (in postgresql.conf file) will help you here if you have properly setup your kernel.SHMMAX value.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )

On 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> wrote:

 

 Hello!

 

 I'd like to ask you if there is any Postgre configuration parameter (like the ones defined in postgresql.conf file) that could be used for increasing the shared memory for Postgre?

 

Thank you very much

With best regards,

Sorin


------=_Part_83619_21115058.1175508040355-- From ciolofan@ics.forth.gr Mon Apr 2 14:51:46 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2D9B89FA5FA; Mon, 2 Apr 2007 11:51:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 87000-03-2; Mon, 2 Apr 2007 11:51:24 -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 263519FA521; Mon, 2 Apr 2007 11:51:35 -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 l32EpW63016534; Mon, 2 Apr 2007 17:51:32 +0300 Received: from sappho.ics.forth.gr (unknown [127.0.0.1]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id 3414B16C061; Mon, 2 Apr 2007 17:51:32 +0300 (EEST) X-AuditID: 7f000001-ac8e3bb00000050a-d4-46111873f553 Received: from SHADOWFAX (unknown [139.91.183.33]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id 4D83A8E40FC; Mon, 2 Apr 2007 17:51:31 +0300 (EEST) From: "Sorin N. Ciolofan" To: "'Shoaib Mir'" Cc: , Subject: Re: Increasing the shared memory Date: Mon, 2 Apr 2007 17:52:15 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0073_01C7754F.A670B960" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 thread-index: Acd1DdYTjBIPJwQkRZGPd9A1KjHqywAJ/BmQ X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 In-Reply-To: Message-Id: <20070402145131.4D83A8E40FC@mailhost.ics.forth.gr> X-Brightmail-Tracker: AAAAAA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/13 X-Sequence-Number: 25077 This is a multi-part message in MIME format. ------=_NextPart_000_0073_01C7754F.A670B960 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thanks, I've a value of 1000 set for shared_buffers, does this means that I use 8kbX1000=8Mb of Shared Mem? The definition from the manual is quite confusing: shared_buffers (integer) Sets the amount of memory the database server uses for shared memory buffers. The default is typically 32 megabytes (32MB), but may be less if your kernel settings will not support it (as determined during initdb). This setting must be at least 128 kilobytes and at least 16 kilobytes times max_connections . What does the integer number represent? Number of shared buffers? If yes, what size does each shared buffer have? "The default is typically 32 megabytes" suggests that this integer could also represent the number of megabytes?!? In the postgresql.conf file is an ambiguous comment that could induce the idea that each shared buffer has 8 kb. So, which is the meaning of this integer? Thanks. S. _____ From: Shoaib Mir [mailto:shoaibmir@gmail.com] Sent: Monday, April 02, 2007 1:01 PM To: Sorin N. Ciolofan Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Increasing the shared memory I guess shared_buffers (in postgresql.conf file) will help you here if you have properly setup your kernel.SHMMAX value. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com ) On 4/2/07, Sorin N. Ciolofan wrote: Hello! I'd like to ask you if there is any Postgre configuration parameter (like the ones defined in postgresql.conf file) that could be used for increasing the shared memory for Postgre? Thank you very much With best regards, Sorin ------=_NextPart_000_0073_01C7754F.A670B960 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

   = ;          Thanks,

 =

 =

   = ;         I’ve a value of 1000 set for = shared_buffers, does this means that I use 8kbX1000=3D8Mb of Shared = Mem?

   = ;        

The definition = from the manual is quite confusing:

 =

shared_buffers (integer)

Sets the amount of memory the = database server uses for shared memory buffers. The default is typically 32 megabytes = (32MB), but may be less if your kernel settings will not support it = (as determined during initdb). This setting = must be at least 128 kilobytes and at least 16 kilobytes times max_connections.

 =

What does the = integer number represent? Number of shared buffers? If yes, what size does each = shared buffer have?

“The default is typically 32 megabytes” suggests that this integer could also represent the = number of megabytes?!?

In the = postgresql.conf file is an ambiguous comment that could induce the idea that each shared = buffer has 8 kb.

So, which is the = meaning of this integer?

 =

Thanks.

S.

 =


From: Shoaib Mir [mailto:shoaibmir@gmail.com]
Sent: Monday, April 02, = 2007 1:01 PM
To: Sorin N. Ciolofan
Cc: = pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] = Increasing the shared memory

 

I guess shared_buffers (in postgresql.conf = file) will help you here if you have properly setup your kernel.SHMMAX value.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com = )

On = 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> = wrote:

 

 Hello!

 

 I'd like to ask you = if there is any Postgre configuration parameter (like the ones defined in postgresql.conf file) that could be used for increasing the shared = memory for Postgre?

 

Thank you very = much

With best = regards,

Sorin

 

------=_NextPart_000_0073_01C7754F.A670B960-- From shoaibmir@gmail.com Mon Apr 2 15:02:03 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 381C39FB408 for ; Mon, 2 Apr 2007 12:02:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99606-05 for ; Mon, 2 Apr 2007 12:01:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id 8B5B79FB588 for ; Mon, 2 Apr 2007 12:01:51 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1291581ana for ; Mon, 02 Apr 2007 08:01:50 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=BMx90szqZ8VSYZlWFYNEETiFAvMMqtG5fbNRRnCsFy/R9gMtQagCRJYZ6dl6qcUksK4T1l/x4wGhXElDu3ZLbDtzrh1TBiEzNCLulPDo1h3NJlrgykopWGlwUrvC1yZbVn0YpMmO+jL2Pu0/h+6QD7yuj/5iQwoGNonWJwCOFS0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WXJjfG0jnDSAlmwWF/pbCCb+HzAmLPDMCezt+G2rcM7QxGRa4uj+/fPZbopp6+d87yt0aax8R4M/ZCOyaa7qAPlN7CDtl2rf6Kv1+TS0ykqJy28rnF/UDM/dtjc5ibvtmcDnRUrJBmNOTiV3EVXG0h4XcNsuqtoNdZQt0iafBSI= Received: by 10.100.197.15 with SMTP id u15mr3535605anf.1175526110618; Mon, 02 Apr 2007 08:01:50 -0700 (PDT) Received: by 10.100.14.1 with HTTP; Mon, 2 Apr 2007 08:01:50 -0700 (PDT) Message-ID: Date: Mon, 2 Apr 2007 20:01:50 +0500 From: "Shoaib Mir" To: "Sorin N. Ciolofan" Subject: Re: Increasing the shared memory Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <20070402145131.4D83A8E40FC@mailhost.ics.forth.gr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_87573_6044492.1175526110574" References: <20070402145131.4D83A8E40FC@mailhost.ics.forth.gr> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/14 X-Sequence-Number: 25078 ------=_Part_87573_6044492.1175526110574 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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) On 4/2/07, Sorin N. Ciolofan wrote: > > Thanks, > > > > > > I've a value of 1000 set for shared_buffers, does this means > that I use 8kbX1000=8Mb of Shared Mem? > > > > The definition from the manual is quite confusing: > > > > shared_buffers (integer) > > Sets the amount of memory the database server uses for shared memory > buffers. The default is typically 32 megabytes (32MB), but may be less if > your kernel settings will not support it (as determined during initdb). > This setting must be at least 128 kilobytes and at least 16 kilobytes times > max_connections. > > > > > What does the integer number represent? Number of shared buffers? If yes, > what size does each shared buffer have? > > "The default is typically 32 megabytes" suggests that this integer could > also represent the number of megabytes?!? > > In the postgresql.conf file is an ambiguous comment that could induce the > idea that each shared buffer has 8 kb. > > So, which is the meaning of this integer? > > > > Thanks. > > S. > > > ------------------------------ > > *From:* Shoaib Mir [mailto:shoaibmir@gmail.com] > *Sent:* Monday, April 02, 2007 1:01 PM > *To:* Sorin N. Ciolofan > *Cc:* pgsql-general@postgresql.org; pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] Increasing the shared memory > > > > I guess shared_buffers (in postgresql.conf file) will help you here if you > have properly setup your kernel.SHMMAX value. > > -- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com ) > > On 4/2/07, *Sorin N. Ciolofan* wrote: > > > > Hello! > > > > I'd like to ask you if there is any Postgre configuration parameter (like > the ones defined in postgresql.conf file) that could be used for > increasing the shared memory for Postgre? > > > > Thank you very much > > With best regards, > > Sorin > > > ------=_Part_87573_6044492.1175526110574 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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)

On 4/2/07, Sorin N. Ciolofan <ciolofan@ics.forth.gr> wrote:

             Thanks,

 

 

            I've a value of 1000 set for shared_buffers, does this means that I use 8kbX1000=8Mb of Shared Mem?

           

The definition from the manual is quite confusing:

 

shared_buffers (integer )

Sets the amount of memory the database server uses for shared memory buffers. The default is typically 32 megabytes (32MB), but may be less if your kernel settings will not support it (as determined during initdb). This setting must be at least 128 kilobytes and at least 16 kilobytes times max_connections.

 

What does the integer number represent? Number of shared buffers? If yes, what size does each shared buffer have?

"The default is typically 32 megabytes " suggests that this integer could also represent the number of megabytes?!?

In the postgresql.conf file is an ambiguous comment that could induce the idea that each shared buffer has 8 kb.

So, which is the meaning of this integer?

 

Thanks.

S.

 


From: Shoaib Mir [mailto:shoaibmir@gmail.com]
Sent: Monday, April 02, 2007 1:01 PM
To: Sorin N. Ciolofan
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Increasing the shared memory

 

I guess shared_buffers (in postgresql.conf file) will help you here if you have properly setup your kernel.SHMMAX value.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )

On 4/2/07, Sorin N. Ciolofan < ciolofan@ics.forth.gr> wrote:

 

 Hello!

 

 I'd like to ask you if there is any Postgre configuration parameter (like the ones defined in postgresql.conf file) that could be used for increasing the shared memory for Postgre?

 

Thank you very much

With best regards,

Sorin

 


------=_Part_87573_6044492.1175526110574-- From wmoran@potentialtech.com Mon Apr 2 15:32:42 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A0E8D9FB2F5; Mon, 2 Apr 2007 12:32:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 93155-04-4; Mon, 2 Apr 2007 12:32:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.potentialtech.com (internet.potentialtech.com [66.167.251.6]) by postgresql.org (Postfix) with ESMTP id AEA859FB7BE; Mon, 2 Apr 2007 12:32:24 -0300 (ADT) Received: from vanquish.pgh.priv.collaborativefusion.com (pr40.pitbpa0.pub.collaborativefusion.com [206.210.89.202]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.potentialtech.com (Postfix) with ESMTP id A1A48EBC6D; Mon, 2 Apr 2007 11:32:23 -0400 (EDT) Date: Mon, 2 Apr 2007 11:32:22 -0400 From: Bill Moran To: "Sorin N. Ciolofan" Cc: "'Shoaib Mir'" , , Subject: Re: [GENERAL] Increasing the shared memory Message-Id: <20070402113222.9415258c.wmoran@potentialtech.com> In-Reply-To: <20070402145131.4D83A8E40FC@mailhost.ics.forth.gr> References: <20070402145131.4D83A8E40FC@mailhost.ics.forth.gr> X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/16 X-Sequence-Number: 25080 In response to "Sorin N. Ciolofan" : > > I've a value of 1000 set for shared_buffers, does this means > that I use 8kbX1000=8Mb of Shared Mem? > > > > The definition from the manual is quite confusing: > > > > shared_buffers (integer) > > Sets the amount of memory the database server uses for shared memory > buffers. The default is typically 32 megabytes (32MB), but may be less if > your kernel settings will not support it (as determined during initdb). This > setting must be at least 128 kilobytes and at least 16 kilobytes times > max_connections > l#GUC-MAX-CONNECTIONS> . > > > > What does the integer number represent? Number of shared buffers? If yes, > what size does each shared buffer have? > > "The default is typically 32 megabytes" suggests that this integer could > also represent the number of megabytes?!? > > In the postgresql.conf file is an ambiguous comment that could induce the > idea that each shared buffer has 8 kb. > > So, which is the meaning of this integer? Older versions of PostgreSQL, the number was the _number_ of shared buffers. Each buffer is typically 8K, but this can be altered at compile time (I believe) and the PGDG has no way to know for sure if whatever packaged version you installed might have done so. However, on more recent versions of Postgres (although I don't know exactly what version first included this) you can specify this value as "M" or "G" to specify a number of megabytes or gigabytes. Internally, this is still converted to a number of 8K buffers, but it makes the config file easier to read and understand. -- Bill Moran http://www.potentialtech.com From dbrain@bandwidth.com Mon Apr 2 16:01:48 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 4945A9FB7D8 for ; Mon, 2 Apr 2007 13:01:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 14750-04 for ; Mon, 2 Apr 2007 13:01:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx1.bandwidth.com (nat0.bandwidth.com [216.27.14.90]) by postgresql.org (Postfix) with SMTP id 6A93C9FB7C2 for ; Mon, 2 Apr 2007 13:01:33 -0300 (ADT) Received: (qmail 27125 invoked by uid 1008); 2 Apr 2007 16:01:44 -0000 Received: from dbrain@bandwidth.com by mx1 by uid 1002 with qmail-scanner-1.20 (spamassassin: 2.63. Clear:RC:1(10.1.53.112):. Processed in 0.018309 secs); 02 Apr 2007 16:01:44 -0000 Received: from unknown (HELO ?10.1.53.112?) (10.1.53.112) by mx1.bandwidth.com with SMTP; 2 Apr 2007 16:01:44 -0000 Message-ID: <46112821.8010006@bandwidth.com> Date: Mon, 02 Apr 2007 11:58:25 -0400 From: David Brain User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: pgsql-general@postgresql.org CC: pgsql-admin@postgresql.org Subject: Re: [GENERAL] Increasing the shared memory References: <20070402145131.4D83A8E40FC@mailhost.ics.forth.gr> <20070402113222.9415258c.wmoran@potentialtech.com> In-Reply-To: <20070402113222.9415258c.wmoran@potentialtech.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/17 X-Sequence-Number: 25081 There is also a add on in contrib (pg_buffercache) that can be used to give an indication of the number of buffers in use, this can be used to help find a 'good' shared mem size for your configuration. David. From ciolofan@ics.forth.gr Thu Apr 12 12:15:12 2007 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-- From wmoran@potentialtech.com Thu Apr 12 13:13:52 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 43FA99FB915; Thu, 12 Apr 2007 10:13:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 57989-03; Thu, 12 Apr 2007 10:13:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.potentialtech.com (internet.potentialtech.com [66.167.251.6]) by postgresql.org (Postfix) with ESMTP id DC6789FA312; Thu, 12 Apr 2007 10:13:39 -0300 (ADT) Received: from vanquish.pgh.priv.collaborativefusion.com (pr40.pitbpa0.pub.collaborativefusion.com [206.210.89.202]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.potentialtech.com (Postfix) with ESMTP id BEA3CEBC76; Thu, 12 Apr 2007 09:13:37 -0400 (EDT) Date: Thu, 12 Apr 2007 09:13:36 -0400 From: Bill Moran To: "Sorin N. Ciolofan" Cc: "'Shoaib Mir'" , , , "'Dimitris Kotzinos'" Subject: Re: [GENERAL] Increasing the shared memory Message-Id: <20070412091336.aa6cc58e.wmoran@potentialtech.com> In-Reply-To: <20070412121456.B494C8E40FC@mailhost.ics.forth.gr> References: <20070412121456.B494C8E40FC@mailhost.ics.forth.gr> X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/120 X-Sequence-Number: 25184 In response to "Sorin N. Ciolofan" : > 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 > ml#GUC-MAX-CONNECTIONS> + max_prepared_transactions > #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 From tgl@sss.pgh.pa.us Thu Apr 12 14:01:54 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5DCBF9FB3E0; Thu, 12 Apr 2007 11:01:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64073-02; Thu, 12 Apr 2007 11:01:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 7F95B9FA1A1; Thu, 12 Apr 2007 11:01:41 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l3CE15fL019447; Thu, 12 Apr 2007 10:01:05 -0400 (EDT) To: "Sorin N. Ciolofan" cc: "'Shoaib Mir'" , pgsql-general@postgresql.org, pgsql-admin@postgresql.org, "'Dimitris Kotzinos'" Subject: Re: Increasing the shared memory In-reply-to: <20070412121456.B494C8E40FC@mailhost.ics.forth.gr> References: <20070412121456.B494C8E40FC@mailhost.ics.forth.gr> Comments: In-reply-to "Sorin N. Ciolofan" message dated "Thu, 12 Apr 2007 15:15:45 +0300" Date: Thu, 12 Apr 2007 10:01:05 -0400 Message-ID: <19446.1176386465@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/121 X-Sequence-Number: 25185 "Sorin N. Ciolofan" writes: > 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. This is the first you've mentioned about *why* you wanted to increase the settings, and what it sounds like to me is that you are increasing the wrong thing. What's the actual problem? regards, tom lane From ciolofan@ics.forth.gr Fri Apr 13 08:12:45 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 164839FB59D; Fri, 13 Apr 2007 05:12:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 53815-03; Fri, 13 Apr 2007 05:12:37 -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 85BBC9FB5D0; Fri, 13 Apr 2007 05:12:39 -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 l3D8CZRp024008; Fri, 13 Apr 2007 11:12:35 +0300 Received: from sappho.ics.forth.gr (unknown [127.0.0.1]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id A375716C061; Fri, 13 Apr 2007 11:12:35 +0300 (EEST) X-AuditID: 7f000001-a8f9cbb00000050a-74-461f3b734974 Received: from SHADOWFAX (unknown [139.91.183.33]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id B59698E40FC; Fri, 13 Apr 2007 11:12:34 +0300 (EEST) From: "Sorin N. Ciolofan" To: "'Tom Lane'" Cc: "'Shoaib Mir'" , , , "'Dimitris Kotzinos'" Subject: Re: Increasing the shared memory Date: Fri, 13 Apr 2007 11:13:24 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 thread-index: Acd9CxcosNwBOthLTKirkIUmOD6d4gAlgA2Q X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 In-Reply-To: <19446.1176386465@sss.pgh.pa.us> Message-Id: <20070413081234.B59698E40FC@mailhost.ics.forth.gr> X-Brightmail-Tracker: AAAAAA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/128 X-Sequence-Number: 25192 I will simplify the things in order to describe when the error occurred: The input of the application is some data which is read from files on disk, processed and then inserted in the database in one transaction. This total quantity of data represents an integer number of data files, n*q, where q is a file which has always 60kb and n is the positive integer. For n=23 and shared_buffers=1000 and max_locks_per_transaction=64 the Postgres throws the following exception: org.postgresql.util.PSQLException: ERROR: out of shared memory at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI mpl.java:1525) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja va:1309) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j ava:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St atement.java:340) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State ment.java:286) at gr.forth.ics.rdfsuite.rssdb.repr.SSRepresentation.createClassTable(SSReprese ntation.java:1936) at gr.forth.ics.rdfsuite.rssdb.repr.SSRepresentation.store(SSRepresentation.jav a:1783) at gr.forth.ics.rdfsuite.swkm.model.db.impl.RDFDB_Model.storeSchema(RDFDB_Model .java:814) at gr.forth.ics.rdfsuite.swkm.model.db.impl.RDFDB_Model.store(RDFDB_Model.java: 525) at gr.forth.ics.rdfsuite.services.impl.ImporterImpl.storeImpl(ImporterImpl.java :79) ... 50 more For n=23 I estimated that we create and manipulate about 8000 tables. One of the suggestion received here was that maybe there are not sufficient locks slots per transaction, that's why I've increased the max_locks_per_transaction (to 128) in order to be able to manipulate about 12 800 tables. So, I doubled both shared_buffers and max_locks_per_transaction and for n=23 I received the same error. I would expect to see a difference, even a little one, for example from n=23 to n=24 but the maximum quantity of data accepted was the same. Thank you very much, With best regards Sorin -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thursday, April 12, 2007 5:01 PM To: Sorin N. Ciolofan Cc: 'Shoaib Mir'; pgsql-general@postgresql.org; pgsql-admin@postgresql.org; 'Dimitris Kotzinos' Subject: Re: [ADMIN] Increasing the shared memory "Sorin N. Ciolofan" writes: > 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. This is the first you've mentioned about *why* you wanted to increase the settings, and what it sounds like to me is that you are increasing the wrong thing. What's the actual problem? regards, tom lane From tgl@sss.pgh.pa.us Fri Apr 13 14:13:37 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 37AF19FBD08; Fri, 13 Apr 2007 11:13:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 10433-02; Fri, 13 Apr 2007 11:13:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 816079FBDB3; Fri, 13 Apr 2007 11:09:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l3DE8TKe018738; Fri, 13 Apr 2007 10:08:31 -0400 (EDT) To: "Sorin N. Ciolofan" cc: "'Shoaib Mir'" , pgsql-general@postgresql.org, pgsql-admin@postgresql.org, "'Dimitris Kotzinos'" Subject: Re: [GENERAL] Increasing the shared memory In-reply-to: <20070413081234.B59698E40FC@mailhost.ics.forth.gr> References: <20070413081234.B59698E40FC@mailhost.ics.forth.gr> Comments: In-reply-to "Sorin N. Ciolofan" message dated "Fri, 13 Apr 2007 11:13:24 +0300" Date: Fri, 13 Apr 2007 10:08:28 -0400 Message-ID: <18737.1176473308@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/130 X-Sequence-Number: 25194 "Sorin N. Ciolofan" writes: > I will simplify the things in order to describe when the error occurred: > The input of the application is some data which is read from files on disk, > processed and then inserted in the database in one transaction. This total > quantity of data represents an integer number of data files, n*q, where q is > a file which has always 60kb and n is the positive integer. > For n=23 and shared_buffers=1000 and max_locks_per_transaction=64 the > Postgres throws the following exception: > org.postgresql.util.PSQLException: ERROR: out of shared memory > For n=23 I estimated that we create and manipulate about 8000 tables. Okay, as far as I know the only user-causable way to get that message is to run out of lock-table space, and a transaction does take a lock for each table it touches, so I concur that raising max_locks_per_transaction is an appropriate response. If you didn't see any change in the maximum N you could handle then I wonder whether you actually did raise it --- does "show max_locks_per_transaction" reflect the intended new value? Another possibility is that there's something about your code that makes the number of locks involved very nonlinear in N. You could try checking the number of rows in pg_locks immediately before commit at some smaller values of N to confirm what the scaling is really like. regards, tom lane From ciolofan@ics.forth.gr Wed Apr 18 13:47:49 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 032489FB2EB; Wed, 18 Apr 2007 10:47:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 76596-01; Wed, 18 Apr 2007 10:47:36 -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 236E89FB2BA; Wed, 18 Apr 2007 10:47:40 -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 l3IDlcZJ019151; Wed, 18 Apr 2007 16:47:38 +0300 Received: from sappho.ics.forth.gr (unknown [127.0.0.1]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id 0823C16C061; Wed, 18 Apr 2007 16:47:38 +0300 (EEST) X-AuditID: 7f000001-aa39ebb00000050a-e7-46262179039e Received: from SHADOWFAX (unknown [139.91.183.33]) by mailhost.ics.forth.gr (Symantec Mail Security) with ESMTP id 894508E40FC; Wed, 18 Apr 2007 16:47:37 +0300 (EEST) From: "Sorin N. Ciolofan" To: "'Bill Moran'" Cc: , Subject: Re: [GENERAL] Increasing the shared memory Date: Wed, 18 Apr 2007 16:48:29 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 thread-index: Acd9BJBwheqr9wALRnCh66YzhWCodwEt92pg X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 In-Reply-To: <20070412091336.aa6cc58e.wmoran@potentialtech.com> Message-Id: <20070418134737.894508E40FC@mailhost.ics.forth.gr> X-Brightmail-Tracker: AAAAAA== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/171 X-Sequence-Number: 25235 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bill Moran Sent: Thursday, April 12, 2007 4:14 PM To: Sorin N. Ciolofan Cc: 'Shoaib Mir'; pgsql-general@postgresql.org; pgsql-admin@postgresql.org; 'Dimitris Kotzinos' Subject: Re: [GENERAL] [ADMIN] Increasing the shared memory In response to "Sorin N. Ciolofan" : > 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 > ml#GUC-MAX-CONNECTIONS> + max_prepared_transactions > #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. > From wmoran@potentialtech.com Wed Apr 18 13:54:29 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A4A789FB352; Wed, 18 Apr 2007 10:54:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 79567-03; Wed, 18 Apr 2007 10:54:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.potentialtech.com (internet.potentialtech.com [66.167.251.6]) by postgresql.org (Postfix) with ESMTP id 1661C9FB2EB; Wed, 18 Apr 2007 10:54:22 -0300 (ADT) Received: from vanquish.pgh.priv.collaborativefusion.com (pr40.pitbpa0.pub.collaborativefusion.com [206.210.89.202]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.potentialtech.com (Postfix) with ESMTP id 39E7FEBC78; Wed, 18 Apr 2007 09:54:20 -0400 (EDT) Date: Wed, 18 Apr 2007 09:54:19 -0400 From: Bill Moran To: "Sorin N. Ciolofan" Cc: , Subject: Re: [GENERAL] Increasing the shared memory Message-Id: <20070418095419.169bc6cc.wmoran@potentialtech.com> In-Reply-To: <20070418134737.894508E40FC@mailhost.ics.forth.gr> References: <20070412091336.aa6cc58e.wmoran@potentialtech.com> <20070418134737.894508E40FC@mailhost.ics.forth.gr> X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/172 X-Sequence-Number: 25236 In response to "Sorin N. Ciolofan" : > > 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? It's part of the contrib directory that ships with the source tarball. Depending on your OS and associated packaging system (which you don't bother to mention) it's probably available via RPMs or DEBs or whatever. For example, under FreeBSD it's in /usr/ports/databases/postgresql-contrib -- Bill Moran http://www.potentialtech.com