Hints on finding performance bottlenecks on OData producer

[former_SQLA_member1694901]

This relates to https://sqlanywhere-forum.sap.com/questions/38148/how-to-interpret-diagnostic-file-with-orgeclipseje...

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

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.

Update

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.

[VolkerBarth]

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.

[Vlad]

I have seen this question too, and wrote you a small comment there: https://sqlanywhere-forum.sap.com/questions/38148/how-to-interpret-diagnostic-file-with-orgeclipseje...

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.

[Vlad]

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

[VolkerBarth]

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.)

[former_SQLA_member1694901]

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

[Vlad]

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.

[former_SQLA_member1694901 · Accepted solution]

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...