3

Very recently, I started getting the SQL max user connections limit reached error. I contacted my host and was notified that my limit is 10 concurrent connections at a time. But SQL is central to my website, so instead of the error I want the code to keep trying to get connection till the query is successful.

How do I do this in PHP?

EDIT: Here is the thing, I make AJAX calls to retrieve the data for the client. Every AJAX call though it takes ~400 milliseconds to finish, opens up a new MySQL connection. With 10 or more users, I run into problems.

david
  • 2,529
  • 1
  • 34
  • 50
user1137403
  • 45
  • 1
  • 7

2 Answers2

1

For starters, close connections when you finish with them, so that they don't stay opened until the end of the script.

A better solution though is to just use a single connection to do all your work.

Use PDO, and pass the PDO object around as you need it. One connection, multiple operations.

Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
1

Why don't you try using persistent connections instead?

If you need the database connection in order for your page to work, you should block until the connection has been established. This is best implemented by having a pool of ten connections and sharing those amongst all your page instances.

This has the added benefit of reducing the cost of connection in the first place, since fewer connection-establishment and connection-teardown calls are made.

You didn't say which DB engine you were using, but mysql_pconnect is one such PHP call.

EDIT: after reading the other answers, it's worth noting that you should never use more than one connection per page load. I'm assuming you are already only using one per page load, but you have more than ten simultaneous instances of PHP going.

Borealid
  • 95,191
  • 9
  • 106
  • 122
  • I am using INNODB as my storage engine. How do I go about making a pool of connections? – user1137403 Jan 28 '12 at 20:11
  • So I have 6 different PHP files that are called to by different AJAX Calls to fetch different data. So, following your suggestion, I would like to open 10 persistent connections and share them? How do I "share"? – user1137403 Jan 28 '12 at 20:14
  • @user1137403 Just change all the `mysql_connect` calls to `mysql_pconnect`, and presto, it's shared. The server just needs to *allow* persistent connections. – Borealid Jan 28 '12 at 20:18