Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Oliver Scheit
3) using PG 7.3 or less, you will also need to REINDEX these tables+indexes often (daily?). This issue will go away in 7.4, which should make you an early adopter of 7.4. Is this true? Haven't heard of this before. If so, how can this be managed in a cronjob? For the hourly VACUUM there's

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Christopher Kings-Lynne
3) using PG 7.3 or less, you will also need to REINDEX these tables+indexes often (daily?). This issue will go away in 7.4, which should make you an early adopter of 7.4. Try monthly maybe. Is this true? Haven't heard of this before. If so, how can this be managed in a cronjob?

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Oliver Scheit
Yes, there is reindexdb :) Not on my machine. (RH 7.3) #rpm -qa|grep postgres postgresql-server-7.2.3-5.73 postgresql-libs-7.2.3-5.73 postgresql-devel-7.2.3-5.73 postgresql-7.2.3-5.73 What package am I missing? regards, Oliver Scheit ---(end of

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Christopher Kings-Lynne
#rpm -qa|grep postgres postgresql-server-7.2.3-5.73 postgresql-libs-7.2.3-5.73 postgresql-devel-7.2.3-5.73 postgresql-7.2.3-5.73 What package am I missing? It's part of postgresql 7.3. Just get it from the 7.3 contrib dir - it works fine with 7.2 Note that this index growth problem has

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Oliver Scheit
It's part of postgresql 7.3. Just get it from the 7.3 contrib dir - it works fine with 7.2 That's nice to hear. Thanx for that info. That's alright - cron job it for once a month - that's what I do. Basically the problem is that in certain cases (monotonically increasing serial indexes)

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-18 Thread Josh Berkus
Guys, I also wrote a perl script that reindexes all tables, if anyone can't get reindexdb working or find it for 7.2. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread Matt Clark
I'm running a load of stress scripts against my staging environment to simulate user interactions, and watching the various boxen as time goes by. I noticed that the CPU utilisation on the DB server (PG 7.2.3, RH7.3, Dual PII 550MHz, 1GB RAM, 1GB database on disk, Single 10k SCSI drive) was

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread scott.marlowe
On Wed, 17 Sep 2003, Matt Clark wrote: *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated as a good time to vac-an, but does my typical count of 3 indexes per

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread Josh Berkus
Matt, Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? No. You've already proven that the performance gain on queries offsets the loss from the vacuuming. There is no other gotcha. However: 1) You may be

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread Matt Clark
: Re: [PERFORM] Is there a reason _not_ to vacuum continuously? On Wed, 17 Sep 2003, Matt Clark wrote: *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread Matt Clark
2) Are you sure that ANALYZE is needed? Vacuum is required whenever lots of rows are updated, but analyze is needed only when the *distribution* of values changes significantly. You are right. I have a related qn in this thread about random vs. monotonic values in indexed fields. 3) using

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread Christopher Browne
Oops! [EMAIL PROTECTED] (Josh Berkus) was seen spray-painting on a wall: I understand this needs an exclusive lock on the whole table, which is simply not possible more than once a month, if that... Workarounds/hack suggestions are more than welcome :-) Would it be reasonable to use partial