虽然黑色星期五有惊无险的过去了, 但是 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
所有获取数据的方法如下:
1 2 3 4 5 6 7 8 9 10 11 | 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() 为例, 分析该方案的可行性以及实现方法。
1 2 3 4 5 6 7 8 9 10 | /** * 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:
1 2 3 4 5 6 7 8 9 10 | /** * 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:
1 2 3 4 5 6 7 8 9 | /** * 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:
1 2 3 4 5 6 7 8 | /** * 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 的购物车:
1 | ab -C PHPSESSID=acmsj8q8ld1tvdo77lm5t0dr9b -n 40 -c 5 http://localhost/checkout/cart/ |
没有缓存的时候:
test-No-Cache-1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 | 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; } } |