3479664 - SQL Queries are not executing on secondary DB in Active/Active (Read Enabled) setup that uses hint-based statement routing.

The HANA system has been configured with an Active/Active replication setup using hint-based routing to the read-enabled secondary site. However, when reports are executed, the SQL statements are not routing to the secondary DB due to resource container shrinks and high memory consumption on the secondary DB.

The secondary site indexserver traces depict these memory availability issues and the aforementioned resource container shrinks:

[9468]{-1}[-1/-1] 2024-04-13 17:24:20.341455 w ResMan           ResourceContainerShrink.cpp(03332) : Information about shrink at 2024-04-13 17:24:20 338 Local:
Reason for shrink: IPMM (other process). Request ID= 2459002
ShrinkCaller
   0: 0x00007f1e297822a9 in ResourceManager::ShrinkInformation::updateShrinkInformation(ResourceManager::ValidMemoryTypes const&, char const*, unsigned long, bool)+0xa5 at Basis/ResourceManager/impl/ResourceContainerShrink.cpp:1170 (libhdbbasis.so)
   1: 0x00007f1e2979a247 in ResourceManager::ResourceContainerImpl::genericShrinkPreparationsAndSkipCheck(ResourceManager::ArrayForSwappableDispositions<unsigned int>&, unsigned long&, unsigned long, char const*, unsigned long, MemoryManager::ReclaimMemoryTaskType, ResourceManager::ValidMemoryTypes const&) [clone .part.0]+0x3b3 at Basis/ResourceManager/impl/ResourceContainerShrink.cpp:3331 (libhdbbasis.so)
   2: 0x00007f1e297922c7 in ResourceManager::ResourceContainerImpl::genericShrink(unsigned long, unsigned long, char const*, unsigned long, MemoryManager::ReclaimMemoryTaskType, MemoryManager::MemoryTypeArray<bool> const&)+0x123 at Basis/ResourceManager/impl/ResourceContainerShrink.cpp:3365 (libhdbbasis.so)
   3: 0x00007f1e297798ac in ResourceManager::ResourceContainerImpl::compactMemory(MemoryManager::MemoryCompactor::CompactionInfoBlock&, unsigned long, char const*, unsigned long, MemoryManager::ReclaimMemoryTaskType, MemoryManager::MemoryType)+0x48 at Basis/ResourceManager/impl/ResourceContainerShrink.cpp:1938 (libhdbbasis.so)
   4: 0x00007f1e298b410a in MemoryManager::CompactionList::compactCompactors(unsigned long, bool&, char const*, unsigned long, MemoryManager::ReclaimMemoryTaskType, MemoryManager::CompactionThreadStatistics&, MemoryManager::MemoryType) [clone .constprop.0]+0x596 at Basis/MemoryManager/impl/mmCompactionList.cpp:229 (libhdbbasis.so)
   5: 0x00007f1e296607bd in MemoryManager::CompactionThread::doCompactionIntern(MemoryManager::Precharge&, unsigned long, unsigned long, unsigned long, unsigned long&, char const*, unsigned long, MemoryManager::ReclaimMemoryTaskType, bool)+0x1019 at Basis/MemoryManager/impl/mmCompactionThread.cpp:218 (libhdbbasis.so)
   6: 0x00007f1e29661628 in MemoryManager::CompactionThread::doIpmmCompactionIntern(unsigned long, int, unsigned long, unsigned long, unsigned long, MemoryManager::ProcessSlot::RequestedTask)+0x254 at Basis/MemoryManager/impl/mmCompactionTrigger.cpp:492 (libhdbbasis.so)
   7: 0x00007f1e29661e30 in MemoryManager::CompactionThread::run(Execution::ThreadRC&)+0x220 at Basis/MemoryManager/impl/mmIPMMShm.cpp:1631 (libhdbbasis.so)
   8: 0x00007f1e294a8ab4 in Execution::Thread::staticMainImp(Execution::Thread*)+0x610 at Basis/Execution/impl/Thread.cpp:612 (libhdbbasis.so)
   9: 0x00007f1e294af2bf in Execution::pthreadFunctionWrapper(Execution::Thread*)+0x1eb at Basis/Execution/impl/ThreadInterposition.cpp:684 (libhdbbasis.so)
  10: 0x00007f1e2a7a06ea in start_thread+0xd8 (libpthread.so.0)
  11: 0x00007f1e2882a50f in __GI___clone+0x3d (libc.so.6)
