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 !!!
What is the lock level for a MyISAM table ? Table Level Locking
This is by design : (See http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html)
I wrote about before : Benefits of table level locking
You may want to take a shot at INSERT DELAYED. Note what page 410 bulletpoint 5 of "MySQL 5.0 Certification Study Guide" says on INSERT DELAYED:
DELAYED may be used with INSERT (and REPLACE). The server buffers the rows in memory and inserts them when the table is not being used. Delayed inserts increase efficiency because they are done in batches rather than individually. While inserting the rows, the server checks periodically to see whether other requests to use the table have arrived. If so, the server suspends insertion of delayed rows until the table becomes free again. Using DELAYED allows the client to proceed immediately after issuing the INSERT statementrather than waiting until it completes.
There are risks to using INSERT DELAYED. Page 411 says
If you use DELAYED, keep the following points in mind:
- Delayed rows tends to held for a longer time on a very busy server than on a lightly loaded one.
- If a crash occurs while the server is buffering delayed rows in memory, those rows are lost.
Looking over the whole section of the book, HIGH_PRIORITY is only applied to SELECT queries.
For more information, "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.
CAVEAT : When it comes to MySQL Replication, INSERT DELAYED executed on a Master is treated as INSERT on a Slave. If a master crashes, missing rows are logged in the binary logs of the Master. It would be very possible that the missing rows on the Master replicated successfully to the Slave.
UPDATE 2012-08-20 11:18 EDT
Please note what page 410 bulletpoint 3 of "MySQL 5.0 Certification Study Guide" says on LOW_PRIORITY:
The LOW_PRIORITY modifier may be applied to statements that update tables (INSERT, DELETE, REPLACE, or UPDATE). A low-priority write request waits not only until all current readers have finished, but for any pending read requests that arrive while the write request itself is waiting. That is, it waits until there are no pending read requests at all. It is therefore possible for a low-priority write request never to be performed, if read requests keep arriving while the write request is waiting.
Given this description, low-priority UPDATEs definitely take a backseat to INSERTs that have normal priority. Notwithstanding, when a SELECT occurs on a MyISAM table, all write requests take a backseat regardless of priority. If the read traffic is high enough, the precedence of INSERT over UPDATE LOW_PRIOIRTY would be negligible, nominal at best.
UPDATE 2012-08-20 11:36 EDT
If the read traffic is hindering your INSERT over UPDATE LOW_PRIOIRTY situation, I would have to recommend switching your MyISAM table to InnoDB. In fact, InnoDB can be tuned for performing heavy INSERTs. Please see my posts about tuning InnoDB for this purpose:
Best Answer
FWIW, I think this is the wrong way to do this; unless you need to do real-time reporting on webserver access write the log as a file and bulk-load it.
Having said that, there is a way to do what you want, which if I understand it correctly is to queue up pending inserts while doing maintenance on your database. In other words, decoupling the webserver's
INSERToperation from the actualINSERTin the database in some sort of buffer. The way to do it using two instances, one that is directly written to in which you will use the blackhole storage engine, and one that you will replicate to, which will be your "real" database. The pending inserts will live in the binlogs; you simply stop replicating to the slave, perform your maintenance, then resume replication again and the slave/real database catches up. The slave is available forSELECTs for your reporting. You can even multi-master for load balancing, or have multiple slaves and switch between them during maintenance activities.