Thread: Number of connections
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
Roberts, Jon wrote: > 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). Would be problematic at least for the Query Windows, because the SQL script might set session-variables, create temp tables etc. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
> -----Original Message----- > From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki > Linnakangas > Sent: Monday, December 10, 2007 9:12 AM > To: Roberts, Jon > Cc: pgadmin-hackers > Subject: Re: [pgadmin-hackers] Number of connections > > Roberts, Jon wrote: > > 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). > > 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
Roberts, Jon wrote: > 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.
> -----Original Message----- > From: Dave Page [mailto:dpage@postgresql.org] > Sent: Monday, December 10, 2007 9:42 AM > To: Roberts, Jon > Cc: pgadmin-hackers > Subject: Re: [pgadmin-hackers] Number of connections > > Roberts, Jon wrote: > > 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. > 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
Roberts, Jon wrote: > 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. And would make pgAdmin more or less useless for most people. > 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. 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.
> -----Original Message----- > From: Dave Page [mailto:dpage@postgresql.org] > Sent: Monday, December 10, 2007 10:11 AM > To: Roberts, Jon > Cc: pgadmin-hackers > Subject: Re: [pgadmin-hackers] Number of connections > > Roberts, Jon wrote: > > 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. > > And would make pgAdmin more or less useless for most people. > > > Additionally, this second solution could be a preference setting: > > "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