Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-5321

The optimization of the Query Analytics parser code for PostgreSQL queries allowed us to reduce the processor and memory resources consumption by 1-5%, and the parsing time of an individual query by 30-40%

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Done
    • Priority: Medium
    • Resolution: Done
    • Affects Version/s: 2.2.0
    • Fix Version/s: 2.2.2
    • Component/s: PMM Agent
    • Story Points:
      3
    • Sprint:
      Platform Sprint 8
    • Needs Review:
      Yes
    • Needs QA:
      Yes
    • Needs Packaging:
      No
    • Needs Doc:
      No

      Description

      https://github.com/percona/pmm-agent/pull/146 added benchmarks for PostgreSQL query parser and a long sysbench-generated query example. Parsing that long query takes 800ms and 245MB on my machine. Quick profiling shows that most of that time is spent not on cgo function doing SQL-to-JSON-AST conversion, but on Go side doing JSON decoding.

      We should improve performance and memory consumption.

      As an idea, we can try to decode into map[string]json.RawMessage and work with that map step-by-step instead of decoding the whole AST in a single pass.

      Alternatively, we may want to generate decoders for all github.com/lfittl/pg_query_go/nodes types with https://github.com/mailru/easyjson, https://github.com/francoispqt/gojay or something else.

        Attachments

        1. cpu.out
          21 kB
        2. image (3).png
          image (3).png
          261 kB
        3. mem.out
          7 kB
        4. Screenshot from 2020-01-22 15-01-25.png
          Screenshot from 2020-01-22 15-01-25.png
          138 kB
        5. Screenshot from 2020-01-22 16-40-03.png
          Screenshot from 2020-01-22 16-40-03.png
          32 kB

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              alexey.palazhchenko Alexey Palazhchenko
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 7 hours, 35 minutes
                  7h 35m

                    Smart Checklist