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.
In a ready-heavy environment, a MyISAM table behaves a like a prioritized queue.
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 !!!
