Thread: VACUUM FULL doesn't reduce table size
I have deleted a large number of records from my_table, which originally had 288 MB. Then I ran vacuum full to make the table size smaller. After this operation size of the table remains the same, despite of the fact that table contains now only 241 rows and after rewriting it in classic way: CREATE TABLE new_table AS SELECT * FROM old_table - new_table size is 24kB. What went wrong? And how can I remove those blocks? -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
pinker wrote: > I have deleted a large number of records from my_table, which originally had > 288 MB. Then I ran vacuum full to make the table size smaller. After this > operation size of the table remains the same, despite of the fact that table > contains now only 241 rows and after rewriting it in classic way: CREATE > TABLE new_table AS SELECT * FROM old_table - new_table size is 24kB. > What went wrong? And how can I remove those blocks? Normally this happens when you have transactions that are older than the delete, so the deleted rows cannot be removed by vacuum because that transaction might still see them. If you close all old transactions, vacuum full would be able to reclaim all the space. You might have prepared transactions also, see select * from pg_prepared_xacts; -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Query output is empty... -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5840797.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
pinker wrote: > Query output is empty... I hope you read the whole paragraph, not just the last phrase. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Fri, Mar 6, 2015 at 5:59 AM, pinker <pinker@onet.eu> wrote:
I have deleted a large number of records from my_table, which originally had
288 MB. Then I ran vacuum full to make the table size smaller. After this
operation size of the table remains the same, despite of the fact that table
If your remaining records were in say, block 2 and block 10000, then the blocks in between won't be returned to the system.
You could achieve your "fix" by running cluster on the table, which will rewrite the table in-place. There are also scripts out there that do in-place compaction by running targeted updates and vacuum commands to get the empty pages at the end of the files truncated off the file.
Vick Khera wrote > On Fri, Mar 6, 2015 at 5:59 AM, pinker < > pinker@ > > wrote: > >> I have deleted a large number of records from my_table, which originally >> had >> 288 MB. Then I ran vacuum full to make the table size smaller. After this >> operation size of the table remains the same, despite of the fact that >> table >> > > If your remaining records were in say, block 2 and block 10000, then the > blocks in between won't be returned to the system. Really? This is vacuum full we are talking about. How would such a thing occur? The OP hasn't stated his version and I wouldn't assume 9.x I have no experience here but given recent versions rewrite the table the vacuum verbose output shown seems unusual. David J. -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5840897.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David G Johnston <david.g.johnston@gmail.com> writes: > I have no experience here but given recent versions rewrite the table the > vacuum verbose output shown seems unusual. "vacuum verbose output shown?" There wasn't any. [ digs about ... ] Oh. The version of the message that nabble sent to the postgresql lists was missing vital parts of what got posted at nabble: compare http://www.postgresql.org/message-id/1425639585904-5840782.post@n5.nabble.com http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-td5840782.html I've seen that before. I'm about ready to propose that we flat out ban messages from nabble to the PG lists; I'm tired of them wasting our time with this sort of BS. (FWIW, the output shown on nabble doesn't look materially different from what I see in HEAD. It also proves positively that the OP has uncommitted transactions blocking cleanup of known-dead rows. But we were just guessing at that over here.) regards, tom lane
Yes I have. Environment I'm working on is production snapshot, so there is no active transactions that could block those blocks from being removed... -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841085.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Vick Khera wrote > On Fri, Mar 6, 2015 at 5:59 AM, pinker < > pinker@ > > wrote: > >> I have deleted a large number of records from my_table, which originally >> had >> 288 MB. Then I ran vacuum full to make the table size smaller. After this >> operation size of the table remains the same, despite of the fact that >> table >> > > If your remaining records were in say, block 2 and block 10000, then the > blocks in between won't be returned to the system. > > You could achieve your "fix" by running cluster on the table, which will > rewrite the table in-place. There are also scripts out there that do > in-place compaction by running targeted updates and vacuum commands to get > the empty pages at the end of the files truncated off the file. I have tried many combinations of VACUUM, VACUUM FULL and CLUSTER after first failed VACUUM FULL and nothing works... -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841086.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/09/2015 04:22 AM, pinker wrote: > Yes I have. Environment I'm working on is production snapshot, so there is no > active transactions that could block those blocks from being removed... > Well the below from your original post would say different: VACUUM FULL VERBOSE output: INFO: vacuuming "my_table" INFO: "my_table": found 0 removable, 2989662 nonremovable row versions in 36910 pages DETAIL: 2989421 dead row versions cannot be removed yet. CPU 1.10s/1.82u sec elapsed 9.46 sec. Query returned successfully with no result in 9826 ms. So some information is in order: What version of Postgres are you using? What do you mean by 'production snapshot' or more to the point how did you get the snapshot? What does select * from pg_stat_activity show? > > > > > -- > View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841085.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
I did: select pg_cancel_backend(pid) from pg_stat_activity where usename <> 'mine'; and then tried again vacuum full: INFO: vacuuming "my_table" INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in 37580 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 1.07s/1.56u sec elapsed 3.24 sec. Query returned successfully with no result in 6436 ms. PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit It was flash copy snapshot -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841110.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/09/2015 07:08 AM, pinker wrote: > I did: select pg_cancel_backend(pid) from pg_stat_activity where usename <> > 'mine'; What makes you think that queries from usename = 'mine' are not important? Or to get back to the original request: What does select * from pg_stat_activity show? Also did the queries actually get cancelled? > > and then tried again vacuum full: > > INFO: vacuuming "my_table" > INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in > 37580 pages > DETAIL: 0 dead row versions cannot be removed yet. > CPU 1.07s/1.56u sec elapsed 3.24 sec. > Query returned successfully with no result in 6436 ms. > > > PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 > 20140120 (Red Hat 4.8.2-16), 64-bit > > It was flash copy snapshot So what does that mean? In other words detail the steps you took to get the snapshot. > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > On 03/09/2015 07:08 AM, pinker wrote: >> I did: select pg_cancel_backend(pid) from pg_stat_activity where usename >> <> >> 'mine'; > > What makes you think that queries from usename = 'mine' are not important? > > Because on production I don't have access to this table. > > Or to get back to the original request: > > What does select * from pg_stat_activity show? > > mainly idle connections and queries to tables in different schemas. > query select * from pg_stat_activity where query ilike '%my_table%' shows > nothing as well. > > Also did the queries actually get cancelled? > Yes, but not all. > >> >> and then tried again vacuum full: >> >> INFO: vacuuming "my_table" >> INFO: "my_table": found 0 removable, 3043947 nonremovable row versions >> in >> 37580 pages >> DETAIL: 0 dead row versions cannot be removed yet. >> CPU 1.07s/1.56u sec elapsed 3.24 sec. >> Query returned successfully with no result in 6436 ms. >> >> >> PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 >> 20140120 (Red Hat 4.8.2-16), 64-bit >> >> It was flash copy snapshot > > So what does that mean? > > In other words detail the steps you took to get the snapshot. > > I would like to know as well. Sysadmin team manage it, I'll ask them, but > as far I know it's matrix feature.... > >> > > > -- > Adrian Klaver > adrian.klaver@ > > > -- > Sent via pgsql-general mailing list ( > pgsql-general@ > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841117.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/09/2015 07:37 AM, pinker wrote: > Adrian Klaver-4 wrote >> On 03/09/2015 07:08 AM, pinker wrote: >>> I did: select pg_cancel_backend(pid) from pg_stat_activity where usename >>> <> >>> 'mine'; >> >> What makes you think that queries from usename = 'mine' are not important? >> >> Because on production I don't have access to this table. >> >> Or to get back to the original request: >> >> What does select * from pg_stat_activity show? >> >> mainly idle connections and queries to tables in different schemas. >> query select * from pg_stat_activity where query ilike '%my_table%' shows >> nothing as well. And there is no relation between this table and the tables or functions being queried? >> >> Also did the queries actually get cancelled? >> Yes, but not all. So what do you see in pg_stat_activity where state ilike 'idle in transaction%'? >> >>> >>> and then tried again vacuum full: >>> >>> INFO: vacuuming "my_table" >>> INFO: "my_table": found 0 removable, 3043947 nonremovable row versions >>> in >>> 37580 pages >>> DETAIL: 0 dead row versions cannot be removed yet. >>> CPU 1.07s/1.56u sec elapsed 3.24 sec. >>> Query returned successfully with no result in 6436 ms. >>> >>> >>> PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 >>> 20140120 (Red Hat 4.8.2-16), 64-bit >>> >>> It was flash copy snapshot >> >> So what does that mean? >> >> In other words detail the steps you took to get the snapshot. >> >> I would like to know as well. Sysadmin team manage it, I'll ask them, but >> as far I know it's matrix feature.... If snapshot is what I think it means, you might want to point them at: http://www.postgresql.org/docs/9.3/interactive/backup-file.html >> >>> >> >> >> -- >> Adrian Klaver > >> adrian.klaver@ > >> >> >> -- >> Sent via pgsql-general mailing list ( > >> pgsql-general@ > >> ) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > > > -- > View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841117.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
select * from pg_stat_activity where state ilike 'idle in transaction%' it's empty. >And there is no relation between this table and the tables or functions being queried? no... >If snapshot is what I think it means, you might want to point them at: >http://www.postgresql.org/docs/9.3/interactive/backup-file.html hmmm, maybe it's because WAL is in another tablespace? -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841123.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
and select txid_current_snapshot() - 1694632069:1694632069: select distinct xmin::text::bigint from sms_actual_mapper order by 1 desc 1694595273 -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841128.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
pinker <pinker@onet.eu> wrote: > INFO: vacuuming "my_table" > INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in 37580 pages > DETAIL: 0 dead row versions cannot be removed yet. So there are no longer any dead rows being left behind, right? Why are we still discussing this? Do you have some other question? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 03/09/2015 08:05 AM, pinker wrote: > select * from pg_stat_activity where state ilike 'idle in transaction%' it's > empty. > >> And there is no relation between this table and the tables or functions > being queried? > > no... > >> If snapshot is what I think it means, you might want to point them at: > >> http://www.postgresql.org/docs/9.3/interactive/backup-file.html > > hmmm, maybe it's because WAL is in another tablespace? Well without information on how the 'snapshot' is being created it is just speculation at the moment. I would say to move forward the snapshot process needs to be spelled out. -- Adrian Klaver adrian.klaver@aklaver.com
On 03/09/2015 08:49 AM, Kevin Grittner wrote: > pinker <pinker@onet.eu> wrote: > >> INFO: vacuuming "my_table" >> INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in 37580 pages >> DETAIL: 0 dead row versions cannot be removed yet. > > So there are no longer any dead rows being left behind, right? > > Why are we still discussing this? Do you have some other question? Well from the original post: "I have deleted a large number of records from my_table, which originally had 288 MB. Then I ran vacuum full to make the table size smaller. After this operation size of the table remains the same, despite of the fact that table contains now only 241 rows and after rewriting it in classic way: CREATE TABLE new_table AS SELECT * FROM old_table - new_table size is 24kB. " So I think the question remains how is 241 rows = 3043947 nonremovable row versions? And that number is an increase from the original number which was 2989662 nonremovable row versions. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@aklaver.com
> So there are no longer any dead rows being left behind, right? > > Why are we still discussing this? Do you have some other question? There are no dead rows, but postgres still cannot reuse the space because of 3043947 nonremovable row versions .. INFO: vacuuming "my_table" INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in 37580 pages DETAIL: 0 dead row versions cannot be removed yet. CPU 2.67s/1.59u sec elapsed 7.71 sec. Query returned successfully with no result in 8319 ms. -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841137.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Mon, 9 Mar 2015 09:05:07 -0700 (MST) pinker <pinker@onet.eu> wrote: > > > So there are no longer any dead rows being left behind, right? > > > > Why are we still discussing this? Do you have some other question? > > There are no dead rows, but postgres still cannot reuse the space because of > 3043947 nonremovable row versions .. > > INFO: vacuuming "my_table" > INFO: "my_table": found 0 removable, 3043947 nonremovable row versions in > 37580 pages > DETAIL: 0 dead row versions cannot be removed yet. > CPU 2.67s/1.59u sec elapsed 7.71 sec. > Query returned successfully with no result in 8319 ms. Given your weird description of the "snapshot" I wouldn't be surprised if that instance of PostgreSQL had subtle corruption. pg_dump that database, re-init it and reload the dump. Then recreate the situation and see if the rows are sill nonremovable. I bet you $5.34 that everything works fine after that, which would indicate that the folks who made the snapshot didn't do it correctly. -- Bill Moran
On 03/09/2015 08:57 AM, Adrian Klaver wrote: > > On 03/09/2015 08:49 AM, Kevin Grittner wrote: >> pinker <pinker@onet.eu> wrote: >> >>> INFO: vacuuming "my_table" >>> INFO: "my_table": found 0 removable, 3043947 nonremovable row >>> versions in 37580 pages >>> DETAIL: 0 dead row versions cannot be removed yet. >> >> So there are no longer any dead rows being left behind, right? >> >> Why are we still discussing this? Do you have some other question? > > Well from the original post: > > "I have deleted a large number of records from my_table, which > originally had 288 MB. Then I ran vacuum full to make the table size > smaller. After this operation size of the table remains the same, > despite of the fact that table contains now only 241 rows and after > rewriting it in classic way: CREATE TABLE new_table AS SELECT * FROM > old_table - new_table size is 24kB. " > > So I think the question remains how is 241 rows = 3043947 nonremovable > row versions? And that number is an increase from the original number > which was 2989662 nonremovable row versions. TGL has answered this before: http://www.postgresql.org/message-id/14512.1282137722@sss.pgh.pa.us There are a number of things that can cause this but they are all about making sure that all versions of the tuple are completely and utterly of no use before vacuum will remove them. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc Now I get it: your service is designed for a customer base that grew up with Facebook, watches Japanese seizure robot anime, and has the attention span of a gnat. I'm not that user., "Tyler Riddle"
Joshua D. Drake <jd@commandprompt.com> wrote: > On 03/09/2015 08:57 AM, Adrian Klaver wrote: >> On 03/09/2015 08:49 AM, Kevin Grittner wrote: >>> pinker <pinker@onet.eu> wrote: >>>> DETAIL: 0 dead row versions cannot be removed yet. >>> >>> So there are no longer any dead rows being left behind, right? >>> >>> Why are we still discussing this? Do you have some other >>> question? >> >> Well from the original post: >> >> "I have deleted a large number of records from my_table, which >> originally had 288 MB. Then I ran vacuum full to make the table >> size smaller. After this operation size of the table remains >> the same, despite of the fact that table contains now only 241 >> rows and after rewriting it in classic way: CREATE TABLE >> new_table AS SELECT * FROM old_table - new_table size is 24kB." Initially the OP was reporting this, too: DETAIL: 2989421 dead row versions cannot be removed yet. Now that number is zero. >> So I think the question remains how is 241 rows = 3043947 >> nonremovable row versions? And that number is an increase from >> the original number which was 2989662 nonremovable row versions. > > TGL has answered this before: > > http://www.postgresql.org/message-id/14512.1282137722@sss.pgh.pa.us No, that thread was about the same thing that this thread *started* with, which was that there were a large number of dead row versions which could not be removed yet. On *this* thread that was corrected by terminating long-running transactions. That number is now *zero*. AFAICS we have not seen the results of "SELECT count(*)" or any other information to show that there is still any problem after that was done. Maybe there is, but it has not yet been demonstrated. pinker: You can probably get to a solution to this much faster if you do two things: (1) Send emails directly to the pgsql-general@postgresql.org list, rather than going through nabble. Tom showed at least one case where nabble failed to pass along useful information to the list, and I have no idea how much other useful information it has not passed along. (2) Try to send enough information about the current state of the problem to allow diagnosis. It is best if you can create a reproducible test case (where you demonstrate the problem starting from an empty database, creating all the objects and loading all the data needed to show the problem). https://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 03/09/2015 09:19 AM, Joshua D. Drake wrote: > > On 03/09/2015 08:57 AM, Adrian Klaver wrote: >> >> On 03/09/2015 08:49 AM, Kevin Grittner wrote: >>> pinker <pinker@onet.eu> wrote: >>> >>>> INFO: vacuuming "my_table" >>>> INFO: "my_table": found 0 removable, 3043947 nonremovable row >>>> versions in 37580 pages >>>> DETAIL: 0 dead row versions cannot be removed yet. >>> >>> So there are no longer any dead rows being left behind, right? >>> >>> Why are we still discussing this? Do you have some other question? >> >> Well from the original post: >> >> "I have deleted a large number of records from my_table, which >> originally had 288 MB. Then I ran vacuum full to make the table size >> smaller. After this operation size of the table remains the same, >> despite of the fact that table contains now only 241 rows and after >> rewriting it in classic way: CREATE TABLE new_table AS SELECT * FROM >> old_table - new_table size is 24kB. " >> >> So I think the question remains how is 241 rows = 3043947 nonremovable >> row versions? And that number is an increase from the original number >> which was 2989662 nonremovable row versions. > > TGL has answered this before: > > http://www.postgresql.org/message-id/14512.1282137722@sss.pgh.pa.us > > There are a number of things that can cause this but they are all about > making sure that all versions of the tuple are completely and utterly of > no use before vacuum will remove them. And that is what this thread is trying to determine. As others and I suspect, there is good reason to believe there is some sort of data corruption at work. This awaits a clearer understanding of what 'It was flash copy snapshot' means. Also per Kevin Grittner and Tom Lane there is a Nabble issue at work where the list here is not seeing all the information. Example: On this list I saw: "In other words detail the steps you took to get the snapshot. I would like to know as well. Sysadmin team manage it, I'll ask them, but as far I know it's matrix feature...." on Nabble I see: "> In other words detail the steps you took to get the snapshot. First, they call pg_start_backup, then flash copy is done for blocks, that were changed. Flash copy is made on another filesystem. If this matters - xmaxes for all rows are 0. " Also per Kevin Grittner we are looking at a moving target, so some sort of information about current state would be helpful. > > JD > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > Also per Kevin Grittner and Tom Lane there > is a Nabble issue at work where the list here is not seeing all the > information. Example: It's because I edited some post. Then is visible only on nabble because edited version isn't sent back to the mailing list. Adrian Klaver-4 wrote > Also per Kevin Grittner we are looking at a moving target, so some sort > of information about current state would be helpful. I'm doing pg_dump from snapshot - on production that isn't possible. Today at night my colleague will run vacuum full verbose on this table in production environment, so we will see if this problem occurs only on snapshot or was replicated from production. -- View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841434.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 03/11/2015 08:52 AM, pinker wrote: > Adrian Klaver-4 wrote >> Also per Kevin Grittner and Tom Lane there >> is a Nabble issue at work where the list here is not seeing all the >> information. Example: > > It's because I edited some post. Then is visible only on nabble because > edited version isn't sent back to the mailing list. Per previous suggestions I would post to this(pgsql-general) list directly. Hidden information is not to going to be well received here and will impede an answer to your issue, just saying. > > > Adrian Klaver-4 wrote >> Also per Kevin Grittner we are looking at a moving target, so some sort >> of information about current state would be helpful. > > I'm doing pg_dump from snapshot - on production that isn't possible. Today > at night my colleague will run vacuum full verbose on this table in > production environment, so we will see if this problem occurs only on > snapshot or was replicated from production. Alright. > > > > -- > View this message in context: http://postgresql.nabble.com/VACUUM-FULL-doesn-t-reduce-table-size-tp5840782p5841434.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com