VS: performance question

>Tom Lane wrote:
>>
>> ernie cline <ernie(dot)cline(at)ipgdirect(dot)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(dot)blomqvist(at)hansel(dot)fi