Configuring Stored Procedure Query Caching

The Transparent Cache feature makes it possible to configure the databases for which query responses need to be cached and specify the patterns or rules that trigger data cache. Once these rules have been set up, frequently used queries will be offloaded to ScaleArc to reduce database load and improve stored procedure response times.

This article describes the procedure for configuring the stored procedure query caching feature. Ensure that Query Caching is enabled before proceeding with the following steps: 

1. On the ScaleArc dashboard, navigate to CLUSTERS > Settings >Transparent Cache.
   
    
    
2. Click on the Stored Procedure tab.
   
   
   
   

List stored procedures

1. Click on the gear icon against the database to be configured.
   
   
   
   

2. Follow the table to configure the stored procedure query caching. Refer to Query patterns under Cache Rules for a description of the remaining widgets on the screen. 

   Field	Description	Default/User input
   List Stored Procedures	Lists stored procedure kept on the server. For the selected database, the list displays only those procedures that have not yet been added as stored procedure patterns for caching.	Select and add to the cache.

3. Click List Stored Procedures to view all the stored procedures present in the database. 
   
   
   
   
4. Select the patterns to add to the cache. 

Add a pattern

ScaleArc provides an easy way to add a pattern to a cache rule.

1. Click on the Add Pattern button.
   
   
    
2. Enter the order number.
3. Tab over and enter the pattern's Regular Expression in the next field. 
4. Enter the TTL value and enable/disable as required.
5. Click Save. 

6. To edit, delete the pattern and recreate, using the Add Pattern button.