Re: [PERFORM] Vacuum not identifying rows for removal..
Hi Joshua, Thanks for the info...but, what I already have the backend id. I was trying to get the process id of the client application. The client is using libpq and running on the same workstation. We have approximately 22 different clients running and it would help to isolate the client program that is causing the problem. I was unable to locate the client using the backend server's process id with lsof and netstat. Really the information should be there...since, each (I believe) each backend postgreSQL server will service a single client via a unix socket (in the case where they are collocated on a unix workstation). Thanks Ike Any ideas of how to identify the application process that is the postgres process (whose id I know). Perhaps I need to turn on a different log flag? select * from pg_stat_activity will give you the pid :) Joshua D. Drake Thanks Ike -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, August 21, 2006 2:06 PM To: Eamonn Kent Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum not identifying rows for removal.. Eamonn Kent [EMAIL PROTECTED] writes: 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). ... 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. This means you've got an open transaction somewhere that could potentially still be able to see those rows. Look around for applications sitting idle in transaction. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Vacuum not identifying rows for removal..
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
Re: [PERFORM] Vacuum not identifying rows for removal..
On Mon, 2006-08-21 at 11:50 -0700, Eamonn Kent wrote: 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. If you have any long running transactions - idle or active, that's your problem. Vacuum can only clear out dead tuples older than that oldest transaction. Deal with those. Make sure every single transaction your app initiates commits or rolls back every single time. You'll generally find them in pg_stat_activity, but not always. ps may show you idle transactions not showing as idle in pg_stat_activity -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Vacuum not identifying rows for removal..
Eamonn Kent [EMAIL PROTECTED] writes: 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). ... 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. This means you've got an open transaction somewhere that could potentially still be able to see those rows. Look around for applications sitting idle in transaction. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Vacuum not identifying rows for removal..
Hello, Thanks for the help...It appears that a transaction is indeed being opened and remains idle. I am able to identify the postgreSQL backend process that is associated with the transaction, however, I need to further localize the issue. We have around 22 (postgres) backend processes associated with various application processes. I would like to identify our application process. I have tried using netstat -ap and looking through the logs..but, to no avail. (Both the database and the server processes are running on the same server...connected via unix sockets I believe, perhaps this is making the association difficult to determine). Any ideas of how to identify the application process that is the postgres process (whose id I know). Perhaps I need to turn on a different log flag? Thanks Ike -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, August 21, 2006 2:06 PM To: Eamonn Kent Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum not identifying rows for removal.. Eamonn Kent [EMAIL PROTECTED] writes: 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). ... 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. This means you've got an open transaction somewhere that could potentially still be able to see those rows. Look around for applications sitting idle in transaction. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Vacuum not identifying rows for removal..
Any ideas of how to identify the application process that is the postgres process (whose id I know). Perhaps I need to turn on a different log flag? select * from pg_stat_activity will give you the pid :) Joshua D. Drake Thanks Ike -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, August 21, 2006 2:06 PM To: Eamonn Kent Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Vacuum not identifying rows for removal.. Eamonn Kent [EMAIL PROTECTED] writes: 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). ... 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. This means you've got an open transaction somewhere that could potentially still be able to see those rows. Look around for applications sitting idle in transaction. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
