Redis can be used for query cache in myDBR to speed up reports. Redis is an open source (BSD licensed), in-memory key-value data store. In myDBR, report content can be cached in Redis for better performance. Redis usage can be defined per report. The cache usage is fully transparent to the end user.
If you have complex, slow-running reports, accessed by many users, you may want to try Redis out. To make the best use of the caching consider the following report types/use cases:
The reports that you probably do not want to use the Redis for
When you configure a report to use the cache (check the 'Use cache'-checkbox in report info and set the cache to expire time in seconds), myDBR checks if the report content can be found on the cache based on the database query. If it is not there (first run/previous cache expired), the report is run and the results from the database are stored in the cache. When a subsequent run is done, the data is readily available from the cache, and no DB access is needed.
If the parameters change for the query (and likely the content of the report), it is treated by the cache as a separate report.
Use your package manager in your Linux/Unix distro. In Windows, the Redis can be installed via the WSL.
Set up Redis on a server and enable it from the Environment settings. You can then define the usage per a report from the report's Edit Report-page (enable/disable and set the expiration time in seconds). The Environment settings include the hostname, TCP port, and the optional password. Redis serializes the cached elements with the serializer chosen. The available serializers depend on the compiled version of your Redis. The serializers supported are:
igbinary- The recommended serializer. Consumes the least amount of memory and is the fastest.
json- Plain text JSON serializer
php- PHP's serialize
In Admin tools->Query cache, you can see the cached items. You can also delete the items from the cache.
Databases are designed to optimize queries quite efficiently. For indexed, direct queries, this works extremely well. However, a report can contain multiple heavy queries, where the database needs to go through a vast amount of rows. Your report may also have temporary tables to store intermediate results and some non-optimal queries. When you have several users accessing these kinds of reports, the database load increases.
When the Redis is used with myDBR, the output of all queries is stored into the Redis and is being served to all users instantaneously. Not only do users get the data faster, but also the database resources are preserved for better use.
When myDBR cannot connect to Redis, the query is run as a normal report accessing the database. By default, for normal users, no error messages are shown. If you want end-users to see the error codes you can add the following statement to
$mydbr_defaults['query_cache']['show_redis_errors_to_users'] = true;
The default myDBR statistics report shows the cache being used. Alternatively, a Redis logo can be shown on the report header when the report is run as admin. This can be enabled if you add the following statement to
$mydbr_defaults['query_cache']['debug'] = true;
myDBR shows a red circle in the admin view for those reports that are being cached.