column table reloading on startup

[Former Member]

Hello,

For some tables in our HANA the load into memory starts automatically on startup.

How do we configure which tables to load into memory on startup?

Thanks,

Amir

[lbreddemann]

As I've described in Richard's and my book () there are basically two ways that column tables get re-loaded during the db restart:

- PREloading, where you have to actively set the PRELOAD flag for the tables (check the ALTER TABLE command reference- there is not GUI option for that) you want to have in memory right after instance startup

and

- REloading. This is a automatic mechanism that has been implemented for replication sites, so that in case of a fail-over the required data is already in memory. For this SAP HANA automatically keeps track on what columns are loaded and reloads those after instance restart.

Cheers,

Lars

[deepak_chodha]

Hi Lars,

good that you brought this point. I was searching for PREloading the parameter in configuration tab. But couldn't find it.

1. Was it recently removed?

2. Why was it removed from GUI!

3. And now if don't want my 'any' column tables to be loaded during single site startup, how do I stop?

Happy HANA

Deepak

[lbreddemann]

With SAP HANA only explicitly set parameters are visible in the configuration files.

The GUI only reflects what's in these files (based on contents of M_INIFILE_CONTENTS).

None of that has changed.

You may want to check the documentation for the parameter.

Your last question doesn't make any sense to me.

[Former Member]

Hi Lars,

These tables were created by SLT. Is there a place we can see if the preload falg is on?

I'm not sure i understand what do you mean by "Replication site" Do you mean DRP system?

so the system reloads the data to be synchronized with DRP?

Thanks,

Amir

[lbreddemann]

The PREload flag is visible in the TABLES system view.

With replication site I referred to the SAP HANA system replication. This has nothing to do with SLT.

[gil_halevy]

Hello Lars,

All of our tables have a value of '?' in their IS_PRELOAD field in the TABLES system table

There is also 1 table with a value of 'FALSE'. It is a BW table called TPDB2

Why are the SLT tables loaded at startup ?

Where is the indication which tables are loaded at startup ?

How do we control which are loaded and which are not and when ?

Thanks and kind regards,

Amir & Gil

[lbreddemann]

A ? indicates that there is a NULL value - so these tables won't be automatically PREloaded.

This answers the first question: they are not PREloaded (but might be REloaded).

The other questions had already been answered here before.

- Lars

[gil_halevy]

Hello Lars,

Yes, we already know that they are Not PREloaded

How do we Stop the REloading of the tables (after just a Normal stop/start of the HANA - Not a DR) ?

Do we force an Unload of the tables before the Stop ?

Does the HANA REload All tables that are currently loaded before the stop/start ?

Kind regards,

Gil

[former_member182302]

Hello Gil,

The "Help" screen says you can deactivate the reload feature by the below mentioned setting in indexserver.ini file.

Regards,

Krishna Tangudu

[gil_halevy]

Hello Krishna Tangudu,

Thanks !

The Default value of  preload_column_tables and reload_tables is True.

Do I just change the  reload_tables to False or also change preload_column_tables ?

Our tables are Huge so it takes sometimes even 20-30 minutes until the re-load finishes. Until then the BW ABAP instance cannot connect to the HANA DB.

Thanks again and kind regards,

Gil

[former_member182302]

Hello Gil,

As Lars already mentioned, PREload and REload are 2 different ways of reloading the column tables during db restart.

As I can see from the above conversation, that IS_PRELOAD is set to NULL. So REload was doing the reload of the tables. Hence setting reload_tables to False should suffice.

Regards,

Krishna Tangudu

[lbreddemann]

Sorry, that's wrong. The reload is done _after_ the instance is up and running.

When you face connection issues during this time, there is another problem present.

- Lars

[gil_halevy]

Hello Lars,

The connection from the ABAP instance to the DB is Hanging until the re-load is finished (as the tables are Huge it takes a lot of resources and a lot of Total time). HDBstudio connection works but Very slowly.

SELECT DISTINCT  FIELD1 ||'_' || FIELD2 FROM HUGE_TABLE  consumes 100GB

The size of the table on disk is 350GB (in Memory - 260GB)

Do I set  reload_tables to False or Not ?

Thanks and kind regards,

Gil

[lbreddemann]

Well, the connection from ABAP is technically not very different than the one from HANA studio.

If the reload really affects the overall system utilization so badly, that the system becomes unresponsive, I tend to say that the system is not properly setup/sized.

Maybe your I/O bottlenecks and/or uses too much CPU or interrupts...

350 GB is not that much actually...

You may set reload_tables to FALSE, but this is not the fix for your problem - you merely avoid hitting it in this situation.

- Lars

[gil_halevy]

Hello Lars,

We do Not understand this behavior either.

We have 4 nodes each with 512GB

The table has 3Billion records

How do we further investigate this issue ?

R3trans -d   is Hanging during the Re-Loading of the table's fields.

When R3trans finally finishes, the ABAP processes are in a weird state that don't allow entering the system.

I also tried using hdbsql on the ABAP host and managed to enter and run SQL statements But they were very Slow.

Please, advise on how to investigate the matter further

Thanks and kind regards,

Gil

[lbreddemann]

Check IO times.

Check workload on the system (LOAD graph, statistics server data, solution manager reports or use one of the many scripts available via sap notes to gather data).

Get involved with support.

that's what I would recommend here.

- Lars

[gil_halevy]

Hello Lars,

I see a bit of Network (I/O) trafic - 140,000 KB/sec

However, the ABAP processes are in WP_NEW state until the TableReload finishes.

How do we further investigate the issue ?

Thanks and kind regards,

Gil

[lbreddemann]

Ok, this clearly is beyond the original discussion of preload/reload of columns in SAP HANA.

Finding out about performance issues in a multi-tier-architecture like the SAP NetWeaver stack is not as easy to explain it in a few lines here in an SCN thread.

There are whole books on this topic.

My recommendation stands: get in touch with SAP support on this.

- Lars