Re: testing and optimizing mysql based app for a shared server
Thanks for replying, Rick.
The storage engine is MyISAM.
I am not sure about number of instances since this is a shared server cpanel account.
The error:
Warning: mysql_connect() [function.mysql-connect]: User '--Removed--' has exceeded the 'max_connections_per_hour' resource (current value: 1) in/home/--removed--/public_html/--Removed--/xyz.php on line 7
Could not connect: User '--Removed--' has exceeded the 'max_connections_per_hour' resource (current value: 1)
The above error was in front end when we tried to access the app.
What the host send us in email was this message:
***************
This message is to advise you of a temporary block placed on your database. The database was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system's CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case. Requests to this database may become degraded by limiting the maximum number of queries or connections for a limited amount of time, or if there are sustained issues, ultimately we may be forced to block access to this database until the issue has been resolved.
Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.
If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.
*********
I fixed the error by removing the nested query and passing a variable value in session instead of pulling it through sql. That fixed it. But - my concern is about load. If there were 100 people at the time using the application when the error was triggered, they were likely gone by the time host conducted a re-run to see whether the problem is recurring or not. So, what I need is a guide to load test my app on a test server where I can see how it behaves with 500 users simultaneously logging in.
It does not have to be a specific solution; I am just looking for standard procedures to cover up such situations before launching an app on a shared server (or otherwise on any server).
Thanks