Insert Statement on large MySQL table does not terminate

insertMySQLperformance

I have a MySQL database with several tables.
One table (InnoDB) is quite large (about 490 million rows).
The table consists of 4 columns:

  • id: int(11), auto_increment
  • term: varchar(255)
  • docId: int(11)
  • count: int(11)

The table has several indices that involve up to three columns (BTREE).

The table has been filled with data for months and everything worked well.
Today, all of a sudden, insert statements don't work any more. There is no error message from the DB. The insert statement just does not terminate.

I thouoght of a disk space or memory limitation. But even if I delete a number of rows (delete works) I can't insert new rows.

What could cause this problem?

Thanks in advance.

Best Answer

from my knowledge:

  1. Check if there is enough disk space available for the database to perform the insert operation - which you said thats ok!
  2. Check the transaction log to see if there is anything locking the table.
  3. As @Ergest Basha said, try changing the int type to BIGINT.
  4. is there any chance of having full the log file?

Kind regards, Bill

Related Question