Обсуждение: Slow connection
Hi everybody, I'm developing some projects in C++ (2.96) on linux (2.4.18-3) and I connect to a postgres (7.2.1) database using libpq++. My connection is fine and my queries execute normally (and quite fast). The problem that all the other users/applications on our LAN experience difficulties with their connections while my program is running - ie very slow or timeouts. We are all accessing a central postgres server. My queries have no problems and as soon as I close my connection all the other connection on the LAN are normal, once more. Any advise or suggestions? thanks Arthur
On Fri, Jun 07, 2002 at 04:39:53PM +0200, Arthur wrote:
> I can outline the basic logic of the program, maybe you can pick
> something up. I query a table and process the rows. For each row an
> update statement is run that updates data in the processed row. I
> use a persistant connection for the updates, but I'm not keen to do
> transaction batches wtih commit/rollback, etc.
Are you quite sure you're not in a transaction? Because given that
you're doing updates, and everyone else is apparently blocked, I'd
expect you're holding a lock on data they're trying to read. Update
causes a very aggressive lock, for obvious reasons.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
My inclination as a sysadmin type is to first isolate the problem to either your program, or the queries that your program is performing. To do that, I would try running your program from some other machine and see if it has the same effect. If it doesn't, I'd suspect something wrong with the way your machine is communicating with the machine the server is running on. If your program still causes all of the other connections to have problems, I'd suggest that you perform all of the queries that your program is doing through the psql interface. (I'd perform this step only because I'm not familiar enough with the guts of the libpq and psql interfaces to know if they interact with the server in the exact same way, or if they do things differently. Because the guts are unknown to me, I would test them. YMMV.) If other users still see the poor performance while you're running the queries (in a script, to more closely simulate the c++ program) via the psql interface, then you'll need to figure out which query is causing the problems. If the other users only see the problem with the c++ program, I'd think that you'd need to start there. -ron > -----Original Message----- > From: Arthur [mailto:arthur@radioretail.co.za] > Sent: Thursday, June 06, 2002 11:53 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Slow connection > > > Hi everybody, > I'm developing some projects in C++ (2.96) on linux > (2.4.18-3) and I connect > to a postgres (7.2.1) database using libpq++. My connection > is fine and my > queries execute normally (and quite fast). > > The problem that all the other users/applications on our LAN > experience > difficulties with their connections while my program is > running - ie very slow > or timeouts. > > We are all accessing a central postgres server. > My queries have no problems and as soon as I close my > connection all the > other connection on the LAN are normal, once more. > > Any advise or suggestions? > > > > thanks > > > > Arthur > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > http://archives.postgresql.org
On Fri, 7 Jun 2002 08:52:42 +0200
"Arthur" <arthur@radioretail.co.za> wrote:
> Hi everybody,
> I'm developing some projects in C++ (2.96) on linux (2.4.18-3) and I connect
> to a postgres (7.2.1) database using libpq++. My connection is fine and my
> queries execute normally (and quite fast).
<plug type="gratuitous">
Have you taken a look at libpqxx?
http://members.ams.chello.nl/j.vermeulen31/proj-libpqxx.html
</plug>
> The problem that all the other users/applications on our LAN experience
> difficulties with their connections while my program is running - ie very slow
> or timeouts.
>
> We are all accessing a central postgres server.
> My queries have no problems and as soon as I close my connection all the
> other connection on the LAN are normal, once more.
>
> Any advise or suggestions?
Sounds like a LAN problem -- AFAICT the connection to PostgreSQL
should be just like any other TCP connection. Are you transferring
a lot of data? Try simulating LAN traffic using another network
application that transfers a lot of data, and see if the problem
occurs then.
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Andrew Sullivan wrote:
> On Fri, Jun 07, 2002 at 04:39:53PM +0200, Arthur wrote:
>
> > I can outline the basic logic of the program, maybe you can pick
> > something up. I query a table and process the rows. For each row an
> > update statement is run that updates data in the processed row. I
> > use a persistant connection for the updates, but I'm not keen to do
> > transaction batches wtih commit/rollback, etc.
>
> Are you quite sure you're not in a transaction? Because given that
> you're doing updates, and everyone else is apparently blocked, I'd
> expect you're holding a lock on data they're trying to read. Update
> causes a very aggressive lock, for obvious reasons.
In PostgreSQL that lock is not as aggressive as you might
think.
PostgreSQL uses MVCC to avoid readers beeing blocked by
writers. Try it. Start a transaction, update a row and in
another session SELECT that row. You'll not get blocked.
So obviously "everyone else" tries to get a lock for update
too. The question is, does everyone else need that lock?
Read only transaction (reporting) do not need to lock
anything. A transaction has a snapshot view of the entire
database. That's the way pg_dump is creating a consistent
snapshot of the entire database without locking up anything.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Fri, 7 Jun 2002, Ron Snyder wrote: > My inclination as a sysadmin type is to first isolate the problem to either > your program, or the queries that your program is performing. To do that, I > would try running your program from some other machine and see if it has the > same effect. If it doesn't, I'd suspect something wrong with the way your > machine is communicating with the machine the server is running on. > > [edited to keep short] > > > -----Original Message----- > > From: Arthur [mailto:arthur@radioretail.co.za] > > Sent: Thursday, June 06, 2002 11:53 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] Slow connection > > > > > > Hi everybody, > > I'm developing some projects in C++ (2.96) on linux > > (2.4.18-3) and I connect > > to a postgres (7.2.1) database using libpq++. My connection > > is fine and my > > queries execute normally (and quite fast). > > > > The problem that all the other users/applications on our LAN > > experience > > difficulties with their connections while my program is > > running - ie very slow > > or timeouts. > > > > We are all accessing a central postgres server. > > My queries have no problems and as soon as I close my > > connection all the > > other connection on the LAN are normal, once more. > > > > Any advise or suggestions? I'd see what network traffic you're generating as well if it's bring your LAN to it's knees. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
On Fri, Jun 07, 2002 at 02:38:27PM -0400, Jan Wieck wrote:
> Andrew Sullivan wrote:
> > expect you're holding a lock on data they're trying to read. Update
> > causes a very aggressive lock, for obvious reasons.
>
> In PostgreSQL that lock is not as aggressive as you might
> think.
Sorry, I knew that, abd just wasn't clear. I was assuming that
someone else must have been trying to write.
Hmm. Or maybe an RI trigger is getting fired? If someone is trying
to insert on a _related_ table you could get the holdups as well.
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
> On Fri, Jun 07, 2002 at 08:52:42AM +0200, Arthur wrote: > > The problem that all the other users/applications on our LAN > > experience difficulties with their connections while my program is > > running - ie very slow or timeouts. > >On Friday 07 June 2002 16:16, you wrote: > It's not clear from this whether they are experiencing general > network problems, of Postgres problems. If the latter, I'd suspect > something your program is doing -- a transaction which is blocking > other operations. If the former, I don't know. > > A Thanks for your reply, I know its a rather vague question, but it is a rather puzzling problem. I am sure its something that my program is doing, beacuse when the program stops evrything returns to normal. The LAN itself does not become slow, just all programs or users that connect to the postgres server (irrespective of the specific database). For example, while my program is running, using another interface, a query to a relatively small table my take about 12 seconds, but when I stop the program, it takes around 4 seconds. I can outline the basic logic of the program, maybe you can pick something up. I query a table and process the rows. For each row an update statement is run that updates data in the processed row. I use a persistant connection for the updates, but I'm not keen to do transaction batches wtih commit/rollback, etc. Is it possible that the postgres server gives precidence to the libpq++ connection? thanks for your time, have a nice weekend! cheers Arthur
Perhaps I missed an earlier message-- what else have you done to isolate the problem? In addition to the list of things I sent last week, I'd suggest configuring your server to log all queries for a while (or even set up a separate server for this) so that you can take a look at what queries are actually being received by the server. (BTW, if you do set up a separate server, do other users still experience the slowness when you are talking to that test server? If YES, then that indicates that perhaps your server machine doesn't have enough resources to accommodate the queries you are doing and other users at the same time. It also could indicate (as others have suggested) that you've got some sort of a machine configuration (or networking) problem. If users DON'T experience the same slowdowns, then I'd think you'd need to start looking at your postgres software configuration. (Like, how many backend processes are allowed, etc.) We had some bizarre (read surprisingly unacceptable) performance from our postgres server and couldn't figure out why until we turned up logging and discovered that someone had a program stuck in a loop doing the same thing hundreds of times/second. -ron > -----Original Message----- > From: Arthur [mailto:arthur@radioretail.co.za] > Sent: Friday, June 07, 2002 7:40 AM > To: andrew@libertyrms.info; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Slow connection > > > > On Fri, Jun 07, 2002 at 08:52:42AM +0200, Arthur wrote: > > > The problem that all the other users/applications on our LAN > > > experience difficulties with their connections while my program is > > > running - ie very slow or timeouts. > > > >On Friday 07 June 2002 16:16, you wrote: > > It's not clear from this whether they are experiencing general > > network problems, of Postgres problems. If the latter, I'd suspect > > something your program is doing -- a transaction which is blocking > > other operations. If the former, I don't know. > > > > A > > Thanks for your reply, I know its a rather vague question, > but it is a rather > puzzling problem. I am sure its something that my program is > doing, beacuse > when the program stops evrything returns to normal. The LAN > itself does not > become slow, just all programs or users that connect to the > postgres server > (irrespective of the specific database). > For example, while my program is running, using another > interface, a query to > a relatively small table my take about 12 seconds, but when I > stop the > program, it takes around 4 seconds. > > I can outline the basic logic of the program, maybe you can > pick something up. > I query a table and process the rows. For each row an update > statement is run > that updates data in the processed row. I use a persistant > connection for the > updates, but I'm not keen to do transaction batches wtih > commit/rollback, > etc. > > Is it possible that the postgres server gives precidence to > the libpq++ > connection? > > thanks for your time, have a nice weekend! > > cheers > Arthur > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > http://www.postgresql.org/users-lounge/docs/faq.html