Overview

As the second feature of Index Advisor released in Couchbase server 6.5 (Developer Preview), Advisor function extends the scope from advising on single query to providing index recommendations for query workload and support on session handling. In this article, we will have a brief review on how it works in these two different ways.

Advisor Function On Workload

Advisor function works in the following steps:

    1. Take one single query or query workload consisting of multiple queries as input.
    2. Process each individual unique query with index recommendation.
    3. Merge and output the current/recommended indexes for the entire workload.

Syntax:

SELECT ADVISOR(“query”)

SELECT ADVISOR([“query1”, “query2”, “query3″…])

The example below shows the scenario in which query workload is from system:completed_requests:

Similar to ADVISE statement, the output of ADVISOR() function consists of three parts:

    • Current_used_indexes

This session summarizes the current used indexes with the corresponding list of query statements and the run count of each query.

    • Recommended_indexes

This session provides the information on the recommended indexes based on predicates in WHERE/ON clause for the entire workload.

    • Recommended_covering_indexes

Covering index recommendation applying to the query workload will be listed here.

Advisor Function on Session Handling

The session handling function allows users to set up the index advisor in advance for the incoming query workload in the following steps.

    • Define the queries of interest with specific properties.
    • Initialize a session to monitor and collect the query workload running on a cluster for a period of time.
    • Invoke index advisor to perform analysis on the collected query workload asynchronously afterwards.
    • Extract and process the index recommendations after the completion of the entire process.

Syntaxes for usage:

1. ADVISOR( { “action” : “start”,

           “profile”: ”john”,

           “response” : “3s”,     

           “duration” : “1m” ,

           “query_count” : 200 } )

    • “action”- users to set for the session to proceed: “start”, “stop”, “get”, “list”, “purge”, “abort”.
    • “profile”(optional) – the specified user profile whose query will be of interest. Will collect the queries run by anyone if not set.
    • “response”(optional) – the time threshold set for the query to run longer than, default setting is 0s.
    • “duration”(mandatory) – how long for the session to be running to collect query for.  Valid time units are “ns”, “us” (or “µs”), “ms”, “s”, “m”, “h”.
    • “query_count”(optional) – The upper limit for the number of queries the session will collect. The query_limit is 4000 per query node. If users expect to collect more than that, they need to modify the global setting before initializing the session by:

curl http://localhost:port/admin/settings -d  ‘{“completed-limit”:10000}’ -u user:password”

Once the session has been initialized successfully, a unique session name will be returned for reference as shown below, at the same time an asynchronous task of invoking index advisor to do analysis will be scheduled to run immediately after the completion of collecting workload.

2. ADVISOR( { “action” : “get”,

           “session”: “06fcdefe-f864-48f5-a79b-b2f3345a6745” } )

After the analysis process is completed, user can run another advisor() function to retrieve the index recommending results using the corresponding session name.

3. ADVISOR( { “action” : “purge”,

           “session”: “06fcdefe-f864-48f5-a79b-b2f3345a6745” } )

When the index recommending results for a particular session is not required any longer, user can choose to purge it for space efficiency.

The least recent session will also be flushed automatically once reaching the capacity of the cache.

4. ADVISOR({ “action”: “list”,

    “status”:”completed”})

        This function can provide the list of currently active sessions, currently completed sessions and all the existing sessions. The input for “status” can be one of “active”, “completed” and “all”.

The output information is from system:tasks_cache. Let’s take an example to understand the relevant information regarding Index Advisor:

    • class: this task comes from index advisor.
    • delay: how long the phase of collecting queries will last for.
    • name: session name same as the one above.
    • state: the status of the session: [“completed”, “schedule”, “running”].

 

5. ADVISOR( { “action” : “stop”,

           “session”: “06fcdefe-f864-48f5-a79b-b2f3345a6745” } )

User can take an early stop on collecting queries and bring forward the index analysis to run immediately.

6. ADVISOR( { “action” : “abort”,

           “session”: “06fcdefe-f864-48f5-a79b-b2f3345a6745” } )

Users can also cancel one session without running analysis.

 

Summary

Advisor function works with single query and multiple queries, and provide information on the current used indexes, recommended indexes and recommended covering indexes for the entire query workload.

Advisor session handling allows users to create a session to collect the queries which meet the specified requirements for a period of time and invoke index advisor to provide recommendations for them.

As there‘s plenty of room for improvement on the functionality and performance for both the functions, we will continue to explore and take feedbacks to optimize the design in the next step.

Author

Posted by Chang Liu

Software Engineer in N1QL Query Team

Leave a reply