Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

This relates to

We have an installation using an ODATA server on SQL Anywhere Network Server Version

The installation work fine up to a certain request rate, after which the response time deteriorates rapidly. We are now trying to find the bottleneck.

The server is running on a 20 CPU virtual machine with 40 GB assigned to the database server. CPU load is around 10-15% (two or three CPUs), file IO is not critical, something around a few MB/sec or 0.1 to 0.5 queue length, and network load is far from the possible maximum.

The profiler does not report any long running statements or lock waits. The statements which correspond to the OData requests run smoothly in ISQL, even during such a (probably) overload condition. The perfmon counters we look at (Connection Count, Statements, Transaction Commits, Requests) do not vary dramatically, maybe a factor of 10 from (almost) idle to overload, while the response times vary by a factor of 1000 (from tens of milliseconds to tens of seconds).

Then we try to connect to the OData server directly with a browser during such an overload condition, merely establishing the connection, so the browser asks for credentials, takes minutes and more (leading to timeouts). This gave us the idea that maybe the OData producer itself is the bottleneck.

We did not find performance counters dedicated to the OData producer, is there any way for us to verify our assumption? Is it possible to create two (or more) OData producer processes accessing the same database as backend?

Thanky in advance for any hints.


In the meantime we tried using multiple ODATA servers on one database server. This was possible, though it was considerable work (as I have been told).

Sadly, we got no significant improvement. So maybe we are wrong with our assumption. Or the test scenario did not match the production situation.

asked 27 Oct '22, 08:46

tedfroehlich's gravatar image

accept rate: 18%

edited 04 Nov '22, 04:44


I don't know whether the same database can offer both OData producers via the builtin Jetty web server and a third party web server.

However, you could try to use a third party webserver (be it Jetty or Apache) and check whether this performs better.

(27 Oct '22, 09:44) Volker Barth

I have seen this question too, and wrote you a small comment there:

I think, the producer should be analyzed as a Java application with SAP JDK profiling tools. There is a chance that:
1. thread pool HTTP/JDBC is exhausted;
2. frequent GCs
3. Something's wrong with the world today.

(28 Oct '22, 15:46) Vlad
Replies hidden

I have checked my demo server, its max heap size is set to 1G (by default).

(28 Oct '22, 15:57) Vlad

To your update:

we tried using multiple ODATA servers on one database server

Does this mean you have now used third party webs servers? If so, running on the same machine?

(And no, I still don't have any clue.)

(04 Nov '22, 05:23) Volker Barth
Replies hidden

As I understood my colleague, he did install multiple Jetty producers on the same machine.

(04 Nov '22, 05:55) tedfroehlich

You couldn’t find the bottleneck, but your colleague blindly installed additional servers. I told you earlier, try to attach the JVM profiler to your Java instance and check its health.

(05 Nov '22, 05:52) Vlad
showing 3 of 6 show all flat view

We now found that our problem could be fixed by using the ConnectionPoolMaximum parameter to increase the number of available database connections, see CREATE ODATA PRODUCER documentation.

The default seems to be somewhere below 100, which is way to few if we have 500 or 1000 different users who try to connect simultaneously. Raising the limit to 800 (and ensuring appropriate computing power) did solve our problem.

It looks like during our tests from Update 1 we tested many connections with the same user, which, if you think about it, is indeed a completely different story as when testing with many different users...

permanent link

answered 14 Mar '23, 08:48

tedfroehlich's gravatar image

accept rate: 18%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 27 Oct '22, 08:46

question was seen: 679 times

last updated: 14 Mar '23, 08:48