Memory/Performance issue: server closed the connection unexpectedly
| От | Pieter-Jan Savat |
|---|---|
| Тема | Memory/Performance issue: server closed the connection unexpectedly |
| Дата | |
| Msg-id | 434B9169.6040404@barclab.com обсуждение исходный текст |
| Ответы |
Re: Memory/Performance issue: server closed the connection unexpectedly
|
| Список | 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
В списке pgsql-admin по дате отправления: