up vote 1 down vote favorite
share [g+] share [fb]

I killed a MySQL process which is running a "LOAD DATA INFILE" command to load a 9GB .csv file into a table in a InnoDB database.

Now, when I run "mysql DATABASE", it just stopped there and showed

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

What can I do now? I guess it's doing some repair and cleanning work caused by the interrupted "LOAD DATA INFILE" command.

Solution:

I tried "service mysql restart" but I decided to kill all mysqld processes forcibly after restarting took more than 1 hours and still no clue how many more hours ahead to complete.

Then I use "service mysql start" to run MySQL server again. Fortunately the InnoDB rebuild process is quite fast, took about 15 minutes to complete, no data loss.

link|improve this question
86% accept rate
feedback

1 Answer

up vote 1 down vote accepted

Reading table information can be an intensive process depending on the size or load on the database.

While you killed the system process running the MySQL command, you likely did not have the MySQL connection terminate.

Run mysql -A DATABASE or just mysql, look at the process list with show full processlist; and if you want the connection to terminate run kill processnumber;.

link|improve this answer
I tried "mysql -A DATABASE" and "mysql", all the same as without "-A" switch. I'm now shutting MySQL server down. It's still in progress after 20 minutes. – jack Aug 18 '10 at 14:16
I did a check with "free" command, the Swap figure is very high. I killed memcached service and the Swap figure is declining now. What should I do at the moment? Maybe wait for MySQL server to be shutdown is the best option? – jack Aug 18 '10 at 14:18
Sounds like you ran out of system resources resulting from the load. Check your .err log. Forcibly terminated the process risks data but InnoDB will (usually) rebuild and you can repair table with MyISAM. Forcibly terminating usually isn't a good idea unless you're prepared to accept complete data loss and potentially the need to rebuild. You might need to tune MySQL to better utilize the available system resources. Running memcached on the same server as a database seems like a bad idea to me as well. – Warner Aug 18 '10 at 14:24
I checked mysql-error.log and it shows only logs like "100818 8:53:07 [Warning] /usr/sbin/mysqld: Forcing close of thread 15677304 user: 'root' ". My server has 8GB RAM and it was running very well before I tried to load the 9GB .csv file to database (it took 6 hours to load a 3GB .csv file before which was not bad). – jack Aug 18 '10 at 14:34
feedback

Your Answer

 
or
required, but never shown
discard

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