Say cheese... on taking snapshots with SAP HANA

lbreddemann · ‎12-11-2012

Based on revision 45 (SPS 5) of SAP HANA Studio and Database

As projects with HANA (just like with any other technology) move on, at some point in time the requirement to perform tests and trainings comes up.

If this is not the case in your project, please go and talk to your project responsible right away! :wink:

Stil here? Ok, so you're actually want to perform some testing?

One important thing when performing tests is that you repeat them in the same environment (and maybe alter one thing at a time).

So, how do you achieve this with a database?

You restore a backup taken earlier, right?

Yeah, right!

That's one way to do it.

A disadvantage of this approach is that it takes a lot of time as the whole database backup needs to be read from the backup medium and be written to the database storage.

Another way to deal with this requirement is to take snapshots of the database and set it back to a snapshot once required.

If you've worked with a virtual machine (e.g. VMWare, VirtualBox or Parallels) you know how snapshots work:

the system is 'frozen' for a moment, a snapshot is taken and afterwards you can continue to work with the system.

From that moment on you always have the option to 'flash back' to the system state when you took the snapshot.

SAP HANA is offering a pretty similar functionality.

Those of you that know SAP MaxDB snapshots will clearly recognize the concepts.

The HANA implementation is quite close to the MaxDB feature.

SNAPSHOTS in HANA

So, I can take snapshots of my HANA instance?

Great, show me the button for that in HANA studio!

Unfortunately there is no frontend UI support for HANA snapshots (yet - no idea whether this is on the backlog for development).

Therefore, this little walktrough involves a bit of typing and reading...

But, wait a minute: where is all the old data stored then?

Ok, let's answer this obvious question first. Similar to many filesystems and in fact very similar to MaxDB HANA's persistency employs the shadow paging concept (http://en.wikipedia.org/wiki/Shadow_paging). That means that whenever a changed data page is written from memory to the disks (this is called a savepoint in HANA) the changed page is stored at a different physical position. The old page is not immediately overwritten. Instead the old version of the page remains in the storage until one of the subsequent savepoints overwrites it with newly changed data.

To keep track of which pages are current and which ones can be overwritten, HANA uses a mapping structure called the converter (hello MaxDB again).

Having these two concepts savepoint+converter in place, a snapshot is now easy to create.

All you've to do is to keep a specific state of the Converter and keep all pages referenced by this converter version from being overwritten.

And that's exactly what happens within HANA when you take a snapshot.

Since savepoints can be recovered transactionally consistent by their design, HANA can simply switch back to a saved Converter version and resume working from there.

By knowing this, the answer to the question is easy to give: the data is still in the data area and won't be overwritten.

Obviously, if you do change a lot of the data that is part of the snapshot, the data area usage will grow quite a bit, even if you're only doing UPDATEs or DELETEs (funny effect, isn't it? 🙂 ).

Well, then let's see what the snapshot is like with HANA.

And off we go - my annotated HANA snapshot walkthrough:

The commands I use I found in our documentation:

   System Administration and Maintenance Information
    SAP HANA Administration Guide
    13.14 SQL Syntax for Backup and Recovery.
    13.14.4 SQL Statements for Data Snapshot..........................................188
    Syntax
    <execute_create_snapshot>::= BACKUP DATA CREATE SNAPSHOT
<execute_drop_snapshot>::= BACKUP DATA DROP SNAPSHOT

Just there I also found this interesting remark that I will check out later in more detail:

   "Examples of SQL Statements for Data Snapshot
    BACKUP DATA CREATE SNAPSHOT
    Create a database-wide snapshot based on a transactional consistent savepoint similar to the data backup.
    If a snapshot exists, no complete data backup is possible.
    Every request of this kind is rejected with a notification that a data backup is still in process.
    BACKUP DATA DROP SNAPSHOT
    Drop a database-wide snapshot. From this point in time on, complete data backups are possible."

To start I setup a test table and fill it with some data.

