19

Any pool in an environment in which time can flow backwards or leap forwards, which it can in a virtual machine, is going to be subject to spurious failure when it does so. Moreover, it is not just connection pools that are affected. Any... Timed waits Timed polls from a concurrent collection Object.wait() with a timeout Thread.sleep() call ... Anything ...


14

A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary. The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the ...


12

"Can support" != "optimal throughput". You can use lots of connections, but it's slower. If you use fewer connections and queue work, you get the same amount of work done in a smaller time. Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well? ...


12

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK). More than 500 simultaneous connections per box will be a problem though (like actively querying the database at the exact same time). More cpus/cores is better. Use SSDs with RAID 10. Your SaaS application should connect ...


10

Essentially this is enabled by default when using the System.Data.SqlClient data provider. There is a very good article about the semantics which can be found here: SQL Server Connection Pooling (ADO.NET) Also, you can view the ConnectionString property reference of the SqlConnection class here: SqlConnection.ConnectionString Property This contains a lot ...


10

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


7

Is there any way to track the connections opened and relate it to a connection pool created from database side. Set the Application Name in your config and you can monitor from the server side in sys.dm_exec_sessions. You'll get a separate Connection Pool for each Connection String, so if you set the Application Name in the connection strings you can tell ...


6

First, CONTEXT_INFO is a property of the session, not the connection. It gets reset by sp_reset_connection when the same session is reused and the first batch is executed. It seems that CONTEXT_INFO was not reset in SQL Server 2000 and possibly earlier versions, but starting with SQL Server 2005 it is definitely reset to NULL. Part of the confusion here is ...


6

So I am bit confused now, if I am using only 2 connection string with connection pool then why we are seeing so many sleeping connection, I am thinking it should be only 2? Min Pool Size=50 specifies number of connections established when the pool is created and will remain in the pool until the application pool is unloaded (including an IIS restart). ...


5

Well, there are four main aspects to this: Why are you asking the general Interwebs for an opinion when there are three other sources that should be checked first, one of which will be definitive and not an opinion: When someone tells you a technical / scientific piece of information, whenever possible, ask them how they know that to be the case. Did they ...


5

Isn't it self-contradictory paragraph ("until" vs. "retained")? Not to me, but I can see there are other ways to read it. If you want the documentation updated to make it clearer, the proper place to request this is on Microsoft Connect. The people here do not maintain Microsoft documentation. that the default isolation level will have arbitrary value (...


5

1 - can't say for sure, I'd have to go find a server to dig into myself. 2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist? The way ...


5

The default max pool size is 100 and one should generally stick with that unless there are special requirements. Setting the max connection pools size higher than that means you could have that many queries against the database concurrently and exacerbate problems like long-term blocking or long-running queries. Unused pooled connections are always reused ...


5

I understand that if you don't pool database connections at all, then you bear the overhead of establishing a new database connection every time you need to query the database, which will make your app slow, and that you save that overhead by pooling. Generally, but it depends... If it's a long lived process or connection, a dedicated connection can be more ...


5

My question is on sql server side, how can I know which session's connection string has pooling property set to true and which set to false? You generally won't and can't. The connection string isn't sent to SQL Server, it's a directive for the driver on what information and options it needs in order connect and how it should act in certain situations. The ...


4

IHMO, using of connection pool hasn't a minimum. You can use it when you are sure there are a lot of accesses on your DB and they are very frequently, so you're using the CP guaranteed to use in better way the available connections that caught by users when they are idles. The numbers of connections must consider the dimension of DB server. There's no a ...


4

Community Wiki answer originally left as a comment by the question author In my case it turned out to be a runaway logging table that somebody had switched into verbose to troubleshoot an issue, but forgot to switch off. It ended up logging up to 1000 records a second. Another job was attempting to delete old records from the table. ended up getting itself ...


4

No, the connection properties should be immutable. However, even if you could edit them, there is still an easier (and better) way to correlate Logon Events to the same Connection: the connect_time field in sys.dm_exec_connections. You just look up that field based on the session_id (which correlates to SPID in the XML returned from the EVENTDATA() function)....


4

Spinning off thousands of processes to do tiny pieces of a task is simple. But inefficient. "Processes" are heavyweight. Threads are somewhat heavy. Thousands of connections to MySQL is possible, but not all at the same time. In any multi-process or multi-threaded environment, having "too many" will slow things down. This is because the OS (or some ...


4

In a web application you use a connection pool that establishes a fixed amount of database connections that are then shared between all sessions in your web applications. Even when a web application has thousands of concurrent users, it might only need as much as 50 database connections (if the connection pooling is done right). Meaning, if a user loads ...


3

SQL Server 2014 now resets the isolation level of pooled connections for most TDS protocol versions. Previous SQL Server versions did not reset the isolation level.


3

You can spoof this in the application's connection string, or you can store override info elsewhere (like CONTEXT_INFO()) but, no, I don't believe there is any way to change what SQL Server shows in APP_NAME() (or the undocumented PROGRAM_NAME()) from inside SQL Server. Now that you've explained what you're really trying to do...


3

Increasing removeAbandonedTimeout will alleviate the problem but not fix it. This issue can be caused by not closing the connections properly or by queries that take too long. See if you can monitor the queries that are run, time them and see if they are being closed correctly.


3

This started off as a comment but it's getting a bit long. Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else. If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how ...


3

The error is due to the number of connections to the database, each Lambda function will open at least one, and in your case, the number exceeds the number of connections available in you RDS instance. Through the AWS Console you can raise it, just look for max_connections, here is a step by step tutorial: https://github.com/jollygoodcode/jollygoodcode....


3

This is base of pgBoouncer or any pooler software - the main work of pgBouncer is reusing PostgreSQL connections. Creating lot of connections can be very expensive for short sessions - with fast queries. When physical connection can be shared (reused) for more clients, you can get much faster execution. Second important role of pgbouncer and similar ...


3

Limits are there to prevent runaway resource usage. If you're confident that your database can handle 1000 concurrent connections during the highest load periods (because it's exactly the time when the pool usage will be the highest), and that you won't have another application server running concurrently, then there's no harm in setting the pool size to ...


3

Great question! To find out if the connection is still open right now, look in sys.sysprocesses: SELECT * FROM sys.sysprocesses; Generally, as long as they're not doing anything, open connections aren't a problem for SQL Server - it can handle tens of thousands. Connection pooling - keeping a connection open and ready for reuse - is fine, and doesn't ...


3

pgBouncer cannot do that, and indeed it is a questionable feature. How can you reliably tell that a statement like SELECT pg_create_physical_replication_slot('aname'); won't modify your database? There is pgPool which boasts such a feature, you might investigate it. The better technique is usually for your application to use two database connections: one ...


3

If I'm doing something wrong or missing some Point please let me know. You're not missing anything. SSMS always has a separate session per tab. it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran. In SSMS the Execute ...


Only top voted, non community-wiki answers of a minimum length are eligible