Using diagnostic tools

• 1: Determining your version of Vertica
• 2: Collecting diagnostics: scrutinize command
• 2.1: Running scrutinize
• 2.2: Informational options
• 2.3: Redirecting scrutinize output
• 2.4: Scrutinize security
• 2.5: Data collection scope
• 2.6: Uploading scrutinize results
• 2.7: Troubleshooting scrutinize
• 3: Exporting a catalog
• 4: Exporting profiling data

1 - Determining your version of Vertica

To determine which version of Vertica is installed on a host, log in to that host and type:

$ rpm -qa | grep vertica

The command returns the name of the installed package, which contains the version and build numbers. The following example indicates that both Vertica 9.3.x and Management Console 9.3.x are running on the targeted host:

$ rpm -qa | grep vertica
vertica-9.3.0-0
vertica-console-9.3.0-0.x86_64

When you are logged in to your Vertica Analytic Database database, you can also run a query for the version only, by running the following command:

=> SELECT version();
                  version
-------------------------------------------
 Vertica Analytic Database v9.3.0-0

2 - Collecting diagnostics: scrutinize command

The diagnostics tool scrutinize collects a broad range of information from a Vertica cluster. It also supports a range of options that let you control the amount and type of data that is collected. Collected data can include but is not limited to:

• Host diagnostics and configuration data

• Run-time state (number of nodes up or down)

• Log files from the installation process, the database, and the administration tools (such as, vertica.log, dbLog, /opt/vertica/log/adminTools.log)

• Error messages

• Database design

• System table information, such as system, resources, workload, and performance

• Catalog metadata, such as system configuration parameters

• Backup information

Requirements

scrutinize requires that a cluster be configured to support the Administration Tools utility. If Administration Tools cannot run on the initiating host, then scrutinize cannot run on that host.

2.1 - Running scrutinize

You can run scrutinize with the following command:

$ /opt/vertica/bin/scrutinize

Unqualified, scrutinize collects a wide range of information from all cluster nodes. It stores the results in a .tar file (VerticaScrutinize.NumericID.tar), with minimal effect on database performance. scrutinize output can help diagnose most issues and yet reduces upload size by omitting fine-grained profiling data.

Command options

scrutinize options support the following tasks:

• Obtain version information about scrutinize and Vertica, and online help.

• Redirect output.

• Access a password-protected database.

• Control the scope of data collection.

• Upload results to Vertica Customer Support.

Privileges

You must have database administrator (dbadmin) privileges to run scrutinize. If you run scrutinize as root when the dbadmin user exists, Vertica returns an error.

Disk space requirements

scrutinize requires temporary disk space where it can collect data before posting the final compressed (.tar) output. How much space depends on variables such as the size of the Vertica log and extracted system tables, as well as user-specified options that limit the scope of information collected. Before scrutinize runs, it verifies that the temporary directory contains at least 1 GB of space; however, the actual amount needed can be much higher.

You can redirect scrutinize output to another directory. For details, see Redirecting scrutinize output.

Database specification

If multiple databases are defined on the cluster and more than one is active, or none is active, you must run scrutinize with one of the following options:

$ /opt/vertica/bin/scrutinize {--database=`*`database`* `| -d `*`database`*`}

If you omit this option when these conditions are true, scrutinize returns with an error.

2.2 - Informational options

scrutinize supports two informational options that cannot be combined with any other options:

--version

Obtains the version number of the Vertica server and the scrutinize version number, and then exits. For example:

=> \!scrutinize --version
Scrutinize Version 10.0.1-20200426

--help -h

Lists all scrutinize options to the console, and then exits:

=> \! scrutinize -h
Usage: scrutinize [options]
  
Options:
--version             show program's version number and exit
-h, --help            show this help message and exit
-X LIST, --exclude-tasks=LIST
            Skip tasks of a particular type. Provide a comma-
            separated lists of types to skip. Types are case-
            sensitive. Possible types are: Command, File,
            VerticaLog, DC, SystemTable, CatalogObject, Query,
            all.
...

2.3 - Redirecting scrutinize output

By default, scrutinize uses the temporary directory /opt/vertica/tmp execution to compile output while it executes. On completing its collection, it saves the collection to a tar file to the current directory. You can redirect scrutinize output with two options:

--tmpdir=path

Directs temporary output to the specified path, where the following requirements apply to path:

• The directory must have at least 1 GB of free space.

• You must have write permission to it.

--output_dir=path
-o path

Saves scrutinize results to a tar file in path. For example:

$ scrutinize --output_dir="/my_diagnostics/"

