Hello!
We have been starting a project 3 months ago with postgresql. It
is a portal site. Now we have 12000 users, and every page hit generates an update to
this table. This makes it _very_ fragmented after one day (60000
update/day), so we run vacuum hourly (only for this table) and daily
(for the whole database), and once a week a full backup-restore
session in made. The server has 256M memory, and a raid5 disk in it. I thought the once-an-hour vacuum would be
good, but it is not.
Normally the "vaccumdb --table users dbname" is finished in 10
seconds, but in heavy load this can took 5-6 minutes, and the web is
unusable in this period! This is a very big problem. We have the
usual performance inprovements added (-o -F, more buffers, etc), but
it seems that sometimes the VACUUM doesn't work properly. When vaccum is active, the other postgres processes eats up
mycpu
time, and _this_ makes the situation wronger! We are using postgersql 6.5.2 on a Pentium II 450 machine (RedHat
Linux 6.0 with security patches). The database contains a word-index table which have about 2
million entries. It is not so often updated, so this is not required
to be vacuumed more frequent than one days.
If we fail to make vacuum in the scheduled period (e.g pg_vlock
stucked in a crash), the postgres processes usually takes more up
CPU time than usual.
Sometimes I see this in my top:
PID USER PRI NI SIZE SWAP RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
31944 postgres 7 0 3916 0 3916 3232 R 0 6.0 1.5 0:00
/opt/postgres/bin/postgres localhost www kapu idle
31600 postgres 0 0 3928 0 3928 3232 S 0 5.2 1.5 0:46
/opt/postgres/bin/postgres localhost www kapu idle
31982 postgres 0 0 3752 0 3752 3176 S 0 3.4 1.4 0:01
/opt/postgres/bin/postgres localhost www kapu idle
Why idle processes eats 6% CPU time? Is it normal?
Do you have any performance-improvement-ideas? We don't want to
spend lotsa money for a commercial dbms (e.g.Adabas D) only because
of the vacuum problem. Postgresql has many users, testers, and
that's why the support is cannot be compared to any commercial
product (maybe for Oracle, but it is too expensive). I am not in the list, so please reply to my personal address
also.
Thanks for the help in advance.
dLux
--
"There are two kinds of people, those who do the work and those who
take the credit. Try to be in the first group; there is less
competiton there."