0

I am doing some research in order to increase the performance of my asp.net webapi core (5.0) when working with an oracle (12g) database. I have made two endpoints for tests purpose in this API :

  1. Raw helloworld api endpoint :

    this endpoind will only send back a helloworld string. This is a trivial code.

  2. Oracle Odp.Net api endpoint : in this endpoint I am opening an oracle connection retrieve the version of oracle and send back this response to the client.

The code used is this one :

 string constr = "user id=scott;password=tiger;data source=oracle;CONNECTION LIFETIME=90000;MAX POOL SIZE=300;MIN POOL SIZE=20;INCR POOL SIZE=5;DECR POOL SIZE=3";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
    string connectionVersion =  con.ServerVersion;
    con.Dispose();

For my tests, I had also changed some settings :

  • ThreadPool : 100 wordkerThread and 100 iocpThread
  • Database max process : 500

The problem that I have, is that I am observing the following results under load with 500 client threads hammering my API :

  1. Api endpoint 1 (raw helloworld) : 13 000 request/second

  2. Api endpoint 2 (using odp.net) : 800 request/second

After doing some profiling, I had found that most of the time is spent on the SocketPal.Receive method (surely used by ODP.Net) : Performance profiling

How could I get a better throughtput when using oracle with odp.net ?

Is it normal that this throughtput is divided by almost 15,5 (this is a loss of nearly 1525%) ?

Dypso
  • 563
  • 1
  • 5
  • 15
  • 2
    Why do you think it peculiar for an endpoint that essentially does nothing ("hello world") to perform exponentially better than an endpoint that connects to a database? – Kirk Woll Feb 28 '22 at 15:13
  • It is very normal to see throughput take a nose dive when working with IO, IO is slow, very very slow. The only thing I see that may increase throughput is reusing the `OracleConnection` instead of creating (and connecting) a new one on every API call, you could do this by using dependency injection, but I don't know if `OracleConnection` is thread safe, do some research. But anyways, premature optimisation is a bad thing. Develop your API, deploy it, measure performance and then optimise once it's actually a problem – MindSwipe Feb 28 '22 at 15:14
  • @KirkWoll : I was expecting a loss in fact, but not in this magnitude. For example I have already used Redis and the throughtput is not affected as much (in this test case I could achieve 7 000 request/second with Redis for example). So I suspect that there is something linked to oracle or odp.net that I did not configured correctly. – Dypso Feb 28 '22 at 15:21
  • @MindSwipe : I am using the ODP.Net pooling setting so the connections are not recreated from scratch and serve from the pool when available.. I have already some problems in production on similar code, and I suspect that the calls to the database are the bottleneck, so I want to study how to increase the thoughtput when calling an oracle Database from c#.. – Dypso Feb 28 '22 at 15:24
  • You *suspect* or you *know* that calls to the DB are the problem? Measuring is step 1 to optimising anything, without identifying the slow running code there's no way you can actually optimise anything of value. Also of course Redis is faster, Redis isn't a database, it's a cache that lives in RAM, accessing things in RAM is orders of magnitude faster than even the fastest SSD. – MindSwipe Feb 28 '22 at 15:29
  • @MindSwipe : for the moment I am focussing on this test : the call to one endpoint not doing any database io give me a throughtput of 13 000 req/sec and if I make the same endpoint only opening and closing a connection to oracle database with only a query to have the version of the db make the responses/sec drop to 800 request/second. So yes I am pretty sure here that the call to the database is my bottleneck. And I am wondering how could I have a more decent throughtput in this test. – Dypso Feb 28 '22 at 18:02
  • I have done some profiling and found that the time is mostly spent on the socket.receive method (see the screenshot attached) , but I have yet to find how to reduce this time – Dypso Mar 02 '22 at 02:04
  • you have not provided practically any info about your Oracle database. OS, version (enterprise, etc). Server on which it runs (hardware), – T.S. Aug 17 '22 at 14:12

0 Answers0