Re: [GENERAL] V8.4 TOAST table problem
Hi, I'm talking about our own massively bloated toast table - described in an earlier post - that I think I can replicate. I didn't mean to steal your thread, but the problem seems very similar, and we're using 9.1. I don't know a lot about Postgres internals, but to me it smells like a bug of some sort. On Mon, Jul 15, 2013 at 7:23 PM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, I'm sorry, but I'm not sure that I follow how this is pertinent to this particular thread. Are you proposing a way to replicate the scenario we experienced of our massively bloated TOAST table? If so, I'm not entirely sure that's doable given that the source of the issue was never clear. There still remains a number of reasons for why that table had so much still in use bloat. At this moment, it's near impossible to tell given that it is no longer a problem. Thanks for the offer, and I apologize if I'm just slightly ignorant about your intentions. On Mon, Jul 15, 2013 at 4:33 AM, David Welton dav...@dedasys.com wrote: Hi, I think I could write a script to do something similar to what is happening if anyone is interested. I'd want some direction as to the best way to handle this though: it'd be easier for me to script it as Rails code because that's what the app is. Perhaps from that we can get the generated SQL so as to make it easier for others to deal with. The operation itself is basically: * Extract a value from a row of a table that is stored as a bytea. * Unmarshall it into a Ruby object. * Add to that Ruby object. * update the row and set the value by marshalling the Ruby object. I suspect that the actual value isn't terribly relevant, and they how's and why's of what it is like it is are best left for a different discussion. -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- David N. Welton http://www.dedasys.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] V8.4 TOAST table problem
Hi, I think I could write a script to do something similar to what is happening if anyone is interested. I'd want some direction as to the best way to handle this though: it'd be easier for me to script it as Rails code because that's what the app is. Perhaps from that we can get the generated SQL so as to make it easier for others to deal with. The operation itself is basically: * Extract a value from a row of a table that is stored as a bytea. * Unmarshall it into a Ruby object. * Add to that Ruby object. * update the row and set the value by marshalling the Ruby object. I suspect that the actual value isn't terribly relevant, and they how's and why's of what it is like it is are best left for a different discussion. -- David N. Welton http://www.dedasys.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] V8.4 TOAST table problem
David, I'm sorry, but I'm not sure that I follow how this is pertinent to this particular thread. Are you proposing a way to replicate the scenario we experienced of our massively bloated TOAST table? If so, I'm not entirely sure that's doable given that the source of the issue was never clear. There still remains a number of reasons for why that table had so much still in use bloat. At this moment, it's near impossible to tell given that it is no longer a problem. Thanks for the offer, and I apologize if I'm just slightly ignorant about your intentions. On Mon, Jul 15, 2013 at 4:33 AM, David Welton dav...@dedasys.com wrote: Hi, I think I could write a script to do something similar to what is happening if anyone is interested. I'd want some direction as to the best way to handle this though: it'd be easier for me to script it as Rails code because that's what the app is. Perhaps from that we can get the generated SQL so as to make it easier for others to deal with. The operation itself is basically: * Extract a value from a row of a table that is stored as a bytea. * Unmarshall it into a Ruby object. * Add to that Ruby object. * update the row and set the value by marshalling the Ruby object. I suspect that the actual value isn't terribly relevant, and they how's and why's of what it is like it is are best left for a different discussion. -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune
Re: [GENERAL] V8.4 TOAST table problem
Thanks, Scott. Currently, it's a bit difficult due to resources for a complete copy of the database to be useful. I won't get into the details, but it just wasn't an option at the time. With that said, I'm definitely making it a major concern of ours for such future issues, so post mortem and such is possible (probably via virtual instances). As always, I appreciate the response. On Fri, Jul 12, 2013 at 5:34 PM, Scott Marlowe scott.marl...@gmail.comwrote: It's always a good idea to keep a copy of the database for a post mortem if possible. If you've found a bug, it's nice to find and fix it. If you were suffering from an operational failure of some sort, then it helps to figure that out too. On Fri, Jul 12, 2013 at 2:42 PM, Bradley McCune bradley.mcc...@noaa.govwrote: Well, the issue was corrected by completely rebuilding the database a few days ago (all the way to reinitializing the database directory). With that said, I did check that table at the time, and I received an empty result set from such a SELECT statement. The same goes for max_prepared_transactions. Perplexing. On Fri, Jul 12, 2013 at 4:35 PM, Scott Marlowe scott.marl...@gmail.comwrote: So what id select * from pg_prepared_xacts ; show? On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune bradley.mcc...@noaa.gov wrote: Scott, Purely idle. I compared these transactions with our other healthy databases, and they checked out. On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe scott.marl...@gmail.com wrote: Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe scott.marl...@gmail.com wrote: Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.com wrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10
Re: [GENERAL] V8.4 TOAST table problem
Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] V8.4 TOAST table problem
David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune
Re: [GENERAL] V8.4 TOAST table problem
Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] V8.4 TOAST table problem
The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.comwrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Bradley D. J. McCune NOAA/OCWWS/HSD Community Hydrologic Prediction System - Support CHPS FogBugz Administrator Office phone: (301) 713-1625 x160
Re: [GENERAL] V8.4 TOAST table problem
Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.govwrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.comwrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Bradley D. J. McCune NOAA/OCWWS/HSD Community Hydrologic Prediction System - Support CHPS FogBugz Administrator Office phone: (301) 713-1625 x160 -- To understand recursion, one must first understand recursion.
Re: [GENERAL] V8.4 TOAST table problem
Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.govwrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.comwrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Bradley D. J. McCune NOAA/OCWWS/HSD Community Hydrologic Prediction System - Support CHPS FogBugz Administrator Office phone: (301) 713-1625 x160 -- To understand recursion, one must first understand recursion. -- To understand recursion, one must first understand recursion.
Re: [GENERAL] V8.4 TOAST table problem
Scott, Purely idle. I compared these transactions with our other healthy databases, and they checked out. On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.govwrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.com wrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Bradley D. J. McCune NOAA/OCWWS/HSD Community Hydrologic Prediction System - Support CHPS FogBugz Administrator Office phone: (301) 713-1625 x160 -- To understand recursion, one must first understand recursion. -- To understand recursion, one must first understand recursion. -- Bradley D. J. McCune NOAA/OCWWS/HSD
Re: [GENERAL] V8.4 TOAST table problem
So what id select * from pg_prepared_xacts ; show? On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune bradley.mcc...@noaa.govwrote: Scott, Purely idle. I compared these transactions with our other healthy databases, and they checked out. On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.com wrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could probably script a similar situation... -- David N. Welton http://www.dedasys.com/ -- Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Bradley D. J. McCune NOAA/OCWWS/HSD Community Hydrologic Prediction System - Support CHPS FogBugz Administrator Office phone: (301) 713-1625 x160 -- To understand recursion, one must
Re: [GENERAL] V8.4 TOAST table problem
Well, the issue was corrected by completely rebuilding the database a few days ago (all the way to reinitializing the database directory). With that said, I did check that table at the time, and I received an empty result set from such a SELECT statement. The same goes for max_prepared_transactions. Perplexing. On Fri, Jul 12, 2013 at 4:35 PM, Scott Marlowe scott.marl...@gmail.comwrote: So what id select * from pg_prepared_xacts ; show? On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune bradley.mcc...@noaa.govwrote: Scott, Purely idle. I compared these transactions with our other healthy databases, and they checked out. On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.com wrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors. VACUUM FULL fixes the problem for us by recouping all the wasted disk space. I don't have the knowledge to investigate much further on my own, but I'd be happy to try out a few things. The database is, unfortunately, sensitive data that I can't share, but I could
Re: [GENERAL] V8.4 TOAST table problem
It's always a good idea to keep a copy of the database for a post mortem if possible. If you've found a bug, it's nice to find and fix it. If you were suffering from an operational failure of some sort, then it helps to figure that out too. On Fri, Jul 12, 2013 at 2:42 PM, Bradley McCune bradley.mcc...@noaa.govwrote: Well, the issue was corrected by completely rebuilding the database a few days ago (all the way to reinitializing the database directory). With that said, I did check that table at the time, and I received an empty result set from such a SELECT statement. The same goes for max_prepared_transactions. Perplexing. On Fri, Jul 12, 2013 at 4:35 PM, Scott Marlowe scott.marl...@gmail.comwrote: So what id select * from pg_prepared_xacts ; show? On Fri, Jul 12, 2013 at 2:30 PM, Bradley McCune bradley.mcc...@noaa.govwrote: Scott, Purely idle. I compared these transactions with our other healthy databases, and they checked out. On Fri, Jul 12, 2013 at 4:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: Prepared transactions that are sitting still do the same thing, and show no connections. On Fri, Jul 12, 2013 at 2:25 PM, Scott Marlowe scott.marl...@gmail.com wrote: Idle in Transaction? Or plain Idle? Idle in Transaction stops vacuum from reclaiming space and is indicative of a broken application. On Fri, Jul 12, 2013 at 9:39 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: The only transactions present were IDLE for current_query. I even stopped the remote services, restarted the PostgreSQL server (assumingly, there should be no transactions occurring now), and performed another VACUUM FULL followed by REINDEX due to known fullvac index bloat in pre-9 pgsql version. On Fri, Jul 12, 2013 at 11:28 AM, Scott Marlowe scott.marl...@gmail.com wrote: Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune bradley.mcc...@noaa.gov wrote: David, (As a preface, I have already gone forward with completely rebuilding the database which seems to have finally fixed the problem. Rebuilding the table itself had no effect, and I couldn't wait much longer to move forward.) Yes, this seems similar, however, the key difference being that VACUUM FULL did not alleviate the problem. The extra bloated disk space was still considered in use by the data server, and so it was never returned to the system. I have a suspicion that the server was storing the table data in pages in an inefficient manner (by unknown means) because we had roughly ~5x the number of pages used on that TOAST table to store the same number of tuples compared to other similar databases. Depending on how often you have to use VACUUM FULL, you might want to consider tweaking the autovacuum to be more aggressive on that hot table to keep it in check more often. (Recycling the disk space more efficiently rather than sending it back to the server only to be reallocated to the database again.) On Fri, Jul 12, 2013 at 4:09 AM, David Welton dav...@dedasys.com wrote: Hi, I have a very similar problem... details below. On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles paul.til...@noaa.gov wrote: Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features. This server exists in a very closed environment (isolated network, limited root privileges; this explains the older software in use) and runs on RHEL5.5 (i686). After the upgrade, the database has constantly been growing to the tune of 5-6 GB a day. Normally, the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple other servers which run equivalent databases and actually synchronize the records to each other via a 3rd party application (one I do not have access to the inner workings). The other databases are ~20GB as they should be. Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table. Same thing here: we have a table with around 2-3 megs of data that is blowing up to *10 gigs*. This TOAST table is for a table called timeseries which saves large records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be [b]no reason[/b] this table's TOAST table should be as large as it is. Similar situation: it's a bytea column that gets a lot of updates; in the order of 10's of thousands a day. I've performed a VACUUM FULL VERBOSE ANALYZE timeseries,
[GENERAL] V8.4 TOAST table problem
Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order
to take advantage of autovacuum features. This server exists in a very
closed environment (isolated network, limited root privileges; this
explains the older software in use) and runs on RHEL5.5 (i686). After
the upgrade, the database has constantly been growing to the tune of 5-6
GB a day. Normally, the database, as a whole, is ~20GB; currently, it is
~89GB. We have a couple other servers which run equivalent databases and
actually synchronize the records to each other via a 3rd party
application (one I do not have access to the inner workings). The other
databases are ~20GB as they should be.
Running the following SQL, it's fairly obvious there's an issue with a
particular table, and, more specifically, its TOAST table.
|SELECT nspname|| '.' || relnameAS relation,
pg_size_pretty(pg_relation_size(C.oid)) AS size
FROM pg_class C
LEFT JOIN pg_namespace NON (N.oid= C.relnamespace)
WHERE nspnameNOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT20;|
Which produces:
relation | size
--+-
pg_toast.pg_toast_16874 | 89 GB
fews00.warmstates | 1095 MB
...
(20 rows)
This TOAST table is for a table called timeseries which saves large
records of blobbed data. A|SUM(LENGTH(blob)/1024./1024.)| of all the
records in timeseries yields ~16GB for that column. There should be
[b]no reason[/b] this table's TOAST table should be as large as it is.
I've performed a |VACUUM FULL VERBOSE ANALYZE timeseries|, and the
vacuum runs to completion with no errors.
INFO: vacuuming pg_toast.pg_toast_16874
INFO: pg_toast_16874: found 22483 removable, 10475318 nonremovable
row versions in 10448587 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2036 bytes long.
There were 20121422 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
4944885 pages are or will become empty, including 0 at the end of
the table. 4944885 pages containing 0 free bytes are potential move
destinations.
CPU 75.31s/29.59u sec elapsed 877.79 sec.
INFO: index pg_toast_16874_index now contains 10475318 row
versions in 179931 pages
DETAIL: 23884 index row versions were removed.
101623 index pages have been deleted, 101623 are currently reusable.
CPU 1.35s/2.46u sec elapsed 21.07 sec.
REINDEXed the table which freed [b]some[/b] space (~1GB). I can't
CLUSTER the table as there isn't enough space on disk for the process,
and I'm waiting to rebuild the table entirely as I'd like to find out
why it is so much bigger than equivalent databases we have.
Ran a query from the PostgreSQL wiki here - Show Database Bloat
http://wiki.postgresql.org/wiki/Show_database_bloat, and this is what
I get:
current_database | schemaname | tablename | tbloat | wastedbytes |
iname | ibloat | wastedibytes
--++++-+-++--
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_synchlevel
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_localavail
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expirytime
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | idx_timeseries_expiry_null
| 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | uniq_localintid | 0.0 | 0
ptrdb04 | fews00 | timeseries | 1.0 | 0 | pk_timeseries | 0.1 | 0
ptrdb04 | fews00 | idx_timeseries_expiry_null | 0.6 | 0 | ? | 0.0 | 0
It looks like the database doesn't consider this space as empty, at
all, but I just don't see where all the disk space is coming from!
I suspect that this database server is deciding to use 4-5x as much disk
space to save the same records pulled from the other data servers. My
question is this: Is there a way I can verify the physical disk size of
a row? I'd like to compare the size of one row on this database to
another healthy database.
Thanks for any help you can provide!
Paul Tilles
