Persistent vs non-Persistent - Which should I use?

Question

My site has always used persistent connections, based on my understanding of them there's no reason not to. Why close the connection when it can be reused? I have a site that in total accesses about 7 databases. It's not a huge traffic site, but it's big enough. What's your take on persistent, should I use them?

Answer 1

My knowledge on the area is kinda limited so I can't give you many details on the subject but, as far as I know, the process of creating connections and handing them to a thread really costs resources, so I would avoid it if I were you. Anyhow I think that most of this decisions can't be generalized and depend on the business.

If, for instance, your application communicates continuously with the Database and will only stop when the application is closed, then perhaps persistent connections are the way to go, for you avoid the process mentioned before.

However, if your application only communicates with the Database sporadically to get minor information then closing the connection might be more sane, for you won't waste resources on opened connections that are not being used.

Also there is a technique called "Connection Pooling", in which you create a series of connections a priori and keep them there for other applications to consume. In this case connections are persistent to the database but non-persistent to the applications.

Note: Connections in MSSQL are always persistent to the database because connection pooling is the default behavior.

Answer 2

With persistent connections:

• You cannot build transaction processing effectively
• impossible user sessions on the same connection
• app are not scalable. With time you mau need to extend it and it will require management/tracking of persistent connections
• if the script, for whatever reason, could not release the lock on the table, then any following scripts will block indefinitely and one should restrart the db server. Using transactions, transaction block will also pass to the next script (using the same connection) if script execution ends before the transaction block completes, etc.

Persistent connections do not bring anything you can do with non-persistent connections.
Then, why to use them, at all?
The only possible reason is performance, to use them when overhead of creating a link to your SQL Server is high. And this depends on many factors like:

• database type
• whether MySQl server is on the same machine and, if not, how far? might be out of your local network /domain?
• how much overloaded by other processes the machine on which MySQl sits

One always can replace persistent connections with non-persistent connections. It might change the performance of the script, but not its behavior!

Commercial RDMS might be licensed by the number of concurrent opened connections and here the persitent connections can misserve

BTW, MySQL is not free for commercial use, as many (mis)believe.