Re: [GENERAL] DELETE statement KILL backend
От | Florian Wunderlich |
---|---|
Тема | Re: [GENERAL] DELETE statement KILL backend |
Дата | |
Msg-id | 3.0.32.19980824181303.00a15b90@wifomail обсуждение исходный текст |
Список | pgsql-general |
>In my experience, the problem seems to be caused by a lot of data being put >into the database. We are using the database to ingest real-time data 24 >hours a day/7 days a week. The data comes in about every three minutes. >While I was not able to identify what the exact cause has been, I have >noticed that before the problem becomes critical (before the backend >terminates abnormally), the (number of) indexes do not correspond to the >actual table. That leads me to believe that the indexes do not get created >on all occasions. After some time, the table's internal indexes may be come >corrupted, and vacuuming does no good. > >When trying to fix this, I first delete the index, then recreate it., then >vacuum. If that doesn't work, then I drop the table, create the index, >reload the data, and then vacuum the table. > >I would be curious to see if anyone else has had this type of problem and >what their solutions were. Same with us here, we use a really big database, but in my experience, it happend only when I killed (with SIGTERM, but anyway) a single postgres process that made an "endless" query. I think that some internal tables are left over in the data/base directory tree, and postgres tends to get confused about them. Not sure about this anyway. DROP/CREATE INDEX didn't solve this, I always did a DROP DATABASE and a complete reload of all data, and then it worked fine again.
В списке pgsql-general по дате отправления: