pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Naveen Palavalli <palavall@ISI.EDU>
To: pgsql-performance@postgresql.org
Subject: Query on Postgresql performance
Date: Wed, 3 Sep 2003 12:32:42 -0700
Message-ID: <001a01c37252$24f69390$8000000a@usc1mk75s5t1b6> (raw)

Hi ,

I am currently using Postgresql for a Research project . I observed some performance 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 Postgresql database . The server ( a C/C++ program running on Linux ) accesses Postgresql using psqlodbc .
My server spawns a new connection to Postgresql foreach client. My postgresql.conf has the following additional settings . I am also running Postmaster with "-o -F" option .

tcpip_socket = true
max_connections = 100
shared_buffers = 200
vacuum_mem = 16384

My clients are adding strings of length approximately 20 bytes . The database size is 1 Million entries .

I observed the following results :-

1) Effects related to Vaccum :- I performed 10 trials of adding and deleting 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 trial . But when I do a Vaccuum , Immediately I get back the Add Rates to  around 200 Adds/Second . 
    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 number of client increases) . 
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 clients .
    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 decreases the scond time I repeat the same trial with everything being the same . 
       During each trial , each thread of each client  adds 3000 entries and 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 after a Vaccum . So I switched to a older and stabler version  7.2.4

Any comments on these observations will be very welcome . Additional details will be provided if needed .

Thanking you in Advance,
Naveen.
        

view thread (3+ messages)  latest in thread

Message-ID: <001a01c37252$24f69390$8000000a@usc1mk75s5t1b6>
Permalink:  ../001a01c37252$24f69390$8000000a@usc1mk75s5t1b6/
Also on:    postgresql.org/message-id/001a01c37252$24f69390$8000000a@usc1mk75s5t1b6

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: palavall@ISI.EDU
  Subject: Re: Query on Postgresql performance
  In-Reply-To: <001a01c37252$24f69390$8000000a@usc1mk75s5t1b6>
  References: <001a01c37252$24f69390$8000000a@usc1mk75s5t1b6>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox