pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedShared Memory: out of memory
4+ messages / 4 participants
[nested] [flat]
* Shared Memory: out of memory
@ 2000-12-19 14:51 Alexaki Sofia <alexaki@ics.forth.gr>
0 siblings, 2 replies; 4+ messages in thread
From: Alexaki Sofia @ 2000-12-19 14:51 UTC (permalink / raw)
To: pgsql-interfaces
Hello,
I'm working with the JDBC driver from the Postgresql 7.0.2 distribution.
My application creates transactions consisting of about 3000
updates operations ('insert into table' and 'create table').
My database consists of a huge (200000) number of tables which
form a hierarchy.
The postmaster throws the following error massage
>NOTICE: ShmemAlloc: out of memory
>FATAL 1: LockAcquire: lock table 1 is corrupted
What can have caused this error? How can I overcome it?
Should I define the parameter -B of the postmaster?
Thank you in advance
Sofia Alexaki
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Shared Memory: out of memory
@ 2000-12-20 02:41 Joseph Shraibman <jks@selectacast.net>
parent: Alexaki Sofia <alexaki@ics.forth.gr>
1 sibling, 1 reply; 4+ messages in thread
From: Joseph Shraibman @ 2000-12-20 02:41 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; +Cc: pgsql-interfaces
200000 tables is rather a lot. As I'm sure someone on hackers will tell
you, postgres is not designed to handle such bad database design.
Alexaki Sofia wrote:
>
> Hello,
>
> I'm working with the JDBC driver from the Postgresql 7.0.2 distribution.
>
> My application creates transactions consisting of about 3000
> updates operations ('insert into table' and 'create table').
> My database consists of a huge (200000) number of tables which
> form a hierarchy.
>
> The postmaster throws the following error massage
>
> >NOTICE: ShmemAlloc: out of memory
> >FATAL 1: LockAcquire: lock table 1 is corrupted
>
> What can have caused this error? How can I overcome it?
> Should I define the parameter -B of the postmaster?
>
> Thank you in advance
> Sofia Alexaki
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Shared Memory: out of memory
@ 2000-12-20 19:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexaki Sofia <alexaki@ics.forth.gr>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2000-12-20 19:09 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; +Cc: pgsql-interfaces
Alexaki Sofia <alexaki@ics.forth.gr> writes:
> My application creates transactions consisting of about 3000
> updates operations ('insert into table' and 'create table').
> My database consists of a huge (200000) number of tables which
> form a hierarchy.
> The postmaster throws the following error massage
>> NOTICE: ShmemAlloc: out of memory
>> FATAL 1: LockAcquire: lock table 1 is corrupted
You could try increasing the NLOCKS_PER_XACT space-estimation parameter
in src/include/storage/lock.h. However, I'd advise rethinking your
database design instead. 200000 tables will incur all sorts of
performance penalties, starting with long Unix directory search times.
You'll be much better off if you put the same data into fewer tables
(probably with more columns).
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Shared Memory: out of memory
@ 2001-01-03 13:33 Cedar Cox <cedarc@visionforisrael.com>
parent: Joseph Shraibman <jks@selectacast.net>
0 siblings, 0 replies; 4+ messages in thread
From: Cedar Cox @ 2001-01-03 13:33 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; +Cc: pgsql-interfaces
(Just getting caught up on all my emails..)
Yes, you might want to, if it's not too late already, rethink your
database design. I'm guessing that these tables are generated by code and
almost all of them are probably identical. Am I correct? If so, you
might be able to do it the way we did, nicknamed STH for Single Table
Hierarchy. This design merged about eight tables all into one and
basically allows us unlimited depth in the hierarchy. It's quite simple
really, just add a ParentID field to your table. This is how we do it:
CREATE TABLE tblStResTree (
TreeID int4 NOT NULL CHECK (TreeID>0),
Name character text NOT NULL CHECK (Name<>''),
Description text,
ParentID int4 NOT NULL,
SurID text,
PRIMARY KEY (TreeID)
);
Simplified of course.. there's actually eight other fields we use. Now,
you may or may not want to have a ID 0 as root. Queries look slightly
strange, but you'll get the hang of it. If you wanted to view the
parent's name instead of it's id, do this:
SELECT child.TreeID, child.Name, child.Description, parent.Name
FROM tblStResTree as child, tblStResTree as parent
WHERE child.ParentID=parent.TreeID;
Because it's a hierarchy, all you have to do to move an entire section of
your tree is change the ParentID. SurID is sort of a numeric PATH
generated by a trigger, so instead of 'var/spool/mail/cedarc' you would
have something like '8.29.41.132', where the numbers are the TreeID's of
all ancestors of the record. You can use this for all sorts of cool
things like finding all descendents:
SELECT ... WHERE SurID LIKE '<my surid>.%'
We use this for doing inheritance where a value in a record higher up the
hierarchy will propagate down to all it's descendents if that field is
null in the descendents. Very useful for us..
Hope this helps,
-Cedar
On Tue, 19 Dec 2000, Joseph Shraibman wrote:
> 200000 tables is rather a lot. As I'm sure someone on hackers will tell
> you, postgres is not designed to handle such bad database design.
>
> Alexaki Sofia wrote:
> >
> > Hello,
> >
> > I'm working with the JDBC driver from the Postgresql 7.0.2 distribution.
> >
> > My application creates transactions consisting of about 3000
> > updates operations ('insert into table' and 'create table').
> > My database consists of a huge (200000) number of tables which
> > form a hierarchy.
> >
> > The postmaster throws the following error massage
> >
> > >NOTICE: ShmemAlloc: out of memory
> > >FATAL 1: LockAcquire: lock table 1 is corrupted
> >
> > What can have caused this error? How can I overcome it?
> > Should I define the parameter -B of the postmaster?
> >
> > Thank you in advance
> > Sofia Alexaki
>
> --
> Joseph Shraibman
> jks@selectacast.net
> Increase signal to noise ratio. http://www.targabot.com
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-01-03 13:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-19 14:51 Shared Memory: out of memory Alexaki Sofia <alexaki@ics.forth.gr>
2000-12-20 02:41 ` Joseph Shraibman <jks@selectacast.net>
2001-01-03 13:33 ` Cedar Cox <cedarc@visionforisrael.com>
2000-12-20 19:09 ` Tom Lane <tgl@sss.pgh.pa.us>
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