What ways are there to limit the processor / ram requirements of a specific query?

Posted on

Question :

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?

Answer :

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


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!).

Leave a Reply

Your email address will not be published. Required fields are marked *