上周我们的数据库瞬间出现了很多locked进程,很快,所有服务器页面都无法打开。我们有两个大的项目部署同时在这几台webserver上,分别访问两台mysql server。

通过查看慢查询log发现,有一条管理后台sql语句执行时间为近800秒,其后有多条locked time占总时间90%以上的慢查询。但是,这条管理后台sql只是select语句,怎么会使其他select语句locked呢?一般思考读锁是相容的啊。经实验发现,该sql句子涉及到多张表,且逻辑比较复杂,需要对一张MyISAM类型的product表加读锁,而该表经常会有写操作,这些写操作尝试加写锁时blocked,但是会wait写锁(之前书上看到过,没有查看mysql资料,大概原理应该相同),这就同时会使后面的读操作加读锁失败。

除了需要对这些sql句子进行优化之外,因为之前进行过一次长时间的mysqldump操作,所以show status like ‘%qcache%’发现该数据的query_cache_type为DEMAND,即只有在显示写成select SQL_CACHE时才会使用到查询缓存。在这种配置下,不存在缓存竞争的问题。

为了解决这种读写锁竞争的问题,做了读写分离。之前两台数据库已经是master-slave备份形式了,但是slave并没有承担线上压力,现在从代码层面上处理。

之前的代码描述如下:

class Core_Mysql{
   function querys{...}
   function query{...}
   function update{...}
}
class Core_Model{
   protected $db;
   function __construct{
       $db = new Core_Mysql();
   }  

   function func1_read{ $sql = '...'; $this->db->querys($sql);}
   function func2_write{ $sql = '...'; $this->db->update($sql);}
}

Core_Model还有多个子类继承并override了__construct方法,但是都没有改变db变量的初始化方式。为了最小限度的重构,代码如下:
class Core_Mysql{
   function querys{...}
   function query{...}
   function update{...}
}
class Core_ModelParent{
   private $db_r;
   private $db_rw;

   function __get($name){ return ('db' == $name)? $this->db_rw: (('db_r' == $name)? $this->db_r: null); }
   function lazy_get_r(){...}
   function lazy_get_rw(){...}
   function set_db_info(){...}
}
class Core_Model{
   protected $db;
   function __construct{
       $db = new Core_Mysql();
   }  

   function func1_read{ $sql = '...'; $this->db_r->querys($sql);}
   function func2_write{ $sql = '...'; $this->db->update($sql);}
}

这样,只需要修改model层里希望访问只读库的方法了。当然,以上只是一个大概的代码,在实际中,还考虑了如果主库挂了,则所有访问转移到从库,但只读(在Core_mysql的update方法里控制即可);或者如果从库挂了,则所有访问指向主库。在故障前后,不需要修改代码。

目前仅将前几天导致故障的那个管理后台sql句子指向了从库,待运行几天稳定后,再将更多的流量导向从库。

也需要考虑到,主从复制是有时延的,所以时间敏感的读操作,还需要保留在主库上。

———————————

这里还有一个问题,由于lazy连接mysql数据库,而在构造sql句子时,我会调用mysql_real_escape_string来封装字符串,在手册里写道:

Note:

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn’t defined, the last MySQL connection is used.

这就导致会引发如下错误:

PHP Warning:  mysql_real_escape_string() [<a href=’function.mysql-real-escape-string’>function.mysql-real-escape-string</a>]: A link to the server could not be established
PHP Warning:  mysql_real_escape_string() [<a href=’function.mysql-real-escape-string’>function.mysql-real-escape-string</a>]: Access denied for user ‘www-user-name’@’localhost’ (using password: NO)

即,在没有mysql连接时,调用mysql_real_escape_string会触发mysql_connect使用当前用户(webserver运行者)连接本地的数据库。为了解决该问题,可以在Core_model的构造器中显示连接读写库。或者压根不使用该方法,自己写一个替代的方法。

Leave a Reply