RE: [HACKERS] vacuum of permanently updating database

Hiroshi,

thank you for the message. I'll try current tree but if
it's a bug (probable ?) why don't try to fix it for 6.5.3 ?

Regards,

Oleg

On Sat, 16 Oct 1999, Hiroshi Inoue wrote:

> Date: Sat, 16 Oct 1999 10:06:36 +0900
> From: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: hackers(at)postgreSQL(dot)org
> Subject: RE: [HACKERS] vacuum of permanently updating database
>
> Hi
>
> Could you try the current tree ?
>
> As far as I see,there are 2 possibilities.
>
> 1. Relation cache invalidation mechanism is much improved
> by Tom in the current tree.
> In your case,index tuples may be inserted into invalid index
> relation and vanish.
>
> 2. If vacuum aborts after the internal commit,the transaction
> status is changed to be ABORT. This causes inconsistency.
> I have changed not to do so in the current tree.
>
>
> In CURRENT tree,you may have to change vacuum_hits.sql
> as follows.
>
> drop index hits_pkey;
> vacuum analyze hits(msg_id);
> create unique index hits_pkey on hits(msg_id);
>
> Probably DROP INDEX couldn't be executed inside transactions.
>
> Regards.
>
> Hiroshi Inoue
> Inoue(at)tpf(dot)co(dot)jp
>
> >
> > I've already posted my question about NOTICE message I'm getting
> > from vacuum but didn't get any response :-(
> >
> > Today I decided to do some experiments to reproduce my problem.
> >
> > I run two independent processes:
> >
> > 1. send parallel requests to apache server in loop. On this request server
> > does following:
> >
> > LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE
> > UPDATE hits SET count=count+1,last_access=now() WHERE msg_id=1468
> >
> > 2. vacuum table hits in shell scripts
> >
> > #!/bin/sh
> > while true ;do
> > /usr/local/pgsql/bin/psql -tq discovery <vacuum_hits.sql
> > rc=$?
> > i=$((i+1))
> > echo Vaccuming: $i, RC=$rc
> > sleep 10;
> > done
> >
> > where vacuum_hits.sql:
> >
> > begin work;
> > drop index hits_pkey;
> > create unique index hits_pkey on hits(msg_id);
> > end work;
> > vacuum analyze hits(msg_id);
> >
> >
> > Sometimes I get the message:
> >
> > NOTICE: Index hits_pkey: NUMBER OF INDEX' TUPLES (173) IS NOT
> > THE SAME AS HEAP' (174)
> >
> > also several times I get:
> > ERROR: Can't create lock file. Is another vacuum cleaner running?
> > If not, you may remove the pg_vlock file in the
> > /usr/local/pgsql/data//base/discovery
> > directory
> >
> > I had to remove this file by hand.
> >
> >
> > I understand that experiment is a little bit artificial but I'd like
> > to know what I'm doing wrong and what's the safest way to vacuum
> > table which is permanently updating. Actually, I got about
> > 12 requests/sec on my home P166, 64Mb, Linux 2.2.12 - each request is a
> > plenty of database work. I have to vacuum table because at this rate
> > I got very quick performance degradation.
> >
> > This is 6.5.2, Linux
> >
> > Regards,
> >
> > Oleg
> >
> > _____________________________________________________________
> > Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> > Sternberg Astronomical Institute, Moscow University (Russia)
> > Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> > phone: +007(095)939-16-83, +007(095)939-23-83
> >
> >
> > ************
> >
>
>
> ************
>

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83