Frequently Asked Questions

Getting help is easy. If the answers below don’t resolve your question, or if you’d just like to learn more about Database Performance Monitor, you can reach our Customer Support team live using the in-app chat at the bottom right of the screen. During business hours, you’ll typically receive a reply in under ten minutes.

Installation & Setup

Data

Using DPM

Query Samples & Digests

I provided credentials, but the agent cannot connect.

First, please double-check that you have created a user correctly in your database and can log in as that user with the specified password. To verify this in MySQL, which has “anonymous” users that can cause unexpected results, issue the SHOW GRANTS command after logging in. The column name should be something like Grants for user@host. Verify that this is as expected.

A common problem with this process in MySQL is specifying which host the agent is allowed to connect from. The DPM agent will only connect over TCP/IP, not a Unix socket. If you’ve specified the allowed host in the GRANT statement as user@locahost, then you’ve created a MySQL user account that only permits Unix socket logins. You will need to specify a hostname or IP address, such as user@127.0.0.1, to permit a TCP/IP login.

You can check to mimic how the agent will log into the database server, substituting appropriate values below:

$ mysql --protocol=tcp -uUSER -pPASSWORD -hHOST --port=PORT
ERROR 1045 (28000): Access denied for user 'USER'@'127.0.0.1' (using password: YES)

Please note the user from which access was denied. If this doesn’t match how you’ve created the user, that could be the problem.

You may also have specified a wrong host, such as specifying 127.0.0.1 for the host to which the agent should connect, when MySQL is not listening on that IP address. If this is the case, you’ll get an error message similar to this:

ERROR 2003 (HY000): Can't connect to MySQL server on 'HOST' (61)

After you get everything working, please be sure to remove any unwanted users, and be sure to grant all necessary privileges to the new user you create to replace it.

I can query pg_stat_statements, but DPM says it is not enabled.

In order for the agent to read data from pg_stat_statements the pg_stat_statements view must be added to the database that DPM is connecting to. This is the internal PostgreSQL database, not the host itself. For example, you may have run the CREATE EXTENSION on the reporting database, while DPM is connecting to postgres.

Check the database that has been provided in the host’s Credentials page. If no database is provided, DPM will default to postgres. You can also read the agent’s log, which will print the connection information at startup. To confirm this is the issue, connect to PostgreSQL using the user and database provided to DPM and select count(*) from pg_stat_statements. If this still succeeds, please contact Support; otherwise, you will need to either change the agent’s connection information, or add the pg_stat_statements view to the connected database.

How do I use a local proxy with DPM?

Information about using a proxy can be found in our Configuration documentation.

I do not see agents installed on a new host.

If you’re using a free trial or you’re on a capped contract, you may have exceeded your quota. Check the /var/log/vividcortex/vc-agent-007.log log file for messages to that effect. Contact us if you need to increase your quota or if you’re interested in evaluating DPM on more hosts during your trial.

Also see Missing Hosts on the Managing Hosts page.

How do I control what query samples are sent to DPM?

For information about how to control the query data sent to DPM, see our documentation on query samples here.

How do I start / stop the DPM service?

See Starting and Stopping DPM in our documentation.

How do I view the agent’s log?

You can view the agent logs in /var/log/vividcortex. Alternatively, you can use the Agents page to view logs within the DPM app.

How are licenses used? How many do I have?

Each database and its associated monitoring operating system (whether remote or local) consumes one license. Any operating system instances which do not monitor a database also consume one license. You can see the number of licenses you are currently using, and the number that your organization has available for use, on the Settings page under Organization Overview.

I’m collecting data, but it is appearing in the past / future.

It is possible that your system time is incorrect. Check that the system time is accurate, such as with the Linux date command, and correct if necessary. We strongly recommend making sure ntpd is running on your server.

I see data in the Profiler, but I don’t see charts.

You can see the queries because there are two database-specific plugins. One plugin decodes the database network protocol, recording queries directly, so it doesn’t need credentials. On the other hand, the metrics plugin needs to connect to the database to get the server’s status and other data, so it needs credentials. If you don’t see any charts with database status metrics, it’s likely that the agent is having trouble logging into the database to get the status metrics. Check the Events page for any information the agent may have sent about its error, or check /var/log/vividcortex/ for any error messages.

I do not see queries that I know are executing.

When monitored on-host, our agent only captures queries sent to the server over unencrypted TCP/IP connections, not Unix sockets. We won’t see the following types of queries:

  • Queries sent over a Unix socket
  • Queries sent over an encrypted connection
  • Queries executed from stored routines (stored procedures, events, or triggers)
  • Queries that execute via replication
  • Queries that were killed or otherwise did not finish

When monitored off-host, missing queries usually means that performance_schema or pg_stat_statements is not capturing the query for some reason. Contact Support and we will try to help find the cause.

What is performance_schema_digest_lost?

