We have an RHEL 7.7 machine with 1.9TB RAM,32 (dual core) processors, with 4K page size(default) and configured huge pages 501760(2048 KB) for allocating the shared memory.
Coming to the dataserver part, we are using 16.0 SP03 PL04 (16K page size) with a database 11TB(compressed to 3.8TB), the total memory configured through max memory is 900 GB with 50 threads on syb_default_pool, global cache partitions as 128, strict LRU strategy, APF as 10 with the largest buffer pool being 128K.
Size of the default data cache is 5 GB (16K - 2000 MB, 32 K - 1500 MB, 128K - 1500 MB)
Size of the billing_data_cache is 600 GB (16K - 350000 MB, 128K - 250000 MB)
Observation 1: As soon as we started the dataserver, we are noticing the minor and major faults and at the same time 80% spinlock contention on the default data cache.
Observation 2: When we execute a stored procedure from the database - Billing (bounded to billing_data_cache) the minor faults are increasing with few major faults are some what moderate(for sure we want to eliminate this) and at the same time the spinlock contention on default data cache,IDES spin lock contention more than 80% some times more than 85%.
We tried setting open index hash spinlock ratio to 50 from 100 but still the issue persists. Please provide your valuable views and inputs on this.
NOTE: We are in testing phase and want to get this fixed before going live because in live environment we have parallel jobs and we don't want to end up with time slices and stacktraces because of these faults and contention during peak hours.