vacuum of permanently updating database
От | Oleg Bartunov |
---|---|
Тема | vacuum of permanently updating database |
Дата | |
Msg-id | Pine.GSO.3.96.SK.991015233006.11898p-100000@ra обсуждение исходный текст |
Ответы |
RE: [HACKERS] vacuum of permanently updating database
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: