0

After some recent changes a site has started showing the max_user_connections error which is probably a sign that too many concurrent connection attempts are being to the MySQL database.

I've noticed that the original programmers implemented a "DB" class for managing database connections and it seems that a close is only called in the footer of each page. How could I go about improving this? Should I create a db object for each request I want to make and then close immediately after results have been retrieved?

SysDragon
  • 9,692
  • 15
  • 60
  • 89
James P.
  • 19,313
  • 27
  • 97
  • 155

2 Answers2

2

I am not familiar with PHP but closing DB-Connections as soon as possible is always good and independent from the programming language used. Also try to get a new DB-Connection as late as possible.

If you're doing transactional DML then you should also release locks as soon as possible by commiting as soon as possible (but as late as necessary). Having transactions opened longer than necessary may result in other transactions waiting for it. These waiting transactions are consuming a connection each while doing nothing than waiting.

Your "DB-Class" sounds like a Utility-Class for handling such purposes.

Have a look at this question, too.

Community
  • 1
  • 1
Fabian Barney
  • 14,219
  • 5
  • 40
  • 60
0

Php already close the conn when the scripts ends.

You may want to rise the max comm limit in mysql config file

Also execpt your script takes very long you wont do nothing by closing the conn after a query

dynamic
  • 46,985
  • 55
  • 154
  • 231
  • This is on mutualized hosting so increasing connection limit isn't an option unfortunately. The scripts aren't exceptionally long but there are quite a few requests in them. – James P. Jun 06 '11 at 15:27
  • if you have quite a few requests you can't clsoe the connection after the first query.. – dynamic Jun 06 '11 at 17:05
  • What do you think of the following approach (from page top to bottom)?: include header+initialization=>sql requests=>save results=>close connection=>render html. – James P. Jun 06 '11 at 23:05
  • @james: it will change nothing. because render html doesn't take much – dynamic Jun 06 '11 at 23:07
  • I see. Just realized that one page is missing a close at the bottom. This might be the source of the issue. – James P. Jun 07 '11 at 04:13