Обсуждение: Crash in postgres/linux on verly large database

Hi,

we have a table width about 60.000.000 entrys and about 4GB storage size.
When creating an index on this table the whole linux box freezes and the
reiser-fs file system is corrupted on not recoverable.

Does anybody have experience with this amount of data in postgres 7.4.2?
Is there a limit anywhere?

Thanks

Bernhard Ankenbrand

Bernhard Ankenbrand <b.ankenbrand@media-one.de> writes:
> we have a table width about 60.000.000 entrys and about 4GB storage size.
> When creating an index on this table the whole linux box freezes and the
> reiser-fs file system is corrupted on not recoverable.

> Does anybody have experience with this amount of data in postgres 7.4.2?
> Is there a limit anywhere?

Many people run Postgres with databases far larger than that.  In any
case a Postgres bug could not cause a system-level freeze or filesystem
corruption, since it's not a privileged process.

I'd guess that you are dealing with a hardware problem: flaky disk
and/or bad RAM are the usual suspects.  See memtest86 and badblocks
as the most readily available hardware test aids.

            regards, tom lane

On Tuesday 06 April 2004 12:22, Bernhard Ankenbrand wrote:
> Hi,
>
> we have a table width about 60.000.000 entrys and about 4GB storage size.
> When creating an index on this table the whole linux box freezes and the
> reiser-fs file system is corrupted on not recoverable.
>
> Does anybody have experience with this amount of data in postgres 7.4.2?
> Is there a limit anywhere?

Plenty of people with more data than that. It should be impossible for an
application to corrupt a file-system in any case. The two things to look at
would be your hardware or perhaps reiser-fs itself. I have heard about
problems with SMP machines locking up (some unusual glitch in some versions
of Linux kernel IIRC).

You might be able to see what is going wrong with careful use of vmstat and
strace -p <pid>. Start to create your index, find the pid of the backend
doing so and strace it. See if anything interesting comes out of it.

HTH, and stick around - someone else might have better advice.
--
  Richard Huxton
  Archonet Ltd

On Tue, 6 Apr 2004, Bernhard Ankenbrand wrote:

> Hi,
>
> we have a table width about 60.000.000 entrys and about 4GB storage size.
> When creating an index on this table the whole linux box freezes and the
> reiser-fs file system is corrupted on not recoverable.
>
> Does anybody have experience with this amount of data in postgres 7.4.2?
> Is there a limit anywhere?

If your file system is getting corrupted, then you likely have found a bug
in reiserfs or the linux kernel.  While some pgsql bug might be able to
corrupt the contents of a file belonging to it, it doesn't have the power
to corrupt the file system itself.

Or is the problem a corrupted database, not a corrupted file system?

Different file system but we've loaded 39,000,000 row tables (about 30 gigs of spatial data) without any issues ... CPU
loadwas 1.something for a while but not problems with loading, indexing or doing the stats command. I might suspect the
underlyingOS ... 

Greg Williamson
DBA
GlobeXplorer LLC

-----Original Message-----
From: Bernhard Ankenbrand [mailto:b.ankenbrand@media-one.de]
Sent: Tuesday, April 06, 2004 4:22 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Crash in postgres/linux on verly large database


Hi,

we have a table width about 60.000.000 entrys and about 4GB storage size.
When creating an index on this table the whole linux box freezes and the
reiser-fs file system is corrupted on not recoverable.

Does anybody have experience with this amount of data in postgres 7.4.2?
Is there a limit anywhere?

Thanks

Bernhard Ankenbrand


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On 4/6/04 5:36 PM, "Gregory S. Williamson" <gsw@globexplorer.com> wrote:

> we have a table width about 60.000.000 entrys and about 4GB storage size.
> When creating an index on this table the whole linux box freezes and the
> reiser-fs file system is corrupted on not recoverable.
>
> Does anybody have experience with this amount of data in postgres 7.4.2?
> Is there a limit anywhere?

This may or may not have anything to do with your problem, but I have seen
issues with the 2.4.x kernel where kscand goes ape under heavy I/O.  In the
cases I've seen, the system appears to freeze for a period of time (15-30
seconds) while kscand goes nuts, then the system takes off again for a
while, freezes, etc...  Upgrading to the 2.6 kernel solved our problem.

I assume you gave it plenty of time before hitting the button?

Wes