Обсуждение: Memory/Performance issue: server closed the connection unexpectedly
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
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
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.
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
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?
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.