I also make sure that the table is not fully merged, since I want to know whether the snapshot works with the change log of column tables as well

    (no commits here as AUTOCOMMIT = ON)

    drop table lars.snappy;

    create column table lars.snappy (col1 varchar(20));

    insert into lars.snappy values ('LARS');

    merge delta of lars.snappy;

    -- table created, one record in and merged to the main store

    -- now another record for the delta store:

    insert into lars.snappy values ('NORA');

    -- let's check what we've got:

    -- (I cut away the columns that I'm not interested in for readabilty)

    select * from m_cs_tables where table_name='SNAPPY';

    SCHEMA_NAME TABLE_NAME  RECORD_COUNT    RAW_RECORD_COUNT_IN_MAIN    RAW_RECORD_COUNT_IN_DELTA

    LARS        SNAPPY      2               1                           1

Now, I take a snapshot of this database.

    backup data create snapshot;

    Statement 'backup data create snapshot' successfully executed in 33.449 seconds

    Started: 2012-12-06 21:43:58 (server processing time: 33.428 seconds)

    - Rows Affected: 0

That was easy.

Noteworthy is that the time to take a snapshot is *not* related to the size of the database.

Instead it's bound to how many changed pages have to be written out for the savepoint that is triggered.

Remember? A HANA snapshot is a 'frozen' savepoint.

Well, while we're at it, we could also just check on this, shall we?

    select * from m_savepoints;

    HOST             PORT           VOLUME_ID          START_TIME                       STATE          VERSION          REQUESTED_FREQUENCY          TIME_SINCE_PREVIOUS          DURATION          CRITICAL_PHASE_DURATION          TOTAL_SIZE          FLUSHED_PAGES          FLUSHED_PAGES_IN_CRITICAL_PHASE          FLUSHED_ROWSTORE_PAGES          FLUSHED_ROWSTORE_PAGES_IN_CRITICAL_PHASE          FLUSHED_SIZE          FLUSHED_SIZE_IN_CRITICAL_PHASE          FLUSHED_ROWSTORE_SIZE          FLUSHED_ROWSTORE_SIZE_IN_CRITICAL_PHASE          RTT_SIZE

    vml3012          30003          2                  2012-12-06 21:43:59.017          DONE           85775            300                          171                          5592076           15801                            973209600           14637                  9                                        0                               0                                                 972029952             1179648                                 0                              0                                                0

    vml3012          30003          2                  2012-12-06 21:41:07.241          DONE           85774            300                          114                          22376             111                              2048000             8                      0                                        3                               0                                                 1507328               0                                       540672                         0                                                0

    [...]

    (RTT =  rollback transaction table - see RTT_SIZE column description for m_savepoints view)

The first line shows our snapshot savepoint.

Don't ask me about the mismatch of DURATION (5592076 ms) and the reported 33.449 seconds, no clue on that.

Another system view to check out is - wait for it - m_snapshots:

    select * from m_snapshots;

    HOST             PORT           VOLUME_ID          ID             TIMESTAMP                        FOR_BACKUP          ANCHOR

    vml3012          30003          2                  85775          2012-12-06 21:43:59.017          TRUE                105553116266525

    vml3012          30005          3                  24610          2012-12-06 21:43:58.106          TRUE                105553116266559

    vml3012          30007          4                  69057          2012-12-06 21:43:58.194          TRUE                105553116266504

The avid reader will have noticed, that we took ONE snapshot and now we see THREE entried in our system view.

Upton closer look we find that these snapshots belong to different volumes of different services (notice the PORT cokumn).

This is already a hint to how snapshots are handled in our distributed system (distributed over separate services with separate persistencies eventually distributed over several nodes).

Being the old support guy I am, I also peek into the current indexserver trace file and find one line:

    [...]

    [8319]{0}[0] 2012-12-06 21:44:04.608995 i Logger       SavepointImpl.cpp(02077) : Snapshot 85775->85776, snapsp=85775, RTT size=0P+0O+0D

    [...]

Noticable here is apart from the savepoint number (85775->85776) the information that there is currently a snapshot currently present (snapsp=85775).

If no snapshot would be present we would find snapsp=0 there.

Finally, the backup.log file provides more information:

    [...]

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   SNAPSHOT started

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupPrepareSavepointInProgress

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupPrepareSavepointInProgress

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupPrepareSavepointInProgress

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupPrepareSavepointInProgress

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupPrepareSavepointFinished

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupPrepareSavepointFinished

    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupPrepareSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupPrepareSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupSynchronizeSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupSynchronizeSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupSynchronizeSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupSynchronizeSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupSynchronizeSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupSynchronizeSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupSynchronizeSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupSynchronizeSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupFinishSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupFinishSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupFinishSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupFinishSavepointInProgress

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupFinishSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupFinishSavepointFinished

    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupFinishSavepointFinished

    2012-12-06T21:44:04+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupFinishSavepointFinished

    2012-12-06T21:44:04+01:00  P07589      13b71f5e371 INFO    BACKUP   SNAPSHOT finished successfully

    [...]

And since the snapshot command was bundled into the BACKUP DATA command, finding this information here kind of makes sense.

Maybe we even find something in m_backup_catalog (the system view used to track data backups) for our snapshot?

NOPE. NADA. RIEN.

If you ask me this is just consequent because a snapshot is all but a backup.

It doesn't prevent you from loss of data by media failure.

You can't store the backed up data somewhere else, somewhere safe.

A snapshot is not a backup.

The mixing up of these technically extremely close concepts on a semantic level (by using the same command to trigger both) is not the wisest design decision, if you ask me.

Even worse, due to the overlapping of both functions, there is also a severe limitation that comes into play, when you create a snapshot.

I mentioned it already above: once there is a snapshot, it's not possible to take new data backups anymore!

WOW - this is not nice.

And this wasn't the case with MaxDB.

To bad it really isn't possible to run a data backup on top of a snapshot. If you use the backup wizard to start a new data backup, the wizard will present the following message:

    Backup of system HAN failed

    Could not start backup of system 'HAN'. '

    The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation'

The same thing happens when you try to add another snapshot (multiple snapshots are supported by MaxDB, so why not try this?):

    Could not execute 'backup data create snapshot' in 26 ms 719 µs Started: 2012-12-06 21:59:11.

    SAP DBTech JDBC: [2]: general error:

    Backup error: The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation

To be honest, no idea why multiple snapshots and backups are not possible.

What is easy to see however, is that the converter structure is more complex than in MaxDB:

    select * from m_converter_statistics;

    HOST             PORT           VOLUME_ID          TYPE                        MAX_LEVEL          MAX_PAGENUMBER          ALLOCATED_PAGE_COUNT          ALLOCATED_PAGE_SIZE CREATE_SNAPSHOT_COUNT          DROP_SNAPSHOT_COUNT

    vml3012          30003          2                  StaticConverter             0                  16382                   4                             1048576             10                             9

    vml3012          30003          2                  TemporaryConverter          0                  0                       0                             0                   0                              0

    vml3012          30003          2                  RowStoreConverter           1                  688127                  94208                         1543503872          10                             9

    vml3012          30003          2                  DynamicConverter            1                  212990                  28270                         1818025984          10                             9

    vml3012          30005          3                  StaticConverter             0                  0                       0                             0                   10                             9

    vml3012          30005          3                  TemporaryConverter          0                  0                       0                             0                   0                              0

    vml3012          30005          3                  RowStoreConverter           0                  8191                    4096                          67108864            10                             9

    vml3012          30005          3                  DynamicConverter            0                  16382                   10113                         675201024           10                             9

    vml3012          30007          4                  StaticConverter             0                  0                       0                             0                   10                             9

    vml3012          30007          4                  TemporaryConverter          0                  0                       0                             0                   0                              0

    vml3012          30007          4                  RowStoreConverter           0                  8191                    4096                          67108864            10                             9

    vml3012          30007          4                  DynamicConverter            0                  16382                   23                            1863680             10                             9

In order to be able to backup data again, the existing snapshot (frozen savepoint) needs to be dropped/unfrozen:

    backup data drop snapshot;

After this command, you can take new backups and snapshots again.

Sadly, the error message when you try to drop a snapshot when there is no snapshot is cryptic, to say the least:

    Could not execute 'backup data drop snapshot' in 54 ms 338 µs Started: 2012-12-11 13:42:44.

    SAP DBTech JDBC: [2]: general error:

    Backup error: The state 'ManagerIdle' of the BackupManager does not allow the requested operation

Seriously, why not something more appealing like, I don't know, "no snapshot present to drop"?

BACK TO THE PAST!

Okay, okay, enough looking around in the system.

Let's change some data and return to the snapshot afterwards (I didn't drop the snapshot during but after my tryout :wink: )

    insert into lars.snappy (select * from lars.snappy);

    Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 44 ms 380 µs Started: 2012-12-06 22:17:03 (server processing time: 19 ms 481 µs) - Rows Affected: 2

    Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 22 ms 763 µs Started: 2012-12-06 22:17:06 (server processing time: 4 ms 60 µs) - Rows Affected: 4

    Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 21 ms 419 µs Started: 2012-12-06 22:17:07 (server processing time: 2 ms 507 µs) - Rows Affected: 8

    Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 21 ms 435 µs Started: 2012-12-06 22:17:07 (server processing time: 2 ms 658 µs) - Rows Affected: 16

