Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-42445

[Advisor] No session advise recommendation provided for stopped session

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • Cheshire-Cat
    • 7.0.0
    • query
    • CentOS (VM)
      CB Version 7.0.0-3570
    • Untriaged
    • 1
    • Unknown

    Description

      I tried a simple session advisor as follow:

      1. start session
      2. list active session
      3. run query (which could need advise)
      4. stop session
      5. get session advice

      No advises are returned for the given session. Note that I cannot see completed session because of MB-42444.

      cbq> SELECT ADVISOR(\{'action': 'start', 'duration': '5m', 'query_count': 1 }) ;
      {
          "requestID": "143de145-f344-4493-8d93-9ea15fb3ff5d",
          "signature": {
              "$1": "object"
          },
          "results": [
          {
              "$1": {
                  "session": "757f4d97-f450-4078-ab0c-7207f6a990fc"
              }
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "10.267871ms",
              "executionTime": "10.199061ms",
              "resultCount": 1,
              "resultSize": 95,
              "serviceLoad": 12
          }
      }
       
      cbq> SELECT ADVISOR(\{'action': 'list', 'status': 'active'}) ;
      {
          "requestID": "265cfaff-aa3e-4e8f-8b2b-779448f4954f",
          "signature": {
              "$1": "object"
          },
          "results": [
          {
              "$1": [
                  {
                      "tasks_cache": {
                          "class": "advisor",
                          "delay": "5m0s",
                          "id": "ad93956c-bf8b-5fd1-998b-995fdd441bc1",
                          "name": "757f4d97-f450-4078-ab0c-7207f6a990fc",
                          "node": "127.0.0.1:8091",
                          "state": "scheduled",
                          "subClass": "analyze",
                          "submitTime": "2020-11-02 19:51:15.241552153 +0000 UTC m=+157394.156729069"
                      }
                  }
              ]
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "30.945167ms",
              "executionTime": "30.754443ms",
              "resultCount": 1,
              "resultSize": 550,
              "serviceLoad": 12
          }
      }
       
      cbq> SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';
      {
          "requestID": "6151e34a-aec0-442f-a9cf-aeb7623ac9dc",
          "signature": {
              "city": "json"
          },
          "results": [
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "442.810494ms",
              "executionTime": "442.755494ms",
              "resultCount": 0,
              "resultSize": 0,
              "serviceLoad": 12
          }
      }
       
      cbq> SELECT ADVISOR(\{'action': 'stop', 'session': '757f4d97-f450-4078-ab0c-7207f6a990fc'}) ;
      {
          "requestID": "6d948543-3a53-4ae4-a6ef-75870f599753",
          "signature": {
              "$1": "object"
          },
          "results": [
          {
              "$1": []
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "34.295689ms",
              "executionTime": "34.234517ms",
              "resultCount": 1,
              "resultSize": 24,
              "serviceLoad": 12
          }
      }
       
      cbq> SELECT ADVISOR(\{'action': 'list', 'status': 'active'}) ;
      {
          "requestID": "30ebcf85-d226-4ea9-89e8-c04d4eeb2ef3",
          "signature": {
              "$1": "object"
          },
          "results": [
          {
              "$1": []
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "27.877528ms",
              "executionTime": "27.825136ms",
              "resultCount": 1,
              "resultSize": 24,
              "serviceLoad": 12
          }
      }
       
      cbq> SELECT ADVISOR(\{'action': 'get', 'session': '757f4d97-f450-4078-ab0c-7207f6a990fc'}) ;
      {
          "requestID": "76e188c1-0af4-4f4d-a401-e44286e88a43",
          "signature": {
              "$1": "object"
          },
          "results": [
          {
              "$1": []
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "27.387279ms",
              "executionTime": "27.264943ms",
              "resultCount": 1,
              "resultSize": 24,
              "serviceLoad": 12
          }
      }
      

      Advise on query executed shows:

      cbq> ADVISE SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';
      {
          "requestID": "6de2c0cb-b6c5-47d1-8846-5afcab02c69c",
          "signature": "json",
          "results": [
          {
              "#operator": "Advise",
              "advice": {
                  "#operator": "IndexAdvice",
                  "adviseinfo": {
                      "current_indexes": [
                          {
                              "index_statement": "CREATE INDEX def_city ON `travel-sample`(`city`)",
                              "keyspace_alias": "travel-sample"
                          }
                      ],
                      "recommended_indexes": {
                          "covering_indexes": [
                              {
                                  "index_statement": "CREATE INDEX adv_lower_city_country_city ON `travel-sample`(lower((`city`)),`country`,`city`)",
                                  "keyspace_alias": "travel-sample"
                              }
                          ],
                          "indexes": [
                              {
                                  "index_statement": "CREATE INDEX adv_lower_city_country ON `travel-sample`(lower((`city`)),`country`)",
                                  "keyspace_alias": "travel-sample",
                                  "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing."
                              }
                          ]
                      }
                  }
              },
              "query": "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';"
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "5.005999ms",
              "executionTime": "4.945675ms",
              "resultCount": 1,
              "resultSize": 1320,
              "serviceLoad": 12
          }
      }
      

       

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            Marking this as a must pass since the basic functionality is not working as intended.

            mihir.kamdar Mihir Kamdar (Inactive) added a comment - Marking this as a must pass since the basic functionality is not working as intended.

            Build couchbase-server-7.0.0-3704 contains query commit 9c597f3 with commit message:
            MB-42445 Advisor() evaluates its own statement

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-3704 contains query commit 9c597f3 with commit message: MB-42445 Advisor() evaluates its own statement

            Build couchbase-server-7.0.0-3706 contains query commit b3332ad with commit message:
            MB-42445 Add new ADVISOR Phase Operator at execution time instead of prepare time

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-3706 contains query commit b3332ad with commit message: MB-42445 Add new ADVISOR Phase Operator at execution time instead of prepare time

            Verified on Enterprise Edition 7.0.0 build 3706 ... got recommendation on stopped session:

            cbq> SELECT ADVISOR({'action': 'get', 'session':'51a2a21f-cdb9-4fd3-9870-6f26e5da19bc'});
            {
                "requestID": "e8df5a62-4ae6-4d2e-917b-c5a78b143eba",
                "signature": {
                    "$1": "object"
                },
                "results": [
                {
                    "$1": [
                        [
                            {
                                "current_used_indexes": [
                                    {
                                        "index": "CREATE INDEX def_city ON `travel-sample`(`city`)",
                                        "statements": [
                                            {
                                                "run_count": 1,
                                                "statement": "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';"
                                            }
                                        ]
                                    }
                                ],
                                "recommended_covering_indexes": [
                                    {
                                        "index": "CREATE INDEX adv_lower_city_country_city ON `travel-sample`(lower((`city`)),`country`,`city`)",
                                        "statements": [
                                            {
                                                "run_count": 1,
                                                "statement": "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';"
                                            }
                                        ]
                                    }
                                ],
                                "recommended_indexes": [
                                    {
                                        "index": "CREATE INDEX adv_lower_city_country ON `travel-sample`(lower((`city`)),`country`)",
                                        "statements": [
                                            {
                                                "run_count": 1,
                                                "statement": "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';"
                                            }
                                        ]
                                    }
                                ]
                            }
                        ]
                    ]
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "17.854586ms",
                    "executionTime": "17.808375ms",
                    "resultCount": 1,
                    "resultSize": 1822,
                    "serviceLoad": 12
                }
            }
            

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on Enterprise Edition 7.0.0 build 3706 ... got recommendation on stopped session: cbq> SELECT ADVISOR({ 'action' : 'get' , 'session' : '51a2a21f-cdb9-4fd3-9870-6f26e5da19bc' }); { "requestID" : "e8df5a62-4ae6-4d2e-917b-c5a78b143eba" , "signature" : { "$1" : "object" }, "results" : [ { "$1" : [ [ { "current_used_indexes" : [ { "index" : "CREATE INDEX def_city ON `travel-sample`(`city`)" , "statements" : [ { "run_count" : 1 , "statement" : "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';" } ] } ], "recommended_covering_indexes" : [ { "index" : "CREATE INDEX adv_lower_city_country_city ON `travel-sample`(lower((`city`)),`country`,`city`)" , "statements" : [ { "run_count" : 1 , "statement" : "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';" } ] } ], "recommended_indexes" : [ { "index" : "CREATE INDEX adv_lower_city_country ON `travel-sample`(lower((`city`)),`country`)" , "statements" : [ { "run_count" : 1 , "statement" : "SELECT city FROM `travel-sample` WHERE lower(city) = 'sanfrancisco' and country = 'France';" } ] } ] } ] ] } ], "status" : "success" , "metrics" : { "elapsedTime" : "17.854586ms" , "executionTime" : "17.808375ms" , "resultCount" : 1 , "resultSize" : 1822 , "serviceLoad" : 12 } }

            People

              pierre.regazzoni Pierre Regazzoni
              pierre.regazzoni Pierre Regazzoni
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty