Delete by primary key takes too long What would make a query that deletes a row using the primary key of the table .. take too long ?

This is a table with about 1.4M rows that gets .. 70%/30% read/write .

I don't know where to look.

Thanks.

EDIT


Engine: InnoDB
Columns: 30 - 40
Indexes: 


SHOW INDEXES FROM produced this:

course_id           1   course_id   A   4516
complete_flag   1   complete_flag   A   16
pass_flag           1   pass_flag   A   16
reported_flag   1   reported_flag   A   16
student_id_2    1   student_id  A       50436
student_id_2    2   course_id   A       907849
student_id_2    3   complete_flag   A   907849
student_id_2    4   pass_flag   A       907849
reported_status 1   reported_status A   16
reported_status 2   reported_reason A   16
course_id_index 1   course_id   A       391
reported_flag   1   reported_flag   A   16


No Triggers

No Cascading

UPDATE

OK, so eventually things started getting worse and worse and after tuning some other queries  decided to restart the mysql server. 

After restart the system needed to do a FSCK and after that it currently seems stable.

Thanks for the links in the answer, those will surely be very helpful in continuing to tune-up the database.

PS: the delete statement was a simple 

delete from x_table where id = 1
Looking at your current information, I see you posted the following info from SHOW INDEXES FROM:


Key_name
Seq_in_index
Column_name
Collation
Cardinality


The indexes you have are


course_id
complete_flag
pass_flag
reported_flag
student_id_2
reported_status
course_id_index


Give the read/ratio of 70%/30%, running a DELETE against a Row in this InnoDB table is not as simple as one would think. Why?

InnoDB's Index structure and Locking. All secondary indexes in an InnoDB table have a key back to the Clustered Index (a.k.a. gen_clust_index). Therefore, index contention (intermittent deadlocking) would always be a problem. I have written past posts about this drawback


MySQL deadlock - cannot restart normally?
Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?


If you are always having this issue with DELETE on a single row, you may want to think about applying either a tombstone table or adding a deleted flag to your table. I wrote a post about this as well: Tombstone Table vs Deleted Flag in database syncronization &amp; soft-delete scenarios