Memory/Performance issue: server closed the connection unexpectedly

From: Pieter-Jan Savat <pieterjan(dot)savat(at)barclab(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Memory/Performance issue: server closed the connection unexpectedly
Date: 2005-10-11 10:18:17
Message-ID: 434B9169.6040404@barclab.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

I'm performing some tests on performance: when to use or not use foreign
keys.
I have a table A with 50 columns, 8 of them have a foreign key to some
other table,
and there is a table B with 65 columns, only 5 of them have a foreign
key to some table (so foreign data is copied locally).
The data inside tables A and B is identical. The only difference is that
table A has 3 (foreign) ids more than
table B, and table B contains the actual (foreign) data.

My first test of course pointed out that the amount of memory used by
table A is a lot less than table B.

In my second test I wanted to check if table A was slower in doing
updates because of its foreign keys, or if
it would be faster (because the amount of data temporarily stored to
commit the updates in one transaction would
be less)
The queries I used:
- update A set flag = 'X' where type_id = 13
- update B set flag = 'X' where type_id = 13
And I have to mention that there is no foreign key on the flag column.

Now here's my problem. Updating table B works fine, but updating table A
makes the server "close the connection unexpectedly".
After looking at the 'top' of my server I found that the update of B
took a fixed amount of data (28MB), while the update of A resulted in a
continuous increase in the amount of data needed (it crashed when
reaching 55MB)

I'm doing my tests on a small server (not much RAM) with out-of-the-box
settings using PostgreSQL 8.0.

Does anyone know why
a) the server "closes the connection unexpectedly" ?
b) the update of a table with less data but more foreign keys requires
increasingly more memory?

-- pj

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jim C. Nasby 2005-10-11 13:46:26 Re: A tool to extract the problematic points of the postgresl log.
Previous Message David Pradier 2005-10-11 09:59:29 A tool to extract the problematic points of the postgresl log.