Uploaded image for project: 'Lucee Development'
  1. LDEV-991

SQL Server as a DB causes Lucee to spin up many threads

    Details

    • Type: Bug
    • Status: Deployed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 4.5.2.017
    • Fix Version/s: 5.0.1.73
    • Labels:
      None

      Description

      Background:

      The Microsoft SQL Server Driver is a JDBC 4 compliant driver and therefore implements the Statement.setQueryTimeout() method. It's implementation of the query timeout functionality includes spinning up a thread for each statement executed when the query does not return after the specified time the thread will kill the query. I don't like this implementation, but it is what it is.

      The problem with Lucee is that in Query.java at the beginning of doStartTag() we set the timeout of all queries to be the remaining request timeout if it is not set. This spins up a thread for every query. We tested jTDS (out of support, no longer modern) and it was super fast compared to Microsoft. I wonder if this is one reason?

      Regardless, We had a problem recently where our test servers were spinning up to 20,000 of these statement timeout threads. It wasn't a condition that always occurred and in a way it does not make sense. because the threads should die after the query completes (timed out or not). and if the query hasn't timed out the request should wait. We didn't have 20k requests, so it was clear the the statements were executing correctly and the threads not dying.

      Either way, one way we found made the issue work considerably better is if we commented out the code at the beginning of Query.java that sets the timeout to be the remaining request timeout.

      //timeout not defined
      if(timeout==null || ((int)timeout.getSeconds())<=0)

      { // not set this.timeout=PageContextUtil.remainingTime(pageContext,true); }

      // timeout bigger than remaining time
      else

      { TimeSpan remaining = PageContextUtil.remainingTime(pageContext,true); if(timeout.getSeconds()>remaining.getSeconds()) timeout=remaining; }

      Even if there is a different problem. I don't like a thread being spun up for each query. It slows things down.

      This was put in for LDEV-471 Deployed . There are lots of changes that went in for Postgres, From my read of the Postgres driver docs it says that it doesn't require setQueryTimeout to be called, which it won't be called if you remove the code.

      I may be missing something, but that is our issue.

        Attachments

          Activity

            People

            • Assignee:
              michaeloffner Michael Offner
              Reporter:
              colby.litnak Colby Litnak
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: