Bottleneck: MultiProgramming Level

Question: Should the multiprogramming level be increased?

With SQL Anywhere 12, the answer is... Ask a different question! The new AutoMultiProgrammingLevel feature lets the database server choose what the multiprogramming level should be.

So, the new question is two-fold:

---

Question: Will a higher multiprogramming level improve performance, and if so, how do I get the server to pick a higher value?"

---

Here's a Foxhound snapshot of a server in difficulty: 35 connections (Conns) are pounding the server with around 3,000 transactions per second (Commits), the CPU Time is hitting 95%, and two-thirds of the connections are waiting for work to be done (Waiting Conns)... yet the multiprogramming level (Max Req) has only grown to 11!

(click image to see full-size)

So the answer is "Yes" to the original question, "Should the multiprogramming level be increased?"

But the answer is "No" to the implied question, "Should I increase the dbsrv12 -gn value?" with the reason being that forcing SQL Anywhere to use a larger value can have unintended consequences... it can lead to worse performance, in this or other scenarios.

Yes, the multiprogramming level should be increased... but SQL Anywhere should do it itself.

In this case, the problem is "not enough CPUs"... if you really want to push thousands of transactions through a database server, you really need more than one CPU.

Here are the same 35 connections pushing the same workload through a server with 8 CPUs:

(click image to see full-size)

With 8 CPUs, the multiprogramming level (Max Req) has only grown by 3 to 14 but the effect on performance is huge: Now the workload pushes the CPU Time from 95% of one CPU to 60% of eight, and the overall throughput as measured from the client side has grown from 2,759 transactions per second to 11,722... which is a 300% improvement.

---