Pause and resume mysql query execution

Question

I have a rather slow query (actually it is loading data from mysql.dump). Now I want to check how many lines are already loaded.

select count(*) from my_table; 

As for data loading, my_table blocked, so I cannot execute this sql before loading the dump.

 Cmd ID State User Host DB Time Query Query 191 update root localhost rehub 00:09 INSERT INTO `my_table` VALUES ... Query 189 Waiting for table root localhost rehub 06:25 select count(*) from my_table 

So, is there a way to suspend request 191, execute request 189, and then resume request 191?

Answer 1

There is no way to pause the request.

If you are doing bulk loading, you might want to use a large bootloader that captures every few thousand lines.

In addition, using MyISAM instead of InnoDB will alleviate this problem. MyISAM is a good choice for bulk processing, where you are not primarily concerned with transactional integrity.

Answer 2

To quote from the manual :

All options that are indicated by --opt are also enabled by default because -opt is enabled by default.

Therefore, when you add –-extended-insert=FALSE and --add-locks=FALSE to the mysqldump parameters, your counter (*) should work. Depending on your situation, beware of referential integrity issues with your db. And it will slow down the import!

Answer 3

You can still get the number of records inserted using show innodb status