Uploaded image for project: 'PuppetDB'
  1. PuppetDB
  2. PDB-470

Investigate and implement setStatementsCacheSize for repetitive SQL performance

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: PDB 2.0.0
    • Component/s: None
    • Labels:
    • Template:
    • Story Points:
      2
    • Sprint:
      20140226 to 20140305

      Description

      It seems the setting setStatementsCacheSize for BoneCPConfig will enable prepared statements cache.

      This seems to have a great effect on our insert time, especially at high load.

      So far I've tested this 3 times and found that the number of seconds spent doing inserts is almost halved.

      The proposal is to implement this setting as a user configurable item and set it to a reasonable default perhaps. We should confirm the numbers I've seen here first, and also do some more research on the feature to understand its side-effects. We might also want to consider just doing our own prepared statement handling and the value of that versus this approach, although this might be a good phase 1 regardless.

      The patch I was working with is here:

      --- a/src/com/puppetlabs/jdbc.clj
      +++ b/src/com/puppetlabs/jdbc.clj
      @@ -300,6 +300,7 @@ (defn make-connection-pool
                                 (.setStatisticsEnabled stats)
                                 (.setIdleMaxAgeInMinutes (pl-time/to-minutes conn-max-age))
                                 (.setIdleConnectionTestPeriodInMinutes (pl-time/to-minutes conn-keep-alive
      +                          (.setStatementsCacheSize 1000)
                                 ;; paste the URL back together from parts.
                                 (.setJdbcUrl (str "jdbc:" subprotocol ":" subname))
      

      My test code for submitting a random catalog is here:

      #!/usr/bin/env ruby
       
      require 'rubygems'
      require 'net/http'
      require 'uri'
      require 'json'
      require 'pp'
       
      def replace_catalog(catalog)
        uri = URI.parse("http://localhost:8080/v3/commands")
        http = Net::HTTP.new(uri.host, uri.port)
       
        request = Net::HTTP::Post.new(uri.request_uri)
        request.set_form_data(
          {
            "payload" => {
              "command" => "replace catalog",
              "version" => 3,
              "payload" => catalog.to_json,
            }.to_json
          }
        )
        response = http.request(request)
        puts response.body
      end
       
      def random_string
        (0...50).map { ('a'..'z').to_a[rand(26)] }.join
      end
       
      def random_resource
        {
          "type" => "Class",
          "title" => random_string,
          "exported" => false,
          "file" => "asdf",
          "line" => 3,
          "tags" => ["asdf"],
          "parameters" => {
          }
        }
      end
       
      payload1 = {
        "metadata" => { "api_version" => 1 },
        "data" => {
          "name" => "host-0",
          "version" => "3",
          "transaction-uuid" => "foo",
          "edges" => [
            {
              "source" => {
                "type" => "Class",
                "title" => "foo",
              },
              "target" => {
                "type" => "File",
                "title" => "foo",
              },
              "relationship" => "contains",
            },
          ],
          "resources" => [
            {
              "type" => "Class",
              "title" => "foo",
              "exported" => false,
              "file" => "asdf",
              "line" => 3,
              "tags" => ["asdf"],
              "parameters" => {
              },
            },
            {
              "type" => "File",
              "title" => "foo",
              "exported" => false,
              "file" => "asdf",
              "line" => 3,
              "tags" => ["asdf"],
              "parameters" => {
              },
            },
          ]
        }
      }
       
      10000.times do
        payload1["data"]["resources"].push random_resource
      end
       
      #pp payload1
       
      replace_catalog(payload1)
      

        Attachments

          Issue Links

            Activity

              jsd-sla-details-panel

                People

                • Assignee:
                  ken Ken Barber
                  Reporter:
                  ken Ken Barber
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Zendesk Support