Redis can be used for query caching 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 caching, consider the following report types/use cases:
- You have many concurrent users.
- You have a report that is accessed often (a sample case would be a dashboard) with the same URL (=same query).
- The report is complex and takes some time to execute (many complex queries/temp tables/non-indexed queries).
- The report is shown with static parameters/no parameters at all (parameter change will trigger a new cache item).
- The data shown does not have to be absolutely up-to-date (cache time).
The reports that you probably do not want to use Redis for:
- Reports that need always to contain the latest data (to the second).
- Simple fast reports with direct indexed queries (your database is probably fast enough).
- Reports with multiple parameters (dates, etc.) that the user chooses from (you may end up with multiple cached versions).
When you configure a report to use the cache (check the 'Use cache' checkbox in report info and set the cache expiration time in seconds), myDBR checks if the report content can be found in 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, 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 supported serializers 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 Redis is used with myDBR, the output of all queries is stored in Redis and is 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 user/defaults.php:
$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 an admin. This can be enabled if you add the following statement to user/defaults.php:
$mydbr_defaults['query_cache']['debug'] = true;
myDBR shows a red circle in the admin view for those reports that are being cached.