In show processlist;
, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables.
We tried INSERT HIGH_PRIORITY
and UPDATE LOW_PRIORITY
. However, this did not change the order. Whenever there is an UPDATE
in the queue, INSERTs wait for them to finish.
Is this by design? I could not find any write-ups detailing how this ordering works, any references we can read on this?
UPDATE:
I'm posting how a typical show processlist;
looks. It might clarify the question.
SELECTs are working just fine. LOW_PRIORITY on the UPDATE pushes it to the back of the list. But INSERTs are waiting as well. This is database has concurrent_insert
set to ALWAYS
. Therefore I expect SELECTS and INSERTS to work at the same time and UPDATE LOW_PRIORITYs to take affect only when there are no more INSERTs or SELECTs waiting around. When there are no UPDATEs, concurrent_insert kicks in just fine.
Best Answer
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 onINSERT DELAYED
:There are risks to using
INSERT DELAYED
. Page 411 saysLooking 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 asINSERT
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
:Given this description, low-priority
UPDATE
s definitely take a backseat toINSERT
s that have normal priority. Notwithstanding, when aSELECT
occurs on a MyISAM table, all write requests take a backseat regardless of priority. If the read traffic is high enough, the precedence ofINSERT
overUPDATE LOW_PRIOIRTY
would be negligible, nominal at best.UPDATE 2012-08-20 11:36 EDT
If the read traffic is hindering your
INSERT
overUPDATE 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:Feb 12, 2011
: How do you tune MySQL for a heavy InnoDB workload?Jan 18, 2012
: Insert-heavy InnoDB table won't use all my CPUJul 04, 2012
: Capabilities of InnoDB INSERT PerformanceAug 17, 2012
: When to switch from MyISAM to InnoDB?