SAP Sybase ASE “Magic Numbers” and Hardcoded Behaviour

SAP Sybase “Magic Numbers” hardcoded into the product.

Run Queues
0 – System Service tasks
1/2 – unused
3 Group commit,Site handler,Threshold,Returns from lockwait
4 High User
5 Medium User
6 Low User
7 Housekeeper

Tasks attempting to take an exclusive lock are held up by readers with a shared lock
Up to 3 tasks can skip over a queued update task
After a write transaction has been skipped over by 3 tasks or families (parallel queries) that acquire shared locks,
ASE gives a demand lock to the write transaction.
This indicates that a transaction is next in the queue to lock a table, page or row
Subsequent requests for shared locks are queued behind the demand lock

155 bytes per lock

reorg rebuild minimallly logged, requires 120% table size

Before 15.7 certain tasks could not migrate from engine to engine
– disk IO done by engine which issued the IO
– Network IO done by engine assigned at login
– Replication Agent and CIS tasks cannot migrate

Kernel resource memory

– one 2K block per 2 user connections if <=8 engines
– if more engines increase default by 2K for each user connection

Threaded Kernel sp_sysmon

– Average Running Tasks, always 1,5,15 minute moving averages
– CPU Yields “full sleeps”/”interrupted sleeps” high percent “interrupted sleep” increase “idle timeout”
– Thread Utilization, if >70% increase thread count, if thread utilization > engine utilization increase “idle timeout”
as engine not getting enough CPU time
– Page Faults and Context Switches, high counts page faults, involuntary context switches – lack of resources (CPU/memory)
– IO Controllers, DiskControllerActivity/NetControllerActivity equivalent to Disk/Network Checks.
If polls returning max events or events per poll > 3 increase “number of disk/network tasks”
– Blocking Call Activity
If value of queued requests is high compared to serviced requests or wait time is nonzero increase number of threads in syb_blocking_pool

Spinlocks
– Hash tables to reference memory structures
– spinlock/semaphone same in sp_sysmon
– Log Semaphore Contention, if % of total >20% and “ULC Flushed to Xact Log” and “by Full ULC” also >20%, ULC may be too small
– By Full ULC, if >20% consider increasing ULC
– By Change of Database, ULC flushed when modifies object in different database
– By Single Record, by Unpin, by Other if >20% decrease ULC
– When MAX ULC Size = log cache size check flushes bu Full ULC, if >20% consider increasing ULC
– ULC Spinlock Ratio – number of ULC Caches controlled per spinlock

– Cache Turnover, Buffers Grabbed dirty should be 0, else adjust wash marker

Grabbed Dirty not 0 – significant performance problem
– Increase wash size immediate

sp_logiosize and large IO pool
use 8 if not sure
stored in sysattributes for the database

sp_countmetadata – Number of objects of type and memory needed, add 5% to open objects and 10% to open indexes for temporary tables

Minimum size of a procedure in cache is 2K
3K procedure uses 2x2K pages

Statement cache

– Each cached statement uses 1 object descriptors “number of open objects”

– Each query plan generated and used by the statment cache uses at least 2x2K pages
512-1024 for sql text
– 1 page per copy of the plan

Only 1 user may reference a particular copy of a statement cache plan

Semantic Partitioning

Range partitioning up to 31 partition keys

List partitioning
– only 1 column for the key
– Boundary values must be compatibile with partition key datatype
– Implict conversion will take place for different but compatibile data types
– Transaction will fail if implict conversion results in data loss
– Insert value not part of list will fail
– List can have up to 250 values
– Value list must be unique across partitions

sp_configure “number of open partitions” > “number of open objects” + “number of open indexes”

Histogram tuning factor
– default 20 steps
– default histogram tuning factor 20
– 400 steps

ASE does not consider the number of CPUs or disks
ASE relies on degree of parallelism being configured correctly

Parallel Access

– Lock Promotion occurs per worker hence the family can have (n-1)*threshold for promotion

If cache hit ratio >90% partitioning table not greatly improve performance

Partition skew = largest partition/partition average, rebalance if >1.3.
If >2 optimizer will not choose a parallel plan

For single processor machines set “runnable process search count” to 2/3  For 15.7 thread idle timeout

sp_configure ‘additional network memory” – for additional memory used for network packets which are larger than default size

Clients cannot use larger packet sizes if “max network packet size” is increased but “additional network memory” is not increased

Sizing Additional Network Memory
– Estimate number of users requesting large packet sizes
– Estimate the sizes the application will request
– Multiple by 3
– Add 2% for overhead
– Round to multiple to 2048

sysprocesses.network_pktsz

MRU replacement strategy used when
– optimizer estimates >50% of the buffers in cache will need to be displaced

Relaxed LRU may not be worth considering with <=4 CPUS.

APF defaults to 10% of given buffer pool – if async prefetched pages fill 10% of buffer pool APFs denied until
amount of pending buffers has been reduced

Advertisement

---