2.4 - Scrutinize security

scrutinize can specify user names and passwords as follows:

--user=username
-U username

Specifies the dbadmin user name. By default, scrutinize uses the user name of the invoking user.

--password=password -P password

Sets the database password as an argument to the scrutinize command. Use this option if the administrator account (default dbadmin) has password authentication. If you omit this option on a password-protected database, scrutinize returns a warning, unless the environment variable VSQL_PASSWORD is set.

Passwords with special characters must be enclosed with single quotes. For example:

$ scrutinize -P '@passWord**'
$ scrutinize --password='$password1*'

-prompt-password -W

Specifies to prompt users for their database password before scrutinize begins to collect data.

2.5 - Data collection scope

scrutinize options let you control the scope of the data collection. You can specify the scope of the data collection according to the following criteria:

• Amount of data, including its level of granularity

• Specific nodes

• Types of data to include and exclude

You can use these options singly or in combination, to achieve the desired level of granularity.

Amount of collected data

Several options let you limit how much data scrutinize collects:

--by-second --by-minute=boolean-value

Specifies the granularity of information that is collected from Data Collector tables with one of the following options:

• --by-second: Highest level of granularity, specifies to collect data down to the second.

• --by-minute=boolean-value

  where boolean-value is set to one of the following:

  • {yes|on}: Default setting, specifies to collect data down to the minute.

  • {no|off}: Lowest level of granularity, specifies to collect data down to the hour.

For example, the following command collects data down to the hour:

$ scrutinize --by-minute=no

This command data down to the second:

  
$ scrutinize --by-second

--get-files file-list

Specifies extra files to collect, including globs, where file-list is a semicolon-delimited list of files.

--include_gzlogs=num-files
-z num-files

Specifies to include num-files rotated log files (vertica.log*.gz) in the scrutinize output, where num-files can be one of the following:

• An integer specifies the number of rotated log files to collect.

• all specifies to collect all rotated log files.

By default, scrutinize includes three rotated log files.

For example the following command specifies to collect two rotated log files:

$ scrutinize --include_gzlogs=2

--log-limit=limit
-l limit

Specifies how much data to collect from Vertica logs, where limit specifies, in gigabytes, how much log data to collect, starting from the most recent log entry. By default, scrutinize collects 1 GB of log data.

For example, the following command specifies to collect 4 GB of log data:

$ scrutinize --log-limit=4

Node-specific collection

By default, scrutinize collects data from all cluster nodes. You can specify that scrutinize collect from individual nodes in two ways:

--local_diags -s

Specifies to collect diagnostics only from the host on which scrutinize was invoked.

Tip

To collect data from multiple nodes in the cluster, use the --hosts option.

--hosts=host-list -n host-list

Specifies to collect diagnostics only from the hosts specified in host-list, where host-list is a comma-separated list of IP addresses or host names.

For example:

$ scrutinize --hosts=127.0.0.1,host_3,host_1

Types of data to include

scrutinize provides several options that let you specify the type of data to collect:

--debug

Collects debug information for the log.

--diag-dump

Limits the collection to database design, system tables, and Data Collector tables. Use this option to collect data to analyze system performance.

--diagnostics

Limits the collection to log file data and output from commands that are run against Vertica and its host system. Use this option to collect data to evaluate unexpected behavior in your Vertica system.

--include-ros-info

Includes ROS related information from system tables.

--no-active-queries --with-active-queries

Specifies to exclude diagnostic information from system tables and Data Collector tables about currently running queries. By default, scrutinize collects this information (--with-active-queries).

--tasks=tasks -T tasks

Specifies that scrutinize gather diagnostics on one or more tasks, as specified in a file or JSON list. This option is typically used together with --exclude.

Note

Use this option only in consultation with Vertica Customer Support

--type=type -t type

Specifies the type of diagnostics collection to perform, where type can be one of the following arguments:

• profiling: Gather profiling data.

• context: Gather summary information.

--with-active-queries

The default setting, specifies to include diagnostic information from system tables and Data Collector tables about currently running queries. To omit this data, use --no-active-queries.

Types of data to exclude

scrutinize options also let you specify the types of data to exclude from its collection:

--exclude=tasks -X tasks

Excludes one or more types of tasks from the diagnostics collection, where tasks is a comma-separated list of the tasks to exclude.

Note

This option is typically used only in consultation with your Vertica Customer Support contact.

Specify the tasks to exclude with the following case-insensitive arguments :

• all: All default tasks

• DC: Data Collector tables

