webandco / neos-asset-usage-cache
Improve asset usage query performance
Installs: 1 960
Dependents: 0
Suggesters: 1
Security: 0
Stars: 0
Watchers: 3
Forks: 1
Open Issues: 0
Type:neos-package
Requires
- neos/neos: ^4.3 || ^5.0 || ^7.0 || ^8.0 || dev-master
README
The package provides a strategy to cache the asset usage results. Additionally, the database query to fetch the related nodes is simplified and can be cached by the database server too.
Installation
Install the package with composer. It is recommended to use the package only in development environments.
composer require webandco/neos-asset-usage-cache
Features
This package provides various features to improve performance of querying the asset usage.
The alternative package https://github.com/punktDe/elastic-assetusageinnodes also solves the performance problem using an elasticsearch index. The main differences to the elasticsearch package are:
- This package,
webandco/neos-asset-usage-cache
, only needs the database you use for NEOS. There is no need for elasticsearch. - We assume the cache to be more short-lived, whereas the elasticsearch package is basically an index of the nodes and updated accordingly.
- We also use a different method to inject our own
AssetUsageStrategy
. Via the Settings.yaml you can then disable/enable Strategies as you like. Thus you are more maneuverable and create add/extend your ownAssetUsageStrategy
- which is currently more complicated by default in NEOS. - You can disable the cache completely in
Settings.yaml
if you want and just enable it on purpose, e.g. in a long running command controller of yours. - You can also disable the cache itself and just let the database cache the results. This basically means that the query to determine the asset usage is modified. This approach can be very fast and provides correct results all the time. The drawback is that you need to reconfigure your database to increase query caches which can cause decreased query performance because of internal DB locks. More details and links down below.
Configurable asset usage strategy
Currently the asset usage is generated by iterating over all classes
implementing the Neos\Media\Domain\Strategy\AssetUsageStrategyInterface
and sum up or merge the results from the implementations.
In a default setup, it is not possible to enable/disable strategies, making it
more complicated to replace the current default strategy Neos\Neos\Domain\Strategy\AssetUsageInNodePropertiesStrategy
This package uses an aspect for Neos\Media\Domain\Service\AssetService->getUsageStrategies()
and the configuration to disable or enable strategies. Thus one can easier add or remove
an AssetUsageStrategy.
Simplified database query
The default asset usage query in AssetUsageInNodePropertiesStrategy
adds
multiple LIKE conditions for the properties column to query the nodeData table.
The more an asset is in use, the more image variants exist, the more LIKE conditions are added and this slows the query down.
In this package we implemented an alternative approach, by just querying all
rows which contain __identifier
or asset:
and then filter the results
using PHP's preg_match_all. So the workload is moved away from the database
and into PHP.
To make use of database query caching you likely need to increase query cache sizes, e.g.
SET GLOBAL query_cache_size = 500*1024*1024;
SET GLOBAL query_cache_limit = 200*1024*1024;
Keep in mind, that by increasing the database cache sizes, the database can actually be slowed down, see https://dev.mysql.com/doc/refman/5.6/en/query-cache.html:
Be cautious about sizing the query cache excessively large,
which increases the overhead required to maintain the cache,
possibly beyond the benefit of enabling it.
Sizes in tens of megabytes are usually beneficial.
Sizes in the hundreds of megabytes might not be.
For futher details, see: https://haydenjames.io/mysql-query-cache-size-performance/
If you increase database query cache sizes, the query used in this package is cached, and there is no need for php caching.
Cached asset usage
The database result is managed via a cache, which is updated on signals for node changes and by default flushed every hour.
Performance
The database query used in this package, takes around 4 sec for for around 300.000 nodes in the nodeData table. The more nodes the slower this query becomes.
This database query is independent of any given asset or usage count and performs the same for every asset.
If the cache is populated, the methods provided by AssetUsageCacheStrategy
take around 6ms per asset nearly independent of the usage count.
Configuration
The default configuration, disables the AssetUsageInNodePropertiesStrategy
and enables the provided AssetUsageCacheStrategy
.
Additionally the queryCache is enabled and realTimeUpdate
is enabled.
Webandco:
AssetUsageCache:
assetUsageStrategies:
AssetUsageInNodePropertiesStrategy:
className: 'Neos\Neos\Domain\Strategy\AssetUsageInNodePropertiesStrategy'
disable: true
AssetUsageCacheStrategy:
className: 'Webandco\AssetUsageCache\Domain\Strategy\AssetUsageCacheStrategy'
disable: false
queryCache:
disable: false
realTimeUpdate: true
Use cases
Database query cache only
To make use of the database query cache but not using the PHP cache, just disable the cache in the configuration
Webandco:
AssetUsageCache:
queryCache:
disable: true
Enable cache on purpose
In case the cache should be not used in the media browser, but during a custom command line action, the cache needs to be disabled via configuration, see above.
In the command line action you can then enable the cache
/**
* @Flow\Inject
* @var AssetCacheService
*/
protected $assetCacheService;
....
public function reConfigureAssetCache(){
$this->assetCacheService->setRealTimeUpdate(false);
$this->assetCacheService->setCacheDisabled(false);
// eventually just flush the cache - globally
$this->assetCacheService->flush();
}
Thus the cache is used during the run of the current command line action which can be handy if you need to iterate over all assets and check their usage count.