Website slow response (all other users) when large MySQL query running

Question

This may seem like an obvious question but we have a PHP/MySQL app that runs on Windows 2008 server. The server has about 10 different sites running from it in total. Admin options on the site in question allow an administrator to run reports (through the site) which are huge and can take about 10mins in some cases. These reports are huge mysql queries that display the data on screen. When these reports are running the entire site goes slow for all users. So my questions are:

• Is there a simple way to allocate server resources so if a (website) administrator runs reports, other users can still access the site without performance issues?
• Even though running the report kills the website for all users of that site, it doesn't affect other sites on the same server. Why is that?
• As mentioned, the report can take about 10 minutes to generate - is it bad practice to make these kinds of reports available on the website? Would these typically be generated by overnight scheduled tasks?

Many thanks in advance.

Answer 1

The load your putting on the server will most likely have nothing to do with the applications but the mysql table that you are probably slamming. Most people get around this by generating reports in down time or using mysql replication to have a second database which is used purely for reporting.

I recommend trying to get some server monitoring to see what is actually going on. I think Newrelic just released windows versions of its platform and you can try it out for free for 30 days i think.