4

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.

Improve this question
3
  • Hey Andrew...what type of hardware are we looking at? What do the queries look like (joins?) What do the tables look like (SHOW CREATE TABLE) on the INSERT..SELECT queries? Have you noted in the SHOW FULL PROCESSLIST what state the queries take the most time?
    – Derek Downey
    Commented Jan 30, 2012 at 16:50
  • It's not about time taken, it's about the fact that this single query (INSERT INTO ... SELECT * FROM [table] WHERE group IN (list of 50 groups)) noticeably slows down the entire database system, so that other queries take several times longer than usual. The database it's selecting from is 4.5GB, with 60 columns and 35 indexes. As for what state, I don't recall, I killed it this morning without taking note.
    – Andrew
    Commented Jan 30, 2012 at 16:57
  • It's not just about that one, though - I can run an ALTER TABLE on a table that big and it will slow the system down too. The hardware is pretty good, 16 cores, 16GB of memory... I don't have the exact specs but, again, under most utilization everything flies by. It's just that certain queries slow the whole thing down, to the point that I can't even add an index to some tables during work hours, because the entire site slows down. Is this normal behavior?
    – Andrew
    Commented Jan 30, 2012 at 16:58

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • These tables are recreated from scratch once a week then analyzed and never written to again. I'll take a look at those two variables. And right now I'm running a test on various methods (dumping to a file, importing it into a table, then deleting what DOESN'T match the criteria, rather than inserting what DOES; using a lookup table to minimize the indexes needed; etc) but it would be nice if there were some way to just slow the damn thing down. :) And locking is not a problem; this table is only read by this process, so that's not the problem.
    – Andrew
    Commented Jan 30, 2012 at 17:58
0

Run the query in two parts:

First do the SELECT to get the entire result set. This should not block the entire database if you're using InnoDB tables.

Then insert the result set into the database. If the data set is very large, break it up into numerous separate insert statements. Again, if InnoDB, this will not block the database.

Improve this answer
7
  • Why would one insert command slow down the database more than using multiple? If it's possible to slow down an entire system with a single query then why don't they offer the ability to throttle queries?
    – Andrew
    Commented Jan 30, 2012 at 21:47
  • @Andrew: You should consider moving from MyISAM to InnoDB engine.
    – ypercubeᵀᴹ
    Commented Jan 30, 2012 at 22:03
  • I intend to, but not while we're on 5.0.x. InnoDB won't slow down the entire database? Why does MyISAM?
    – Andrew
    Commented Jan 30, 2012 at 22:08
  • A query can lock a whole table (or multiple tables) in MyISAM. InnoDB uses row locking (when it can) so it's more difficult for one query to lock the entire database.
    – ypercubeᵀᴹ
    Commented Jan 30, 2012 at 22:51
  • Even if using MyISAM, inserting one row at a time into a table will [generally] not block other operations on the table, so my solution solves half your problem.
    – Brian Papantonio
    Commented Feb 2, 2012 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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