最近服务器上经常出现mysql进程占CPU100%的情况,使用命令
show processlist
后,看到出现了很多状态为LOCKED的sql。使用
show status like 'table%'
检查Table_locks_immediate和Table_locks_waited,发现Table_locks_waited偏 大。出问题的表是MyISAM,分析大概是MyISAM的锁表导致。
MyISAM适合于读频率远大于写频率这一情况。而我目前的应用可能会出现在某一时段读写频率相当。大致如下:

一个客户端发出需要长时间运行的SELECT
其他客户端在同一个表上发出INSERT或者UPDATE,这个客户将等待SELECT完成
另一个客户在同一个表上发出另一个SELECT;因UPDATE或INSERT比SELECT有更高有优先级,该SELECT将等待UPDATE或INSERT完成,也将等待第一个SELECT完成

也就是说对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;MyISAM表的读操作与写操作之间,以及写操作之间是串行的!
解决方案大概有如下几种:

MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
  • 0 不允许并发操作
  • 1 如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
  • 2 无论MyISAM表中有没有空洞,都允许在表尾并发插入记录
使用–low-priority-updates启用mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下,在先前情形的最后的SELECT语句将在INSERT语句前执行。
为max_write_lock_count设置一个低值,使得在一定数量的WRITE锁定后,给出READ锁定
使用LOW_PRIORITY属性给于一个特定的INSERT,UPDATE或DELETE较低的优先级
使用HIGH_PRIORITY属性给于一个特定的SELECT
使用INSERT DELAYED语句

附加(一):
mysql的myisam解决并发读写解决方法MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发,同InnoDB相比,MyISAM的效率就会直线下降,而且,MyISAM和InnoDB的数据存储方式也有显著不同:通常,在MyISAM里,新数据会被附加到数据文件的结尾,可如果时常做一些 UPDATE,DELETE操作之后,数据文件就不再是连续的,形象一点来说,就是数据文件里出现了很多洞洞,此时再插入新数据时,按缺省设置会先看这些洞洞的大小是否可以容纳下新数据,如果可以,则直接把新数据保存到洞洞里,反之,则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大小,降低文件碎片的产生。但InnoDB里则不是这样,在InnoDB里,由于主键是cluster的,所以,数据文件始终是按照主键排序的,如果使用自增ID做主键,则新数据始终是位于数据文件的结尾。

了解了这些基础知识,下面说说MyISAM几个容易忽视的配置选项:
concurrent_insert:
通常来说,在MyISAM里读写操作是串行的,但当对同一个表进行查询和插入操作时,为了降低锁竞争的频率,根据concurrent_insert的设置,MyISAM是可以并行处理查询和插入的:
  • 当concurrent_insert=0时,不允许并发插入功能。
  • 当concurrent_insert=1时,允许对没有洞洞的表使用并发插入,新数据位于数据文件结尾(缺省)。
  • 当concurrent_insert=2时,不管表有没有洞洞,都允许在数据文件结尾并发插入。

这样看来,把concurrent_insert设置为2是很划算的,至于由此产生的文件碎片,可以定期使用OPTIMIZE TABLE语法优化。
max_write_lock_count:
缺省情况下,写操作的优先级要高于读操作的优先级,即便是先发送的读请求,后发送的写请求,此时也会优先处理写请求,然后再处理读请求。这就造成一个问题:一旦我发出若干个写请求,就会堵塞所有的读请求,直到写请求全都处理完,才有机会处理读请求。此时可以考虑使用>有了这样的设置,当系统处理一个写操作后,就会暂停写操作,给读操作执行的机会。
low-priority-updates:
我们还可以更干脆点,直接降低写操作的优先级,给读操作更高的优先级。
low-priority-updates=1
综合来看,concurrent_insert=2是绝对推荐的,至于max_write_lock_count=1和low-priority- updates=1,则视情况而定,如果可以降低写操作的优先级,则使用low-priority-updates=1,否则使用 max_write_lock_count=1。


附加(二):查看和修改concurrent_insert
show variables like 'concurrent_insert';

set global concurrent_insert=2;
show variables like 'concurrent_insert';

concurrent_insert:值 
说明
NEVER(或者0) 不允许并发插入功能
AUTO(或者1) (默认值)允许对没有洞洞的表使用并发插入,新数据位于数据文件结尾
ALWAYS(或者2) 不管表有没有洞洞,都允许在数据文件结尾并发插入

提示:MySQL中使用set设置的参数在MySQL服务重启后需要重新设定。如果需要永久生效配置MySQL配置文件my.ini/my.cnf