ASA9 vs SA12

[former_SQLA_member1694894]

Hi, I'm encountering great difficult in ASA9 to SA12 migration. Generally I notice that many (new) automatism don't works well, in our scenarios. For example, today, I've discovered "Multiprogram Level" and, in my scenario, it places in "unscheduled request queue", a request without any really server critical status ! So, now, I'm managing "-gn" server option. Query optimization, also, works worst then on ASA9 ..... My question is: How can I set Sa12 to work as ASA9, WITHOUT any particular optimizazion ?? Please I need a simple efficient DB server .... not a black box that block request. Thanks.

[VolkerBarth]

Well, I can't give general advice, but just three hints:

• I would recommend to give more information (or even samples) as to what kind of queries/usage situations do perform worse with SA 12 - "a simple efficient DB" is difficult to achieve (and nevertheless, that's one of fundamentel goals of the SQL Anywhere engineering team, I would think)...
• The "Multiprogramming Level" (MPL) is absolutely not new in SA12, the according -gn server option is contained in very old versions, too - it's just the automatic MPL tuning that is new in SA12. Note, with "dbsrv12 -gna 0" you can disable that feature. - Likewise, the queue for "unscheduled requests" is not new, either, any system which has to serve requests in parallel will have such kind of queue.
• Some systems may perform worse when using intra-query parallelism, so setting "max_query_tasks" = 1 might lead to a behaviour more like with ASA9.

[former_SQLA_member1694894]

Thank you Volker for your hints ... and excuse me for outpouring ... but I already have two unresolved express-case on SA12 and this irregular behaviour(many timeouts errors with no logical explanation ....) have forced a regression to ASA9 (... customers don't love experiments ....)

[VolkerBarth]

Well, I'm just another customer, so I would think your case should really be handled w.t.h. of technical support, i.a. as you are already doing... - and obviously I can't comment on whether this is working as desired or not:)

[Former Member]

I am skeptical that the new automatic multiprogramming level support is what is at the root of your performance issues. As Volker stated above, you can always turn it off, if you like, to go back to ASA9 behaviour. However, by default, v12's automatic support starts with 20 threads - you can set the -gnl value to 20 as well to prevent the pool from constricting too much if the server becomes idle, and hence the pool will not go below 20 (the ASA9 default).

There are LOTS of differences between ASA9 and SA v12 servers. I have outlined a partial list here. That list includes a new data store, new index type, new I/O cost model, various changes to statistics gathering and histograms, new query execution techniques, and so on - unsurprising since we've added a lot to the server in the last 8 years.

I suggest you open a support case so that someone in support can help you work out solutions or workarounds to these issues.

[former_SQLA_member1694894]

Hi Glenn, and thank you for replay. I actually have 2 opened support cases (...plus last idle problems):
1) Case 11736479: same DB converted from ASA9 works well on Win while produces poor perfomance (4/5 times slower !!) on Linux.
2) Case 11732871: Java+SA12+Linux produces swap escalation with final (after 30/40 hours) process kill.
What happens to Linux releases ?
I always expect that on Linux, all functions works faster !!

[Former Member]

There is usually little to choose between Linux and Windows-based systems when it comes to SQL Anywhere performance - differences of 5-10% is what I would expect, depending on a variety of factors. Calls to external environments, for example, will illustrate performance differences. I certainly would not expect a factor of 4-5 performance degradation with a Linux server; there is something much more fundamental going on, such as a difference access plan being chosen by the query optimizer, that will account for such a large difference.

[Breck_Carter]

I love a good rant!

If you want to try V12 again, make sure you're using a recent EBF (build) of 12.0.1... then try using the Foxhound database monitor to see what's going on.

The free Evaluation Edition of Foxhound is still available (until Version 2 is released, and then the monthly Rental Edition will replace the Evaluation Edition).

[VolkerBarth]

...and I love an appropriate announcement, as well:)