Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6430

It is impossible to see if "filesort with small limit" optimization was used

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.12
    • 10.0.13
    • None
    • None

    Description

      We've ported "filesort with small limit" optimization from mysql-5.6 into MariaDB 10.0.

      The problem is, it is impossible to see whether that optimization is used or not.

      In MySQL, one can check this as follows

      • set optimizer_trace=1
      • SELECT // need to run SELECT, not EXPLAIN
      • select * from optimizer_trace

      And then look for something like this:

            "join_execution": {
      		 
      ...
      		 
                  "filesort_priority_queue_optimization": {
      		 
                    "limit": 10,
      		 
                    "rows_estimate": 198717,
      		 
                    "row_size": 215,
      		 
                    "memory_available": 262144,
      		 
                    "chosen": true
      		 
                  },

      In MariaDB, there is no optimizer_trace, so one can't see it at all.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          Assignee Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia made changes -
          Description We've ported "filesort with small limit" optimization from mysql-5.6 into MariaDB 10.0.

          The problem is, it is impossible to see whether that optimization is used or not.

          In MySQL, one can check this as follows
          * set optimizer_trace=1
          * SELECT // need to run SELECT, not EXPLAIN
          * select * from optimizer_trace

          And then look for something like this:
          {nformat}
                "join_execution": {
          ...
                      "filesort_priority_queue_optimization": {
                        "limit": 10,
                        "rows_estimate": 198717,
                        "row_size": 215,
                        "memory_available": 262144,
                        "chosen": true
                      },
          {noformat}

          In MariaDB, one can't see it at all.           		We've ported "filesort with small limit" optimization from mysql-5.6 into MariaDB 10.0.

          The problem is, it is impossible to see whether that optimization is used or not.

          In MySQL, one can check this as follows
          * set optimizer_trace=1
          * SELECT // need to run SELECT, not EXPLAIN
          * select * from optimizer_trace

          And then look for something like this:
          {noformat}
                "join_execution": {
          ...
                      "filesort_priority_queue_optimization": {
                        "limit": 10,
                        "rows_estimate": 198717,
                        "row_size": 215,
                        "memory_available": 262144,
                        "chosen": true
                      },
          {noformat}

          In MariaDB, one can't see it at all.
          psergei Sergei Petrunia made changes -
          Description We've ported "filesort with small limit" optimization from mysql-5.6 into MariaDB 10.0.

          The problem is, it is impossible to see whether that optimization is used or not.

          In MySQL, one can check this as follows
          * set optimizer_trace=1
          * SELECT // need to run SELECT, not EXPLAIN
          * select * from optimizer_trace

          And then look for something like this:
          {noformat}
                "join_execution": {
          ...
                      "filesort_priority_queue_optimization": {
                        "limit": 10,
                        "rows_estimate": 198717,
                        "row_size": 215,
                        "memory_available": 262144,
                        "chosen": true
                      },
          {noformat}

          In MariaDB, one can't see it at all.           		We've ported "filesort with small limit" optimization from mysql-5.6 into MariaDB 10.0.

          The problem is, it is impossible to see whether that optimization is used or not.

          In MySQL, one can check this as follows
          * set optimizer_trace=1
          * SELECT // need to run SELECT, not EXPLAIN
          * select * from optimizer_trace

          And then look for something like this:
          {noformat}
                "join_execution": {
          ...
                      "filesort_priority_queue_optimization": {
                        "limit": 10,
                        "rows_estimate": 198717,
                        "row_size": 215,
                        "memory_available": 262144,
                        "chosen": true
                      },
          {noformat}

          In MariaDB, there is no optimizer_trace, so one can't see it at all.
          psergei Sergei Petrunia made changes -
          Fix Version/s 10.0.13 [ 16300 ]
          Fix Version/s 10.0 [ 16000 ]
          psergei Sergei Petrunia made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 49715 ] MariaDB v3 [ 64328 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 64328 ] MariaDB v4 [ 148021 ]

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.