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