1

I have a process that needs to run every X minutes to update certain information. It does not matter whether the query takes 10 seconds, 20 seconds or even a minute. What matters though, is that the information is updated at least every 10 minutes for example.

Because the query is slow and because the server is being used for other queries, it affects other users. This is the situation that I am trying to solve.

I would cpulimit whichever process runs the query, but the process taking up all the resources is the MySQL server itself, so it seems like I need some MySQL specific setting.

It is a single MySQL query that's causing the server to freeze. I would like to limit the resources allocated to that specific query.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
wtf8_decode
  • 452
  • 6
  • 19
  • There are no optimizer hints about limiting system resources.. The only way to limiting resources is to optimize the query with index(es). – Raymond Nijland Oct 14 '17 at 14:58
  • Please read this. http://meta.stackoverflow.com/a/271056/ Pay special attention to the section on performance. Please [edit] your question to provide relevant details. – O. Jones Oct 14 '17 at 15:11
  • Can't do resource constraints. Instead, please show us the query, its `EXPLAIN ...` and `SHOW CREATE TABLE` for any table(s) it uses. From there, we can discuss better index(es), breaking into chunks, etc. – Rick James Oct 15 '17 at 03:52

1 Answers1

0

MySQL has no way to tell the mysqld server to use a lower priority or fewer resources for certain queries. You need to focus instead on these things:

  1. Making the offending query take less time. That can be done in one or both of these ways.

    a. breaking it up into smaller queries

    b. creating appropriate indexes to make it faster

  2. Making it interfere less with other queries.

You didn't show us the query so the rest of this answer is based on guesswork.

1a: Do your updates in chunks of a few hundred or thousand records. Repeat these (shorter) updates until your task is complete. Once each segment runs, it will get out of the way and let other queries finish.

1b: Read about using EXPLAIN and using indexes to make queries faster. Make your big query faster. Indexes can accelerate your WHERE clauses a lot.

2: You can set the isolation level for queries on your system so they don't have as much dependence on the each other. That may let them share the server more gracefully. Issue the query to set the isolation level to READ UNCOMMITTED right before you issue the queries that are blocked by your update job. That will let them finish, and use the data that was present before your update job completed, rather than waiting for the job.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

before you issue your other queries.

If you control the whole server, it may be easier to set READ UNCOMMITTED as the default isolation level.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Basically the process updates the SPHINX search database, created from a view from other tables. – wtf8_decode Oct 14 '17 at 15:08
  • Did you mean to recommend `READ COMMITTED`? I've never seen a good use case for `READ UNCOMMITTED`. That isolation level breaks atomicity because it allows a transaction to see partially-completed transactions. – Bill Karwin Oct 14 '17 at 15:19
  • I meant `READ UNCOMMITTED`. It's very possible the questioner's application is a valid use case for this isolation level. Every X minutes some new data arrives for search. The long-running update process makes it available to the search engine. If the generic query traffic can succeed reading old data rather than new, then `UNCOMMITTED` should work tolerably well. That's only true, though, if the updates are to more-or-less independent data items. – O. Jones Oct 14 '17 at 15:24
  • `READ COMMITTED` would also allow the long-running transaction to view recent updates, but only those updates that have been committed. This is desirable, because it won't risk seeing bad data in cases when other clients intended to make multiple updates, but have the changes appear atomic. – Bill Karwin Oct 14 '17 at 15:45