Re: Weird disk/table space consumption problem

On Sat, Jul 11, 2009 at 4:41 PM, Dirk Riehle<dirk(at)riehle(dot)org> wrote:
> Hi,
>
> I had some weird disk space consumption problem. I do (non-mission-critical)
> data crunching using large data sets.
>
> For one analysis, I created a table of about 15 columns, and then ran an
> insert with a subselect on the table, where the select was drawing from
> three other tables, merging over ids. One of the other tables has about 60M
> rows.
>
> The insert ran for about 18h before running out of disk space. Before the
> query, there had been about 1TB free on the disk.
>
> The disk itself is a soft raid 5 array under Ubuntu. (That's what I have for
> large data sets...)
>
> After the insert query failed, the disk space was not made available again;
> PostgreSQL did not free it up (or would not free it up quickly). I rebooted
> soon thereafter.
>
> During boot, fsck (must have been fsck) gave me tons of freeing up inode
> messages.
>
> So, I wonder what went wrong? Any explanation? Soft raid no good for
> PostgreSQL?

I've no such problems caused by modern linux software RAID (md). It's
no surprise pgsql didn't free up the space, as it was all likely dead
tuples at that point. Are you sure you didn't have a cartesian
product that created a larger set than you anticipated? I'd post the
query here to see if anyone has any suggestions on that.

As for the freeing inode messages, it sounds like something is
causeing file system corruption, and my first suspect is always memory
/ hardware issues. Have you run memtest86 on that machine to make
sure it's got good memory etc.?