Re: PG 7.2 on Linux: where's the space?
От | Robert Treat |
---|---|
Тема | Re: PG 7.2 on Linux: where's the space? |
Дата | |
Msg-id | 1030463868.10062.31.camel@camel обсуждение исходный текст |
Ответ на | Re: PG 7.2 on Linux: where's the space? (Kevin Brannen <kevinb@nurseamerica.net>) |
Список | pgsql-admin |
On Fri, 2002-08-23 at 14:15, Kevin Brannen wrote: > Jeff Boes wrote: > > > > 1) More frequent VACUUM FULL operations? > > Possibly. However, it's been noted that if you do a lot of deletes > and/or updates so that you have a lot of "dead" tuples, you probably > need to increase your "fsm_*" parameters. Search the newsgroup archive > for their names for advice on size. > Just to clarify, vacuum full recovers *all* possible space in your database, regardless of FSM settings. It is the regular "lazy" vacuum that hinges on your FSM. That said, you'll want to lazy vacuum your highest "churning" tables at least once per filling of your free space map. You should be able to lazy vacuum with little/no performance hit (I had one tables getting 2000+ updates a minute that I could vacuum every 5 minutes and had no noticeable impact) When trying to figure out the frequency, make sure you do vacuum analyze verbose and watch the output, it will tell you how many tuples you are recovering. > > > > 2) REINDEX our biggest tables? > > It has also been noted that indexes can do this too, again a high churn > rate is the cause. Off the top of my head I don't remember how to find > this out, but it's in the newsgroup archives too. But I think one of > the things you can do was: > > select * from pg_class order by relpages desc; > > to try to find out where the space is going. Search for something like > that. > The above query should work well to find your biggest indexes. If you do rebuild them make sure to get sizes of the indexes before and after you do it to make sure you're only dropping indexes that are really growing. BTW - I tend to use a query like this, substituting names and wildcards where appropriate: SELECT relname, relkind, relpages, relpages / 128 AS MB FROM pg_class WHERE relname LIKE 'tablename'; > > > > 3) Periodicly dump and reload our biggest tables? > > I hope not! :-) Increase your fsm_* values and see what that does for > you, before you take more drastic measures. > > HTH, > Kevin > > > > > I'm really hoping this last one is not the answer, as we'd have to > > take the system offline for hours every week to accomplish this. > You'll definitely want to modify your fsm before you start do dump/reloads. Remember though the first step is to figure out which tables are doing the churning and how much, you can proceed better from there. Robert Treat
В списке pgsql-admin по дате отправления: