pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Pieter-Jan Savat <pieterjan.savat@barclab.com>
To: pgsql-admin@postgresql.org
Subject: Memory/Performance issue: server closed the connection unexpectedly
Date: Tue, 11 Oct 2005 12:18:17 +0200
Message-ID: <434B9169.6040404@barclab.com> (raw)

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



view thread (6+ messages)  latest in thread

Message-ID: <434B9169.6040404@barclab.com>
Permalink:  ../434B9169.6040404@barclab.com/
Also on:    postgresql.org/message-id/434B9169.6040404@barclab.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: pieterjan.savat@barclab.com
  Subject: Re: Memory/Performance issue: server closed the connection unexpectedly
  In-Reply-To: <434B9169.6040404@barclab.com>
  References: <434B9169.6040404@barclab.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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