Re: Maintenance question / DB size anomaly...
От | Kurt Overberg |
---|---|
Тема | Re: Maintenance question / DB size anomaly... |
Дата | |
Msg-id | A0763165-9B49-42C7-BBE7-4A670D74B7EE@hotdogrecords.com обсуждение исходный текст |
Ответ на | Re: Maintenance question / DB size anomaly... (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Maintenance question / DB size anomaly...
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Maintenance question / DB size anomaly... (Bill Moran <wmoran@collaborativefusion.com>) |
Список | pgsql-performance |
That's the thing thats kinda blowing my mind here, when I look at that table: db1=# select count(*) from _my_cluster.sl_log_1 ; count ------- 6788 (1 row) As far as my DB is concerned, there's only ~7000 rows (on average) when I look in there (it does fluctuate, I've seen it go as high as around 12k, but then its gone back down, so I know events are moving around in there). So from what I can tell- from the disk point of view, there's ~11Gb of data; from the vacuum point of view there's 309318 rows. From the psql point of view, there's only around 7,000. Am I missing something? Unless there's something going on under the hood that I don't know about (more than likely), it seems like my sl_log_1 table is munged or somehow otherwise very screwed up. I fear that a re-shuffling or dropping/recreating the index will mess it up further. Maybe when I take my production systems down for maintenance, can I wait until sl_log_1 clears out, so then I can just drop that table altogether (and re-create it of course)? Thanks! /kurt On Jun 19, 2007, at 5:33 PM, Tom Lane wrote: > Kurt Overberg <kurt@hotdogrecords.com> writes: >> mydb # vacuum verbose _my_cluster.sl_log_1 ; >> INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row >> versions in 13764 pages >> DETAIL: 0 dead row versions cannot be removed yet. > > Hmm. So you don't have a long-running-transactions problem (else that > DETAIL number would have been large). What you do have is a failure > to vacuum sl_log_1 on a regular basis (because there are so many > dead/removable rows). I suspect also some sort of Slony problem, > because AFAIK a properly operating Slony system shouldn't have that > many live rows in sl_log_1 either --- don't they all represent > as-yet-unpropagated events? I'm no Slony expert though. You probably > should ask about that on the Slony lists. > >> ...I then checked the disk and those pages are still there. > > Yes, regular VACUUM doesn't try very hard to shorten the disk file. > >> Would a VACUUM FULL take care of this? > > It would, but it will take an unpleasantly long time with so many live > rows to reshuffle. I'd advise first working to see if you can get the > table down to a few live rows. Then a VACUUM FULL will be a snap. > Also, you might want to do REINDEX after VACUUM FULL to compress the > indexes --- VACUUM FULL isn't good at that. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
В списке pgsql-performance по дате отправления: