Thread: Query on Postgresql performance

Query on Postgresql performance

From
"Naveen Palavalli"
Date:
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.
       
 

Re: Query on Postgresql performance

From
Neil Conway
Date:
On Wed, 2003-09-03 at 15:32, Naveen Palavalli wrote:
> shared_buffers = 200

If you're using a relatively modern machine, this is probably on the low
side.

> 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 .

Well, there's nothing wrong with vacuuming frequently (since it won't
block concurrent database operations, and the more often you vacuum, the
less time each vacuum takes).

> 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

Can you reproduce the 7.3.3 crash? (BTW, in the future, it would be
appreciated if you could report these kinds of bugs to the dev team).

-Neil



Re: Query on Postgresql performance

From
Andrew Sullivan
Date:
On Wed, Sep 03, 2003 at 12:32:42PM -0700, Naveen Palavalli wrote:
> My server spawns a new connection to Postgresql foreach client. My

I don't think you want to do that.  You should use a pool.  Back end
startup is mighty expensive.

> 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 ,

You'll want to vacuum after every set of deletes, I should think.  If
you're woking in more than one transaction for the deletes, then
fairly frequent vacuums of that table will be effective.

> 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

You don't want to use 7.3.3.  It has a rare but serious bug and was
replaced in something like 24 hours with 7.3.4.  The 7.2 branch is no
longer being maintained, so you really probably should use the 7.3
branch.  I'm unaware of others having stability problems with 7.3.4,
so if you see them, you should find your core dump and talk to the
people on -hackers.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110