Re: Contrib reindex script:
От | Jason Earl |
---|---|
Тема | Re: Contrib reindex script: |
Дата | |
Msg-id | 87lmau1r2i.fsf@npa01zz001.simplot.com обсуждение исходный текст |
Ответ на | Re: Contrib reindex script: (Shaun Thomas <sthomas@townnews.com>) |
Список | pgsql-general |
Shaun Thomas <sthomas@townnews.com> writes: > On 7 May 2002, Jason Earl wrote: > > > Hey thanks, I was just wondering if such a thing were available. > > Here also is an updated version of the script. I've cleaned up some > of the code, heavily commented it, and fixed a bug or two. > Remember, this is basically just vacuumdb, so it'll take all the > same parameters (except the obvious ones like -z and -f) and you can > put it in your postgres bin directory. I have a line in cron that > runs it every two hours with the -a option with the same user that > owns the install. > > Works great! Perfect. Most of my tables don't get too much turnover, my application is basically insert-heavy (which is why I went with PostgreSQL in the first place), so I don't really have problems with indexes growing out of control. But with automated scripts to maintain the database PostgreSQL can be used in a lot more applications. > > How has your migration to 7.2? Are you still have problems with > > the database growing out of control? > > Actually, now that I have this script to basically be a vacuum > script for indexes to go along with vacuumdb, I've arrested the > database growth. The database I was groaning about before is > standing firm around 87MB instead of the 300MB it would normally be > by now. That is good to know. My own tests have shown that PostgreSQL is now to the point where I consider it to be more than acceptable for use in 24/7 applications, but I don't have the real-world experience with high turnover tables that you have. > So far, 7.2 is fine. Database load is a non issue, growth is gone > thanks to the reindex script, and I couldn't be happier. Now the > real question is, why can't Postgres have a monitor that does these > two things (vacuum, reindex) automatically throughout the day? > Something that just trawls the tables doing a continuous partial > vacuum, and triggers on deletes and updates to keep the indexes > consistant. I believe that this particular issue has come up on hackers a couple of times. However, with workable vacuum and reindex scripts this sort of thing shouldn't be too hard to automate. There is something to be said for a more flexible manual solution as it allows the DBA to vacuum and reindex according to their own needs. Then again, perhaps I am just biased due to the fact that I like software with lots of buttons and levers to play with. Worse comes to worse a few cron jobs and you pretty much have a monitor that automatically reindexes and vacuums throughout the day :). > If what they say is true about row reuse, then I could get rid of > both my vacuumdb and reindexdb scripts at that point. > > 7.3? Take Care, Jason
В списке pgsql-general по дате отправления: