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.sqlrc=$?i=$((i+1))echo Vaccuming: $i, RC=$rcsleep 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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83