Re. : Need for speed

Hi,

How much Ram do you have ?
Could you give us your postgresql.conf ? (shared buffer parameter)

If you do lots of deletes/inserts operations you HAVE to vacuum analyze
your table (especially if you have indexes).

I'm not sure if vacuuming locks your table with pg 7.4.2 (it doesn't with
8.0), you might consider upgrading your pg version.
Anyway, your "SELECT" performance while vacuuming is going to be altered.

I don't know your application but I would certainly try to split your
table. it would result in one table for inserts/vaccum and one for
selects. You would have to switch from one to the other every five
minutes.

Benjamin.

Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se>
Envoyé par : pgsql-performance-owner(at)postgresql(dot)org
16/08/2005 17:39

Pour : pgsql-performance(at)postgresql(dot)org
cc :
Objet : [PERFORM] Need for speed

Hello,

one of our services is click counting for on line advertising. We do
this by importing Apache log files every five minutes. This results in a
lot of insert and delete statements. At the same time our customers
shall be able to do on line reporting.

We have a box with
Linux Fedora Core 3, Postgres 7.4.2
Intel(R) Pentium(R) 4 CPU 2.40GHz
2 scsi 76GB disks (15.000RPM, 2ms)

I did put pg_xlog on another file system on other discs.

Still when several users are on line the reporting gets very slow.
Queries can take more then 2 min.

I need some ideas how to improve performance in some orders of
magnitude. I already thought of a box with the whole database on a ram
disc. So really any idea is welcome.

Ulrich

--
Ulrich Wisser / System Developer

RELEVANT TRAFFIC SWEDEN AB, Riddarg 17A, SE-114 57 Sthlm, Sweden
Direct (+46)86789755 || Cell (+46)704467893 || Fax (+46)86789769
________________________________________________________________
http://www.relevanttraffic.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly