Обсуждение: PostgreSQL unsuitable for large datasets?

    We have a PostgreSQL (7.2.1) database on cygwin with one table that
contains some 2 x 10**6 records.  The database is about 100 MB total.  This
does not seem so large, but while attempting to do an insert into an empty
table using a select that joins to the large table on all the rows the
error,

        Memory exhausted in AllocSetAlloc(36)

occurs.  Reviewing posts on this subject as well as the PostgreSQL FAQ did
not reveal any specific solution.  ulimit -a:

$ ulimit -a
core file size        (blocks, -c) unlimited
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
open files                    (-n) 256
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 2046
cpu time             (seconds, -t) unlimited
max user processes            (-u) 63
virtual memory        (kbytes, -v) 2097152

Virtual memory would appear to be adequate.  Increasing the SORT_MEM
parameter in the postgresql.conf file did not help.  Finally had to
partition the large table into 10 parts and do each individually.

    Seems like this problem would need to have a ready and definitive
solution before PostgreSQL could be used for large, serious databases.  How
large are the databases with which others are using PostgreSQL?  How do
other avoid this error with large datasets?

-- Michael

Michael,

On Sat, Aug 31, 2002 at 11:48:19AM -0700, Michael Labhard wrote:
> We have a PostgreSQL (7.2.1) database on cygwin with one table that
> contains some 2 x 10**6 records.  The database is about 100 MB total.
> This does not seem so large, but while attempting to do an insert into
> an empty table using a select that joins to the large table on all the
> rows the error,
>
>         Memory exhausted in AllocSetAlloc(36)

Does the following help?

    http://sources.redhat.com/ml/cygwin/2000-04/msg00267.html

Jason

Michael,

Please keep your replies on-list.

On Sun, Sep 01, 2002 at 08:05:57AM -0700, Michael Labhard wrote:
> Thanks for the tip.

You are welcome.

> Interesting idea but I monitored the process and found it to be using
> around 200 MB of memory already,

Maybe Cygwin's heap is not quite equivalent to "monitored process
memory"?

> so I'm not sure what to make of the reference you sent.

Note that this suggestion has help many others with Cygwin out-of-memory
problems.

> Unfortunately I'm not in a position to try a test either.

Hmm...

> Appreciate the interest.

Again, you are welcome.  However, it's sad that you are not even willing
to try my suggestion -- especially, since it is so easy to do so.
Nevertheless, you are the one with the itch, so you can choose to
scratch it or not.

Jason

Jason,

    O.k.  Fair enough.  I'll make arrangements to do the test.  It'll be a
week or so and I'll report back when done.  Although the test is simple our
little shop has a lot of competition for the resources.

-- Michael

>
> Again, you are welcome.  However, it's sad that you are not even willing
> to try my suggestion -- especially, since it is so easy to do so.
> Nevertheless, you are the one with the itch, so you can choose to
> scratch it or not.
>
> Jason
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Michael,

On Tue, Sep 03, 2002 at 09:40:28AM -0700, Michael Labhard wrote:
> O.k.  Fair enough.  I'll make arrangements to do the test.

Thanks.

> It'll be a week or so and I'll report back when done.

The above is fine and will be appreciated by the Cygwin PostgreSQL
community.

> Although the test is simple our little shop has a lot of competition
> for the resources.

You can always try it on your home PC.  Or, your friend's... :,)  But
more seriously, make sure that you stop all Cygwin processes to
guarantee that the change takes effect.

Jason

The same insert that elicited the original error was repeated today on the
same machine.  Today the insert completed without error today.  The machine
was modified during the week by doubling the amount of RAM.  Otherwise, I
don't have an explanation for the difference.

-- Michael

Michael,

[Sorry for the sluggish response time, but I just returned from
vacation...]

On Sun, Sep 08, 2002 at 10:57:23AM -0700, Michael Labhard wrote:
> The same insert that elicited the original error was repeated today on
> the same machine.  Today the insert completed without error today.
> The machine was modified during the week by doubling the amount of
> RAM.  Otherwise, I don't have an explanation for the difference.

Thanks for getting back to the list.  Unfortunately, I'm not sure what
to make of it.  Did the original error occur every time?  If not, then
try the test again (with the extra memory).  Does it fail occasionally?
If so, then it's time to debug...  If not, then I'm at a loss at what to
do next...

Jason

Michael,

On Mon, Sep 16, 2002 at 08:42:17AM -0700, Michael Labhard wrote:
> I can add that we had encountered the same error when we increased the
> vacuum memory usage in the postgres.conf file.  Vacuum would not
> finish.  After making the registry change you suggested vacuum
> finished without error.  That was very useful.  Thanks, again.

No problem, I'm glad that my suggestion was helpful.

Jason