Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that.

What ways are there to limit the processor / ram requirements of a specific query, basically trading speed and freshness for reliability?

share|improve this question
add comment

1 Answer

You can set the session to allow uncommitted reads like so:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;

which will reduce the locking abnd concurrency overhead of your queries (but may make the results inconsistent as you will sometimes see "dirty reads" and similar). For more detail see http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

This will not limit the CPU/RAM/IO footprint of the query itself significantly though.

A common solution to this sort of thing (which may be overkill depending on your circumstances) is to maintain a slave replica on another server (or the same server but on different physical disks if I/O contention is your only/main concern) and run hefty reporting queries from there. Similarly if your reports are not real-time you could restore your backups to another place instead of the hassle of maintaining replication, so if you have daily backups you restore those elsewhere after they are made so your reporting queries don't impact the live DB at all but your reports are only correct as of the last backup. Neither of these methods works if your heavy SQL needs to perform updates or inserts though.

Reusing your backups in this way can be a useful way to verify that your backup arrangement is working: if your automatic restore to the reporting copy fails then that may be an early warning that your backup regime needs work. It is surprising how many people religiously schedule backups but never test them until they need to restore one in an emergency (hence there are quite a few panicked "how do I restore from a corrupt backup?" questions out there in various forums!).

share|improve this answer
    
I'm doing the "run on a separate instance from a backup" thing already. Thanks. –  Gabriel R. Sep 13 '13 at 10:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.