Now there's some more data in our table.

    select * from lars.snappy;

    COL1

    ----

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

    LARS

    NORA

And of course:

    merge delta of lars.snappy;

    select * from m_cs_tables where table_name='SNAPPY';

    SCHEMA_NAME TABLE_NAME  RECORD_COUNT    RAW_RECORD_COUNT_IN_MAIN    RAW_RECORD_COUNT_IN_DELTA

    LARS        SNAPPY      32              32                          0

To get the old version of the database content back, I first have to shutdown the instance and then use a command line tool:

    -- shutdown via HANA studio

    -- logon to HANA server via SSH:

Tell the database to use the snapshot:

    hanadm@lxxxx:/usr/sap/HAN/HDB00> hdbnsutil -useSnapshot

    nameserver lxxxx:30001 not responding.

    opening persistence ...

    run as transaction master

    done.

For some reason the database is not online now, but has to be started manually...

    hanadm@lxxxx:/usr/sap/HAN/HDB00> HDB start

    StartService

    OK

    OK

    Starting instance using: /usr/sap/HAN/SYS/exe/hdb/sapcontrol -prot NI_HTTP -nr 00 -function StartWait 2700 2

    06.12.2012 22:20:49

    Start

    OK

    06.12.2012 22:23:13

    StartWait

    OK