IPMM NearDRAM short info:
GLOBAL_ALLOCATION_LIMIT (GAL) = 1.66tb (1831635738624b), SHARED_MEMORY = 28.89gb (31024885760b), CODE_SIZE = 3.04gb (3272458240b), NVM_ALLOCATED_SIZE = 0b, OOM_RESERVATION_ALLOCATOR_SIZE = 96.14mb (100810752b)
PID=9336 (hdbindexserver), PAL=1.66tb (1831769956352b), AB=1.24tb (1368559468544b), UA=0, U=1.23tb (1354337682262b), FRO=0b, FSL=0b, FMC=0b
[Some processes may have been skipped]
Total allocated memory= 1.66tb (1831630401536b)
Total used memory     = 1.64tb (1804418115555b)
Sum AB                = 1797333057536
Sum Used              = 1770120771555
Heap memory fragmentation: 1
Top "M_HEAP_MEMORY" allocators (component, name, size). Ordered descending by exclusive_size_in_use.
 1: Column Store Tables: Pool/ColumnStore/Main/Dictionary/RoDict                               455.25gb (488830128607b)
 2: Column Store Tables: Pool/ColumnStore/Main/Index/Single                                    153.67gb (165003097336b)
 3: Column Store Tables: Pool/ColumnStore/Main/Uncompressed                                    144.52gb (155177944904b)
 4: Column Store Tables: Pool/ColumnStore/Main/Compressed/Indirect                             116.85gb (125477338460b)
 5: Column Store Tables: Pool/SerializedObject                                                 103.93gb (111600552528b)
 6: Column Store Tables: Pool/ColumnStore/Main/Rowid                                           61.94gb (66511905152b)
 7: Column Store Tables: Pool/ColumnStore/Main/Compressed/Sparse                               54gb (57987035776b)
 8: Column Store Tables: Pool/ColumnStore/Main/Dictionary/ValueDict                            23.58gb (25330198972b)
 9: System:              Pool/PersistenceManager/PersistentSpace/DefaultPageProvider:DataPage  23.21gb (24931460336b)
10: Column Store Tables: Pool/ColumnStore/Main/Compressed/Rle                                  15.66gb (16821313656b)
11: System:              Pool/PersistenceManager/MidSizeLOBContainerFileIDMapping/Map          13.65gb (14657025840b)
12: System:              AllocateOnlyAllocator-unlimited/FLA-UL<24592,1>/MemoryMapLevel3Nodes  11.38gb (12230536096b)
13: Column Store Tables: Pool/PersistenceManager/UnifiedTableContainer/MVCC                    10.87gb (11680194192b)
14: System:              Pool/PersistenceManager/MidSizeLOBContainerFileIDMapping/OwnerBackMap 8.23gb (8845596464b)
15: Column Store Tables: Pool/PersistenceManager/UnifiedTableContainer                         7.49gb (8046769433b)
16: Column Store Tables: Pool/Text/AEText/split_positional_index                               6.33gb (6800854512b)
17: System:              Pool/PersistenceManager/DisasterRecoverySecondary/ReplayLogCache      5.27gb (5668360344b)
18: Row Store Tables:    Pool/RowStoreTables/CpbTree                                           4.45gb (4786189200b)
19: Column Store Tables: Pool/ColumnStore/Main/Index/PageableSingle                            3.76gb (4041634448b)
20: Column Store Tables: Pool/Text/AEText/split_document_index                                 2.83gb (3046693224b)
Process: AB=1.24tb (1368559468544b), U=1.23tb (1354337682926b), fragmentation=1%.

SAP HANA, platform edition

Hint based Routing, Fallback Routing, RESULT_LAG(), GAL, memory shrinks, global_allocation_limit, allocation limit, Active/Active(Read Enabled) , KBA , HAN-DB-HA , SAP HANA High Availability (System Replication, DR, etc.) , HAN-DB , SAP HANA Database , Problem