PHP/MySQL database connection priority?

Hello,

I have a production database where usage statistics reside. This database is responsible for many other things (not just statistics calcs). I use php to periodically roll up different resolutions (day, week, month, year) of interesting statistics in buckets dictated by the resolution.

The php application I've written "completes" its data when its run, such that it will calculate all the rolled-up statistics for the resolutions and periods since it was last run. This is useful if we want to turn this off to debug database performance issues, because I can turn it back on and have it complete its data set independent of the script run frequency (cron job could be moved from daily to weekly, etc).

The problem I have, is the calculations are fairly intensive and drive the QPS of the production database server up. Is there a way to set a "priority" on a particular database connection so that it will only use "off-cycles" to do these calculations?

Maybe a proper response would be to replicate the tables I'm working on into a different stats database, but, unfortunately I don't have the resources in place to attempt such a thing (yet).

Thanks in advance for any help, Josh

Curious how this works..."If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table."This appears to be a server setting? It doesn't seem appropriate, because I don't want ALL inserts, updates, deletes to be affected (its a production database that does many other things). in fact, i want ALL queries issued by this connection to be treated low priority (even selects, etc)

Josh 2010-03-25 20:17:59

The documentation says "Variable Scope: Both" which means you can set it globally but also only for the session/connection. And it's marked as "dynamic: yes", so http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html applies.

VolkerK 2010-03-25 20:37:22

Thanks for the reponses -- I voted up the answers. However, the nature of the queries that are intensive are select avg() and select max() on large datasets, as well as many small selects over small time domains. its not really the insert, update, and deletes that i'm worried about per se (they are on a table that has no interaction with the product and so locks are not intrusive). i was more worried about maxing I/O or crushing the processor because of the sorting etc. I'm not sure there is a general purpose way to just simply use the db when its not in use, unless i've missed something.

Josh 2010-03-28 03:42:32

ansaurus

tags:

views:

answers:

PHP/MySQL database connection priority?

related questions