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: [PERFORM] Query on Postgresql performance Naveen Palavalli
- Re: [PERFORM] Query on Postgresql performance Neil Conway
- Re: [PERFORM] Query on Postgresql performance Andrew Sullivan