pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sorin N. Ciolofan <ciolofan@ics.forth.gr>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Shoaib Mir' <shoaibmir@gmail.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Cc: 'Dimitris Kotzinos' <kotzino@ics.forth.gr>
Subject: Re: Increasing the shared memory
Date: Fri, 13 Apr 2007 11:13:24 +0300
Message-ID: <20070413081234.B59698E40FC@mailhost.ics.forth.gr> (raw)
In-Reply-To: <19446.1176386465@sss.pgh.pa.us>


 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" <ciolofan@ics.forth.gr> 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





view thread (14+ messages)  latest in thread

Message-ID: <20070413081234.B59698E40FC@mailhost.ics.forth.gr>
Permalink:  ../20070413081234.B59698E40FC@mailhost.ics.forth.gr/
Also on:    postgresql.org/message-id/20070413081234.B59698E40FC@mailhost.ics.forth.gr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: ciolofan@ics.forth.gr, tgl@sss.pgh.pa.us, shoaibmir@gmail.com, pgsql-general@postgresql.org, kotzino@ics.forth.gr
  Subject: Re: Increasing the shared memory
  In-Reply-To: <20070413081234.B59698E40FC@mailhost.ics.forth.gr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox