We have a MariaDB table that is moderately sized (thousands of rows, MBs of data), rarely written, read almost continuously, and the reads often have bursts of repeated parameters. It's a perfect candidate for named query caching and does pretty good with it. However, on the rare occurrence that we write a new value to a row, we need the reads to start getting the new value immediately.
We are currently using system.db.clearNamedQueryCache after each write, and while it gets the job done, I can actually see spikes in the SQL server load as it is suddenly being queried for all the data again.
I only really need to invalidate ONE set of read query parameters. I would really like if system.db.runNamedQuery had an optional parameter that could force a read-through and update the cache. Then, after a write that updates the DB, instead of clearing the WHOLE cache, I could just do a targeted runNamedQuery with that enabled and the parameters I know were affected. This would get the one new row into the cache while not instantly invalidating the whole cache.
I tried searching over on ideas.inductiveautomation.com and couldn't find anything that looked like this, but forcing a cache read-through is a common idea and I really thought someone would have suggested it before.
Or, maybe I'm missing some other technique that already exists. Does anyone know a way to achieve the result I want with what already exists in Ignition? Is there a feature suggestion that would apply to this that I missed? If no one knows another way or of an existing feature suggestion, I'll go make a feature suggestion tomorrow.
You could script all of your queries and maintain your own cache in a top-level script object.
I considered it, but many of the consumers are Perspective views. Having the ability to avoid script blocks while still caching is nice. Converting to scripting is not off the table yet, but I'm not sure if it's a net win with how many places use this query.
Could you create two named queries, one that caches and one that doesn't. Call the one that doesn't cache after writes, otherwise use the cached one?
After you do your clearNamedQueryCache, presuming you know what parameters are your most used, then call a system.db.runNamedQuery for those params right after to set the cache?
Sounds like a job for my globalVarMap() expression function. How many parameters does the NQ use?
If just one, an expression like this might suit you:
transform(
{path.to.some.query.param},
coalesce(
globalVarMap('someCacheName')[value()], // Try to get the NQ result for the parameter from the cache
objectScript('someCacheScript.getAndCache(args[0])', value()) // If not cached, run the NQ.
)
)
IIRC, coalesce() short circuits when the first arg is non-null.
The accompanying script would take a lock, recheck the cache within the lock, then if still not present, run the NQ and stuff into the globalVarMap before releasing the lock and returning.
3 Likes
two named queries, one that caches and one that doesn't
Not quite the issue I'm trying to resolve. Our users open dashboards made up of mostly independent sub-views, and while each view has it's own call to this query, sets of sub-views that open at the same time tend to use the same parameters. If a user changes something in the query results, we need all the sub-views to get the new value sooner than later.
After you do your clearNamedQueryCache ... call a system.db.runNamedQuery ... to set the cache
The problem is that with multiple users viewing dashboards at the same time, the caches for all of them are going to be invalidated at the same time and each user likely has two or three set of parameters to this query on their views. I know only ONE set of parameters needs to be removed from the cache but when I clear the whole thing it starts fetching for everyone.
Sounds like a job for my globalVarMap() expression function. How many parameters does the NQ use?
Two strings, usually 16 characters or less in length each. But I can guarantee there's never a / in either string, so I could plausibly adapt the cache key to be CONCAT(one_param,'/',second_param) or whatever the Ignition expression is. (My mind is stuck in SQL right now)
I would have to change a LOT of views, but it would do what I want...
Another option entirely: Cache in MariaDB directly?
I only just skimmed that it but it looks like you might be able to 'hint' in your named queries that they should cache, and presumably you could write another named query to explicitly clear the cache/some cache entry.
Having this capability in Ignition would be nice, and I think it's a worthwhile feature. I spent a few minutes seeing if it would be doable to hack your way in to the cache via scripting, and the short answer is no 
3 Likes
It might be easier to document/explain as an optional parameter on clearNamedQueryCache, where you can pass in a parameters value just like you would've used to runNamedQuery and it will clear only that part of the cache. I could see it working either as a "targeted clear" or a "forced bypass" of the cache, though.
I made a feature suggestion if anyone wants to vote or comment: