I have a web application, which has been suffering high load recent days. The application runs on single server which has 8-core Intel CPU and 4gb of RAM. Software: Drupal 5 (Apache 2, PHP5, MySQL5) running on Debian.
After reaching 500 authenticated and 200 anonymous users (simultaneous), the application drastically decreases its performance up to total failure. The biggest load comes from authenticated users, who perform activities, causing insert/update/deletes on db. I think mysql is a bottleneck. Is it normal to slow down on such number of users?
EDIT: I forgot to mention that I did some kind of profiling. I runned commands top, htop and they showed me that all memory was being used by MySQL! After some time MySQL starts to perform terribly slow, site goes down, and we have to restart/stop apache to reduce load. Administrators said that there was about 200 active mysql connections at that moment.
The worst point is that we need to solve this ASAP, I can't do deep profiling analysis/code refactoring, so I'm considering 2 ways:
It's impossible to give a specific number of users that would cause a slowdown on any random application. It depends entirely on what the application does and how it's run. There are a number of things to look at.
Profile. Run your application through a profiler with as much real-world usage as possible. The best thing to do is use an automated test or series of unit tests that run through all layers to make the profiling session as repeatable as possible. Even if you profile your application under lower load, you can identify bottlenecks and improve them. You should profile both your application code and SQL code.
Bottlenecks. Profiling will tell you what code and/or queries are taking up the most time and fixing those will help a lot, but you also want to look for architectural bottlenecks that you can avoid. Do you have a users waiting on writes that they don't really need to wait on? Can you use a producer/consumer queue to queue up certain non-critical writes so the app can respond faster to users and flush this data to the db lazily. Are there any long-running requests that are waiting on other outside resources that can benefit from asynchronous processing?
Caching. Are there some requests or data that can be cached? Even if this is not the bottleneck, reducing the load on the server as much as possible will help. Particularly if you have a lot of database contention and you can cache some commonly used data in the application, then you can avoid some database round-trips.
Memory data. Take a look at how your application uses the database and see if there is anything that doesn't really need to be in the database. If so, moving this data to in-memory data structures (or even an in-memory database) would vastly improve performance. This isn't commonly possible though, but when it is, it's a huge benefit.