Persistent DB Connections – Yea or Nay?

by

You could use this as a rough “ruleset”:

YES, use persistent connections, if:

  • There are only few applications/users accessing the database, i.e. you will not result in 200 open (but probably idle) connections, because there are 200 different users shared on the same host.
  • The database is running on another server that you are accessing over the network
  • An (one) application accesses the database very often

NO, don’t use persistent connections, if:

  • Your application only needs to access the database 100 times an hour.
  • You have many webservers accessing one database server
  • You’re using Apache in prefork mode. It uses one connection for each child process, which can ramp up fairly quickly. (via @Powerlord in the comments)

Using persistent connections is considerable faster, especially if you are accessing the database over a network. It doesn’t make so much difference if the database is running on the same machine, but it is still a little bit faster. However – as the name says – the connection is persistent, i.e. it stays open, even if it is not used.

The problem with that is, that in “default configuration”, MySQL only allows 1000 parallel “open channels”. After that, new connections are refused (You can tweak this setting). So if you have – say – 20 Webservers with each 100 Clients on them, and every one of them has just one page access per hour, simple math will show you that you’ll need 2000 parallel connections to the database. That won’t work.

Ergo: Only use it for applications with lots of requests.

More Related Contents:

  1. MySQL remote connection fails with “unknown authentication method”
  2. php, mysql – Too many connections to database error
  3. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  4. How to connect to SQL Server database from JavaScript in the browser?
  5. How persistent is localStorage?
  6. SQL Express connection string: mdf file location relative to application location
  7. PDO with INSERT INTO through prepared statements [closed]
  8. PDO::fetchAll vs. PDO::fetch in a loop
  9. Are there good tutorials on how to use PDO? [closed]
  10. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  11. How to form a correct MySQL connection string? [closed]
  12. Is there a timeout for idle PostgreSQL connections?
  13. Using javafx.beans properties in model classes
  14. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  15. Parametrized PDO query and `LIMIT` clause – not working [duplicate]
  16. Using htmlspecialchars function with PDO prepare and execute
  17. How to increase MySQL connections(max_connections)?
  18. Easiest way to persist a data structure to a file in python?
  19. PDO::PARAM for type decimal?
  20. Is “SET CHARACTER SET utf8” necessary?
  21. Retrieve (or simulate) full query from PDO prepared statement
  22. com.mysql.jdbc.PacketTooBigException
  23. PDO returning incorrect, but duplicate, data. Key’s not in database.
  24. Set PDO to throw exceptions by default [duplicate]
  25. PDO: MySQL server has gone away
  26. Sharing a persistence unit across components in a .ear file
  27. Checking for an empty result (PHP, PDO, and MySQL) [duplicate]
  28. PHP/SQL Insert Error when using Named Placeholders
  29. PHP: mysql v mysqli v pdo [closed]
  30. PDO Return All Rows [duplicate]

Leave a Comment