Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table.

We're talking about 10-20 inserts per second and quite rare but yet some selects on the log that could take around 10s. I don't want my 100-200 inserts wait for these selects.

My question is quite simple actually. Taken aside the hole not-always-being-filled concern, when and how would it do any harm ?

Nota Bene: I wouldn't use DELAYED INSERT as they are of the INSERT ... ON DUPLICATE KEY UPDATE form.

share|improve this question
add comment

1 Answer

The only immediate harm I can think of is dealing with contention from the UPDATE.

Normal INSERT, UPDATE, and DELETE commands execute a full table lock. Doing ON DUPLICATE KEY UPDATE should work the same.

In addition, what if the row's size changes to the point that the row needs to be bigger that the row's original allocation because of increasing the length of a VARCHAR column? That would require writing the row to a new part of the MyISAM table.

The only exception to this concern is when the MyISAM table

  • has no TEXT or VARCHAR fields
  • has no TEXT fields, has VARCHAR fields but ROW_FORMAT=Fixed

The MyISAM table would probably have some contention issues if you have TEXT fields because of the lack of length control or possible overflow of data to handle no matter what the table's ROW_FORMAT is set to.

share|improve this answer
    
So you're telling me that, at worst, I'd still have contention on UPDATEs, when the UPDATE would enlarge the row. Sounds better than dealing with contention for each and every INSERT and UPDATE, right? –  Tom Desp Nov 18 '13 at 16:34
    
You are correct, provided UPDATE and ON DUPLICATE KEY UPDATE commands are rare (or at least infrequent). There may still be a full table lock in those two instances. –  RolandoMySQLDBA Nov 18 '13 at 17:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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