pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum Full not clean the table
8+ messages / 4 participants
[nested] [flat]
* Vacuum Full not clean the table
@ 2023-02-07 16:51 Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
2023-02-07 18:17 ` Re: Vacuum Full not clean the table Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 8+ messages in thread
From: Erik Serrano @ 2023-02-07 16:51 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Dear All,
Along with saying hello, I would like to consult about a failure that is
happening to me with a PG 9.2 base and with a table that weighs 116GB and
its index 13Gb, mounted on a Linux Centos 7.2 with 4Core and 32Ram.
The failure occurs mainly in that I execute a Vacuum Full on the table and
it ends after 6 hours, but it does not clean anything. When checking the
dead tuples I realize that they follow the same dirty records in the table.
REF: (table of 420MM records with 20MM dead tuples)
I hope you can guide me in finding a solution to clean that table.
Greetings and many thanks to the Postgresql.org community
Atte.
*Erik Serrano*
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
@ 2023-02-07 16:56 ` Holger Jakobs <holger@jakobs.com>
2023-02-07 18:45 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Holger Jakobs @ 2023-02-07 16:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 07.02.23 um 17:51 schrieb Erik Serrano:
> Dear All,
>
> Along with saying hello, I would like to consult about a failure that
> is happening to me with a PG 9.2 base and with a table that weighs
> 116GB and its index 13Gb, mounted on a Linux Centos 7.2 with 4Core and
> 32Ram.
> The failure occurs mainly in that I execute a Vacuum Full on the table
> and it ends after 6 hours, but it does not clean anything. When
> checking the dead tuples I realize that they follow the same dirty
> records in the table. REF: (table of 420MM records with 20MM dead tuples)
>
> I hope you can guide me in finding a solution to clean that table.
>
> Greetings and many thanks to the Postgresql.org community
> Atte.
>
> /*
> */
> /*Erik Serrano*/
>
Hi Erik,
Does it end cleanly or with an error after 6 hours?
Do you have enough disk space for the vacuum full? The old and the newly
written data have to exist at the same time. Only after a successful
vacuuming the old data get deleted.
BTW, PG 9.2 has been out of service for quite some time. Only versions
11 to 15 are supported, with the support for version 11 lasting less
than a year.
Kind Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../8aacabc6-5919-5f37-9c9a-2a3cd67adfa3@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
@ 2023-02-07 18:45 ` Erik Serrano <eserranos@gmail.com>
2023-02-07 18:47 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
2023-02-07 19:03 ` Re: Vacuum Full not clean the table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Erik Serrano @ 2023-02-07 18:45 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; pgsql-admin
Thank you very much Holger for answering, I'll tell you... regarding your
question about whether it has enough provisioned space to be able to do the
vacuum, yes... yes it does.
Regarding your indication of the version we are working on, I know that 9.2
is out of all support and that its update is imminent... But that's how it
is right now and I have to deal with it...
For this reason, I appreciate that you can even guide me to see what is
happening or could have happened regarding the end of the process without
the expected result, which is cleaning the table.
Thank you in advance for your help
*Erik R. Serrano Saavedra*
*Ingeniero de Sistemas Informáticos*
* Data Base Administrator*
* eserranos@gmail.com <eserranos@gmail.com>*
* 998596691*
El mar, 7 feb 2023 a las 13:56, Holger Jakobs (<holger@jakobs.com>)
escribió:
> Am 07.02.23 um 17:51 schrieb Erik Serrano:
>
> Dear All,
>
> Along with saying hello, I would like to consult about a failure that is
> happening to me with a PG 9.2 base and with a table that weighs 116GB and
> its index 13Gb, mounted on a Linux Centos 7.2 with 4Core and 32Ram.
> The failure occurs mainly in that I execute a Vacuum Full on the table and
> it ends after 6 hours, but it does not clean anything. When checking the
> dead tuples I realize that they follow the same dirty records in the table.
> REF: (table of 420MM records with 20MM dead tuples)
>
> I hope you can guide me in finding a solution to clean that table.
>
> Greetings and many thanks to the Postgresql.org community
> Atte.
>
>
> *Erik Serrano*
>
>
> Hi Erik,
>
> Does it end cleanly or with an error after 6 hours?
>
> Do you have enough disk space for the vacuum full? The old and the newly
> written data have to exist at the same time. Only after a successful
> vacuuming the old data get deleted.
>
> BTW, PG 9.2 has been out of service for quite some time. Only versions 11
> to 15 are supported, with the support for version 11 lasting less than a
> year.
>
> Kind Regards,
>
> Holger
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
2023-02-07 18:45 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
@ 2023-02-07 18:47 ` Holger Jakobs <holger@jakobs.com>
2023-02-07 18:59 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Holger Jakobs @ 2023-02-07 18:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 07.02.23 um 19:45 schrieb Erik Serrano:
> Thank you very much Holger for answering, I'll tell you... regarding
> your question about whether it has enough provisioned space to be able
> to do the vacuum, yes... yes it does.
> Regarding your indication of the version we are working on, I know
> that 9.2 is out of all support and that its update is imminent... But
> that's how it is right now and I have to deal with it...
>
> For this reason, I appreciate that you can even guide me to see what
> is happening or could have happened regarding the end of the process
> without the expected result, which is cleaning the table.
>
> Thank you in advance for your help
>
> /*
> */
> /*Erik R. Serrano Saavedra*/
> /*Ingeniero de Sistemas Informáticos*/
> /* Data Base Administrator*/
> /*eserranos@gmail.com
> */
> /* 998596691*/
> /**/
>
>
> El mar, 7 feb 2023 a las 13:56, Holger Jakobs (<holger@jakobs.com>)
> escribió:
>
> Am 07.02.23 um 17:51 schrieb Erik Serrano:
>> Dear All,
>>
>> Along with saying hello, I would like to consult about a failure
>> that is happening to me with a PG 9.2 base and with a table that
>> weighs 116GB and its index 13Gb, mounted on a Linux Centos 7.2
>> with 4Core and 32Ram.
>> The failure occurs mainly in that I execute a Vacuum Full on the
>> table and it ends after 6 hours, but it does not clean anything.
>> When checking the dead tuples I realize that they follow the same
>> dirty records in the table. REF: (table of 420MM records with
>> 20MM dead tuples)
>>
>> I hope you can guide me in finding a solution to clean that table.
>>
>> Greetings and many thanks to the Postgresql.org community
>> Atte.
>>
>> /*
>> */
>> /*Erik Serrano*/
>>
>
> Hi Erik,
>
> Does it end cleanly or with an error after 6 hours?
>
> Do you have enough disk space for the vacuum full? The old and the
> newly written data have to exist at the same time. Only after a
> successful vacuuming the old data get deleted.
>
> BTW, PG 9.2 has been out of service for quite some time. Only
> versions 11 to 15 are supported, with the support for version 11
> lasting less than a year.
>
> Kind Regards,
>
> Holger
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
Scott Ribe is right, the percentage of dead tuples is not high. So there
no real reason to change anything.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../7927f0f1-517b-c3e3-a46a-edae56dee442@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
2023-02-07 18:45 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 18:47 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
@ 2023-02-07 18:59 ` Erik Serrano <eserranos@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Erik Serrano @ 2023-02-07 18:59 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
Holger,
Sorry for my ignorance but the vacuum process can discriminate if the table
requires or not to delete its dead tuples ???
thanks
*Erik R. Serrano Saavedra*
*Ingeniero de Sistemas Informáticos*
* Data Base Administrator*
* eserranos@gmail.com <eserranos@gmail.com>*
* 998596691*
El mar, 7 feb 2023 a las 15:47, Holger Jakobs (<holger@jakobs.com>)
escribió:
> Am 07.02.23 um 19:45 schrieb Erik Serrano:
>
> Thank you very much Holger for answering, I'll tell you... regarding your
> question about whether it has enough provisioned space to be able to do the
> vacuum, yes... yes it does.
> Regarding your indication of the version we are working on, I know that
> 9.2 is out of all support and that its update is imminent... But that's how
> it is right now and I have to deal with it...
>
> For this reason, I appreciate that you can even guide me to see what is
> happening or could have happened regarding the end of the process without
> the expected result, which is cleaning the table.
>
> Thank you in advance for your help
>
>
> *Erik R. Serrano Saavedra*
> *Ingeniero de Sistemas Informáticos*
> * Data Base Administrator*
>
> * eserranos@gmail.com <eserranos@gmail.com> *
> * 998596691*
>
>
> El mar, 7 feb 2023 a las 13:56, Holger Jakobs (<holger@jakobs.com>)
> escribió:
>
>> Am 07.02.23 um 17:51 schrieb Erik Serrano:
>>
>> Dear All,
>>
>> Along with saying hello, I would like to consult about a failure that is
>> happening to me with a PG 9.2 base and with a table that weighs 116GB and
>> its index 13Gb, mounted on a Linux Centos 7.2 with 4Core and 32Ram.
>> The failure occurs mainly in that I execute a Vacuum Full on the table
>> and it ends after 6 hours, but it does not clean anything. When checking
>> the dead tuples I realize that they follow the same dirty records in the
>> table. REF: (table of 420MM records with 20MM dead tuples)
>>
>> I hope you can guide me in finding a solution to clean that table.
>>
>> Greetings and many thanks to the Postgresql.org community
>> Atte.
>>
>>
>> *Erik Serrano*
>>
>>
>> Hi Erik,
>>
>> Does it end cleanly or with an error after 6 hours?
>>
>> Do you have enough disk space for the vacuum full? The old and the newly
>> written data have to exist at the same time. Only after a successful
>> vacuuming the old data get deleted.
>>
>> BTW, PG 9.2 has been out of service for quite some time. Only versions 11
>> to 15 are supported, with the support for version 11 lasting less than a
>> year.
>>
>> Kind Regards,
>>
>> Holger
>>
>> --
>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>
>> Scott Ribe is right, the percentage of dead tuples is not high. So there
> no real reason to change anything.
>
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
2023-02-07 18:45 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
@ 2023-02-07 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-07 21:36 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-02-07 19:03 UTC (permalink / raw)
To: Erik Serrano <eserranos@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin
Erik Serrano <eserranos@gmail.com> writes:
> For this reason, I appreciate that you can even guide me to see what is
> happening or could have happened regarding the end of the process without
> the expected result, which is cleaning the table.
If vacuum isn't removing dead tuples, it's because there is an open
transaction somewhere that can still "see" those tuples. Check for
old open transactions in pg_stat_activity and pg_prepared_xacts.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Re: Vacuum Full not clean the table Holger Jakobs <holger@jakobs.com>
2023-02-07 18:45 ` Re: Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 19:03 ` Re: Vacuum Full not clean the table Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-07 21:36 ` Erik Serrano <eserranos@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Erik Serrano @ 2023-02-07 21:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin
thanks Tom , Holder
*Erik R. Serrano Saavedra*
*Ingeniero de Sistemas Informáticos*
* Data Base Administrator*
* eserranos@gmail.com <eserranos@gmail.com>*
* 998596691*
El mar, 7 feb 2023 a las 16:03, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:
> Erik Serrano <eserranos@gmail.com> writes:
> > For this reason, I appreciate that you can even guide me to see what is
> > happening or could have happened regarding the end of the process without
> > the expected result, which is cleaning the table.
>
> If vacuum isn't removing dead tuples, it's because there is an open
> transaction somewhere that can still "see" those tuples. Check for
> old open transactions in pg_stat_activity and pg_prepared_xacts.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum Full not clean the table
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
@ 2023-02-07 18:17 ` Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2023-02-07 18:17 UTC (permalink / raw)
To: Erik Serrano <eserranos@gmail.com>; +Cc: pgsql-admin; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Feb 7, 2023, at 9:51 AM, Erik Serrano <eserranos@gmail.com> wrote:
>
> (table of 420MM records with 20MM dead tuples
Why do you need a vacuum full? Are you really being hurt by 5% dead tuples?
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-02-07 21:36 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-07 16:51 Vacuum Full not clean the table Erik Serrano <eserranos@gmail.com>
2023-02-07 16:56 ` Holger Jakobs <holger@jakobs.com>
2023-02-07 18:45 ` Erik Serrano <eserranos@gmail.com>
2023-02-07 18:47 ` Holger Jakobs <holger@jakobs.com>
2023-02-07 18:59 ` Erik Serrano <eserranos@gmail.com>
2023-02-07 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-07 21:36 ` Erik Serrano <eserranos@gmail.com>
2023-02-07 18:17 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox