Cache warming - is this too easy?

[justin_willey]

My simplistic testing suggests that a statement like

UNLOAD FROM TABLE MyVeryBigTable TO 'nul';

results in all the rows being read - and therefore ending up in the cache, but no disk activity. Is this correct or is there some big catch I'm not spotting?

Background: There have been a number of questions over the years to do with cache warming like this one, but basically everyone one is searching for the "holy grail" of the database that runs as fast after you've restarted it as it did before you stopped it. The old advice was - "Well, don't stop it then!" but this is becoming more of a problem with the ever more frequent Windows updates.

SAP have added some functionality that can help, such as the default cache warming process that reloads the pages loaded when the database was last started, and the new v17 option that allows you to manually chose a point in time to record what is in the cache and have that reloaded. Neither of these quite hits the spot - the new feature being dependent on users choosing a good time to do it and the data not changing much.

A lot of of customers try to address this problem by running big reports etc after a restart - but have to do it manually or set up something to schedule it. Memory is now pretty cheap and for most customers we tend to have pretty well as much RAM as database - so a more lavish approach seems possible.

The idea would be to have an event triggered by database start up that just executes the above unload for all the important tables in the database, omitting audit trails, blob data etc that don't impact normal query performance. The first plan was to force an unload to file, or possibly a variable, that was then discarded, but there are dangers of running out of disk space or exceeding the 2GB limit on variables. The nul device approach would seem to be safer and also more efficient.

---

UPDATE

We ended up using the UNLOAD SELECT method. We also put in a check on ISSERVER so that the process isn't invoked when the database is being started manually or by batch file for things like applying logs etc.

CREATE EVENT "adminuser"."CacheWarm" TYPE "DatabaseStart"
HANDLER
BEGIN 
IF PROPERTY  ( 'IsService' ) = 'Yes' THEN 
    MESSAGE string('Cache Warming Started at ',current timestamp) TO CONSOLE;
    UNLOAD select * from AnImportantTable TO 'nul';
    .... lots more tables
    MESSAGE string('Cache Warming Completed at ',current timestamp) TO CONSOLE;
END IF;
END

[VolkerBarth]

UNLOAD TABLE places an exclusive lock on the whole table or materialized view.

Might that be a show stopper?

[justin_willey]

Mmmm... good point. It's in a database start up event so to some extent we aren't too bothered about immediate access, but we maybe could avoid it with

UNLOAD select * from  MyVeryBigTable TO 'nul';

the docs don't talk about locking in that case.

[justin_willey]

Looking at the lock display in Sybase Central: UNLOAD TABLE puts a Shared Table Lock and a Shared Schema lock on the table in question. UNLOAD select * just applies a Shared Schema lock.

[Breck_Carter]

A very clever post deserves a very clever meme!

[justin_willey]

Durrr.. just got it. Flash-to-bang time getting quite extended these days!

[VolkerBarth]

Hm, I'm still in the brain warming phase... - memory swapped out:(

[VolkerBarth]

Justin, if the "question update" is your (currently best) answer, I'll suggest to provide it as such:)

FWIW, when testing v17's "cache warming to a steady state" feature: Have you tried to "save the cache" just before the database is stopped deliberately (however to achieve that)? I'd think that should work well but have not yet tested for myself...

[VolkerBarth]

BTW: Would the new v17 builtin procedure sp_read_db_pages(), such as

sp_read_db_pages(dbspace_id, null, 1, 0);

do the same for you (for all tables on the specified dbspace)?

[justin_willey]

Yes - it looks like that would have the same (or maybe better as it's designed for the purpose!) effect.

[justin_willey]

I've rather been hoping for an "offical" SAP response along the lines of - "yes that's OK", or "what a $%^&&^ stupid idea, it won't work because..." The v17 feature looks good for the future, but I do need something that will work with v16.

[VolkerBarth]

I've rather been hoping for "an "offical" SAP response...

Hm, is it just my impression that for certain areas, answers by SAP engineers have been less frequent than before? And one would think, hey, that's a v17 feature we have implemented, and someone wants to use that, so we're eager to answer all questions about that...

I'm probably sounding rather naive:)