pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Memory/Performance issue: server closed the connection unexpectedly
6+ messages / 3 participants
[nested] [flat]

* Memory/Performance issue: server closed the connection unexpectedly
@ 2005-10-11 10:18 Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 13:49 ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Pieter-Jan Savat @ 2005-10-11 10:18 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Memory/Performance issue: server closed the connection unexpectedly
  2005-10-11 10:18 Memory/Performance issue: server closed the connection unexpectedly Pieter-Jan Savat <pieterjan.savat@barclab.com>
@ 2005-10-11 13:49 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-10-11 15:53   ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2005-10-11 13:49 UTC (permalink / raw)
  To: Pieter-Jan Savat <pieterjan.savat@barclab.com>; +Cc: pgsql-admin

Pieter-Jan Savat <pieterjan.savat@barclab.com> writes:
> Now here's my problem. Updating table B works fine, but updating table A 
> makes the server "close the connection unexpectedly".

That shouldn't happen.  What shows up in the postmaster log?  If the
server is dumping core, can you get a stack trace from it?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Memory/Performance issue: server closed the connection
  2005-10-11 10:18 Memory/Performance issue: server closed the connection unexpectedly Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 13:49 ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-11 15:53   ` Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 15:58     ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Pieter-Jan Savat @ 2005-10-11 15:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin



Tom Lane wrote:

>That shouldn't happen.  What shows up in the postmaster log? 
>
Here is the output of the log:

2005-10-11 17:22:00 CEST (@) [] DEBUG:  checkpoint complete; 0 
transaction log file(s) added, 0 removed, 3 recycled
2005-10-11 17:22:26 CEST (@) [] LOG:  checkpoints are occurring too 
frequently (27 seconds apart)
2005-10-11 17:22:26 CEST (@) [] HINT:  Consider increasing the 
configuration parameter "checkpoint_segments".
2005-10-11 17:22:26 CEST (@) [] DEBUG:  checkpoint starting
2005-10-11 17:22:27 CEST (@) [] DEBUG:  checkpoint complete; 0 
transaction log file(s) added, 0 removed, 3 recycled
2005-10-11 17:22:28 CEST ([unknown]@[unknown]) [2005-10-11 17:22:28 
CEST] LOG:  incomplete startup packet
2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:49 CEST] 
PANIC:  right sibling's left-link doesn't match
2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:49 CEST] 
STATEMENT:  update aaa set blinding_flag = 'D' where study_id = 144
2005-10-11 17:22:28 CEST (@) [] LOG:  server process (PID 13726) was 
terminated by signal 6
2005-10-11 17:22:28 CEST (@) [] LOG:  terminating any other active 
server processes
2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:48 CEST] 
WARNING:  terminating connection because of crash of another server process
2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:48 CEST] 
DETAIL:  The postmaster has commanded this server process to roll back 
the current tran
saction and exit, because another server process exited abnormally and 
possibly corrupted shared memory.
2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:48 CEST] 
HINT:  In a moment you should be able to reconnect to the database and 
repeat your comm
and.
2005-10-11 17:22:28 CEST (postgres@template1) [2005-10-11 17:08:48 CEST] 
WARNING:  terminating connection because of crash of another server process
2005-10-11 17:22:28 CEST (postgres@template1) [2005-10-11 17:08:48 CEST] 
DETAIL:  The postmaster has commanded this server process to roll back 
the current tr
ansaction and exit, because another server process exited abnormally and 
possibly corrupted shared memory.
2005-10-11 17:22:28 CEST (postgres@template1) [2005-10-11 17:08:48 CEST] 
HINT:  In a moment you should be able to reconnect to the database and 
repeat your co
mmand.
2005-10-11 17:22:28 CEST (@) [] LOG:  all server processes terminated; 
reinitializing
2005-10-11 17:22:28 CEST (@) [] LOG:  database system was interrupted at 
2005-10-11 17:22:27 CEST
2005-10-11 17:22:28 CEST (@) [] LOG:  checkpoint record is at B/FB02FD7C
2005-10-11 17:22:28 CEST (@) [] LOG:  redo record is at B/FB008754; undo 
record is at 0/0; shutdown FALSE
2005-10-11 17:22:28 CEST (@) [] LOG:  next transaction ID: 4546927; next 
OID: 83401
2005-10-11 17:22:28 CEST (@) [] LOG:  database system was not properly 
shut down; automatic recovery in progress
2005-10-11 17:22:28 CEST (@) [] LOG:  redo starts at B/FB008754
2005-10-11 17:22:28 CEST (@) [] LOG:  unexpected pageaddr B/F4150000 in 
log file 11, segment 251, offset 1376256
2005-10-11 17:22:28 CEST (@) [] LOG:  redo done at B/FB14FF98
2005-10-11 17:22:28 CEST (@) [] LOG:  database system is ready
2005-10-11 17:22:48 CEST ([unknown]@[unknown]) [2005-10-11 17:22:48 
CEST] LOG:  incomplete startup packet

> If the server is dumping core, can you get a stack trace from it?
>  
>
I can't find any special file in /pgdb/system/base or its subdirectories.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Memory/Performance issue: server closed the connection unexpectedly
  2005-10-11 10:18 Memory/Performance issue: server closed the connection unexpectedly Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 13:49 ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
  2005-10-11 15:53   ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
@ 2005-10-11 15:58     ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-10-12 09:18       ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2005-10-11 15:58 UTC (permalink / raw)
  To: Pieter-Jan Savat <pieterjan.savat@barclab.com>; +Cc: pgsql-admin

Pieter-Jan Savat <pieterjan.savat@barclab.com> writes:
> 2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:49 CEST] 
> PANIC:  right sibling's left-link doesn't match

You've got a corrupt btree index.  Try reindexing that table.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Memory/Performance issue: server closed the connection
  2005-10-11 10:18 Memory/Performance issue: server closed the connection unexpectedly Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 13:49 ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
  2005-10-11 15:53   ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 15:58     ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-12 09:18       ` Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-27 02:47         ` Re: Memory/Performance issue: server closed the connection Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 6+ messages in thread

From: Pieter-Jan Savat @ 2005-10-12 09:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin



Tom Lane wrote:

>Pieter-Jan Savat <pieterjan.savat@barclab.com> writes:
>  
>
>>2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:49 CEST] 
>>PANIC:  right sibling's left-link doesn't match
>>    
>>
>
>You've got a corrupt btree index.  Try reindexing that table.
>
>			regards, tom lane
>  
>
You were right, thanks.

But of course this is not something I would like to see in my production 
database. Do you have
an idea what could be the cause of my corrupt index?



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Memory/Performance issue: server closed the connection
  2005-10-11 10:18 Memory/Performance issue: server closed the connection unexpectedly Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 13:49 ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
  2005-10-11 15:53   ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
  2005-10-11 15:58     ` Re: Memory/Performance issue: server closed the connection unexpectedly Tom Lane <tgl@sss.pgh.pa.us>
  2005-10-12 09:18       ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
@ 2005-10-27 02:47         ` Bruno Wolff III <bruno@wolff.to>
  0 siblings, 0 replies; 6+ messages in thread

From: Bruno Wolff III @ 2005-10-27 02:47 UTC (permalink / raw)
  To: Pieter-Jan Savat <pieterjan.savat@barclab.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

On Wed, Oct 12, 2005 at 11:18:02 +0200,
  Pieter-Jan Savat <pieterjan.savat@barclab.com> wrote:
> 
> 
> Tom Lane wrote:
> 
> >Pieter-Jan Savat <pieterjan.savat@barclab.com> writes:
> > 
> >
> >>2005-10-11 17:22:28 CEST (postgres@TheCore) [2005-10-11 17:08:49 CEST] 
> >>PANIC:  right sibling's left-link doesn't match
> >>   
> >>
> >
> >You've got a corrupt btree index.  Try reindexing that table.
> >
> >			regards, tom lane
> > 
> >
> You were right, thanks.
> 
> But of course this is not something I would like to see in my production 
> database. Do you have
> an idea what could be the cause of my corrupt index?

This is probably hardware related. In particular using IDE drives with
write caching turned on can cause problems if your server loses power.
Not using ECC memory is another source of potential trouble.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-10-27 02:47 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-11 10:18 Memory/Performance issue: server closed the connection unexpectedly Pieter-Jan Savat <pieterjan.savat@barclab.com>
2005-10-11 13:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-11 15:53   ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
2005-10-11 15:58     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-12 09:18       ` Re: Memory/Performance issue: server closed the connection Pieter-Jan Savat <pieterjan.savat@barclab.com>
2005-10-27 02:47         ` Re: Memory/Performance issue: server closed the connection Bruno Wolff III <bruno@wolff.to>

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