agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedSlow Postgres performance
7+ messages / 3 participants
[nested] [flat]
* Slow Postgres performance
@ 1995-08-25 01:01 Casey Claiborne <mskc@io.com>
0 siblings, 0 replies; 7+ messages in thread
From: Casey Claiborne @ 1995-08-25 01:01 UTC (permalink / raw)
To: legacy
Hello -
I had downloaded postgres and am using it on a 486 SX 33Mhz machine
under the Linux 1.2.1 operating system. 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
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 ]
if (*res == 'E')
{
printf("this did not work 1 \n");
PQexec("end");
exit();
}
if (*res == 'I')
{
printf("this did not work 2 \n");
PQexec("end");
exit();
}
PQexec("end");
PQfinish();
[Because the return codes do not work above, I had to add another call to
the server to test if the row actually went in]
}
One must note the importance of setting reasonable and achievable goals for
"If you aim for nothing, you'll hit it every time"
-Theodore Roosevelt
==============================================================================
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* slow postgres performance
@ 1995-08-27 22:35 Casey Claiborne <mskc@io.com>
1995-08-28 01:27 ` Re: slow postgres performance Paul Cole <pcole@ccwf.cc.utexas.edu>
1995-08-28 01:41 ` Re: slow postgres performance Paul Cole <pcole@ccwf.cc.utexas.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Casey Claiborne @ 1995-08-27 22:35 UTC (permalink / raw)
To: legacy
Hello -
I had downloaded postgres and am using it on a 486 SX 33Mhz machine
under the Linux 1.2.1 operating system. 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?
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 ]
if (*res == 'E')
{
printf("this did not work 1 \n");
PQexec("end");
exit();
}
if (*res == 'I')
{
printf("this did not work 2 \n");
PQexec("end");
exit();
}
PQexec("end");
PQfinish();
[Because the return codes do not work above, I had to add another call to
the server to test if the row actually went in]
}
One must note the importance of setting reasonable and achievable goals for
"If you aim for nothing, you'll hit it every time"
-Theodore Roosevelt
==============================================================================
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: slow postgres performance
1995-08-27 22:35 slow postgres performance Casey Claiborne <mskc@io.com>
@ 1995-08-28 01:27 ` Paul Cole <pcole@ccwf.cc.utexas.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Paul Cole @ 1995-08-28 01:27 UTC (permalink / raw)
To: ; +Cc: legacy
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 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?
>
>
> 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 ]
>
>
> if (*res == 'E')
> {
> printf("this did not work 1 \n");
> PQexec("end");
> exit();
> }
>
> if (*res == 'I')
> {
> printf("this did not work 2 \n");
> PQexec("end");
> exit();
> }
> PQexec("end");
> PQfinish();
>
> [Because the return codes do not work above, I had to add another call to
> the server to test if the row actually went in]
>
> }
>
>
> One must note the importance of setting reasonable and achievable goals for
> "If you aim for nothing, you'll hit it every time"
> -Theodore Roosevelt
>
>
> ==============================================================================
> 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/
>
==============================================================================
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: slow postgres performance
1995-08-27 22:35 slow postgres performance Casey Claiborne <mskc@io.com>
@ 1995-08-28 01:41 ` Paul Cole <pcole@ccwf.cc.utexas.edu>
1995-08-28 02:25 ` Re: res results from PQexec() Paul Cole <pcole@ccwf.cc.utexas.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Paul Cole @ 1995-08-28 01:41 UTC (permalink / raw)
To: Casey Claiborne <mskc@io.com>; +Cc: legacy
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: res results from PQexec()
1995-08-27 22:35 slow postgres performance Casey Claiborne <mskc@io.com>
1995-08-28 01:41 ` Re: slow postgres performance Paul Cole <pcole@ccwf.cc.utexas.edu>
@ 1995-08-28 02:25 ` Paul Cole <pcole@ccwf.cc.utexas.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Paul Cole @ 1995-08-28 02:25 UTC (permalink / raw)
To: Casey Claiborne <mskc@io.com>; legacy
I played around with your code snippet. I found a couple things that my
version of postgres doesn't like right off the bat.
Your class was called "user" and you had "user" as a data member as well.
My postgres <4.2a> doesn't like "user" at all in any queries whatsoever.
Must be a reserved word I guess?
Secondly you specified your query as <user remplaced with template and name>
append template (template.name = blah, template.address = blah,
template.city = blah, template.state = blah)
My postgres doesn't like that either. It dislikes your qualification of
the data members with the class. You've already qualified it by saying
"append class_name" so..
append template (name = blah, address = blah, city = blah, state = blah)
is quite sufficient.
other than that I found no problems.
I ran the test with:
--------------------
>destroydb testdb
>createdb testdb
>
> monitor testdb
Welcome to the POSTGRES terminal monitor
Go
* create TEMPLATE (name = text,
address = text, city = text,
state = text ) \g
* \q
>
> ./test_libpq
>
> monitor testdb
Welcome to the POSTGRES terminal monitor
Go
* retrieve (TEMPLATE.all) \g
Query sent to backend is "retrieve (TEMPLATE.all) "
---------------------------------------------------------
| name | address | city | state |
---------------------------------------------------------
| Joe Schmoe | 1234 NoWhere Drive| BFE City | Junk |
---------------------------------------------------------
Enjoy!
--Paul Cole
--pcole@ccwf.cc.utexas.edu
=======================================
=======================================
the source for test_libpq follows:
----------------------------------
#include <stdio.h>
#include "libpq.h"
struct struct_user {
char name[20];
char address[50];
char city[20];
char state[10];
};
typedef struct struct_user user;
user TEST_USER = { "Joe Schmoe", "1234 NoWhere Drive", "BFE City", "Junk" };
int main(void)
{
char* res;
char tempdata[600];
sprintf(tempdata, "append TEMPLATE (name = \"%s\", address=\"%s\",
city=\"%s\", state=\"%s\") ",
TEST_USER.name, TEST_USER.address, TEST_USER.city, TEST_USER.state);
PQsetdb("testdb");
PQexec("begin");
res = PQexec(tempdata);
if (*res == 'E')
{
printf("this did not work 1 \n");
PQexec("end");
exit(0);
}
if (*res == 'I')
{
printf("this did not work 2 \n");
PQexec("end");
exit(0);
}
PQexec("end");
PQfinish(); /*
* even though I still don't know what this REALLY does
* PQfinish() isn't even defined in my libpq.h. *shrug*
*/
return (0);
}
==============================================================================
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Slow Postgres performance
@ 1995-08-28 08:56 Rosemarie Power <rosemarie.power@ucd.ie>
0 siblings, 0 replies; 7+ messages in thread
From: Rosemarie Power @ 1995-08-28 08:56 UTC (permalink / raw)
To: legacy; +Cc: arthur@midir.ucd.ie
Greetings -
We have Postgres (database) installed on two OSF/1 systems here, one
being a 3000/300 LX with 32 MB memory running OSF v1.3 (which I think has a
100 MHz CPU), the other being a 2000 4/233 with 128 MB memory running OSF
v3.2. We've just installed Postgres on the 2000.
We've experienced severe performance problems with Postgres on the
2000 which occur when doing searches (and may occur on other actions as well).
The system is running at least 5 times slower than the equivalent search on the
3000. The search uses the regexp library. The problem doesn't appear if we copy
the binaries from the 3000, rather than those built on the 2000.
Note that these problems are with Postgres -- the 2000 is humming
along nicely while the search goes on.
Build details are:
2000 : Postgres v4.2 built using OSF v3.2 cc
3000 : Postgres v4.2 built using either OSF v1.3 cc or GCC v2.5.8
My question is: is there a known problem with the regexp library
supplied with OSF v3.2?
Thanks for any and all assistance ...
Regards,
Rosemarie Power,
Systems Management Group,
U.C.D. Computing Services.
Tel.: +353 1 7062429.
==============================================================================
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Postgres performance
@ 1995-08-30 13:24 Rosemarie Power <rosemarie.power@ucd.ie>
0 siblings, 0 replies; 7+ messages in thread
From: Rosemarie Power @ 1995-08-30 13:24 UTC (permalink / raw)
To: legacy; +Cc: arthur@midir.ucd.ie
>From: Rosemarie Power <rosemarie.power@ucd.ie>
>Organization: University College Dublin
>To: postgres@nobozo.CS.Berkeley.EDU
>Date: Mon, 28 Aug 1995 09:56:15 +0100 (BST)
>Subject: Slow Postgres performance
>Cc: arthur@midir.ucd.ie
>Priority: normal
>Greetings -
>
>We have Postgres (database) installed on two OSF/1 systems here, one
>being a 3000/300 LX with 32 MB memory running OSF v1.3 (which I think has a
>100 MHz CPU), the other being a 2000 4/233 with 128 MB memory running OSF
>v3.2. We've just installed Postgres on the 2000.
>
>We've experienced severe performance problems with Postgres on the
>2000 which occur when doing searches (and may occur on other actions as well).
>The system is running at least 5 times slower than the equivalent search on the
>3000. The search uses the regexp library. The problem doesn't appear if we copy
>the binaries from the 3000, rather than those built on the 2000.
>Note that these problems are with Postgres -- the 2000 is humming
>along nicely while the search goes on.
>
>Build details are:
> 2000 : Postgres v4.2 built using OSF v3.2 cc
> 3000 : Postgres v4.2 built using either OSF v1.3 cc or GCC v2.5.8
>
>My question is: is there a known problem with the regexp library
>supplied with OSF v3.2?
>
We are going to try rebuilding the OSF v3.2 version with gcc instead
of cc and see what happens.
I wonder if installing postgres95 is worth trying, does anyone know
if it has the same problems?
>Thanks for any and all assistance ...
>
>Regards,
>
>Rosemarie Power,
>Systems Management Group,
>U.C.D. Computing Services.
>Tel.: +353 1 7062429.
>
>==============================================================================
> 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/
Regards,
Rosemarie Power,
Systems Management Group,
U.C.D. Computing Services.
Tel.: +353 1 7062429.
==============================================================================
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1995-08-30 13:24 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1995-08-25 01:01 Slow Postgres performance Casey Claiborne <mskc@io.com>
1995-08-27 22:35 slow postgres performance Casey Claiborne <mskc@io.com>
1995-08-28 01:27 ` Re: slow postgres performance Paul Cole <pcole@ccwf.cc.utexas.edu>
1995-08-28 01:41 ` Re: slow postgres performance Paul Cole <pcole@ccwf.cc.utexas.edu>
1995-08-28 02:25 ` Re: res results from PQexec() Paul Cole <pcole@ccwf.cc.utexas.edu>
1995-08-28 08:56 Slow Postgres performance Rosemarie Power <rosemarie.power@ucd.ie>
1995-08-30 13:24 Re: Slow Postgres performance Rosemarie Power <rosemarie.power@ucd.ie>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox