Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199508280134.SAA03683@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Sun, 27 Aug 1995 20:41:30 -0500 (CDT)
From: Paul Cole <pcole@ccwf.cc.utexas.edu>
To: Casey Claiborne <mskc@io.com>
cc: postgres@postgres.Berkeley.EDU
Subject: Re: slow postgres performance
In-Reply-To: <199508272235.RAA23176@tristero.io.com>
Message-ID: <Pine.LNX.3.91.950827202744.3813D-100000@localhost>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Sun, 27 Aug 95 18:34:58 -0700
Resent-XMts: smtp


First off, let me apologize for quoting the whole article with nothing added.
I hit ctrl-x instead of ctrl-c *blush*.  My apologies.

Continuing on...

I haven't the feintest idea about your performance issue but..
You left some comments in the code that I would like to remark on.
And also I'd like to pose a question or two myself.

On Sun, 27 Aug 1995, Casey Claiborne wrote:

> Hello -
>         I had downloaded postgres and am using it on a 486 SX 33Mhz machine
> under the Linux 1.2.1 operating system. 

I'm using it on a 486dx2 80Mhz linux box.  I assume you have a 
sufficiently large amount of physical memory to handle the postgres 
application.  In case your new to linux, "free" will show you how much 
mem you are using, how much is free, and most importantly, how much you 
are stuffing into the swap space.  If your swap soace is being used a 
lot, there's a potential problem right there.

> I am making calls to the postgres
> server from "C" code (I am using embedded sql). I have routines such as
> db_add_user_info, db_get_user_info, db_delete_user info which are written
> very much similar to the one below with the exception that some of the code
> and the queries themselves will change. Whenever I run my application using the 
> routines the performance is VERY slow. I have not yet added indexes - I do 
> not know
> if that alone will solve the problem. Is there a *better approach* I can use?
> 
> *Any* help is welcomed and appreciated.
> 
> TIA
> 
> P.S. I had heard of a company that sold postgres and thus would also provide
> some support for the product. Does anyone know the name of the company
> and how they can be contacted?

Illustra has taken over the Postgres project.  Its a spinoff corporation 
from the Berkeley Database Research Group.  First thing I'd use to find 
them is use netsearch from netscape on Illustra.  They have a decent web 
page demonstrating their product and from personal experience are MORE 
than happy to send you some documents comparing the Illustra  server to 
traditional <or non-traditional> postgres.  I haven't checked into 
pricing, if you do please tell me how much it runs.  If you can't find 
them, ask me again and I'll look it up for ya.

> > > Casey > > 
> ========================= code starts below ================================
> 
> My routines look something like the following:
> 
> [other code]
> 
> typedef user struct_user {
> char name[20];
> char address[50];
> char city[20];
> char state[10];
> } USER;
> 
> 
> [other code]
> 
> db_add_user_info(USER user_info)
> {
> char* res;
> char tempdata[600];
> 
> sprintf(tempdata, "append user (user.name = \"%s\", user.address=\"%s\", 
> user.city=\"%s\", user.state=\"%s\") ",
>         user_info.name, user_info.address, user_info.city, user_info.state);
> 
> PQsetdb("testdb");
> PQexec("begin");
> 
> res = PQexec(tempdata);
> ^^^
> [ the return values for PQ exec *do not* work! Whenever I made tests using 
> the return code, it was *never* correct ]

This is the part I wanted to comment on.  Mine works fine?!  I get back 
the expected values of E<erorr message> when it screws up and C<command> 
when it works.  I'm using the binary portal method for retrievals so I 
guess I can't help you much with the rest of your code here.

I can however, test this out on my own system here in a jiffy. 

I assume you have your database setup as -- 
create template ( name = char[20], address = char[50], city = char[20], 
                  state = char[10] ) \g 

Please send me some mail and tell me how your database is defined and 
We'll try to fix your problem together, if your willing.

-=-=-=-=-=-=-=-=

[snip]

> PQfinish();
> 
[snip]

Now a question of my own.  I see that you are using PQFinish().  I'm a 
little confused on where and why I should use that function.  I wrote a 
little test_libpq.c application that was basically

int main(void)
{
  while (1) 
  {
    PQexec("begin")
    PQexec("end")
    PQfinish();
    sleep(2);
  }
}

and then did some ps -ux's on it while it was running to see how it was 
performing.. Turns out that my RSS goes up by 4 every time through the loop!
So I looked into it some more and turned out that if I got rid of 
PQfinish() the memory leak <if thats what it is> went away with no harm 
to my program.  Since them I now have an application that 
appends/replaces to two difference classes and no where in teh code at 
all is PQfinish() used ever.  It runs just fine. ??

  --Paul

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU".
   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================
              URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/