Query cache

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.

When to use Redis?

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:

  • You've got many concurrent users
  • You have a report that is accessed often (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 the 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)
  • Report with multiple parameters (dates etc), that the user choose from (you may end up with multiple cached versions)

How Redis is utilised by myDBR?

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.

How To install Redis

Use your package manager in your Linux/Unix distro. In Windows, the Redis can be installed via the WSL.

Enabling Redis in myDBR

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.

Why use Redis with myDBR? Isn't the database access fast enough?

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.

What happens if myDBR cannot connect to Redis?

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;

How to know if Redis is used?

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 user/defaults.php:

$mydbr_defaults['query_cache']['debug'] = true;

How to know which reports have the cache enabled?

myDBR shows a red circle in the admin view for those reports that are being cached.

How to refresh the cached content prior to cache expiration?

One can add &cache=0 to the report URL. This will instruct myDBR to fetch the data from the database and refresh the cache.