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

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

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