Thread: performance question
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 problems when running 2 programs that access the database at once (only doing querys, and not even complex ones). -ernie
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
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.
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: > Tom Lane wrote: > > > > SELECTs certainly don't block each other (except the SELECT FOR UPDATE > > variety). > > > > 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
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: > > 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
> ... 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