给 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

所有获取数据的方法如下:

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;
}
}