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.

It was my understanding that MyISAM uses table locking during inserts and updates. To me that meant that read and write by other queries on that table is disallowed until the first insertion or update is finished.

However, I am reading the book High Performance MySQL and it clearly states on page 64 that MyISAM allows "Table with concurrent inserts".

Does this mean that MyISAM allows concurrent writes but locks all reads during the writes? Kindly explain.

share|improve this question
    
Rolando's answer is excellent (and I have upvoted it, as should you), but I'd like to add that the use case for MyISAM tables is almost non-existent these days. Consider switching to InnoDB. –  Aaron Brown Apr 15 '12 at 12:50
add comment

1 Answer

In a ready-heavy environment, a MyISAM table behaves a like a prioritized queue.

  • SELECTs will make all DDL, INSERTs, UPDATEs, & DELETEs, wait until all SELECTs are done
  • A single write operation makes all SELECT wait. The exception to this rule is Concurrent INSERT. The environment for Concurrent INSERTs:
    • Only INSERTs and SELECTs
    • MyISAM tables must not contain any gaps

In other words, SELECTs are not blocked on a MyISAM table as long as newly INSERTs rows are entering a table with no gaps. IF any row being INSERTed has to fill any gaps, then the conditions for Concurrent INSERTs are no longer applicable. SELECTs go back to being handled the way they are normally handled.

If you are performing bulk loading of a MyISAM table, you will need to rev up certain things. For instance, you will need to add this option and restart mysql:

[mysqld]
bulk-insert-buffer-size=256M

Next, change the way you delete the data. Instead of running the DELETE query, try copying the data to be retained into a temp table and then rename. For example, if you have to delete rows from table mydb.mytb whose id <= 500000, run these steps (it should be faster):

use mydb
create table mybt like mytb;
alter table mybt disable keys;
insert into mybt select * from mytb where id > 500000;
alter table mybt enable keys;
drop table mytb;
alter table mybt rename mytb;

Instead of lots of INSERTs into your table, try putting all your new data into a CSV file and use LOAD DATA INFILE to mass populate your production table. You should disable keys before loading and enable keys after the new data is loaded.

There is no need to optimize table doing these things. You may want to run analyze table instead. That will update the index statistics on the MyISAM table.

I hope these suggestions help !!!

share|improve this answer
    
I will need to delete 1/2 million rows from a table and insert 1/2 new rows every night. I will do this in batches. Should I run "optimize table after each batch delete and before each batch update so concurrent inserts can take place and the jobs finish quickly? Sorry I am not a DBA, I am trying to learn. –  ryy Nov 25 '11 at 23:56
    
Thank you for teaching me. I WILL accept your answer. But I would like to ask few more question. 1.Would you have recommended something else if it were an innodb table? 2.I've been told that LOAD DATA INFILE can skip rows and not give any error message. Is there a remedy for that? Thanks again. –  ryy Nov 26 '11 at 4:24
    
3. I just also read that enabling keys take such a long time that it eats up the time saved during insertions, is that true? –  ryy Nov 26 '11 at 4:34
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.