Quantcast

fsm and vacuum

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

fsm and vacuum

Little, Douglas

We’re new to Greenplum, based on PG (8.2.13).  we weren’t advised to increase the max_fsm_relations switch as our db has grown.

Currently we’re nearly 14k tables/indexes and the switch is set to 1000.

We’ve got it updated now, but wondering about the effect & recovery.

 

We’ve regularly been reloading tables with CTAS.

I’ve done some experiments and want to confirm my understanding.

 

1. Vacuum full only compacted the  oldest? tables (reducing pages from 109 to 5)

2.  vacuum full frees pages, but doesn’t reduce the file size (newer tables).  Table needed to be recreated to reduce size

 

I’ve searched the archives and haven’t found what I’m looking for.  I hope someone would just dash off a simple explanation.

 

1.  when are relations added to the fsm? (at create, or some other time)

2.  will vacuum (full) add a relation to the fsm - ? 

3.  if bloated table is vacuum full’d,  will the free space pages be use?

 

My assumptions are:

1. relation added to fsm list at create time.

2. fsm_pages keep track of free space for the relations being tracked in the fsm_relation list

3. table recreate is the only way to have a relation tracked.

 

Thanks in advance.

 

 

 

Doug Little

 

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

[hidden email]

 cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
Le 02/12/2010 22:00, Little, Douglas a écrit :

> We're new to Greenplum, based on PG (8.2.13).  we weren't advised to increase the max_fsm_relations switch as our db has grown.
> Currently we're nearly 14k tables/indexes and the switch is set to 1000.
> We've got it updated now, but wondering about the effect & recovery.
>
> We've regularly been reloading tables with CTAS.
> I've done some experiments and want to confirm my understanding.
>
> 1. Vacuum full only compacted the  oldest? tables (reducing pages from 109 to 5)
> 2.  vacuum full frees pages, but doesn't reduce the file size (newer tables).  Table needed to be recreated to reduce size
>
> I've searched the archives and haven't found what I'm looking for.  I hope someone would just dash off a simple explanation.
>
> 1.  when are relations added to the fsm? (at create, or some other time)

During vacuum, if there are dead spaces in the table.

> 2.  will vacuum (full) add a relation to the fsm - ?

Yes, for tables with dead spaces.

> 3.  if bloated table is vacuum full'd,  will the free space pages be use?

If vacuum full did its job, it shouldn't be.

> My assumptions are:
> 1. relation added to fsm list at create time.

Nope.

> 2. fsm_pages keep track of free space for the relations being tracked in the fsm_relation list

At vacuum time.

> 3. table recreate is the only way to have a relation tracked.

Nope.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
Hi,

Le 03/12/2010 00:19, Little, Douglas a écrit :
> [...]
> Thanks for the response.

No problem, but keep your anwser to the list, even if it's not the good
one :)

> Still a bit confused.
> Q: The guk settings  max_fsm_relations/pages are used by the db engine to set the size of the freespace map.

In memory, yes.

> Q: vacuum scans thru the file and adds free slots to the map when a table is vacuumed

Yes.

> Q: the map is used by the engine when inserting a row (new or versioned).

Yes.

> So is the only way to initialize the fsm to run vacuum?

Yes.

> We're experiencing problems using vacuum full.  GP recommends ctas/truncate/reload as alternative.
> Obviously won't work for system tables.
> My thought is vacuum full isn't working because the fsm was undersized.

vacuum full first scans the whole table to find free space, and then
scans backward to move every still-in-use space at the beginning of the
table. I don't know if vacuum full puts its information in the fsm, but
I believe so. So, if the fsm is undersized, you risk to have a not fully
effective vacuum full.

Anyway, you should probably not use vacuum full, unless you have a
*really* good reason.

Remember to REINDEX after your VACUUM FULL. Meaning you should probably
use CLUSTER, which will be fully effective and quicker. But you need an
index.

> Anything in the developers docs that would help me understand how it works?

This could be of interest:

  http://wiki.postgresql.org/wiki/VACUUM_FULL


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Little, Douglas

Guillaume,

 

Given this syscat bloat, what would you recommend doing?

 

schemaname

tablename

reltuples

relpages

otta

tbloat

wastedpages

wastedbytes

wastedsize

pg_catalog

pg_exttable

7092

3137

49

64

3088

101187584

97 MB

pg_catalog

pg_shdepend

48674

2349

84

28

2265

74219520

71 MB

pg_catalog

pg_shdepend

48674

2349

84

28

2265

74219520

71 MB

pg_catalog

gp_distribution_policy

19810

2131

38

56.1

2093

68583424

65 MB

pg_catalog

pg_class

33044

10139

235

43.1

9904

324534272

310 MB

pg_catalog

pg_class

33044

10139

235

43.1

9904

324534272

310 MB

pg_catalog

pg_attribute

513871

98646

2135

46.2

96511

3162472448

3016 MB

 

Thanks

Doug

 

 

-----Original Message-----
From: Guillaume Lelarge [mailto:[hidden email]]
Sent: Friday, December 03, 2010 2:55 AM
To: Little, Douglas
Cc: PgAdmin Support
Subject: Re: [pgadmin-support] fsm and vacuum

 

Hi,

 

Le 03/12/2010 00:19, Little, Douglas a écrit :

> [...]

> Thanks for the response.

 

No problem, but keep your anwser to the list, even if it's not the good

one :)

 

> Still a bit confused.

> Q: The guk settings  max_fsm_relations/pages are used by the db engine to set the size of the freespace map.

 

In memory, yes.

 

> Q: vacuum scans thru the file and adds free slots to the map when a table is vacuumed

 

Yes.

 

> Q: the map is used by the engine when inserting a row (new or versioned).

 

Yes.

 

> So is the only way to initialize the fsm to run vacuum?

 

Yes.

 

> We're experiencing problems using vacuum full.  GP recommends ctas/truncate/reload as alternative.

> Obviously won't work for system tables.

> My thought is vacuum full isn't working because the fsm was undersized.

 

vacuum full first scans the whole table to find free space, and then

scans backward to move every still-in-use space at the beginning of the

table. I don't know if vacuum full puts its information in the fsm, but

I believe so. So, if the fsm is undersized, you risk to have a not fully

effective vacuum full.

 

Anyway, you should probably not use vacuum full, unless you have a

*really* good reason.

 

Remember to REINDEX after your VACUUM FULL. Meaning you should probably

use CLUSTER, which will be fully effective and quicker. But you need an

index.

 

> Anything in the developers docs that would help me understand how it works?

 

This could be of interest:

 

  http://wiki.postgresql.org/wiki/VACUUM_FULL

 

 

--

Guillaume

http://www.postgresql.fr

http://dalibo.com

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
Le 03/12/2010 14:40, Little, Douglas a écrit :
> [...]
> Given this syscat bloat, what would you recommend doing?
>

Sorry but that's really unreadable.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Michael Shapiro-5
In reply to this post by Guillaume Lelarge-3
The document http://wiki.postgresql.org/wiki/VACUUM_FULL says:

VACUUM FULL, unlike VACUUM, tuples data that has not been deleted, moving it into spaces earlier in the file that have been freed. Once it's created a free space at the end of the file, it truncates the file so that the OS knows that space is free and may be reused for other things. Moving in-use data around this way has some major downsides and side-effects, especially the way VACUUM FULL does it. There are better ways to free space if you need to and better ways to optimize tables (see below) so you should essentially never use VACUUM FULL.


PgAdmin does not give the user a comparable warning when it goes to execute a VACCUM FULL. Given the potential problems with the FULL option, would it make sense for PgAdmin to issue a warning to this effect?

On Fri, Dec 3, 2010 at 2:54 AM, Guillaume Lelarge <[hidden email]> wrote:

> We're experiencing problems using vacuum full. 
This could be of interest:

 http://wiki.postgresql.org/wiki/VACUUM_FULL


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
Le 03/12/2010 15:17, Michael Shapiro a écrit :

> The document http://wiki.postgresql.org/wiki/VACUUM_FULL says:
>
> VACUUM FULL, unlike VACUUM, tuples data that has not been deleted, moving it
> into spaces earlier in the file that have been freed. Once it's created a
> free space at the end of the file, it truncates the file so that the OS
> knows that space is free and may be reused for other things. Moving in-use
> data around this way has some major downsides and side-effects, especially
> the way VACUUM FULL does it. There are better ways to free space if you need
> to and better ways to optimize tables (see below) so *you should essentially
> never use VACUUM FULL*.
>
>
> PgAdmin does not give the user a comparable warning when it goes to execute
> a VACCUM FULL. Given the potential problems with the FULL option, would it
> make sense for PgAdmin to issue a warning to this effect?
>

I'm not sure this is the role of pgAdmin to warn people they are doing
potentially stupid things.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Michael Shapiro-5
I understand, but in this case, since the option is offered next to the safe one, most people won't know it isn't safe.
I certainly didn't until I read this posting. I know generally what vacuuming does, but I had no idea that postgres offered a potentially damaging option. Also, PgAdmin sometimes tells me that a table needs vacuuming, so it is already "advising" people in that area ...

On Fri, Dec 3, 2010 at 9:19 AM, Guillaume Lelarge <[hidden email]> wrote:
Le 03/12/2010 15:17, Michael Shapiro a écrit :
> The document http://wiki.postgresql.org/wiki/VACUUM_FULL says:
>
> VACUUM FULL, unlike VACUUM, tuples data that has not been deleted, moving it
> into spaces earlier in the file that have been freed. Once it's created a
> free space at the end of the file, it truncates the file so that the OS
> knows that space is free and may be reused for other things. Moving in-use
> data around this way has some major downsides and side-effects, especially
> the way VACUUM FULL does it. There are better ways to free space if you need
> to and better ways to optimize tables (see below) so *you should essentially
> never use VACUUM FULL*.
>
>
> PgAdmin does not give the user a comparable warning when it goes to execute
> a VACCUM FULL. Given the potential problems with the FULL option, would it
> make sense for PgAdmin to issue a warning to this effect?
>

I'm not sure this is the role of pgAdmin to warn people they are doing
potentially stupid things.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Little, Douglas

Somehow this moved to the pgadmin list.  It was intended for pgsql-admin.  My apologies.

This is a dba task,  I’d never expect pgadmin would do this.

 

 

From: Michael Shapiro [mailto:[hidden email]]
Sent: Friday, December 03, 2010 9:26 AM
To: Guillaume Lelarge
Cc: Little, Douglas; PgAdmin Support
Subject: Re: [pgadmin-support] fsm and vacuum

 

I understand, but in this case, since the option is offered next to the safe one, most people won't know it isn't safe.
I certainly didn't until I read this posting. I know generally what vacuuming does, but I had no idea that postgres offered a potentially damaging option. Also, PgAdmin sometimes tells me that a table needs vacuuming, so it is already "advising" people in that area ...

On Fri, Dec 3, 2010 at 9:19 AM, Guillaume Lelarge <[hidden email]> wrote:

Le 03/12/2010 15:17, Michael Shapiro a écrit :


> The document http://wiki.postgresql.org/wiki/VACUUM_FULL says:
>
> VACUUM FULL, unlike VACUUM, tuples data that has not been deleted, moving it
> into spaces earlier in the file that have been freed. Once it's created a
> free space at the end of the file, it truncates the file so that the OS
> knows that space is free and may be reused for other things. Moving in-use
> data around this way has some major downsides and side-effects, especially
> the way VACUUM FULL does it. There are better ways to free space if you need
> to and better ways to optimize tables (see below) so *you should essentially
> never use VACUUM FULL*.
>
>
> PgAdmin does not give the user a comparable warning when it goes to execute
> a VACCUM FULL. Given the potential problems with the FULL option, would it
> make sense for PgAdmin to issue a warning to this effect?
>

I'm not sure this is the role of pgAdmin to warn people they are doing
potentially stupid things.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

 

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
In reply to this post by Michael Shapiro-5
Le 03/12/2010 16:25, Michael Shapiro a écrit :
> I understand, but in this case, since the option is offered next to the safe
> one, most people won't know it isn't safe.

Both are safe. They don't offer the same service. VACUUM will allow
PostgreSQL to reuse dead space, VACUUM FULL will free space on the hard
drive (and have a negative effect on the table's indexes if you don't do
a REINDEX).

> I certainly didn't until I read this posting. I know generally what
> vacuuming does, but I had no idea that postgres offered a potentially
> damaging option. Also, PgAdmin sometimes tells me that a table needs
> vacuuming, so it is already "advising" people in that area ...

Yea, the guru hints. Don't like that and don't really want to extend them.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
In reply to this post by Michael Shapiro-5
Le 03/12/2010 16:24, Little, Douglas a écrit :
> Michael,
>
> I hear that vacuum full shouldn't be used.
> But what impact does the bloat have on performance?

Bigger on your hard, so slower to scan. And bigger in memory, so you
need to push blocks out to scan it. Other than that, not much.

It's better to not have a lot of bloat. I mean, if you deleted half of
your 20 GB table, it makes sens to do a VACUUM FULL.

> And,   what do we do about the bloat in the pg tables?

Depends on the percentage of bloat and table size.

> I envision this would be a 1 time fix, now that we've got the fsm_relations set properly.

Could be.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
In reply to this post by Little, Douglas
Le 03/12/2010 16:28, Little, Douglas a écrit :
> Somehow this moved to the pgadmin list.  It was intended for pgsql-admin.  My apologies.
> This is a dba task,  I'd never expect pgadmin would do this.
>

It didn't *move* to the pgadmin list, your first mail was sent there.
pgAdmin doesn't do it itself, but it allows the user to ask for it.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fsm and vacuum

Guillaume Lelarge-3
In reply to this post by Guillaume Lelarge-3
Le 03/12/2010 16:19, Little, Douglas a écrit :

>
>
> Sorry,  outlook must of clobbered the table.
>
> [cid:image001.png@01CB92CB.11EA5F60]
>
>
>
> Pg_attribute  has 513871 tuples and should take up 2135 pages.  It's currently allocated to 98646 pages.
>
> Given that much bloat,  what would you suggest doing?
>
> My guess is nothing.  The fsm/vacuum process will reuse empty slots over time.
>

There's 96% of bloat? VACUUM FULL is needed here. According to your
numbers, it means you have a 770MB table of which only 16MB are actual data.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Sent via pgadmin-support mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Loading...