2 Replies Latest reply on Nov 12, 2019 4:23 PM by 3637696

    ORDS Performance issue

    3573277

      We see considerable drop in performance as we increase the number of concurrent processing. From DB side we dont see hug degradation but end to end from ORDS we see significant bad performance

       

      ORD S version 19.2

      DB version 18c

      tomCat version 7.8 -- running on Linux

       

      We have singled handler select from a table with some where conditions and restricted to return 10 rows.  The where clause is restricted to combination of 3 fields and all indexed. The select happens with single input json parameter "where" and it is just added to the select dynamically and executed

       

      Following testing done

       

      ** Java program which hits the ords rest api with various where clauses. The number of threads (concurrent) can be changed and total test cases is 10,000. So given count of thread say x number of test cases executed one after other = 10,000/x

       

      DB -- ExaCC. One container 18c and one DB.

       

       

      1. Installed on tomcat + ords on 8cpu 8GB RAM

                25 threads -- Average milli seconds per txn is around 50 and hence we achieve 20 TPS per thread. So max is 20*25=500 tps

                40 threads -- Average milli seconds increases to 75 and hence we achieve total 533tps

                60 threads -- Average milli seconds increases to 130 and hence starts dropping

      Any further increase the time take per transactions increases --

       

      DB side we took snaps and saw the performance variation is 1 to 5 milli seconds and max is 20ms -- but overall performance is slower. Maximum CPU usage seen was 70%

       

      2. Installed on tomcat + ords on 12Cpu, 8GB RAM

           25 threads      -- Average 40 miili seconds,  total tps ~ 621

           65 threads      -- Average 51 milli seconds, total tps ! 1227

           80 threads      -- Average 65 milli seconds, total tps 1200

          200 threads     -- Average 155 milli seconds, total tps 1226

          500 threads     -- Average 300 milli seconds, total tps 2000

          1000 threads   -- Average 591 milli seconds, total tps 2254

       

      ** We had adjusted the total ORDS SQL connections and tomcat threads and dont see any connection pool error

       

      DB side we took snaps and saw the performance variation is 1 to 5 milli seconds -- but overall performance is slower. Maximum CPU usage seen was 70% (same but higher number of CPU @ server level) . From db side we see not more than 70 ms as max and that translating to average of 591mill second seems to be the problem. THE PROBLEM SQL "SELECT MODULE .. USER_ORDS" does take 35mill seconds and that is included in above 70ms. Believe this is fixed on 19.4 and if any patch available now we can add and test that as well

       

      When we try in both servers-- Server 25 threads and server 2 80 threads we get same performance from each server. So this again shows not much of impact at DB end.

       

      As mentioned @DB end for any volume (even at 2000 tps) we dont see any degradation in db transaction.

       

      We would have around 2500 txn per second as soon as we go into production across 60 APIs (some are single table and some are more tables). From db side we are confident that we can manage < 250ms max but based on the above test we see ORDS takes lot more time --

       

      To ensure no addition to the output size we have tested set of input json restricting to 10rows maximum output -- so we are believe the test condition is constant

       

      Any help will be highly appreciated

        • 1. Re: ORDS Performance issue
          3573277

          Please do let us know if more info is required, we can add to this post.

           

          BTW our db is running on RAC Mode across 2 node and as mentioned on ExaCC box. The tablespace of  ORDS_META_DATA and application tablespace are FLASH_CACHE DEFAULT

          • 2. Re: ORDS Performance issue
            3637696

            BTW the handler is preparing a select like (removed the actual table name)

             

            :whereClause is what gets passed as input json to this POST message

             

            l_sql = 'select * from table1 ';

            if :whereClause is not null

            then

               l_sql := l_sql || " where " || :whereClause;

            end if;

            l_sql := l_sql || '.... necessary offset condition and restricting to 10 rows'