How to prioritise certain queries in MySQL?

views:

128

answers:

+3 Q:

How to prioritise certain queries in MySQL?

I have a large number of background reads and writes, and a much smaller number of user reads, of a highly contended MySQL database - is there any way to flag certain queries (the user queries) as being high priority so that they take preference over background queries? I want user responsiveness to be high, but don't really care about the background queries.

Thanks

+2 A:

MySQl supports insert delayed for low-priority inserts and low_priority for updates. And for your user reads, there's the high_priority extension on select. If you're using the MyISAM storage engine, there's the related (but different) concurrent inserts feature.

T.J. Crowder 2010-03-09 10:35:45

On the other hand, `SELECT` supports the `HIGH_PRIORITY` keyword, which give selects the same priority as an update.

Romuald Brunet 2010-03-09 10:38:00

@Romuald: Apparently I was editing my answer as you commented. :-)

T.J. Crowder 2010-03-09 10:39:45

Am i correct in thinking that if there are no high priority selects all low priority inserts and updates would just operate at normal priority?

MalcomTucker 2010-03-09 10:46:19

also, does a high priority select override a low priority update? thanks

MalcomTucker 2010-03-09 10:49:29

@Malcolm: The linked docs suggest there's an overhead to low-priority stuff, so it wouldn't be free. Re your second question, again deferring to the docs but I'm pretty sure that's the point, yes. I think even a normal select is less-impacted by a low-priority update (for instance) and that a high-priority select is scheduled more aggressively over other statements, including other selects. The only way to know how this will play out with your schema and such is to give it a whirl, but you're at least telling the DBMS what *you* think is a priority.

T.J. Crowder 2010-03-09 10:53:47

The thing they don't seem to offer that I've wanted more than once is a *low-priority* select, a "get to this whenever you get to it," for ad hoc admin queries. But I guess I really shouldn't be doing ad hoc admin queries on the live DB; I should be using a replicated server or a snapshot. Ah, well... :-)

T.J. Crowder 2010-03-09 10:57:23

ok, many thanks for your help :)

MalcomTucker 2010-03-09 11:35:25

ansaurus

tags:

views:

answers:

How to prioritise certain queries in MySQL?

related questions