Getting avoiding locked SELECT:s in MySQL when using MyISAM

Question

MyISAM uses table level locking which means that SELECT:s are blocked while INSERT/UPDATE:s are running.

To mitigate the problem of blocked SELECT:s I've been recommended to configure MySQL with these parameters:

• low_priority_updates=1
• concurrent_insert=2

What are the drawbacks of using low_priority_updates=1 and concurrent_insert=2?

Answer 1

Here's a great post from the MySQL Performance Blog which covers some of this

Lock priorities. By default MySQL treats updates as higher priority operations. You can use SELECT HIGH_PRIORITY or UPDATE LOW_PRIORITY to adjust that or you can simply set low_priority_updates option. Anyway default behavior means any UPDATE statement which is blocked by long running select will also block further selects from this table – they will have to wait until UPDATE is executing which is waiting on SELECT to complete. This is often not accounted for and people think – “OK. I write my script so it does short updates so it will not block anything” – it still may cause total block if there are long selects running.

Another post benchmarks concurrent_inserts and highlights possible downsides, though the post is 3 years old now.