Thread: How do I know my table is bloated?
Hi, everyone.
I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a table is bloated.
But how do I know when a table is bloated?
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Barboza Sent: Thursday, May 30, 2013 9:06 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] How do I know my table is bloated? Hi, everyone. I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a tableis bloated. But how do I know when a table is bloated? You could use PGSTATTUPLE extension, which you could read about here: http://www.postgresql.org/docs/9.2/static/pgstattuple.html Regards, Igor Neyman
On Thu, May 30, 2013 at 11:16 AM, Igor Neyman <ineyman@perceptron.com> wrote:
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Barboza
Sent: Thursday, May 30, 2013 9:06 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] How do I know my table is bloated?You could use PGSTATTUPLE extension, which you could read about here:
Hi, everyone.
I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a table is bloated.
But how do I know when a table is bloated?
http://www.postgresql.org/docs/9.2/static/pgstattuple.html
Regards,
Igor Neyman
Does auto-vaccum do the reindex process for us?
From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com] Sent: Thursday, May 30, 2013 10:42 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? On Thu, May 30, 2013 at 11:16 AM, Igor Neyman <ineyman@perceptron.com> wrote: From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Barboza Sent: Thursday, May 30, 2013 9:06 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] How do I know my table is bloated? Hi, everyone. I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a tableis bloated. But how do I know when a table is bloated? You could use PGSTATTUPLE extension, which you could read about here: http://www.postgresql.org/docs/9.2/static/pgstattuple.html Regards, Igor Neyman --Does auto-vaccum do the reindex process for us? No, autovacuum and reindexing are 2 completely different processes. Igor Neyman
Well, so I need to do reindex frequentely in database? Is that right?
Or should I use cluster, as David said?
How frequently should I do this operation?
On Thu, May 30, 2013 at 11:49 AM, Igor Neyman <ineyman@perceptron.com> wrote:
From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com]
Sent: Thursday, May 30, 2013 10:42 AM
To: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?--Does auto-vaccum do the reindex process for us?
On Thu, May 30, 2013 at 11:16 AM, Igor Neyman <ineyman@perceptron.com> wrote:
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Barboza
Sent: Thursday, May 30, 2013 9:06 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] How do I know my table is bloated?
Hi, everyone.
I saw some people talking about the reindex command and I read in the docs the one reason to use reindex is when a table is bloated.
But how do I know when a table is bloated?
You could use PGSTATTUPLE extension, which you could read about here:
http://www.postgresql.org/docs/9.2/static/pgstattuple.html
Regards,
Igor Neyman
No, autovacuum and reindexing are 2 completely different processes.
Igor Neyman
From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com] Sent: Thursday, May 30, 2013 11:10 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? Well, so I need to do reindex frequentely in database? Is that right? Or should I use cluster, as David said? How frequently should I do this operation? --------------------- Not necessarily. If there is no table or index bloat, which you should be able to check first, - why bother? Normally autovacuum does pretty good job even with default configuration parameters. If not, you could start with adjusting autovacuum parameters. And reindex only as a last resort, when your index size grows "uncontrollably". Igor Neyman
I have some tables that I do a lot of updates, deletes and inserts.
So I am worried that my cluster can grow up to a huge size...
The best option would be to create a scheduled process to check if it is bloated and if so, reindex?
On Thu, May 30, 2013 at 12:17 PM, Igor Neyman <ineyman@perceptron.com> wrote:
Sent: Thursday, May 30, 2013 11:10 AMTo: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?Well, so I need to do reindex frequentely in database? Is that right?---------------------
Or should I use cluster, as David said?
How frequently should I do this operation?
Not necessarily.
If there is no table or index bloat, which you should be able to check first, - why bother?
Normally autovacuum does pretty good job even with default configuration parameters.
If not, you could start with adjusting autovacuum parameters.
And reindex only as a last resort, when your index size grows "uncontrollably".
Igor Neyman
From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com] Sent: Thursday, May 30, 2013 12:04 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? I have some tables that I do a lot of updates, deletes and inserts. So I am worried that my cluster can grow up to a huge size... The best option would be to create a scheduled process to check if it is bloated and if so, reindex? -------------------------- Depends on whether it's table bloat or index bloat. But first, you try to minimize bloat by tuning autovacuum. Igor Neyman
On Thu, May 30, 2013 at 1:35 PM, Igor Neyman <ineyman@perceptron.com> wrote:
Sent: Thursday, May 30, 2013 12:04 PMTo: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?I have some tables that I do a lot of updates, deletes and inserts.--------------------------
So I am worried that my cluster can grow up to a huge size...
The best option would be to create a scheduled process to check if it is bloated and if so, reindex?
Depends on whether it's table bloat or index bloat.
But first, you try to minimize bloat by tuning autovacuum.
Igor Neyman
I am using the defualt values for autovaccum.
How do you suggest to tune the autovacuum?
If the problem is index bloat, autovaccum won't be a solution, am I right?
From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com] Sent: Thursday, May 30, 2013 2:24 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? I am using the defualt values for autovaccum. How do you suggest to tune the autovacuum? If the problem is index bloat, autovaccum won't be a solution, am I right? ---------------------------------------------------------- Rodrigo, I think you are putting "a cart in front of the horse" (so to speak). Did you verify that you have bloated indexes? Under normal conditions it shouldn't happen. From docs (http://www.postgresql.org/docs/9.2/static/routine-reindex.html): "B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility ofinefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore,a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space." So, yes, if index is really gets bloated than reindexing fixes this problem. Igor Neyman
On Thu, May 30, 2013 at 3:36 PM, Igor Neyman <ineyman@perceptron.com> wrote:
Sent: Thursday, May 30, 2013 2:24 PMTo: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?I am using the defualt values for autovaccum.----------------------------------------------------------
How do you suggest to tune the autovacuum?
If the problem is index bloat, autovaccum won't be a solution, am I right?
Rodrigo,
I think you are putting "a cart in front of the horse" (so to speak).
Did you verify that you have bloated indexes? Under normal conditions it shouldn't happen.
From docs (http://www.postgresql.org/docs/9.2/static/routine-reindex.html):
"B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space."
So, yes, if index is really gets bloated than reindexing fixes this problem.
Igor Neyman
Well, maybe I am.
But I am worried because I know that there are some tables that do lots of updates and delete.
As this concept is new for me, I am trying to be prepared to detect a situation like this.
From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com] Sent: Thursday, May 30, 2013 2:50 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? Well, maybe I am. But I am worried because I know that there are some tables that do lots of updates and delete. As this concept is new for me, I am trying to be prepared to detect a situation like this. ------------------------------------------ It all depends on pattern of your inserts/updates/deletes. If your index accumulates lots of almost (but not completely) empty pages with just few entries left, than - yes, REINDEX"is your friend". b.t.w., this concept is not unique to Postgres, it's just a nature of B-tree indexes. Igor
On Thu, May 30, 2013 at 3:55 PM, Igor Neyman <ineyman@perceptron.com> wrote:
Sent: Thursday, May 30, 2013 2:50 PMTo: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?Well, maybe I am.------------------------------------------
But I am worried because I know that there are some tables that do lots of updates and delete.
As this concept is new for me, I am trying to be prepared to detect a situation like this.
It all depends on pattern of your inserts/updates/deletes.
If your index accumulates lots of almost (but not completely) empty pages with just few entries left, than - yes, REINDEX "is your friend".
b.t.w., this concept is not unique to Postgres, it's just a nature of B-tree indexes.
Igor
I see...
I guess the only way to know is if I suspect that size of my db is growing unexpected.
But it is good to know that a b-tree concept concept.
Because I came from a very old mysql installation (that is way postgres is new for me) and I am trying to keep my postgres the most stable possible.
On Thu, May 30, 2013 at 6:05 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > I saw some people talking about the reindex command and I read in the docs > the one reason to use reindex is when a table is bloated. > But how do I know when a table is bloated? Take a look at the pgcompactor tool https://code.google.com/p/pgtoolkit/. This will find out what tables and indexes in your cluster are bloated and softly (without heavy locks) remove the bloat. Before using it setup the pgstattuple extension in all the databases you age going to check for bloat. Some usage examples are below. To get bloat statistics for all the cluster do: pgcompactor --all --reindex --verbose info --dry-run To remove bloat from all the cluster: pgcompactor --all --reindex --verbose info For a specific table use: pgcompactor --table tablename --reindex --verbose info --dry-run You can also specify --dbname, --schema, --exclude-<dbname/schema/table>, etc. For all the options: pgcompactor --man -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
On Fri, May 31, 2013 at 2:06 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
Take a look at the pgcompactor tool> I saw some people talking about the reindex command and I read in the docs
> the one reason to use reindex is when a table is bloated.
> But how do I know when a table is bloated?
https://code.google.com/p/pgtoolkit/. This will find out what tables
and indexes in your cluster are bloated and softly (without heavy
locks) remove the bloat. Before using it setup the pgstattuple
extension in all the databases you age going to check for bloat. Some
usage examples are below.
To get bloat statistics for all the cluster do:
pgcompactor --all --reindex --verbose info --dry-run
To remove bloat from all the cluster:
pgcompactor --all --reindex --verbose info
For a specific table use:
pgcompactor --table tablename --reindex --verbose info --dry-run
You can also specify --dbname, --schema, --exclude-<dbname/schema/table>, etc.
For all the options:
pgcompactor --man
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com
Thanks, guys!