11 Replies Latest reply on Sep 7, 2016 8:31 AM by Kiran Pawar

    Image delivery: REST vs PL/SQL

    Scott Wesley

      I have run a load test comparison for delivery images via REST instead of apex_util.get_blob_file_src, thanks to the lead from Kris

      Kris' blog: Using RESTful to deliver Images for APEX

      However, not only did REST seem to be slower, it also raised errors during peak load.

       

      I'm not sure if this is a middle tier config issue, or something somewhere else, so here are the facts.

       

      APEX 5.0.1.00.06

      ORDS 3.0.6.176.08.46

      DB  12.1.0.2.0

      Tomcat 8

       

      Our engineer ran a load test over two urls for the same image using https://www.paessler.com/tools/webstress

       

      PL/SQL Example

       

      Our current method uses something similar to the download_my_file function described here

      https://docs.oracle.com/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#CHDCGIEF

      using wpg_docload.download_file.

       

      Hence the URL is

      http://zzzzzz:8080/vbs/RENDER_PRODUCT_IMG?p_id=431

       

      As a side note, we also have examples like this we're ready to replace with REST.

      ,decode(nvl(dbms_lob.getlength(i.image),0),0,null,
          '<img src="'||apex_util.get_blob_file_src('P10_IMAGE',i.image_id)||'" />') as detail_img
      


      Here is the performance report, showing steady increase in response time as # requests increases.

      plsql_load.png

       

      Final stats

      plsql_data.png

       

      REST Example

      Using the URL

      http://zzzzz:8080/vbs/rest/image/product/431

      Errors start to appear as the load gets high.

      rest_load.png

      Final stats

      rest_data.png

      Here is a concise log example where a failed and successful request was returned, it was a 404.

       

      Webserver Stress Tool - Logfile for User #89
      18/08/2016 12:55:09 PM: User #89 Click #1: Waiting for global click delay: 1s
      18/08/2016 12:55:10 PM: User #89 Click #1: HTTP request & TCP/IP socket creation: 18 ms
      18/08/2016 12:55:10 PM: User #89 Click #1: ** Click #1 started, used URL #1 (Restful), from IP=0.0.0.0
      18/08/2016 12:55:10 PM: User #89 Click #1: Request 1 HTTP State: Initialized (0 ms)
      18/08/2016 12:55:10 PM: User #89 Click #1: Request 1 HTTP State: DNS Lookup begin (0 ms)
      18/08/2016 12:55:10 PM: User #89 Click #1: Request 1 HTTP State: DNS Lookup done (0 ms)
      18/08/2016 12:55:10 PM: User #89 Click #1: Request 1 HTTP State: Connected (2 ms)
      18/08/2016 12:55:10 PM: User #89 Click #1: Request 1 HTTP State: Waiting for header (2 ms)
      18/08/2016 12:55:17 PM: User #89 Click #1: Request 1 HTTP State: Receiving Header (6,860 ms) (=Time to First Byte)
      18/08/2016 12:55:17 PM: User #89 Click #1: Request 1 HTTP State: Waiting for body (6860 ms)
      18/08/2016 12:55:17 PM: User #89 Click #1: Request 1 HTTP State: Done (6860 ms)
      18/08/2016 12:55:17 PM: User #89 Click #1: CLICK-Request 1: Time=6,864 ms, TFB=6,860 ms, Bytes=4,777, HTTP-StatusCode=404 (Not Found) URL #1=http://zzzzz:8080/vbs/rest/image/product/431 Not Found
      18/08/2016 12:55:17 PM: User #89 Click #1: !FAILED! All requests of click done, but at least one request failed with HTTP result 404 (=Not Found), (6,864 ms)  
      18/08/2016 12:55:17 PM: User #89 Click #2: Waiting for global click delay: 1s
      18/08/2016 12:55:18 PM: User #89 Click #2: HTTP request & TCP/IP socket creation: 31 ms
      18/08/2016 12:55:18 PM: User #89 Click #2: ** Click #2 started, used URL #1 (Restful), from IP=0.0.0.0
      18/08/2016 12:55:18 PM: User #89 Click #2: Request 2 HTTP State: Initialized (0 ms)
      18/08/2016 12:55:18 PM: User #89 Click #2: Request 2 HTTP State: DNS Lookup begin (0 ms)
      18/08/2016 12:55:18 PM: User #89 Click #2: Request 2 HTTP State: DNS Lookup done (0 ms)
      18/08/2016 12:55:18 PM: User #89 Click #2: Request 2 HTTP State: Connected (1 ms)
      18/08/2016 12:55:18 PM: User #89 Click #2: Request 2 HTTP State: Waiting for header (1 ms)
      18/08/2016 12:55:22 PM: User #89 Click #2: Request 2 HTTP State: Receiving Header (3,619 ms) (=Time to First Byte)
      18/08/2016 12:55:22 PM: User #89 Click #2: Request 2 HTTP State: Waiting for body (3624 ms)
      18/08/2016 12:55:22 PM: User #89 Click #2: Request 2 HTTP State: Done (3633 ms)
      18/08/2016 12:55:22 PM: User #89 Click #2: CLICK-Request 2: Time=3,633 ms, TFB=3,619 ms, Bytes=44,420, HTTP-StatusCode=200 (OK)
      18/08/2016 12:55:22 PM: User #89 Click #2: !Success! All requests of click done (3,633 ms)  
      18/08/2016 12:55:22 PM: User #89 Click #3: Waiting for global click delay: 1s
      18/08/2016 12:55:23 PM: User #89 Click #3: HTTP request & TCP/IP socket creation: 77 ms
      18/08/2016 12:55:23 PM: User #89 Click #3: ** Click #3 started, used URL #1 (Restful), from IP=0.0.0.0
      18/08/2016 12:55:23 PM: User #89 Click #3: Request 3 HTTP State: Initialized (0 ms)
      18/08/2016 12:55:23 PM: User #89 Click #3: Request 3 HTTP State: DNS Lookup begin (0 ms)
      18/08/2016 12:55:23 PM: User #89 Click #3: Request 3 HTTP State: DNS Lookup done (2 ms)
      18/08/2016 12:55:23 PM: User #89 Click #3: Request 3 HTTP State: Connected (5 ms)
      18/08/2016 12:55:23 PM: User #89 Click #3: Request 3 HTTP State: Waiting for header (8 ms)
      18/08/2016 12:55:28 PM: User #89 Click #3: Request 3 HTTP State: Receiving Header (5,696 ms) (=Time to First Byte)
      18/08/2016 12:55:28 PM: User #89 Click #3: Request 3 HTTP State: Waiting for body (5696 ms)
      18/08/2016 12:55:28 PM: User #89 Click #3: Request 3 HTTP State: Done (5700 ms)
      18/08/2016 12:55:28 PM: User #89 Click #3: CLICK-Request 3: Time=5,700 ms, TFB=5,696 ms, Bytes=44,420, HTTP-StatusCode=200 (OK)
      18/08/2016 12:55:28 PM: User #89 Click #3: !Success! All requests of click done (5,700 ms)  
      18/08/2016 12:55:28 PM: Writing User Log
      18/08/2016 12:55:29 PM: User passed away
      

       

      Here is an overlay of the performance, as best I got with MSPaint.

      The lower red line is the PL/SQL URL, so measured performance waned before errors began.

      load_overlay.png

      Where should my concerns lie? How can I diagnose/test this further?

      Have I missed anything?

       

      Edit: This was running on Tomcat. I need to verify the config params have been adjusted to match our existing WLS.

      Using same test on existing WLS yielded no errors.

       

      Cheers,

      Scott

        • 1. Re: Image delivery: REST vs PL/SQL
          Capt. Egg

          What were the errors? I'm curious if it's related to your connection pool settings for ORDS, as I still have an outstanding query about that.

           

          If you have time, could you perhaps try the connection pool settings I have listed in my post and see what the performance is and also if the errors go away... Connection Pool settings for production environment

          • 2. Re: Image delivery: REST vs PL/SQL
            Capt. Egg

            I note the URL text RENDER_PRODUCT_IMG suggesting that these images aren't going to need to have any security restrictions. In exchange for helping me understand ideal connection pool settings, I can offer you an alternative solution to either PL/SQL or REST for delivery of these images.

             

            Our APEX static images directory (/i/) is served up by a separate web server in production environment. In our case, it's NGINX based, heavily tuned for delivery and caching of static files, but you could use Apache or any other web server that you are familiar with and no doubt you'll experience performance gains (probably even a java based one).

             

            We gave the database write access to a directory within /i/ dedicated to storing images for this particular application. When the user uploads an image file within the application, the database simply writes the image out to that directory. You can inject an ID into the file name to ensure it's unique within that directory. I was impressed with this solution, it's a very effective method of reducing load on the database for a task that's certainly not it's forte. This all assumes that access to the image files won't need to be restricted in any way though.

            • 3. Re: Image delivery: REST vs PL/SQL
              Scott Wesley

              Here are the errors reported from Tomcat

              19-Aug-2016 10:25:56.089 SEVERE [http-nio-8080-exec-208] oracle.dbtools.http.errors.ErrorPageFilter.log The connection pool named: apex_pu is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use
              
              oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: The connection pool named: apex_pu is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use
              

               

              (Redacted) settings from ORDS

              <?xml version="1.0" encoding="UTF-8" standalone="no"?>
              <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
              <properties>
              <comment>Saved on Fri Aug 12 16:26:29 AWST 2016</comment>
              <entry key="cache.caching">false</entry>
              <entry key="cache.directory">/tmp/apex/cache</entry>
              <entry key="cache.duration">days</entry>
              <entry key="cache.expiration">7</entry>
              <entry key="cache.maxEntries">500</entry>
              <entry key="cache.monitorInterval">60</entry>
              <entry key="cache.procedureNameList"/>
              <entry key="cache.type">lru</entry>
              <entry key="debug.debugger">false</entry>
              <entry key="debug.printDebugToScreen">false</entry>
              <entry key="error.keepErrorMessages">true</entry>
              <entry key="error.maxEntries">50</entry>
              <entry key="jdbc.DriverType">thin</entry>
              <entry key="jdbc.InactivityTimeout">1800</entry>
              <entry key="jdbc.InitialLimit">3</entry>
              <entry key="jdbc.MaxConnectionReuseCount">1000</entry>
              <entry key="jdbc.MaxLimit">10</entry>
              <entry key="jdbc.MaxStatementsLimit">10</entry>
              <entry key="jdbc.MinLimit">1</entry>
              <entry key="jdbc.statementTimeout">900</entry>
              <entry key="log.logging">false</entry>
              <entry key="log.maxEntries">50</entry>
              <entry key="misc.compress"/>
              <entry key="misc.defaultPage">apex</entry>
              <entry key="security.disableDefaultExclusionList">false</entry>
              <entry key="security.maxEntries">2000</entry>
              </properties>
              

               

              Your comment regarding the use of /i/ is interesting. It's a concept that has been considered here.

              There's a few uses of images here, and I'm not sure that will be appropriate for most cases. The images under that function are essentially public domain, but most others need securing.

              • 4. Re: Image delivery: REST vs PL/SQL
                Capt. Egg

                That's certainly the same error we were getting, also on Tomcat. It's interesting that you don't get that error using WebLogic. I selected Tomcat from the list of supported servers only because it was easier to install and maintain, I'll try on WLS instead.

                • 5. Re: Image delivery: REST vs PL/SQL
                  Scott Wesley

                  The only difference in WLS was statementTimeout as 300, not 900. And debugger/logging is on.

                  I'm not sure how much (virtual) hardware resources would impact this result.

                   

                  We have other issues with one of our WLS instances - a semi-regular 100% CPU spin, fixed by service restart. Still working that one out... but that's also on 3.0.0

                  • 6. Re: Image delivery: REST vs PL/SQL
                    Capt. Egg

                    We don't want to have to look after another web server technology if we can avoid it. I'd be interested to see how performance compares when connection pooling is essentially disabled as per my post. I think I could live with a mild performance hit in exchange for reliability and having Redhat maintain the web server for me.

                    • 7. Re: Image delivery: REST vs PL/SQL
                      Scott Wesley

                      Why would you want to disable connection pooling? Wouldn't this scale even less?

                      Reliability over performance for sure, but that seems drastic.

                      • 8. Re: Image delivery: REST vs PL/SQL
                        Capt. Egg

                        Yeah, I have no idea. It has never been a consideration for me in the past when we used mod_plsql. I'll experiment with that load testing tool you linked to.

                        • 9. Re: Image delivery: REST vs PL/SQL
                          Scott Wesley

                          We increased MaxLimit to 100 and received no errors.

                          rest_limit1000.jpg

                          Will test further.

                          • 10. Re: Image delivery: REST vs PL/SQL
                            Scott Wesley

                            What sort of behaviour would people expect the middle tier to have when the number of active sessions matches the MaxLimit parameter?

                             

                            Would this differ based on application server?

                            • 11. Re: Image delivery: REST vs PL/SQL
                              Kiran Pawar

                              Hi Scott,

                              swesley_perth wrote:

                               

                              I have run a load test comparison for delivery images via REST instead of apex_util.get_blob_file_src, thanks to the lead from Kris

                              Kris' blog: Using RESTful to deliver Images for APEX

                              However, not only did REST seem to be slower, it also raised errors during peak load.

                               

                              I'm not sure if this is a middle tier config issue, or something somewhere else, so here are the facts.

                               

                              APEX 5.0.1.00.06

                              ORDS 3.0.6.176.08.46

                              DB 12.1.0.2.0

                              Tomcat 8

                               

                              Our engineer ran a load test over two urls for the same image using https://www.paessler.com/tools/webstress

                               

                              Where should my concerns lie? How can I diagnose/test this further?

                              Have I missed anything?

                               

                              Edit: This was running on Tomcat. I need to verify the config params have been adjusted to match our existing WLS.

                              Using same test on existing WLS yielded no errors.

                                   To enable the debugging enable the following two debug properties in defaults.xml:

                              <entry key="debug.debugger">true</entry> 
                              <entry key="debug.printDebugToScreen">true</entry>
                              

                                   The second entry enables printing debug to screen.

                               

                                   Yes. This is a middle tier config issue. More details on how to config ORDS+Tomcat to efficiently deliver static content is described in the following blog posts by Morten Braten:

                                   NOTE: Also look at the ORDS configuration points mentioned in the blog post.

                                   Additionally, following presentation by Dietmar Aust will also help you regarding middle tier configuration with ORDS+Tomcat:

                               

                              Regards,

                              Kiran

                              Kiran