Let’s say you have a table with 20 million rows in it. It’s performing fine at the moment, but you want to see what will happen when it grows to 40 million rows. You might think that adding 20 million rows of random data to a test copy of your db would probably be best accomplished with raw sql, and you might be tempted to do something like this:
insert ignore into mytable (id, name) select concat(id, round(rand() * 1000000)), concat(name, round(rand() * 1000)) as name from mytable on duplicate key update name=concat(values(name), now());
Do not do this.
First, your server will completely freeze up as you exhaust all of the space in the lock table (ERROR 1206 (HY000): The total number of locks exceeds the lock table size), then you’ll try to limit the subselect to a certain length and it will seem to work, then you’ll realize that it’s all happening inside a single transaction that probably will never finish, then other queries will start throwing the lock error even though this one doesnt, then when you cancel it, innodb will try to roll back the 600k rows it did insert, and it will take a really long time to clean up this mess, and you’ll probably decide to restart the server a few times.
Lessons are fun.