[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > [Step-by-step guides](index.md) > Logs and monitoring > Performance diagnostics

# Performance diagnostics in Managed Service for MySQL®

Managed Service for MySQL® has a built-in tool for collecting session and query statistics. These metrics can be useful when [analyzing the cluster’s performance and optimizing its settings](../tutorials/profiling-mmy.md).

## Enabling statistics collection {#activate-stats-collector}

{% list tabs group=instructions %}

- Management console {#console}

    When [creating a cluster](cluster-create.md) or [updating its settings](update.md#change-additional-settings):

    1. Enable **Statistics sampling** (disabled by default).
    1. Set the **Sessions sampling interval** and **Statements sampling interval**. Valid values:
        
        * For sessions: From `5` to `86400` seconds.
        * For queries: From `60` to `86400` seconds.

- CLI {#cli}

    If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

    The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

    To enable and configure statistics collection, provide the `--performance-diagnostics` parameter in the `cluster update` command:

    ```bash
    yc managed-mysql cluster update <cluster_name_or_ID> \
       ...
       --performance-diagnostics enabled=true,`
                                `sessions-sampling-interval=<session_sampling_interval>,`
                                `statements-sampling-interval=<statement_sampling_interval> \
        ...
    ```

    Allowed values:

    * `sessions-sampling-interval`: From `5` to `86400` seconds.
    * `statements-sampling-interval`: From `60` to `86400` seconds.

- Terraform {#tf}

    1. Open the current Terraform configuration file with the infrastructure plan.

        For more on how to create this file, see [Creating a cluster](cluster-create.md).

    1. To enable and configure statistics collection, add the `performance_diagnostics` section to the cluster configuration:

        ```hcl
        resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
          ...
          performance_diagnostics {
            enabled = true
            sessions_sampling_interval = <session_sampling_interval>
            statements_sampling_interval = <statement_sampling_interval>
          }
          ...
        }
        ```

        Where:
        
        * `enabled`: Enables statistics collection, `true` or `false`.
        * `sessions_sampling_interval`: Session sampling interval, from `5` to `86400` seconds.
        * `statements_sampling_interval`: Statement sampling interval, from `60` to `86400` seconds.

    1. Make sure the settings are correct.

        1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure.
        1. Run this command:
        
           ```bash
           terraform validate
           ```
        
           Terraform will show any errors found in your configuration files.

    1. Confirm updating the resources.

        1. Run this command to view the planned changes:
        
           ```bash
           terraform plan
           ```
        
           If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
        
        1. If everything looks correct, apply the changes:
           1. Run this command:
        
              ```bash
              terraform apply
              ```
        
           1. Confirm updating the resources.
           1. Wait for the operation to complete.

    {% note warning "Timeouts" %}
    
    The Terraform provider sets the following timeouts for Managed Service for MySQL® cluster operations:
    
    * Creating a cluster, including by restoring it from a backup: 15 minutes.
    * Updating a cluster, including the MySQL® version update: 60 minutes.
    * Deleting a cluster: 15 minutes.
    
    Operations exceeding the timeout are aborted.
    
    {% cut "How do I change these limits?" %}
    
    Add the `timeouts` section to the cluster description, such as the following:
    
    ```hcl
    resource "yandex_mdb_mysql_cluster" "<cluster_name>" {
      ...
      timeouts {
        create = "1h30m" # 1 hour 30 minutes
        update = "2h"    # 2 hours
        delete = "30m"   # 30 minutes
      }
    }
    ```
    
    {% endcut %}
    
    {% endnote %}

- REST API {#api}

    1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

        ```bash
        export IAM_TOKEN="<IAM_token>"
        ```

    1. To enable collection of statistics when creating a cluster:

        1. Use the [Cluster.create](../api-ref/Cluster/create.md) method and add the `configSpec.performanceDiagnostics` parameter to the [cURL cluster creation command](cluster-create.md#create-cluster):

            ```bash
            curl \
                --request POST \
                --header "Authorization: Bearer $IAM_TOKEN" \
                --header "Content-Type: application/json" \
                --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters' \
                --data '{
                          "configSpec": {
                            "performanceDiagnostics": {
                              "enabled": <enable_statistics_collection>,
                              "sessionsSamplingInterval": "<session_sampling_interval>",
                              "statementsSamplingInterval": "<statement_sampling_interval>"
                            },
                            ...
                          },
                          ...
                        }'
            ```

            Where `configSpec.performanceDiagnostics` is the statistics collection settings:

            * `enabled`: Enables statistics collection, `true` or `false`.
            * `sessionsSamplingInterval`: Session sampling interval, from `5` to `86400` seconds.
            * `statementsSamplingInterval`: Statement sampling interval, from `60` to `86400` seconds.

        1. Check the [server response](../api-ref/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

    1. To enable collection of statistics when updating an existing cluster:

        1. Call the [Cluster.update](../api-ref/Cluster/update.md) method, e.g., via the following [cURL](https://curl.se/) request:

            {% note warning %}
            
            The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `updateMask` parameter as a single comma-separated string.
            
            {% endnote %}

            ```bash
            curl \
                --request PATCH \
                --header "Authorization: Bearer $IAM_TOKEN" \
                --header "Content-Type: application/json" \
                --url 'https://mdb.api.cloud.yandex.net/managed-mysql/v1/clusters/<cluster_ID>' \
                --data '{
                          "updateMask": "configSpec.performanceDiagnostics",
                          "configSpec": {
                            "performanceDiagnostics": {
                              "enabled": <enable_statistics_collection>,
                              "sessionsSamplingInterval": "<session_sampling_interval>",
                              "statementsSamplingInterval": "<statement_sampling_interval>"
                            }
                          }
                        }'
            ```

            Where `configSpec.performanceDiagnostics` is the statistics collection settings:

            * `enabled`: Enables statistics collection, `true` or `false`.
            * `sessionsSamplingInterval`: Session sampling interval, from `5` to `86400` seconds.
            * `statementsSamplingInterval`: Statement sampling interval, from `60` to `86400` seconds.

        1. Check the [server response](../api-ref/Cluster/update.md#yandex.cloud.operation.Operation) to make sure your request was successful.

- gRPC API {#grpc-api}

    1. [Get an IAM token for API authentication](../api-ref/authentication.md) and put it into an environment variable:

        ```bash
        export IAM_TOKEN="<IAM_token>"
        ```

    1. Clone the [cloudapi](https://github.com/yandex-cloud/cloudapi) repository:
       
       ```bash
       cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi
       ```
       
       Below, we assume that the repository contents reside in the `~/cloudapi/` directory.
    1. To enable the collection of statistics when creating a cluster:

        1. Use the [ClusterService/Create](../api-ref/grpc/Cluster/create.md) method and add the `config_spec.performance_diagnostics` parameter to the [gRPCurl cluster creation command](cluster-create.md#grpc-api):

            ```bash
            grpcurl \
                -format json \
                -import-path ~/cloudapi/ \
                -import-path ~/cloudapi/third_party/googleapis/ \
                -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/cluster_service.proto \
                -rpc-header "Authorization: Bearer $IAM_TOKEN" \
                -d '{
                      "config_spec": {
                        "performance_diagnostics": {
                          "enabled": <enable_statistics_collection>,
                          "sessions_sampling_interval": "<session_sampling_interval>",
                          "statements_sampling_interval": "<statement_sampling_interval>"
                        },
                        ...
                      },
                      ...
                    }' \
                mdb.api.cloud.yandex.net:443 \
                yandex.cloud.mdb.mysql.v1.ClusterService.Create
            ```

            Where `config_spec.performance_diagnostics` is the statistics collection settings:

            * `enabled`: Enables statistics collection, `true` or `false`.
            * `sessions_sampling_interval`: Session sampling interval, from `5` to `86400` seconds.
            * `statements_sampling_interval`: Statement sampling interval, from `60` to `86400` seconds.

        1. Check the [server response](../api-ref/grpc/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

    1. To enable the collection of statistics when updating an existing cluster:

        1. Call the [ClusterService/Update](../api-ref/grpc/Cluster/update.md) method, e.g., via the following [gRPCurl](https://github.com/fullstorydev/grpcurl) request:

            {% note warning %}
            
            The API method will assign default values to all the parameters of the object you are modifying unless you explicitly provide them in your request. To avoid this, list the settings you want to change in the `update_mask` parameter as an array of `paths[]` strings.
            
            {% cut "Format for listing settings" %}
            
            ```yaml
            "update_mask": {
                "paths": [
                    "<setting_1>",
                    "<setting_2>",
                    ...
                    "<setting_N>"
                ]
            }
            ```
            
            {% endcut %}
            
            {% endnote %}

            ```bash
            grpcurl \
                -format json \
                -import-path ~/cloudapi/ \
                -import-path ~/cloudapi/third_party/googleapis/ \
                -proto ~/cloudapi/yandex/cloud/mdb/mysql/v1/cluster_service.proto \
                -rpc-header "Authorization: Bearer $IAM_TOKEN" \
                -d '{
                      "cluster_id": "<cluster_ID>",
                      "update_mask": {
                        "paths": [
                          "config_spec.performance_diagnostics"
                        ]
                      },
                      "config_spec": {
                        "performance_diagnostics": {
                          "enabled": <enable_statistics_collection>,
                          "sessions_sampling_interval": "<session_sampling_interval>",
                          "statements_sampling_interval": "<statement_sampling_interval>"
                        }
                      }
                    }' \
                mdb.api.cloud.yandex.net:443 \
                yandex.cloud.mdb.mysql.v1.ClusterService.Update
            ```

            Where `config_spec.performance_diagnostics` represents the statistics collection settings:

            * `enabled`: Enables statistics collection, `true` or `false`.
            * `sessions_sampling_interval`: Session sampling interval, from `5` to `86400` seconds.
            * `statements_sampling_interval`: Statement sampling interval, from `60` to `86400` seconds.

        1. Check the [server response](../api-ref/grpc/Cluster/create.md#yandex.cloud.operation.Operation) to make sure your request was successful.

{% endlist %}

## Getting session statistics {#get-sessions}

1. Navigate to **Managed Service for&nbsp;MySQL**.
1. Click the name of your cluster and select **Performance diagnostics** → **Sessions**.

    To view session statistics or the history of queries run within a session, select the relevant tab.

    {% list tabs %}

    - Statistics

        To view session statistics:

        1. Specify the time interval.
        1. Optionally, set filters.
        1. Select the [data slice](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-quick-start.html).

        To show or hide individual categories, click the category name in the chart legend.

    - History

        To view the history of queries run within a session:

        1. Specify the required time interval.
        1. Optionally, configure filters.

    {% endlist %}

## Getting query statistics {#get-queries}

1. Navigate to **Managed Service for&nbsp;MySQL**.
1. Click the name of your cluster and select **Performance diagnostics** → **Queries**.

    To view query statistics or compare them across two time intervals, select the relevant tab.

    {% list tabs %}

    - Interval

        To view query statistics:

        1. Select the time interval.
        1. Optionally, set filters.

    - Two intervals

        To get information about the relative changes in query statistics:

        1. In the **Interval 1** field, select the time interval to use as the baseline for calculations.
        1. In the **Interval 2** field, select the time interval to compare against the baseline interval.
        1. Optionally, configure filters.

        Suppose, 10 `SELECT * FROM cities` queries were executed in the first interval and 20 in the second. When comparing statistics, the difference in the number of queries (the `Calls` column in the table) will be `+100%`.

    {% endlist %}

### Available metrics {#metrics}

This section lists metrics used to collect query statistics in [Monitoring](../../monitoring/index.md).

Statistic metric | Unit | Description
----- | ----- | -----
**Total query latency** | Milliseconds | Total query execution time
**Total lock latency** | Milliseconds | Total lock wait time
**Avg query latency** | Milliseconds | Average query execution time
**Avg lock latency** | Milliseconds | Average lock wait time
**Rows examined** | Count | Number of rows read
**Calls** | Count | Number of database calls
**Rows sent** | Count | Number of rows returned
**Rows affected** | Count | Number of rows changed, deleted, added, or returned
**Tmp tables** | Count | Rate of temporary tables created during query processing
**Tmp disk tables** | Count | Rate of temporary tables created on the disk during query processing
**Select full join** | Count | Rate of joins using table scans rather than indexes
**Select full range join** | Count | Rate of joins searching by range in the reference table
**Select range** | Count | Number of range selects
**Select scan** | Count | Number of selects using table scans
**Sort merge passes** | Count | Number of merge sorts on tables
**Sort range** | Count | Number of range sorts per unit of time
**Sort rows** | Count | Number of rows sorted per unit of time
**Sort scan** | Count | Number of sorts using table scans per unit of time
**No index used** | 0/1 | `1` if no index was used for the table scan, `0` otherwise
**No good index used** | 0/1 | `1` if no suitable index was found for the query, `0` otherwise
**Errors** | Count | Number of query execution errors
**Warnings** | Count | Number of query execution warnings

For more information about the statistics you can get, see [this MySQL® article](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-events-statements-current-table.html).