Finally the instance is up and running again and I can look for my old data.

    select * from lars.snappy

    COL1

    ----

    LARS

    NORA

    select * from m_cs_tables where table_name='SNAPPY';

    SCHEMA_NAME TABLE_NAME  RECORD_COUNT    RAW_RECORD_COUNT_IN_MAIN    RAW_RECORD_COUNT_IN_DELTA

    LARS        SNAPPY      2               1                           1

In fact, this is how my data (and the whole database) looked like when I created the snapshot.

Welly, welly, welly, well - not too bad.

Is the snapshot still there now?

What would be your guess?

Mine, based on past experiences with VM-machines and MaxDB, was that the snapshot stays there until I drop it explicitely.

Is it the same with HANA?

Nope.

    select * from m_snapshots;

    ==> EMPTY <==

This is not too problematic.

You could still use the snapshots to re-generate the same database state over and over, for testing or training system purposes.

All you have to keep in mind is to take a snapshot again, immediately after you restarted the instance when you re-activated the former snapshot.

If you do use the return to snapshot functionality and continue working from there, make sure to create a data backup immediately as well, because the indexserver trace file warns us:

    [...]

    [19308]{0}[0] 2012-12-06 22:31:54.782171 w Logger       SavepointImpl.cpp(02091) : NOTE: BACKUP DATA needed to ensure recoverability of the database

    [...]

And that's it once again folks.

Hope there was something in here for you.

Cheers,

Lars

Here are some links to documentation, references etc. that I used...

System view references: