Thread: Memory/Performance issue: server closed the connection unexpectedly

Memory/Performance issue: server closed the connection unexpectedly

From
Pieter-Jan Savat
Date:
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

Re: Memory/Performance issue: server closed the connection unexpectedly

From
Tom Lane
Date:
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

Re: Memory/Performance issue: server closed the connection

From
Pieter-Jan Savat
Date:

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.

Re: Memory/Performance issue: server closed the connection unexpectedly

From
Tom Lane
Date:
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

Re: Memory/Performance issue: server closed the connection

From
Pieter-Jan Savat
Date:

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?

Re: Memory/Performance issue: server closed the connection

From
Bruno Wolff III
Date:
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.