Unanswered: Very High Insert Times Root Cause Analysis?
We have a Sun E4500 running Solaris 8, 6GB RAM, HDS SAN running Sybase 12.5.X for OLTP workload.
We're frequently getting insert execution times of single rows which take upwards of 5 seconds and sometimes higher than 100 seconds--normally, these take a few miliseconds.
There is no perceivable physical IO or memory bottleneck.
The insert process is a black box to us. What appears to happen is that inserts get stuck waiting in spinlock for several seconds. Once the stuck insert clears, everything in queue behind it flushes rapidly--so we tend to end up with clumps of inserts which take a large amount of time but finish in rapid succession.
We've modified cache partitions and have tried other things with varying levels of success.
When the problem is very noticeably bad, sysmon reports high engine utilization. And "modify conflicts" seems to be the most highly represented task context switch reason during these trouble periods.
Can anybody provide insight as to what might be going on? Namely, I'm really curious what happens internally within sybase after the request is parsed and before it's dumped physically into the translog device. We're using raw veritas devices. None of the system tools report IOs taking upwards of several seconds.