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.

Reputation: 85

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

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.

Upvotes: 5

Answers (4)

transilvlad

Reputation: 14532

Are 100% sure you have added all necessary indexes?

You need to have a insanely large website to have this kinds of problems unless you are missing indexes.

Make sure you have the right indexing and make sure you do not have connection fields of varchar, not very fast.

I have a database with quite a few large tables and millions of records that is working 24/7.

Has loads of activity and automated services processing it without issues due to proper indexing.

Upvotes: 0

Ja͢ck

Reputation: 173642

There's the LOW_PRIORITY flag, but I'm not sure whether that would have any positive effect, since it's most likely a table / row locking issue that you're experiencing. You can get an idea of what's going on by using the SHOW PROCESSLIST; query.

If other websites run fine, it's even more likely that this is due to database locks (causing your web processes to wait for the lock to get released).

Lastly, it's always advisable to run big reporting queries overnight (or when the server load is minimal). Having a read replicated slave would also help.

Upvotes: 0

Parallelis

Reputation: 729

I strongly suggest you install a replicated MySQL server, then running large administrator queries (SELECT only naturally) on it, to avoid the burden of having your website blocked! If there's not too much transaction per second, you could even run the replica on a desktop computer remotely from your production server, and thus have a backup off-site of your DB!

Upvotes: 0

Mike

Reputation: 1811

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.

Upvotes: 1

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

Answers (4)

Related Questions