When monitoring MySQL using the performance_schema we fetch data from events_statements_summary_by_digest. This table has a maximum size, configured at server start with the performance_schema_digests_size system variable. The metric performance_schema_digest_lost (generated by MySQL) is the number of query digests per second that MySQL did not record. A value greater than zero indicates that MySQL (and therefore DPM) is not recording some of your database activity.

To fix this, you can run truncate table performance_schema.events_statements_summary_by_digest or restart the server. You can also configure the agent to automatically truncate this table when the percentage of digests lost exceeds some threshold. To enable this, do the following:

  • Grant the DROP privilege on performance_schema.events_statements_summary_by_digest to the database user that DPM is using.
  • In in the global.conf file on the host where the agent is running (the default location is /etc/vividcortex/global.conf), add "ps-truncate-events-summary":"true" and "ps-others-tolerance":"X", where X is some value, such as 0.1, which represents the “max ratio of queries that can be lost before truncation”. Don’t forget to add commas in the configuration file where necessary (it is JSON formatted).
  • Restart the agent with service vividcortex restart on that server.

The agent will truncate events_statements_summary_by_digest at most once per second, and you can monitor how frequently it is preforming this truncation with the DPM metric agents.vc_mysql_metrics.rds.events_truncated. The percentage of digests lost can also be monitored with agents.vc_mysql_metrics.rds.others_ratio.

Why don’t I see any database or table size metrics in the Profiler?

Database and table size metrics, such as data_length and index_length, are disabled by default as collecting those metrics can be costly. To enable them, you can either contact DPM Customer Support (recommended) by chatting with us using the ? button in the lower right corner, or you can enable table sizes using a DPM config file with the option enable-table-sizes. This is available for MySQL, PostgreSQL, and MongoDB.

Why don’t I see any InnoDB mutex data?

If you do not see any data reported in the Profiler or Metrics pages for InnoDB mutexes, check that the wait/synch/mutex/innodb/% instruments are enabled in performance_schema. To check if these are enabled, execute:

SELECT * FROM performance_schema.setup_instruments where NAME like 'wait/synch/mutex/innodb/%'

To enable these instruments, you can either add

performance-schema-instrument='wait/synch/mutex/innodb/%=ON'

to a my.cnf or execute:

UPDATE performance_schema.setup_instruments SET ENABLED='YES' WHERE NAME LIKE 'wait/synch/mutex/innodb/%';

The instruments can be enabled without restarting the server if you use the UPDATE statement, however not all of the instruments will be active until a restart. See the MySQL documentation for more information about these instruments. This data is available for MySQL version 5.7 and above.

What’s the difference between Queries and Processlist/Stat Activity/CurrentOp Queries?

In the Profiler, “Queries” data comes from either decoding TCP traffic (for local installations), pg_stat_statements (remote PostgreSQL), or performance_schema (remote MySQL). This view includes all queries executed on your server.

The other options are …

Category Description Can be ranked by…
MySQL Processlist Queries Query digest data captured from the PROCESSLIST Count, Locks, Total Time
PostgreSQL Activity Query digest data captured from pg_stat_activity Count, Locks, Total Time
MongoDB Current Op Queries Queries captured from the currentOp command Blocked, Count, Total Time

Those tables are queried once per second. This view of the data is very useful to see what activity your database is performing at a given point in time.

How does DPM compute query latency?

For clients monitoring their database locally, query latency is calculated as the time between receiving the last byte of the request and receiving the first byte of the response. For clients monitoring remotely, latency is fetched directly from the database’s own statistics tables (either pg_stat_statements or the performance_schema).

Why does the latency for some queries in DPM not match the MySQL slow query log?

If monitoring locally, latency will include any delay due to network effects. Additionally, the MySQL slow query log does not include time spent waiting for any initial locks.

How do I remove hosts?

If you’d like to deactivate a host so it doesn’t contribute towards your usage quota, you can do so with the following steps:

  • Select the host in the Inventory, and click on the gear that appears to the right of its name. You will see a popup like the one below:

Delete Host Popup

  • Check the box labeled “I know what I’m doing”, and click on the Delete button to confirm deletion. Once deleted a host will automatically be removed from your license count.

You may also uninstall the agents and wait a bit; because usage is metered by the hour, the number of active hosts in use will decrease after two hours.

If I delete a host, will the old data remain available?

Yes. All data collected remains available even if you delete a host. The host and its data will be shown if you select a time range where that host has data.

How do I move a host from one environment to another?

Please contact Support for help moving a host from one environment to another.

I don’t see my MongoDB sharded cluster.

We use the shardingState command in MongoDB to determine if a node is a member of a sharded cluster. In order for this command to provide us the information we need, MongoDB instances must be configured to run as shards using the --shardsvr option or the sharding.clusterRole configuration file setting, as specified by the MongoDB docs. To read more about this requirement, please visit the official MongoDB documentation, which can be found here.

