Yii 2.0 performance optimization on Schema cache
1. When the environment of the application running is the development environment, that is, when the value of yii_env is dev, close the schema cache and edit environments/dev/common/config/main-local.php
'db' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=g-p-y-a-a-c',
'username' => 'g-p-y-a-a-c',
'password' => 'H9xQ0wnWTDEAbRk9',
'tablePrefix' => 'ac_',
'charset' => 'utf8mb4',
'enableSchemaCache' => false,
'schemaCacheDuration' => 3600,
'schemaCache' => 'redisCache',
],
2. When the application running environment is the production environment, that is, when the value of yii_env is prod, turn on the schema cache, edit environments/dev/common/config/main-local.php
'db' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=g-p-y-a-a-c',
'username' => 'g-p-y-a-a-c',
'password' => 'H9xQ0wnWTDEAbRk9',
'tablePrefix' => 'ac_',
'charset' => 'utf8mb4',
'enableSchemaCache' => true,
'schemaCacheDuration' => 3600,
'schemaCache' => 'redisCache',
],
3. After enabling the Schema cache, check Redis, the key name: SA:Cache:84f9ac2fc2a79a80b91b6ddd83286798, already exists schema Cache data, as shown in Figure 1
Figure 1
4. In order to avoid the change of the data table structure after the new database migration is executed. Simulate the implementation of database migration to manually update the table structure and add fields: is_deleted, as shown in Figure 2
Figure 2
5. Request the interface again (related to the user table), check Redis, key name: SA:Cache:84f9ac2fc2a79a80b91b6ddd83286798, found a newly added field: is_deleted, not yet updated to the cache
6. Due to the change of the data table structure, the Schema cache data is inconsistent with the Schema data. Therefore, it is necessary to clear the database table structure cache of the given connection component. Reference URL:https://www.yiiframework.com/doc/guide/2.0/zh-cn/caching-data, execute the console command, but see Redis, key name: SA:Cache:84f9ac2fc2a79a80b91b6ddd83286798, found a newly added field: is_deleted, still not updated to the cache, as shown in Figure 3
Figure 3
PS E:\wwwroot\gitlab-php-yii2-app-advanced-cmc> ./yii cache/flush-schema db
Flush cache schema for "db" connection? (yes|no) [no]:yes
Schema cache for component "db", was flushed.
7. Request the interface again (related to the user table), check Redis, key name: SA:Cache:84f9ac2fc2a79a80b91b6ddd83286798, found a newly added field: is_deleted, has been updated to the cache, in line with expectations. From this, it is concluded that after clearing the database table structure cache of a given connection component, the corresponding cache is not updated in real time, but when the corresponding table structure is used, the update is triggered again (only the corresponding tables involved are updated, and the tables that are not involved will not be updated). as shown in Figure 4
Figure 4
8. Re-analyze the principle of triggering update, it should be the key name: SA: CACHE: 28C0CAA7A3C3C0BFA2E9D2 1956903C9D, after clearing the database table structure cache of a given connection component, its value has changed, and the values before and after are compared, as shown in Figure 5
Figure 5
a:2:{i:0;s:21:"0.25243100 1577424191";i:1;N;}
a:2:{i:0;s:21:"0.84367400 1577424375";i:1;N;}
9. When deploying Docker, when the container is upgraded, after the database migration command is executed, the command to clear the database table structure cache of the given connection component is executed. Edit build/c_files/config/init/console_init.sh, as shown in Figure 6
Figure 6
if [[ $PCS_API_CFG_MIGRATE == "true" ]]
then
php /mcloud/www/pcs-api/yii migrate --migrationPath=@yii/log/migrations/ --interactive=0
php /mcloud/www/pcs-api/yii migrate --interactive=0
php /mcloud/www/pcs-api/yii cache/flush-schema db --interactive=0
elif [[ $PCS_API_CFG_MIGRATE == "false" ]]
then
echo "running without db migrate"
else
echo "please set environment variable PCS_API_CFG_MIGRATE true or false"
fi
10. When upgrading a container based on Rancher, the application running environment is a production environment, that is, when the value of yii_env is prod, check the upgrade log, schema cache for component “db”, was flushed., as shown in Figure 7
Figure 7
2019/12/27 下午3:24:28PCS_API_CFG_ENV=prod
2019/12/27 下午3:24:28Yii Application Initialization Tool v1.0
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28 Start initialization ...
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28 generate console/config/main-local.php
2019/12/27 下午3:24:28 generate console/config/params-local.php
2019/12/27 下午3:24:28 generate common/config/main-local.php
2019/12/27 下午3:24:28 generate common/config/params-local.php
2019/12/27 下午3:24:28 generate yii
2019/12/27 下午3:24:28 generate api/web/index.php
2019/12/27 下午3:24:28 generate api/web/robots.txt
2019/12/27 下午3:24:28 generate api/config/main-local.php
2019/12/27 下午3:24:28 generate api/config/params-local.php
2019/12/27 下午3:24:28 generate frontend/web/index.php
2019/12/27 下午3:24:28 generate frontend/web/robots.txt
2019/12/27 下午3:24:28 generate frontend/config/main-local.php
2019/12/27 下午3:24:28 generate frontend/config/params-local.php
2019/12/27 下午3:24:28 generate rpc/web/index.php
2019/12/27 下午3:24:28 generate rpc/web/robots.txt
2019/12/27 下午3:24:28 generate rpc/config/main-local.php
2019/12/27 下午3:24:28 generate rpc/config/params-local.php
2019/12/27 下午3:24:28 generate backend/web/index.php
2019/12/27 下午3:24:28 generate backend/web/robots.txt
2019/12/27 下午3:24:28 generate backend/config/main-local.php
2019/12/27 下午3:24:28 generate backend/config/params-local.php
2019/12/27 下午3:24:28 generate cookie validation key in backend/config/main-local.php
2019/12/27 下午3:24:28 generate cookie validation key in frontend/config/main-local.php
2019/12/27 下午3:24:28 generate cookie validation key in api/config/main-local.php
2019/12/27 下午3:24:28 chmod 0777 backend/runtime
2019/12/27 下午3:24:28 chmod 0777 backend/web/assets
2019/12/27 下午3:24:28 chmod 0777 frontend/runtime
2019/12/27 下午3:24:28 chmod 0777 frontend/web/assets
2019/12/27 下午3:24:28 chmod 0777 api/runtime
2019/12/27 下午3:24:28 chmod 0777 api/web/assets
2019/12/27 下午3:24:28 chmod 0755 yii
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28 ... initialization completed.
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28PCS_API_CFG_MIGRATE=true
2019/12/27 下午3:24:28Yii Migration Tool (based on Yii v2.0.15.1)
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28No new migrations found. Your system is up-to-date.
2019/12/27 下午3:24:28Yii Migration Tool (based on Yii v2.0.15.1)
2019/12/27 下午3:24:28
2019/12/27 下午3:24:28No new migrations found. Your system is up-to-date.
2019/12/27 下午3:24:29Schema cache for component "db", was flushed.
11. The same interface (query), before the schema cache is turned on, its performance test results, concurrent (peak/up limit): 120/150, as shown in Figure 8
8
12. For the same interface (query), before the schema cache is turned on, its performance test results, when the number of concurrencies is 105, the request is always successful. Ascending to 120, it starts to fail. Therefore, it can be considered that its actual concurrent peak is 105, as shown in Figure 9
Figure 9
13. For the same interface (query), after the schema cache is turned on, the performance test results, when the number of concurrent is 90, the request is always successful. Ascending to 105, it starts to fail. Therefore, it can be considered that its actual concurrency peak is 90, and the performance optimization effect is not very obvious, and even some backwards.
Figure 10
