VS: performance question - Mailing list pgsql-general

From Janne Blomqvist
Subject VS: performance question
Date
Msg-id 2A80FDF6CF54D211A97D00A0C992F5906CE813@hanselx.hansel.fi
Whole thread Raw
List pgsql-general

>Tom Lane wrote:
>>
>> ernie cline <ernie.cline@ipgdirect.com> writes:
>> > Quick question.  I am using postgres 7.0.  When running a select query,
>> > does postgres lock the row (or table) while doing a select?  Only on an
>> > insert/update/delete, right?
>>
>> SELECTs certainly don't block each other (except the SELECT FOR UPDATE
>> variety).

>I kind of didn't think so, but I just wanted to make sure I wasn't
>insane. ;)

>> > We are experiencing some performance
>> > problems when running 2 programs that access the database at once (only
>> > doing querys, and not even complex ones).
>>
>> Need more detail ...

>What else do you need to know?  Postgres 7.0 compiled with default
>options, running on a VA Linux Fullon box, kernel 2.2.13, running VA
>Linux/Redhat 6.1.  Box is a single processor pentium 3 500mhz, 128megs
>of ram, apache 1.3.9.  Using Perl DBI interface to access postgres.  A
>very small html page is built (about 4.5k), and it takes about 2.6
>seconds to execute and build the html.  Running 2 of the pages at once,
>it takes about 5 seconds for them to complete.  Not total, but 5 for
>each one.  This is using just unix 'time' command, and moving my mouse
>to another rxvt window and hitting enter after first hitting it in
>another window ;).

>Is using Perl DBI slow?  Would writing the cgi in C speed things up?
>(Please no holy wars for perl vs. C performance! <G>)
>Any help would be appreciated ...
>
>-ernie

You could take your query and run it in psql to see if the performance problem is with postgres or with your perl stuff. If it's postgres, redesign your query. Of course, this can be quite difficult... BTW, does a reasonably complicated SELECT run faster if you implement it using a view or a stored procedure? If I'm not entirely uncorrect high end commercial db:s like oracle can perform some kind of statistical optimization on views and stored procs. Is this also true for postgres?

If your perl stuff is slow then rewriting it in c won't help that much. Unless you have made some gross programming error which gets magically fixed in the c version, that is. Usually the culprit is the braindead way in which CGI:s work. Ie.:

1. CGI request to apache
2. Apache fires up the perl interpreter which byte-code compiles your script and runs it
3. Script initiates connection to postgres, which forks off a new backend to handle the request.
4. After the script has completed the request, it closes the db connection and gets unloaded. The forked postgres backend also dies, of course.

5. Phew! What a lot of work!
Rewriting in C fixes only point 2 to a certain extent. The buzzwords to fix the CGI problem are 1) in-process script interpreter and 2) persistent database connections. 1) can be solved by using for example mod_perl and 2) hmm... should be doable. I don't know if you have to do this for yourself or if some friendly guy has made some library to implement this. Or you can use AOLServer which does all this for you (my personal choice). But then you have to rewrite your perl stuff in tcl or some other language implemented by aolserver modules (python, java and php at least, I think). But considering that your box seems quite fast I suspect the problem is with your SQL anyway... You could of course also try the postgres tuning tips at

http://www.openacs.org/doc/openacs/html/openacs-install-5.html#ss5.4
But this applies mostly to writes, I think..
If you are planning on running a production server, I also recommend you to upgrade your kernel to 2.2.16 which fixes a few serious bugs:

1) The disk trashing bug (Could be a serious problem! Ok this was fixed in 2.2.15.. or was it 2.2.14 already.. I don't remember.)

2) The much halooed root exploit bug (Applies only if you have untrusted accounts, if I remember correctly..)

Janne Blomqvist
janne.blomqvist@hansel.fi

pgsql-general by date:

Previous
From: Brandon Ibach
Date:
Subject: Re: Load fails
Next
From: "K Parker"
Date:
Subject: Re: php connection