Documentation forDatabase Performance Analyzer

Register a MySQL or Percona MySQL database instance

Complete the following steps to register an individual MySQL or Percona MySQL database instance for monitoring with DPA.

You can use the registration wizard to register a read/write MySQL database instance. To register a read-only instance, see Monitor a read-only MySQL database instance in DPA.

To optimize DPA's reporting capabilities for a MySQL database instance, see the requirements for monitoring MySQL database instances.

You can also use mass registration to registrations to register multiple database instances, or you can register database instances using scripts that call the DPA API.

If you register a database instance within the 14-day trial period, DPA begins monitoring the instance immediately. After the trial period, you must activate a license to monitor the database instance.

Identify the privileged user

If you choose to let DPA create or configure the account used to collect DPA data (the monitoring user), you must provide the credentials of a privileged user. (You can also choose to create the monitoring user yourself.) During registration, the privileged user either creates the monitoring user or grants the required privileges to an existing user that you designate as the monitoring user. DPA does not store the credentials of the privileged user. 

For self-managed MySQL or Percona MySQL database instances:

  • The privileged user requires the following permission: 

    CREATE USER

  • The privileged user must be able to grant the following permissions:

    PROCESS on *.*
    SELECT & UPDATE on performance_schema.*

  • To enable DPA to retrieve query execution plans and generate tuning advisors, the privileged user must also be able to grant the following permissions:

    SELECT, INSERT, UPDATE, DELETE on *.*
    SYSADM

For SSL modes that require certificate verification, upload the CA certificate

If the instance will communicate with DPA in a mode that requires certificate verification (verify_ca or verify_identity), add the certificate authority (CA) certificate to the DPA trust store. See Import a certificate into the DPA trust store.

The verify_ca and verify_identity options are supported in DPA 2025.2 and later.

Complete the registration wizard

  1. In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.

  2. Under Self-managed, click MySQL or Percona. Then click Next.

  3. Enter the following connection information:

    1. Enter the host name or IP address and port of the server.

    2. DPA monitors all databases within the instance. If more than one instance exists on the server, you must register each instance separately in DPA.

    3. Create or specify the account that DPA will use to gather information (the monitoring user).

      In DPA 2024.3 and earlier: If you are registering a MySQL 8.0 instance, you must create the monitoring user manually and you must include the following in the CREATE USER command:

      IDENTIFIED WITH mysql_native_password BY 'yourPassword';

    4. DPA ignores data generated by the monitoring user on the monitored database instance. For this reason, create a separate account for the monitoring user. Do not specify an existing user that generates load on the monitored instance.

      • To let DPA create or configure the monitoring user:

        1. Select Let DPA create a new user or configure an existing user for me.

        2. Enter the credentials of an existing user with the required privileges.

        3. The credentials for the privileged user are not used or stored after the registration.

        4. Select a Tablespace and Temp Tablespace on the monitored database. This is primarily used for gathering Explain Plan data for monitored queries.

        5. Click Next.

      • To create the monitoring user yourself:

        1. Select I'll create the database user.

        2. Click Monitoring User Creation Script.

        3. Copy the script to a file and edit it per the instructions.

        4. Copy the edited script to the MySQL console, and run it.

        5. The monitoring user is created.

        6. Enter this user's credentials in the Username and Password fields.

    5. If your repository database is Oracle, the Oracle Repository Tablespace panel opens. If not, continue with step 5.

  4. If the database server uses SSL, click Advanced Connection Properties. Then, in JDBC URL Properties, enter one of the following to specify the type of secure socket layer (SSL) connections established between the instance and the DPA server:

    SSL mode Description
    sslMode=verify_identity

    The client requires an encrypted connection. It verifies that the server's CA certificate is valid and that the host name the client is using matches the identity in the server’s certificate.

    This option is supported in DPA 2025.2 and later.

    sslMode=verify_ca

    The client requires an encrypted connection and verifies that the server's CA certificate is valid.

    This option is supported in DPA 2025.2 and later.

    sslMode=required

    The client requires an encrypted connection, but certificate or identity verification are not performed.
    sslMode=preferred

    The client attempts to use an encrypted connection, but will use an unencrypted connection if an encrypted connection is not possible.
    sslMode=disabled

    SSL encryption is not used.

  5. If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance. Then click Next.

    By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

    If your repository database is not Oracle, the wizard skips this step.

  6. (Optional) Select the alert groups you want the new database instance to join. Then click Next.

    • If no alert groups exist, or the existing groups do not match the database type of this instance, the wizard skips this step.

    • Group membership can be changed after registration

  7. Select a Typical or Custom configuration. SolarWinds recommends the Typical configuration.

    • Typical is recommended. With this option:

      • The DPA Recommended option (shown in the following table) is used for Performance Schema setup.

      • DPA can run EXPLAIN on SELECT, INSERT, UPDATE, and DELETE statements.

    • If you select Custom, you can:

      • Specify what data the Performance Schema collects and maintains. The following table shows which consumers and instruments each option enables.

      • Specify what types of SQL statements DPA can run EXPLAIN on.

      The MySQL Performance Schema must be enabled. If you select Leave As Is, verify that Global Instrumentation and Thread Instrumentation are enabled in the existing Performance Schema configuration.

      Option Server Default DPA Recommended Detailed Leave As Is
      Consumer Global Instrumentation

      ✔

      ✔

      ✔

      NC*
      Consumer Thread Instrumentation

      ✔

      ✔

      ✔

      NC
      Consumer Statement Digest

      ✔

      ✔

      ✔

      NC
      Consumer Statement (Current)

      ✔

      ✔

      ✔

      NC
      Consumer Wait (Current)   ✔ ✔ NC
      Instrument Wait (Lock/*)   ✔ ✔ NC
      Instrument Wait (I/O table) (I/O/file)   ✔ ✔ NC
      Instrument Wait (I/O/socket)   ✔ ✔ NC
      Instrument Wait (Synch/*)     ✔ NC

      *NC = No change. DPA does not change the existing Performance Schema configuration.

      Values that are outside of the MYSQL_OR_MARIADB_PERFORMANCE_SCHEMA configuration scope of DPA are not changed. For example, an instrument named stage exists in the MySQL Performance Schema. If you enable or disable that instrument, DPA will not change it.

      In DPA 2024.3 and earlier, the MYSQL_OR_MARIADB_PERFORMANCE_SCHEMA option was named MYSQL_PERFORMANCE_SCHEMA.

  8. If you specified a privileged user to create the DPA monitoring user, the wizard displays the Allow EXPLAIN to be run on section. Select what type of statements you want DPA to collect execution plans for. The monitoring user can run EXPLAIN on the selected statement types, and this data is used to generate tuning advisors.

  9. Click Next.

  10. Review the information, and click Register Database Instance.

  11. When the registration is complete, click Finish to return to the DPA home page.

MySQL monitored database requirements

SolarWinds recommends the following settings to provide the best monitoring capabilities for MySQL monitored database instances.

MySQL Performance Schema

The Performance Schema monitors server events and collects performance data. If the Performance Schema is not enabled, DPA provides limited data. Monitoring with the Performance Schema disabled excludes the following data:

  • All instrumented wait events
  • All wait operations
  • All file wait time, broken out by file
  • All object wait time, broken out by index and table
  • SQL statistics
  • Performance-schema dependent alerts

The Performance Schema must be enabled at server startup. In MySQL versions 5.6.6 and later, the Performance Schema is enabled by default.

Global Instrumentation and Thread Instrumentation

Global Instrumentation and Thread Instrumentation must be enabled in the Performance Schema configuration. Disabling these instruments has the same effect as disabling the Performance Schema.

By default, DPA enables these instruments in the configuration. However, if you select the Leave As Is option for Performance Schema setup, you must verify that Global Instrumentation and Thread Instrumentation are enabled in the existing Performance Schema configuration.

show_compatibility_56 system variable

If the monitored database instance is MySQL 5.7.6 or later, SolarWinds recommends turning on the show_compatibility_56 system variable. If this variable is on, DPA can collect data for all metrics.

If this variable is off and the Performance Schema is enabled, DPA cannot collect data for the following metrics:

  • Selects
  • Inserts
  • Updates
  • Deletes