Maintaining MySQL Connections
 

I am using an external MySQL database server due to the high load I receive when having MySQL running locally. Both servers are located in the same datacenter, but I found that, whenever the connection from my server to the MySQL database server goes up to about 70++, the MySQL server blocks new connections. I have been trying to use persistent connections but it still opens up a lot of connections to the database server. Is there any suggestion how to maintain the number of connections?

It sounds like you're using more than the configured max_connections. See http://dev.mysql.com/doc/mysql/en/to...nnections.html

MySQL by default is usually set to 100 connections, so you will need to up this. Persistent connections will give you more of these problems as the connection will remain open, trying using non-persistent connection if your application can handle it.

But if I am to use non-persistent connections, it will definitely exceed the limit the external server set on (15 connections, not under my control). Since there will be more than 15 users going to be accessing the site at 1 time, what am I supposed to do?

Quite the opposite, if you use non-persistent connections, you can have many more people on the website at once as it will use the connection then drop it, which will allow someone else to open a connection.

15 persistent connections will only allow 15 users.

15 non-persistent connections will allow many more than 15 users.

NB: Glad I am not using my ruby on rails app on that connection, it pools 5 connections per user for speed :). So I would only be allowed 3 users :s

Ok, I will be using non-persistent connection for a while, then I will report back what's the situation.

EDIT: I forgot to mention, my script uses Ajax call every single second from each user to get some stats, it won't be accessing the database but it certainly do connect to the database since I include the whole header together with the part connecting to the database. In this case, does any suggestion change?

Ok, it doesn't work at all. It connects more than 50 connections at a time, are there anymore solutions? I tried setting max connection in php.ini but still didn't work.

LINK1

LINK2
Take a look at does links should be some more, information.
on the problem you are having.

and also you could try to google, "setting max connection + mysql" it did give me some info :)


-Cf

Did you edit your mysql config (typically called my.cnf in /etc), you can't edit mysql server settings via php.ini

you will need to add something like:
of course you will need to restart mysql for this change to be effective.

You could also set it to 0, then mysql wont limit it at all, however this probably isn't a good idea.

First, I wish to state that the MySQL server is out of my control, I can't make any changes to it. Second, I hope to solve this problem by PHP.

By the way, please note that the external MySQL server is a shared hosting server. It blocks connection when the connection number reaches 50.

Then I doubt you will be able to do anything about it, unfortunately. To my knowlege you cannot set MySQL server variables via PHP.

Unfortunately, you are s**t out of luck, you can not override this with php! As it would be a security risk. You could try and be clever by queuing the connections - but it wouldnt be easy.

Indeed! This is where shared hosting becomes inadequate you will have to think about getting your own server (be it a dedicated box or even a VPS) if you want to tweak server settings.

Some budget problems prevent me to get a vps...

Ok, I will be using iptables method to solve this problem anyway. Thanks for all the help.

Check out rapidswitch.com, I remember their VPS being very cheap! I have a Dedicated box with them and so far they are pretty good.

They are quite reasonable, the dedi's arnt too bad either.