Number of connections

[Roberts, Jon]

I've noticed that when using pgAdmin, each user will have multiple
connections to the database. We actually observe three connections for each
user which seem to be:
1. pgAdmin UI
2. Maintenance database
3. Query Window

Then for each Query Window, there is another connection created. So if a
user wants to execute two concurrent queries, they actually have four
database connections open.

Wouldn't it be better to create a connection pool and only increment beyond
one connection to the database when there are true concurrent requests? I
believe this is how M$ SQL Server handles this.

Alternatively, limiting the client to only one connection would be OK too
and be less work than implementing a connection pool. Maybe this could be a
configuration setting. (Multi-thread yes/no).



Jon


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[Heikki Linnakangas]

Post by Roberts, Jon
I've noticed that when using pgAdmin, each user will have multiple
connections to the database. We actually observe three connections for each
1. pgAdmin UI
2. Maintenance database
3. Query Window
Then for each Query Window, there is another connection created. So if a
user wants to execute two concurrent queries, they actually have four
database connections open.
Wouldn't it be better to create a connection pool and only increment beyond
one connection to the database when there are true concurrent requests? I
believe this is how M$ SQL Server handles this.
Alternatively, limiting the client to only one connection would be OK too
and be less work than implementing a connection pool. Maybe this could be a
configuration setting. (Multi-thread yes/no).

Would be problematic at least for the Query Windows, because the SQL
script might set session-variables, create temp tables etc.

[Roberts, Jon]

-----Original Message-----
Linnakangas
Sent: Monday, December 10, 2007 9:12 AM
To: Roberts, Jon
Cc: pgadmin-hackers
Subject: Re: [pgadmin-hackers] Number of connections

Post by Roberts, Jon
I've noticed that when using pgAdmin, each user will have multiple
connections to the database. We actually observe three connections for

each

Post by Roberts, Jon
1. pgAdmin UI
2. Maintenance database
3. Query Window
Then for each Query Window, there is another connection created. So if

a

Post by Roberts, Jon
user wants to execute two concurrent queries, they actually have four
database connections open.
Wouldn't it be better to create a connection pool and only increment

beyond

Post by Roberts, Jon
one connection to the database when there are true concurrent requests?

I

Post by Roberts, Jon
believe this is how M$ SQL Server handles this.
Alternatively, limiting the client to only one connection would be OK

too

Post by Roberts, Jon
and be less work than implementing a connection pool. Maybe this could

be a

Post by Roberts, Jon
configuration setting. (Multi-thread yes/no).

Would be problematic at least for the Query Windows, because the SQL
script might set session-variables, create temp tables etc.

It took me a minute, but I think I see what you mean now. It would have to
be a modified connection pool like M$. When you execute a query, it creates
a new connection to the database only if the existing connection isn't busy.
When this happens, the connection is stuck to this query window. Sort of
like a sticky session in a web browser.

I would think far less code would be needed if the tool could be configured
to only use one connection.


Jon

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Dave Page]

Post by Roberts, Jon
I would think far less code would be needed if the tool could be configured
to only use one connection.

How'd you figure that? You'd need to write the connection pooler, start
tracking connection states in far more detail than we do now, and
potentially have to start new connections (which is expensive) in
response to UI interactions which the user may well expect to be almost
instantaneous.

More importantly, I don't imagine you'd save much anyway - connections
are relatively cheap, and pgAdmin only creates them when you actually
need them (and closes them when you close Windows for which they were
created). I can't imagine such an architecture saving more than 1 in ten
connections at best.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[Roberts, Jon]

-----Original Message-----
Sent: Monday, December 10, 2007 9:42 AM
To: Roberts, Jon
Cc: pgadmin-hackers
Subject: Re: [pgadmin-hackers] Number of connections

Post by Roberts, Jon
I would think far less code would be needed if the tool could be

configured

Post by Roberts, Jon
to only use one connection.

How'd you figure that? You'd need to write the connection pooler, start
tracking connection states in far more detail than we do now, and
potentially have to start new connections (which is expensive) in
response to UI interactions which the user may well expect to be almost
instantaneous.
More importantly, I don't imagine you'd save much anyway - connections
are relatively cheap, and pgAdmin only creates them when you actually
need them (and closes them when you close Windows for which they were
created). I can't imagine such an architecture saving more than 1 in ten
connections at best.

I mean this:
Solution 1: Manage a complicated connection pool like you described above.
Solution 2: Only allow one connection to the database. A new query window
will not create a new connection. The tool will not allow new queries to
execute while another query is executing.

The second solution would require less code than the first one.

Additionally, this second solution could be a preference setting:
"Multithreaded Yes/No"

The reason I'm bringing this up is because it is a big deal to manage all of
the connections when using Greenplum. Each connection really means a
connection to each internal database. There is one database per CPU core
plus the master node. For us, that means we have 17 connections per user's
connection. When there are three connections needed for a single query to
run, it really means there are 51 connections total connections to all of
the underlying databases.



Jon

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Dave Page]

Post by Roberts, Jon
Solution 1: Manage a complicated connection pool like you described above.
Solution 2: Only allow one connection to the database. A new query window
will not create a new connection. The tool will not allow new queries to
execute while another query is executing.
The second solution would require less code than the first one.

And would make pgAdmin more or less useless for most people.

Post by Roberts, Jon
"Multithreaded Yes/No"
The reason I'm bringing this up is because it is a big deal to manage all of
the connections when using Greenplum. Each connection really means a
connection to each internal database. There is one database per CPU core
plus the master node. For us, that means we have 17 connections per user's
connection. When there are three connections needed for a single query to
run, it really means there are 51 connections total connections to all of
the underlying databases.

Well, like I said I can't imagine you actually saving any significant
amount of connections using a pool anyway. We already reuse the
maintenance DB connection when browsing that database, and connections
to other databases are only created when required by a user action (and
don't forget of course, a connection is fundamentally tied to one
database in postgres so you can't reuse the maintenance DB connection
for some other database). Other connections are used by tools like the
status monitor, query tool and data editor, but as Heikki pointed out,
we have good reason for wanting to keep those seperate from one another.

If the fact that GP creates a connection to each node for each user
connection is such a resource issue for you, I would suggest petitioning
them to implement a lightweight protocol for inter-node communication.

Regards, Dave.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Roberts, Jon]

-----Original Message-----
Sent: Monday, December 10, 2007 10:11 AM
To: Roberts, Jon
Cc: pgadmin-hackers
Subject: Re: [pgadmin-hackers] Number of connections

Post by Roberts, Jon
Solution 1: Manage a complicated connection pool like you described

above.

Post by Roberts, Jon
Solution 2: Only allow one connection to the database. A new query

window

Post by Roberts, Jon
will not create a new connection. The tool will not allow new queries

to

Post by Roberts, Jon
execute while another query is executing.
The second solution would require less code than the first one.

And would make pgAdmin more or less useless for most people.

Post by Roberts, Jon
"Multithreaded Yes/No"

Having the above option would be overall more useful to the entire
community. For those that want to limit the connections, pick No. For
users that like the current number of connections spawned, leave it as Yes.

PL/SQL Developer by All Around has this same option. Toad by Quest Software
has this option too. Most database IDEs have some sort of way to configure
the number of connections created by the tool.


Jon

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings