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?
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?