performance question

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).

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 ...

regards, tom lane

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

I had (or have) the same concerns. An O'Reilly book i was looking at says
that the Perl DBI for postgres allows only table level locking.
And doesn't go into, at all, the concept of snapshots for record
stability.

So does anyone know anything about this? Are record / row locks comitted
or table locks? And what about the perl interface that postgres comes
with?

thanks for any info
.jtp

On Thu, 13 Jul 2000, ernie cline wrote:

ernie cline <ernie.cline@ipgdirect.com> writes:

Hello all,
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? We are experiencing some performance

Each SELECT is done inside a transaction. Otherwise you cannot ensure
consistent snapshots.

Manuel.

ernie cline <ernie.cline@ipgdirect.com> writes:

Need more detail ...

What else do you need to know?

What queries are you issuing, *exactly*? You might find it easiest
to restart the postmaster with -d2 and collect the queries in the
postmaster's log...

regards, tom lane

* ernie cline <ernie.cline@ipgdirect.com> [000713 11:57] wrote:

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 ...

Before going to the extent of re-implementing your code I would just
print out the "time taken" for various things in your cgi like so:

print "\n<!--\nstarted $time\n-->\n";
# do query
print "\n<!--\nquery done $time\n-->\n";
# do something
print "\n<!--\nsomething done $time\n-->\n";
# do output
print "\n<!--\noutput done $time\n-->\n";

Then you can figure out where the problem is.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Arg! Thanks to everyone for their help. I followed a few suggestions I
got from here, and installed Time::HiRes on my box (that part was my
idea actually ;)), to monitor how long the query's take. I know all the
stats show the MySQL is faster, but in my situation, postgres is really
kicking ass. None of the query's took longer than .03 seconds ;). Then
again, the database is small ... but anyway, in my script, we also call
to an oracle Database. THAT is slow. Of the 2.6 seconds it takes to
execute the script, 2.3 is that oracle call. Granted its a huge ~8 line
query, but its still oracle's "fault". Thanks for all your help guys
and gals!

-ernie
P.S. Anyone know of a script to convert oracle db to postgres? :) Or
if I dump the oracle tables, will postgres be able to read them?

Tom Lane wrote:

... I know all the stats show the MySQL is faster...

A story which, if and when true, stays true only for cases with one or a
very few users. We used to just accept MySQL's claims in this regard
without question, but it is finally dawning on us that they are not
doing representative tests for a multi-client environment :)

- Thomas

Hi,

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

C is certainly quicker, but if you still want to use Perl you could
improve Performance by:

- Using mod_perl in Apache http://perl.apache.org/ (Perl Integration in
Apache)
- Using http://www.fastcgi.com/ (basically the Perl Interpreter isn't
restarted every time)

Bye

Thomas