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, and 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 (e.g., a dashboard) with the same URL (=same query).
- The report is complex and takes some time to execute (many complex queries, temporary 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).
Reports that you probably do not want to use Redis for:
- Reports that need 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 users choose from (multiple cached versions).
Configure a report to use the cache by checking the 'Use cache' checkbox in report info and setting the cache expiration time in seconds. myDBR checks if the report content is in the cache based on the database query. If not found (first run/previous cache expired), the report runs and results are stored in the cache. Subsequent runs fetch data from the cache, avoiding DB access.
Parameter changes for the query (and content of the report) are treated as separate cache items.
Install Redis via your Linux/Unix package manager. On Windows, Redis can be installed using WSL.
Set up Redis on a server and enable it in Environment settings. Define usage per report on the Edit Report page (enable/disable, set expiration time in seconds). Environment settings include hostname, TCP port, and optional password. Redis serializes cached elements with chosen serializer (depends on Redis version):
-
igbinary- Recommended for least memory usage and fastest speed. -
json- Plain text JSON serializer. -
php- PHP's serialize.
In Admin tools->Query cache, view/delete cached items.
Databases optimize queries efficiently, especially for indexed, direct queries. However, reports may include heavy queries, temporary tables, non-optimal queries. With multiple users accessing such reports, database load increases. Redis stores query outputs, serving data faster to users and preserving database resources.
When myDBR cannot connect to Redis, queries run normally accessing the database. By default, no error messages are shown to end-users. To show error codes:
$mydbr_defaults['query_cache']['show_redis_errors_to_users'] = true;
Default myDBR statistics report shows cache usage. Enable Redis logo on report header for admin-run reports by adding:
$mydbr_defaults['query_cache']['debug'] = true;
myDBR admin view shows a red circle for cached reports.