MySQL Performance – How to Keep One Query from Slowing Down Entire Database

MySQLperformance

Using MySQL 5.0.x, we have a process to generate benchmarks for our business that consolidates a bunch of data, then pull out data based on certain criteria. This process used to take 18 hours, it's been creeping up to over 24 hours, meaning it's now intruding on the business day.

The problem with this is that these queries substantially slow down the database. Both the INSERT ... SELECT queries and, even moreso, ANALYZE.

Is there some way to make a single query not slow down the entire system? Can MySQL throttle it to not bog down the whole dbms? If these could continue running during the business day I'd be very happy, but without that, … I don't know because I've made this as streamlined as I can with our current system. We're testing moving to MySQL 5.5, and the improvements it gives (like InnoDB) but it would be nice to know if throttling was possible, or if there's some setting I'm missing that's causing a single query to drag down the system.

Best Answer

Unfortunately, as far as I know, there is no way to throttle either INSERT...SELECT or ANALYZE TABLE. I was going to suggest using INSERT DELAYED ... SELECT but came across this:

DELAYED is ignored with INSERT ... SELECT.

So that's out the window. So, if you can't throttle these long-running queries, you're going to have to look at speeding them up so they don't take so long to complete.

I am assuming from one of your other questions that you are using MyISAM. This means your INSERT..SELECT will be locking the table you are writing to, so any attempt to access it will cause waits and long-running queries.

Speeding up the queries are going to depend on how fast your drives are, how much of your 16GB memory you are using effectively, etc. Without such detail, the rest is guess-work:

  • Do you really need to run ANALYZE so often? If so, how big is your key_buffer_size (again, assuming MyISAM)?

  • The SELECT portion of INSERT...SELECT is probably writing to a temporary file on disk, causing you to be IO-bound (waiting for the temporary disk file to be finished before even starting the INSERT portion). You can look at increasing max_heap_table_size and max_tmp_table_size to be larger than the size of your table you're selecting from (note MySQL will use the value that is least of these two variables).

  • You might be able to break the INSERT...SELECT statements into smaller chunk sizes so each one finishes faster, but it depends on your use case.

I'm sorry if this isn't much use at the moment, and will try to update as we get more information.

Related Question