Thread

PostgreSQL performance problems: heavy load

dLux <dlux@dlux.hu> — 2000-01-12T13:19:21Z

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 my cpu
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."