How can I export data from DPM?

You can export data from the Profiler; click the “Export” button in the top right corner. This will download the currently displayed data.

How do I search for a specific query?

See the section “Query Searching” in the Profiler documentation.

How do I rename or delete an environment?

To rename or delete an environment, go to Settings, then under Organization Settings select “Environments.” From there you will have the option to edit an environment’s name by picking the pencil icon, or delete the environment by clicking the trashcan.

How do I add someone to the app?

There are two ways to invite your coworkers to DPM. You can go to Settings, and then under “Organization Settings” select People. You can also click the Add icon (the plus) at the bottom of the left-hand nav in the app and click “Invite Coworkers.” Note that for accounts using RBAC, you must have sufficient privileges in order to add people to the application.

I was invited to join DPM, but my invitation expired.

If your invitation has expired you can have your account owner resend the invitation, or you can visit the Forgot Password page for your organization.

How do I alert on long-running queries?

Please see our Long-Running Query Event type.

Why am I not seeing query samples?

In on-host installations, the most common reason for not seeing query samples is either that they are either disabled for some or all of your hosts by your administrator, or you are logged in as a user without proper permissions to view query samples; Read-Write permissions are required for customers using RBAC. We will also be unable to capture prepared statements which are executed as part of a long-running connection.

In off-host configurations, the most common reasons are not having events_statements_history_long (MySQL) or pg_stat_activity (PostgreSQL) enabled and collecting query data, or that the DPM user does not have sufficient permissions to access that data. We are also unable to collect query samples for prepared statements for MySQL (as query samples are not made available by the database). PostgreSQL does not provide query samples with the literals included, so we do not show this text as it is identical to the query digest.

Lastly, query samples may be disabled for some or all of your hosts by your account administrator.

Why are my digests truncated?

By default, digests are truncated by DPM to 2048 characters in order to group queries together that might only differ at the very end. If you would like to capture longer digests you can add the option “max-digest-length” to your DPM global config file, with a maximum value of "32000". Be sure to restart the agents so the change takes effect.

If your digests are shorter than this length (or if raising the maximum in DPM does not lengthen them) and you are using the Off-Host configuration for MySQL, the digests are likely being truncated by the PERFORMANCE_SCHEMA. For versions 5.6.24 and newer, you must increase the value of both performance_schema_max_digest_length (MySQL docs) and max_digest_length (MySQL docs). These options require a database restart.

Postgres does not limit the size of digested text in pg_stat_statements. For Amazon Aurora for MySQL, these options are available from version 2.x and greater.

Why are my query samples truncated?

By default, we limit the length of captured query samples to 4096 bytes. If your queries are longer than this length you can add the option “max-sample-length” to your DPM global config file, with a maximum value of "32000". Be sure to restart the agents so the change takes effect.

If your samples are truncated and they are smaller than max-sample-length and you are using the Off-Host configuration, it is likely that MySQL or PostgreSQL is shortening the queries in PERFORMANCE_SCHEMA or pg_stat_statements. Both MySQL and PostgreSQL truncate queries at 1024 characters by default. For MySQL, you can change this value using the performance_schema_max_sql_text_length variable in version 5.7.6 (and later). For PostgreSQL, use the track_activity_query_size variable. These options require a database restart. For Amazon Aurora, these options are available from version 2.x and greater.

Why are some queries not displaying EXPLAIN plans?

We need query samples in order to see EXPLAIN plans, so first ensure you are collecting query samples (above) for the queries you want to have EXPLAINed.

If you have query samples, we will not collect EXPLAIN plans for queries which could cause unpredictable load to the server during an EXPLAIN, such queries containing a sub-query. The DPM user connecting to the database also needs the proper permissions to execute EXPLAIN.

Lastly, EXPLAIN plans can be disabled by your account administrator.

Why are some of my PostgreSQL queries displaying <insufficient privilege>?

Typically this occurs when the user DPM is connecting to the database with does not have the SUPERUSER privilege or the rds_superuser role. In this case, <insufficient privilege> will typically appear as much or most of the query volume. You can read more about the required database privileges here.

However, some query volume for hosted PostgreSQL instances on Amazon, such as RDS and Heroku, will always display as <insufficient privilege> due to Amazon using a custom user (such as rdsadmin) to perform monitoring and maintenance functions, including fetching CloudWatch metrics. The queries being run are not viewable by any user, including users who have been assigned the rds_superuser role. The same applies to Heroku, where an application (“Dyno”) gets a dedicated user and database assigned to it, but one that shares the host server with other applications’ databases.

How do I see what MySQL warnings were associated with a query?

MySQL warnings are generally only visible to the user who issued the query. However, for SELECT statements, we can see the warning associated with a specific query sample.

Learn more about warnings in our documentation here.