How to conserve the cache contents over a server restart?

[VolkerBarth]

AFAIK, there's no current way to do so...

---

Another MS Patch Tuesday with several database server restarts - leading to "emptied" caches - has reminded me of this older product suggestion from the "product futures" newsgroup:

"Cache Hibernation"

It has also been discussed in comments on the answer of this FAQ.

The basic idea would be to add a facility to re-load the cache contents from the time immediately before the database stop, so applications could continue with the same "hot" cache as before.

Currently, "cache warming" does something similar, however it does use the cache contents from the last database start. So in case you delay a server restart to a timestamp when there are no users (say, nightly), the cache might not get filled by "real usage" at all. (A workaround would be to use some well-thought, application-specific queries that try to fill the cache with reasonable values...)

I just would like to know if such a feature is considered internally in development.

[justin_willey]

One frequent criticism of SQLA v SQLServer that we hear from clients (most are the other way) is that SQLA seems slow off the mark compared with SQLServer, in that following a restart it takes sometime before performance is back up to the usual good level. I wonder if reloading cache is part of the explanation?

[reimer_pods]

That's a complaint we hear from some of our clients too, mainly from those with databases > 10 GB. I support the suggestion.

[Former Member]

The issue, at least in part, is because of different default behaviour. With Microsoft SQL Server, the server will grab as much memory as it can at server startup, at the expense of all other applications, for its buffer pool. SQL Anywhere, by default, is more conservative and bases its initial memory allocation on the size of the database using the formula

max( 2 MB, min( dbsize, 0.25*TotalPhysicalMemory ) );

So, without using the switches to set the initial buffer pool size, SQL Anywhere is already at a disadvantage for the initial set of requests until such time as the SQL Anywhere server can grow the cache.

[justin_willey]

With dedicated database servers, which most of the ones we are involved with are, we tend to allocate all the available RAM, less a decent margin, to a fixed size cache, eg 26GB out of 32GB using the -c and -ca 0 options. (Assuming that the database is big enough to require that much).

We then see a gradual increase in the cache size, as monitored by task manager - not accurate I know. However, once the cache is fully populated performance is excellent, but until then it is much more variable. If we could go straight to that position it would be a great improvement - it would of course take some time to read say 26GB from disk to RAM.

[VolkerBarth]

Yes, my suggestion is directed to dedicated servers, too, and surely we do appreciate the "humble way" SQL Anywhere behaves in general:)

---

As a workaround, we usually have a set of pre-defined queries (or sample application tasks) we run after a server re-start to pre-load the cache, which works well...

[justin_willey]

With some large databases on 10.0.1, we get a period where connections are frozen during some of the time the cache is building. This doesn't start straight away, but some time after the system starts to get busy. It can last as long as 15 mins and seems to be proportional to the size of the cache allocated.

We've never been able to get to the bottom of what is happening - CPU usage goes to 0% and the memory usage shown in Task Manager gradually climbs. After a while (and before the cache is fully populated) everything is fine again. It doesn't happen again until the engine is restarted.

We haven't been able to reproduce it artificially by running pre-defined queries, large numbers of connections may be a part of it. We thought it might be connected with cache warming - but it seems not.

We are hoping (!) it won't occur in v12, but time will tell 🙂

[VolkerBarth · Accepted solution]

FWIW, v17 seems to have introduced that topic (as "Cache warming to a steady state"😞

Support added for cache warming to a steady state (database upgrade required)

To improve performance, you can record cache contents during a steady state period and restore this state when necessary. See Cache warming.

(Note: That's just my impression on reading the docs, have not yet tested it, but the general idea would be to issue an ALTER SERVER SAVE CACHE statement before the database is shutdown and add an ALTER SERVER RESTORE CACHE statement when it is re-started...)

[Former Member]

I am aware of no concrete plans to support "cache hibernation", in the manner you state above, at this time.