Hi Rafael, On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote: > > > I have some doubts about Vacuum Full. There We go: > > > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean > > > "dead space" on the disc, and reorganize the relation at the physical > > level? > > > If it's true, so doing this may speed up select's, while the Postgres > > will > > > going to do less hit in the disc. I'm right? > > > > Generally speaking, vacuum full should not normally be needed. > > Regular vacuuming should free up enough free space that the table > > reaches an equilibrium where it has some small percentage of available > > space (5 to 15% or so) and stay there. > > So, why Vacuum Full should not nomally be needed? Vacuum Analyze is enough > for Maintenance Base? You should normally be using autovacuum, possibly slightly tuned, to keep everything nice. > One more question, If I understood Vacuum Full it's similar to Defrag Tool > like Defrag Windows. So, like defraging Windows, it's speed up a little (the > disc is going to read the blocks faster) , Vacuuming Full Postgres should > have the same behavior?! VACUUM FULL should be renamed to "I totally bloated my database, please fix it" or something like that. It is a very intrusive operation (prevents write access to the table IIRC), so it's nothing for regular operation. If you need VACUUM FULL, something went wrong before. Maybe a command name like "UNBLOAT DATABASE" or "REBUILD TABLES" would be more suited. @Developers: I'm serious. People perceive VACUUM FULL as just a more intense version of VACUUM. > > > 2) Doing only Vacuum Analyze I have a enlargement of the parameter > > > max_fsm_pages. Does it have any impact in the operation? > > > Should I increment the value in the conf to be so large than the Vacuum > > > Verbose shows me? > > > > If the needed fsm settings need to keep increasing then something is wrong. > > The fsm_pages values incresead from 120000 to 320000, and now is around that > every day. > On more question, the postgres.conf max_fsm_pages is set to 150000, and > every vacuum hint me to increase this parameter. Does it have any impact in > the normal operation of Postgres? Yes, it takes memory (6 bytes per page). If you have max_fsm_pages too low, you'll get a bloated database because Postgres cannot keep track of which pages are useable in a relation - it needs to append at the end! Then you'll need VACUUM FULL sooner or later. I've heard a rule of thumb: 65536 pages per GB of data. > > > 3) There are differences in performace of Vacuum Full between versions > > 8.1.4 > > > and 8.3.7? > > > As soon as possible we are going to migrate the Postgres to 8.3.7, just > > > waiting the finish of the tests of the software in the new version. > > > > Yeah, 8.3 is faster. > > > > > 4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or > > Reindex > > > and Vacuum? > > > Running Vacuum I have a Index Bloat, right?! So I have to run Reindex > > afet > > > Vacuum?! > > > If true I'll change the script to make first Vacuum and then Reindex. > > > > yep, vacuum full, then reindex. If matters are really worse, it's faster to pg_dump, drop/create and pg_restore. You'll get a nice fresh database afterwards with no bloat. I've seen a database perform VACUUM FULL on pg_largeobject for two weeks! (Only about 200 GB or so of data.) -- "What we nourish flourishes." - "Was wir nähren erblüht." www.lichtkreis-chemnitz.de www.craniosacralzentrum.de -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
