给 Magento 2 添加缓存层的分析与尝试
虽然黑色星期五有惊无险的过去了, 但是 Magento 2 社区版无法读写分离这个限制, 始终是悬在整个网站上的一把利剑。
我之前尝试过给 Magento 2 写一个 MySQL 读写分离的插件, 在深入研究了 Magento 2 的数据库访问层后, 发现通过一个简单的插件, 想做到读写分离基本上是不可能的。Magento 2 社区版读写数据库的逻辑里, 混杂着大量的 Magento 1的代码和逻辑, 无法在修改少量代码的前提下做到读写分离, 后来忙着做网站上的各种需求， 于是读写分离就搁置了。
这次黑五, 整个项目的性能瓶颈就是 MySQL, 流量上来之后, 应用服务器负载基本保持不变, 而数据库服务器负载却翻了3倍多, 而且是在数据库服务器提前升级了硬件配置的基础上。所以我觉得 Magento 2 的数据库层必须要优化一下, 既然没法做读写分离, 那能不能加个缓存层呢？将绝大多数读取操作转移到缓存层去, 理论上数据库的负载会相应下降。
要想改的代码最少, 就得找对地方。 Magento 2 的数据库 Adapter 是 Magento\Framework\DB\Adapter\Pdo\Mysql 类, 该类继承自 Zend_Db_Adapter_Abstract
所有获取数据的方法如下:
Zend_Db_Adapter_Abstract::fetchAll($sql, $bind = array(), $fetchMode = null) Zend_Db_Adapter_Abstract::fetchAssoc($sql, $bind = array()) Zend_Db_Adapter_Abstract::fetchCol($sql, $bind = array()) Zend_Db_Adapter_Abstract::fetchPairs($sql, $bind = array()) Zend_Db_Adapter_Abstract::fetchOne($sql, $bind = array()) Zend_Db_Adapter_Abstract::fetchRow($sql, $bind = array(), $fetchMode = null)
其中, fetchAll() 和 fetchRow() 是用的最多的两个。
下面以 fetchRow() 为例, 分析该方案的可行性以及实现方法。
/** * Fetches the first row of the SQL result. * Uses the current fetchMode for the adapter. * * @param string|Zend_Db_Select $sql An SQL SELECT statement. * @param mixed $bind Data to bind into SELECT placeholders. * @param mixed $fetchMode Override current fetch mode. * @return mixed Array, object, or scalar depending on fetch mode. */ public function fetchRow($sql, $bind = array(), $fetchMode = null)
通过解析 $sql 对象和 $bind 数组， 可以得到精确的、格式化的数据， 包含
1. 数据库表名
2. 字段键值对
通过这些数据，可以构建缓存的键(key)和标签(tag)， 例如：
$cacheKey = table_name::主键键值对
或者
$cacheKey = table_name::唯一键索引键值对
$cacheTags = [
table_name,
table_name::主键键值对
table_name::唯一键索引键值对组1,
table_name::唯一键索引键值对组2,
…
]
cacheTags 的作用是给缓存分类, 方便后续清理。
有了 $cacheKey, $cacheTags 之后, 就可以将数据库查询的结果保存到缓存中去;
下次再有查询过来, 先在缓存中查找有无对应的数据, 如果有就直接返回给数据调用方了;
那么如果数据更新了呢？
数据更新分为三种: 1. UPDATE, 2. INSERT, 3 DELETE
对于 UPDATE:
/** * Updates table rows with specified data based on a WHERE clause. * * @param mixed $table The table to update. * @param array $bind Column-value pairs. * @param mixed $where UPDATE WHERE clause(s). * @return int The number of affected rows. * @throws Zend_Db_Adapter_Exception */ public function update($table, array $bind, $where = '')
update() 方法接收 3 个参数， 分别是 table_name, 待更新数据键值对, where 条件子句。
刚才我们在构建 $cacheTags 时， 分别有 table_name、table_name::主键键值对、table_name::唯一键索引键值对, table_name 是现成的， 其余两种tag 需要从 where 子句中解析。 通过解析，最坏情况是 where 子句未解析到任何键值对, 最好情况是解析到了所有 filed 键值对。最坏情况下， 需要清除 table_name 下的所有缓存数据， 而最好情况下, 只需要清除一条缓存数据。
对于 INSERT:
/** * Inserts a table row with specified data. * * @param mixed $table The table to insert data into. * @param array $bind Column-value pairs. * @return int The number of affected rows. * @throws Zend_Db_Adapter_Exception */ public function insert($table, array $bind)
insert() 方法接收 2 个参数， 分别是 table_name， 待插入数据键值对。 由于新插入的数据根本不存在与缓存中， 所以不需要对缓存进行操作
对于 DELETE:
/** * Deletes table rows based on a WHERE clause. * * @param mixed $table The table to update. * @param mixed $where DELETE WHERE clause(s). * @return int The number of affected rows. */ public function delete($table, $where = '')
delete() 方法接收 2 个参数, table_name 和 where 子句， 假如能从 where 子句中解析到主键键值对 或 唯一键索引键值对, 就只需要清除一条缓存记录， 否则需要清除该 table_name 下的所有缓存记录。
优化效果:
我暂时只是用 ab 测试了 Magento 2 的购物车:
ab -C PHPSESSID=acmsj8q8ld1tvdo77lm5t0dr9b -n 40 -c 5 http://localhost/checkout/cart/
没有缓存的时候：
test-No-Cache-1:
Requests per second: 1.79 [#/sec] (mean) Time per request: 2786.478 [ms] (mean) Time per request: 557.296 [ms] (mean, across all concurrent requests) Percentage of the requests served within a certain time (ms) 50% 756 66% 2064 75% 5635 80% 6150 90% 7632 95% 8530 98% 8563 99% 8563 100% 8563 (longest request) MySQL 进程的 CPU 占用率保持在 20% ~ 24%
test-No-Cache-2:
Requests per second: 1.84 [#/sec] (mean) Time per request: 2720.852 [ms] (mean) Time per request: 544.170 [ms] (mean, across all concurrent requests) Percentage of the requests served within a certain time (ms) 50% 586 66% 1523 75% 4036 80% 5667 90% 10228 95% 11621 98% 12098 99% 12098 100% 12098 (longest request) MySQL 进程的 CPU 占用率保持在 20% ~ 24%
有缓存的时候：
test-With-Cache-1:
Requests per second: 1.99 [#/sec] (mean) Time per request: 2509.273 [ms] (mean) Time per request: 501.854 [ms] (mean, across all concurrent requests) Percentage of the requests served within a certain time (ms) 50% 489 66% 511 75% 574 80% 637 90% 19073 95% 19553 98% 20063 99% 20063 100% 20063 (longest request) MySQL 进程的 CPU 占用率保持在 5% 左右
test-With-Cache-2:
Requests per second: 2.10 [#/sec] (mean) Time per request: 2384.145 [ms] (mean) Time per request: 476.829 [ms] (mean, across all concurrent requests) Percentage of the requests served within a certain time (ms) 50% 465 66% 472 75% 565 80% 620 90% 9509 95% 18374 98% 18588 99% 18588 100% 18588 (longest request) MySQL 进程的 CPU 占用率保持在 5% ~ 7 %
通过上面两组数据的对比， 很明显 MySQL 的 CPU 占用率有了大幅度下降(从 20% 下降到 5%), 可见增加一个缓存层对降低 MySQL 负载是有效果的。
但是有一个小问题, 在不使用缓存的情况下, Percentage of the requests served within a certain time 这个值，在 90% 这个点之后, 表现要比有缓存的情况好, 我猜是大量 unserialize() 操作造成 CPU 资源不够导致响应缓慢。
经过修改后的 vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:
class Mysql extends \Zend_Db_Adapter_Pdo_Mysql implements AdapterInterface { protected $_cache; public function fetchAll($sql, $bind = array(), $fetchMode = null) { if ($sql instanceof \Zend_Db_Select) { /** @var array $from */ $from = $sql->getPart('from'); $tableName = current($from)['tableName']; $cacheKey = 'FETCH_ALL::' . $tableName . '::' . md5((string)$sql); $cache = $this->getCache(); $data = $cache->load($cacheKey); if ($data === false) { $data = parent::fetchAll($sql, $bind, $fetchMode); $cache->save(serialize($data), $cacheKey, ['FETCH_ALL::' . $tableName], 3600); } else { $data = @unserialize($data); } } else { $data = parent::fetchAll($sql, $bind, $fetchMode); } return $data; } public function fetchRow($sql, $bind = [], $fetchMode = null) { $cacheIdentifiers = $this->resolveSql($sql, $bind); if ($cacheIdentifiers !== false) { $cache = $this->getCache()->getFrontend(); $data = $cache->load($cacheIdentifiers['cacheKey']); if ($data === false) { $data = parent::fetchRow($sql, $bind, $fetchMode); if ($data) { $cache->save(serialize($data), $cacheIdentifiers['cacheKey'], $cacheIdentifiers['cacheTags'], 3600); } } else { $data = @unserialize($data); } } else { $data = parent::fetchRow($sql, $bind, $fetchMode); } return $data; } public function update($table, array $bind, $where = '') { parent::update($table, $bind, $where); $cacheKey = $this->resolveUpdate($table, $bind, $where); if ($cacheKey === false) { $cacheKey = $table; } $this->getCache()->clean([$cacheKey, 'FETCH_ALL::' . $table]); } /** * @return \Magento\Framework\App\CacheInterface */ private function getCache() { if ($this->_cache === null) { $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); $this->_cache = $objectManager->get(\Magento\Framework\App\CacheInterface::class); } return $this->_cache; } /** * @param string|\Zend_Db_Select $sql An SQL SELECT statement. * @param mixed $bind Data to bind into SELECT placeholders. * @return array */ protected function resolveSql($sql, $bind = array()) { $result = false; if ($sql instanceof \Zend_Db_Select) { try { /** @var array $from */ $from = $sql->getPart('from'); $tableName = current($from)['tableName']; $where = $sql->getPart('where'); foreach ($this->getIndexFields($tableName) as $indexFields) { $kv = $this->getKv($indexFields, $where, $bind); if ($kv !== false) { $cacheKey = $tableName . '::' . implode('|', $kv); $cacheTags = [ $tableName, $cacheKey ]; $result = ['cacheKey' => $cacheKey, 'cacheTags' => $cacheTags]; } } }catch (\Zend_Db_Select_Exception $e) { } } return $result; } protected function resolveUpdate($tableName, array $bind, $where = '') { $cacheKey = false; if (is_string($where)) { $where = [$where]; } foreach ($this->getIndexFields($tableName) as $indexFields) { $kv = $this->getKv($indexFields, $where, $bind); if ($kv !== false) { $cacheKey = $tableName . '::' . implode('|', $kv); } } return $cacheKey; } protected function getIndexFields($tableName) { $indexes = $this->getIndexList($tableName); $indexFields = []; foreach ($indexes as $data) { if ($data['INDEX_TYPE'] == 'primary') { $indexFields[] = $data['COLUMNS_LIST']; } elseif ($data['INDEX_TYPE'] == 'unique') { $indexFields[] = $data['COLUMNS_LIST']; } } return $indexFields; } protected function getKv($fields, $where, $bind) { $found = true; $kv = []; foreach ($fields as $field) { $_found = false; if (isset($bind[':' . $field])) { // 在 bind 数组中查找 filed value $kv[$field] = $field . '=' .$bind[':' . $field]; $_found = true; } elseif (is_array($where)) { foreach ($where as $case) { // 遍历 where 条件子句， 查找 filed value $matches = []; $preg = sprintf('#%s.*=(.*)#', $field); $_result = preg_match($preg, $case, $matches); if ($_result) { $kv[$field] = $field . '=' .trim($matches[1], ' \')'); $_found = true; } } } if (!$_found) { // 其中任一 field 没找到， $found = false; break; } } return $found ? $kv : false; } }
