[ADMIN] Vacuum issues..
Hi, I am using PostgreSQL 8.1.4 for an embedded application. For some reason, vacuum is not able to identify rows that are candidates for removal (i.e., mark space as available). Background Info: I observed some performance problems – our database seemed to be using an unusually high amount of cpu. Further investigation of the problem revealed a very bloated database; the database was around 300M when it should have been about 150M. A number of the database files were quite large, however, the tables that they stored information for were very small. For example, we had one table that had only 46 rows, but was using up more than 17M of disk space. We had a number of other tables that were similarly large. We run auto vacuum and I can see from the logs that it is running quite frequently. When I run vacuum full from the psql, I can see that space is not being recovered. I have run vacuum full with the verbose flag set, I can see that messages that indicate the existence of “dead row versions that cannot be removed yet. <--- CUT FROM VACUUM OUTPUT ---> CPU 0.00s/0.00u sec elapsed 0.18 sec. INFO: "ibportreceivestatsca": found 0 removable, 88017 nonremovable row versions in 4001 pages DETAIL: 87957 dead row versions cannot be removed yet. There were 1 unused item pointers. <--- CUT FROM VACUUM OUTPUT ---> If I shutdown our application and run a vacuum full, the space is recovered and the database size goes down to 150M. So, my best guess is that something in our application is preventing vacuum from removing dead rows. What could cause this? Would it be caused by a long-living transaction? What is the best way to track the problem down...right now, I am looking through pg_stat_activity and pg_locks to find processes that are “in transaction” and what locks they are holding. Has anyone had a similar problem? If so, how did you resolve it? Thanks Ike
[ADMIN] Vacuum Issues
Hi all I have had this issue a couple of times now. on Solaris, and on 7.3 of the db. I was did a normal vacuum and then I did a full which hung on a table, I waited for over an hour but there were no activity going on so I ctrl'cd the job in a terminal window, kill off all associated processes and try to do another normal vacuum which hung at the same spot. So I once again killed the processes. I went into the db and query the pg_tables tables and found the order of the tables, I vacuumed the ones just before and just after and they went fine but the offending table would not vacuum, Next I thought I could just pg_dump that table but the db would not allow me to do that either, I found a copy of the table from a restore I did on another box( the table has only 7 rows.) So I try to drop it but that would work either. So the only alternative was to stop and restart the db which is totally unacceptable in a 24x7 shop. Does any one else see what I have done wrong or could do differently next time. Oh yes and after the stop and start I could do a full vacuum on that table as well as the whole database. I know it's after st. Patrick's day but I did not find any green men lurking around to be causing this, any ideas?, hints?, suggestions? I am at my wits ends as to where to turn next, I most certainly do not want to another vacuum full. Mark M. Huber Database Administrator (702)-938-9300 [EMAIL PROTECTED] http://www.vmdirect.com 3035 E. Patrick Lane Suite #1 Las Vegas, NV 89120 <> BEGIN:VCARD VERSION:2.1 N:Huber;Mark FN:Mark M. Huber EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040220T180108Z END:VCARD ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Vacuum issues..
Eamonn Kent wrote: I am using PostgreSQL 8.1.4 for an embedded application. For some reason, vacuum is not able to identify rows that are candidates for removal (i.e., mark space as available). [snip] If I shutdown our application and run a vacuum full, the space is recovered and the database size goes down to 150M. The most common answer to this is that your application is leaving open transactions just sitting there. VACUUM can't remove tuples that may still need to be visible to a transaction that's been open for days. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Vacuum Issues
"Mark M. Huber" <[EMAIL PROTECTED]> writes: > I was did a normal vacuum and then I did a full which hung on a table, Sounds to me like some other transaction is holding a lock on that table. In recent releases you can look in the pg_locks system view to see who's got the lock. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
