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.

__________________
JaoudeStudios.com | JaoudeStudios.com Forum | JaoudeStudios.com Blog
OpenSource is the road ahead...!

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

__________________
JaoudeStudios.com | JaoudeStudios.com Forum | JaoudeStudios.com Blog
OpenSource is the road ahead...!

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

__________________
Codefreek's - News Letter, Tech, & some random ramblings

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.

__________________
mysql> SELECT * FROM `users` WHERE `users`.`clue` > 0;
Empty set (0.00 sec)

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.

__________________
mysql> SELECT * FROM `users` WHERE `users`.`clue` > 0;
Empty set (0.00 sec)

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.

__________________
JaoudeStudios.com | JaoudeStudios.com Forum | JaoudeStudios.com Blog
OpenSource is the road ahead...!

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.

__________________
mysql> SELECT * FROM `users` WHERE `users`.`clue` > 0;
Empty set (0.00 sec)

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.

__________________
JaoudeStudios.com | JaoudeStudios.com Forum | JaoudeStudios.com Blog
OpenSource is the road ahead...!

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

__________________
mysql> SELECT * FROM `users` WHERE `users`.`clue` > 0;
Empty set (0.00 sec)