pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? 5+ messages / 4 participants [nested] [flat]
* Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? @ 2018-07-18 09:13 Raghavendra Rao J S V <raghavendrajsv@gmail.com> 0 siblings, 2 replies; 5+ messages in thread From: Raghavendra Rao J S V @ 2018-07-18 09:13 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org We have thousands of tables. But out of those tables, around 20 to 40 tables are always busy due to that those tables are bloating. In order to avoid this we are running a shell script which performs vacuum full on the tables which has more than ten thousand dead tuples. While running this we are stopping all application processors and running vacuum full on the tables which has more dead tuples. 1. Is it ok to run *vacuum full verbose* command for live database for the tables which has more dead tuples(greater than)? 2. Does it cause any *adverse *effect? Please clarify me. Thanks in advance. -- Regards, Raghavendra Rao J S V ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? @ 2018-07-18 10:32 Fabio Pardi <f.pardi@portavita.eu> parent: Raghavendra Rao J S V <raghavendrajsv@gmail.com> 1 sibling, 0 replies; 5+ messages in thread From: Fabio Pardi @ 2018-07-18 10:32 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org Hi Raghavendra answers in line here below: On 18/07/18 11:13, Raghavendra Rao J S V wrote: > We have thousands of tables. But out of those tables, around 20 to 40 tables are always busy due to that those tables are bloating. > > In order to avoid this we are running a shell script which performs vacuum full on the tables which has more than ten thousand dead tuples. While running this we are stopping all application processors and running vacuum full on the tables which has more dead tuples. > > 1. Is it ok to run *vacuum full verbose* command for live database for the tables which has more dead tuples(greater than)? > nope > 1. Does it cause any *adverse *effect? > > exclusively locks the table. Recipe for disaster. What's wrong with the normal operations of autovacuum? regards, fabio pardi > Please clarify me. Thanks in advance. > > -- > Regards, > Raghavendra Rao J S V > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? @ 2018-07-18 13:18 Adrian Klaver <adrian.klaver@aklaver.com> parent: Raghavendra Rao J S V <raghavendrajsv@gmail.com> 1 sibling, 1 reply; 5+ messages in thread From: Adrian Klaver @ 2018-07-18 13:18 UTC (permalink / raw) To: Raghavendra Rao J S V <raghavendrajsv@gmail.com>; pgsql-general@lists.postgresql.org On 07/18/2018 02:13 AM, Raghavendra Rao J S V wrote: > We have thousands of tables. But out of those tables, around 20 to 40 > tables are always busy due to that those tables are bloating. Define bloating? > > In order to avoid this we are running a shell script which performs > vacuum full on the tables which has more than ten thousand dead tuples. Out of how many live tuples? > While running this we are stopping all application processors and > running vacuum full on the tables which has more dead tuples. > > 1. Is it ok to run *vacuum full verbose* command for live database for > the tables which has more dead tuples(greater than)? > 2. Does it cause any *adverse *effect? https://www.postgresql.org/docs/10/static/sql-vacuum.html "FULL Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table. " > > > Please clarify me. Thanks in advance. > > -- > Regards, > Raghavendra Rao J S V > -- Adrian Klaver adrian.klaver@aklaver.com ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? @ 2018-07-19 11:00 Raghavendra Rao J S V <raghavendrajsv@gmail.com> parent: Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 1 reply; 5+ messages in thread From: Raghavendra Rao J S V @ 2018-07-19 11:00 UTC (permalink / raw) To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org Please find my inline comments. On 18 July 2018 at 18:48, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 07/18/2018 02:13 AM, Raghavendra Rao J S V wrote: > >> We have thousands of tables. But out of those tables, around 20 to 40 >> tables are always busy due to that those tables are bloating. >> > > Define bloating? > > Table contains more dead tuples more than 5000 records. Sometimes few >> tables may contains 50million records and size also in GB's. Refer below >> screen print. >> > > >> >> > In order to avoid this we are running a shell script which performs vacuum >> full on the tables which has more than ten thousand dead tuples. >> > > Out of how many live tuples? > Very less > > While running this we are stopping all application processors and running >> vacuum full on the tables which has more dead tuples. >> >> 1. Is it ok to run *vacuum full verbose* command for live database for >> the tables which has more dead tuples(greater than)? >> 2. Does it cause any *adverse *effect? >> > > > https://www.postgresql.org/docs/10/static/sql-vacuum.html > > "FULL > > Selects “full” vacuum, which can reclaim more space, but takes much > longer and exclusively locks the table. This method also requires extra > disk space, since it writes a new copy of the table and doesn't release the > old copy until the operation is complete. Usually this should only be used > when a significant amount of space needs to be reclaimed from within the > table. > > " > > >> >> Please clarify me. Thanks in advance. >> >> -- >> Regards, >> Raghavendra Rao J S V >> >> > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Regards, Raghavendra Rao J S V Mobile- 8861161425 Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadimage.png (image/png, 419.8K) download
view image
* Re: Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? @ 2018-07-19 15:06 Ron <ronljohnsonjr@gmail.com> parent: Raghavendra Rao J S V <raghavendrajsv@gmail.com> 0 siblings, 0 replies; 5+ messages in thread From: Ron @ 2018-07-19 15:06 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org On 07/19/2018 06:00 AM, Raghavendra Rao J S V wrote: > Please find my inline comments. > > On 18 July 2018 at 18:48, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 07/18/2018 02:13 AM, Raghavendra Rao J S V wrote: > > We have thousands of tables. But out of those tables, around 20 to > 40 tables are always busy due to that those tables are bloating. > > > Define bloating? > > Table contains more dead tuples more than 5000 records. > Sometimes few tables may contains 50million records and size also > in GB's. Refer below screen print. > > > > > > > When's your maintenance window? -- Angular momentum makes the world go 'round. Attachments:^ permalink raw reply [nested|flat] 5+ messages in threadimage.png (image/png, 419.8K) download
view image
end of thread, other threads:[~2018-07-19 15:06 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-07-18 09:13 Is it ok to run vacuum full verbose command for live database for the tables which has more dead tuples? Raghavendra Rao J S V <raghavendrajsv@gmail.com> 2018-07-18 10:32 ` Fabio Pardi <f.pardi@portavita.eu> 2018-07-18 13:18 ` Adrian Klaver <adrian.klaver@aklaver.com> 2018-07-19 11:00 ` Raghavendra Rao J S V <raghavendrajsv@gmail.com> 2018-07-19 15:06 ` Ron <ronljohnsonjr@gmail.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