• File: Log files from the installation process, the database, and Administration Tools, such as vertica.log, dbLog, and adminTools.log

• VerticaLog: Vertica logs

• CatalogObject: Vertica catalog metadata, such as system configuration parameters

• SystemTable: Vertica system tables that contain information about system, resources, workload, and performance

• Query: Vertica meta-functions that use vsql to connect to the database, such as EXPORT_CATALOG()

• Command: Operating system information, such as the length of time that a node has been up

--no-active-queries

Specifies to omit diagnostic information from system tables and Data Collector tables about currently running queries. By default, scrutinize always collects active query information (--with-active-queries).

--vsql-off -v

Excludes Query and SystemTable tasks, which are used to connect to the database. This option can help you deal with problems that occur during an upgrade, and is typically used in the following cases:

• Vertica is running but is slow to respond.

• You haven't yet created a database but need help troubleshooting other cluster issues.

2.6 - Uploading scrutinize results

scrutinize provides several options for uploading data to Vertica customer support.

Upload packaging

When you use an upload option, scrutinize does not bundle all output in a single tar file. Instead, each node posts its output directly to the specified URL as follows:

1. Uploads a smaller, context file, enabling Customer Support to review high-level information.

2. On completion of scrutinize execution, uploads the complete diagnostics collection.

Upload prerequisites

Before you run scrutinize with an upload option:

• Install the cURL program installed in the path for the database administrator user who is running scrutinize.

• Verify each node in the cluster can make an HTTP or FTP connection directly to the Internet.

Upload options

--auth-upload=url
-A url

Uses your Vertica license to authenticate with the Vertica server, by uploading your customer name. Customer Support uses this information to verify your identity on receiving your uploaded file. This option requires a valid VerticaPremium Edition license.

For example:

$ scrutinize -U username -P 'password' --auth-upload="url"

--url=url
-u url

Requires url to include a user name and password that is supplied by Vertica Customer Support.

For example:

$ scrutinize -U username -P 'password' --url='ftp://username/password@customers.vertica.com/'

--message=message
-m message

include a message with the scrutinize output, where message is one of the following:

• "message text"
  A message string. For example:

  $ scrutinize --message="re: case number #ABC-12345"
  

• "file-path"
  A path to a text file. For example:

  $ scrutinize --message="/path/to/msg.txt"
  

• PROMPT
  Opens an input stream. scrutinize reads input until you type a period (.) on a new line. This closes the input stream, and scrutinize writes the message to the collected output.

  $ scrutinize --message=PROMPT
  Enter reason for collecting diagnostics; end with '.' on a line by itself:
  Query performance degradation noticed around 9AM EST on Saturday
  .
  Vertica Scrutinize Report
  -----------------------------
  Result Dir:              /home/dbadmin/VerticaScrutinize.20131126083311
  ...
  

The message is set in the output directory, in reason.txt. If no message is specified, scrutinize generates the default message Unknown reason for collection. Messages typically include the following information:

• Reason for gathering/submitting diagnostics.

• Support-supplied case number and other issue-specific information, to help Vertica Customer Support identify your case and analyze the problem.

2.7 - Troubleshooting scrutinize

The troubleshooting advice in this section can help you resolve common issues that you might encounter when using scrutinize.

Collection time is too slow

To speed up collection time, omit system tables when running an instance of scrutinize. Be aware that collecting from fewer nodes does not necessarily speed up the collection process.

Output size is too large

Output size depends on system table size and vertica log size.

To create a smaller scrutinize output, omit some system tables or truncate the vertica log. For more information, see Narrowing the Scope of scrutinize Data Collection.

System tables not collected on databases with password

Running scrutinize on a password-protected database might require you to supply a user name and password:

$ scrutinize -U username -P 'password'

3 - Exporting a catalog

When you export a catalog you can quickly move a catalog to another cluster. Exporting a catalog transfers schemas, tables, constraints, projections, and views. System tables are not exported.

Exporting catalogs can also be useful for support purposes.

See the EXPORT_CATALOG function for details.

4 - Exporting profiling data

The diagnostics audit script gathers system table contents, design, and planning objects from a running database and exports the data into a file named ./diag_dump_<timestamp>.tar.gz, where <timestamp> denotes when you ran the script.

If you run the script without parameters, you will be prompted for a database password.

Syntax

/opt/vertica/scripts/collect_diag_dump.sh [ command... ]

Parameters

command

Example

The following command runs the audit script with all arguments:

$ /opt/vertica/scripts/collect_diag_dump.sh -U dbadmin -w password -c