X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 39170D1B4E2 for ; Wed, 3 Sep 2003 19:33:15 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 35578-07 for ; Wed, 3 Sep 2003 16:32:44 -0300 (ADT) Received: from tnt.isi.edu (tnt.isi.edu [128.9.128.128]) by svr1.postgresql.org (Postfix) with ESMTP id A0061D1B8C0 for ; Wed, 3 Sep 2003 16:32:42 -0300 (ADT) Received: from usc1mk75s5t1b6 (calypso.isi.edu [128.9.72.28]) by tnt.isi.edu (8.11.6p2/8.11.2) with SMTP id h83JWhp28829 for ; Wed, 3 Sep 2003 12:32:43 -0700 (PDT) Message-ID: <001a01c37252$24f69390$8000000a@usc1mk75s5t1b6> From: "Naveen Palavalli" To: Subject: Query on Postgresql performance Date: Wed, 3 Sep 2003 12:32:42 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0017_01C37217.7878C1E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_20_30 X-Spam-Level: X-Archive-Number: 200309/25 X-Sequence-Number: 3391 This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C37217.7878C1E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi , I am currently using Postgresql for a Research project . I observed some pe= rformance results of Postgresql which I would like to discuss . I have a server which accepts requests from clients. It spawns a new thread= for each client. The clients are trying to add entries to a relation in P= ostgresql database . The server ( a C/C++ program running on Linux ) access= es Postgresql using psqlodbc . My server spawns a new connection to Postgresql foreach client. My postgres= ql.conf has the following additional settings . I am also running Postmaste= r with "-o -F" option . tcpip_socket =3D true max_connections =3D 100 shared_buffers =3D 200 vacuum_mem =3D 16384 My clients are adding strings of length approximately 20 bytes . The databa= se size is 1 Million entries . I observed the following results :- 1) Effects related to Vaccum :- I performed 10 trials of adding and deletin= g entries . In each trial , 1 client adds 10,000 entries and then deletes = them . During the course of these 10 trials , the Add Rates (rate at which= my server can add entries to the Postgresql database ) drops from around = 200 Adds/second in the 1st trial to around 100 Adds/second in the 10th tri= al . But when I do a Vaccuum , Immediately I get back the Add Rates to aro= und 200 Adds/Second .=20 This effect is more pronounced if there are more than 1 client. As the = number of clients increases , the Add Rate drops more steeply requiring me = to perform Vaccum more frequently between the trials . So if I draw a graph= of the Add Rates in the Y- Axis and the number of Add Operations in the X-= axis , I get a saw toothed graph . 2) In the second Experiment , I had a multi threaded client . In the sense = , it spawns threads as specified by a command line argument . The server in= turn spawns new connections for each Thread of each client ( even the numb= er of client increases) .=20 I fixed the number of threads per client at 4 . and I increased the number = of clients from 1 to 10 . I observed :- a) As the number of clients are increased , the Add Rate decreases from= around 200 Adds/ Second for 1 client to around 130 Adds/Second for 10 clie= nts . b) suppose I run a trial with 3 clients and 4 threads per client . and = I get a Add Rate of 180 Adds/Second the first time .This Add Rate decrease= s the scond time I repeat the same trial with everything being the same .= =20 During each trial , each thread of each client adds 3000 entries an= d deletes them and I perform vaccuum after each trial . Postgresql version :- 7.2.4 Psqlodbc version :- 7.03.0100 I was using Postgresql 7.3.3 earlier but it kept crashing the database afte= r a Vaccum . So I switched to a older and stabler version 7.2.4 Any comments on these observations will be very welcome . Additional detail= s will be provided if needed . Thanking you in Advance, Naveen. =20=20=20=20=20=20=20=20 ------=_NextPart_000_0017_01C37217.7878C1E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi ,
 
I am currently using Postgresql for a Rese= arch=20 project . I observed some performance results of Postgresql which I would l= ike=20 to discuss .
 
I have a server which accepts requests fro= m=20 clients. It spawns a new thread for each client. The clients are trying to = add=20 entries  to a relation in Postgresql database . The server ( a C/C++= =20 program running on Linux ) accesses Postgresql using psqlodbc=20 .
My server spawns a new connection to Postg= resql=20 foreach client. My postgresql.conf has the following additional settings . = I am=20 also running Postmaster with "-o -F" option .
 
tcpip_socket =3D true
max_connections = =3D=20 100
shared_buffers =3D 200
vacuum_mem =3D 16384
My clients are adding strings of length=20 approximately 20 bytes . The database size is 1 Million entries .
=  
I observed the following results :-=
 
1) Effects related to Vaccum :- I performe= d 10=20 trials of adding and deleting entries . In each trial , 1 client  = ;adds=20 10,000 entries and then deletes them . During the course of these 10 t= rials=20 ,  the Add Rates (rate at which my server can add entries to the Postg= resql=20 database ) drops from  around 200 Adds/second in the 1st trial  t= o=20 around 100 Adds/second in the 10th trial . But when I do a Vaccuum , Immedi= ately=20 I get back the Add Rates to  around 200 Adds/Second .
    This effect is more pro= nounced=20 if there are more than 1 client. As the number of clients increases , the A= dd=20 Rate drops more steeply requiring me to perform Vaccum more frequently betw= een=20 the trials . So if I draw a graph of the Add Rates in the Y- Axis and the n= umber=20 of Add Operations in the X-axis , I get a saw toothed graph .
 
2) In the second Experiment , I had a mult= i=20 threaded client . In the sense , it spawns threads as specified by a comman= d=20 line argument . The server in turn spawns new connections for each Thread o= f=20 each client ( even the number of client increases) .
I fixed the number of threads per client a= t 4 . and=20 I increased the number of clients from 1 to 10 .  I observed=20 :-
 
    a) As the number of cli= ents are=20 increased , the Add Rate decreases from around 200 Adds/ Second for 1 clien= t to=20 around 130 Adds/Second for 10 clients .
    b) suppose I run a tria= l with 3=20 clients and 4 threads per client . and I get a Add Rate of 180 Adds/Second = the=20 first  time .This Add Rate decreases the scond time I repeat the same = trial=20 with everything being the same .
       = During=20 each trial , each thread of each client  adds 3000 entries and de= letes=20  them and I perform vaccuum after each trial .
 
 
Postgresql version    =20 :-        7.2.4
Psqlodbc=20 version       :-    =    =20 7.03.0100
 
I was using Postgresql 7.3.3 earlier but i= t kept=20 crashing the database after a Vaccum . So I switched to a older and stabler= =20 version  7.2.4
 
Any comments on these observations will be= very=20 welcome . Additional details will be provided if needed .
 
Thanking you in Advance,
Naveen.
       
 
------=_NextPart_000_0017_01C37217.7878C1E0--