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

In order for scrutinize to collect data from all system tables, you must have superuser or SYSMONITOR privileges; otherwise, scrutinize collects data only from the system tables that you have privileges to access. 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 12.0.2-20221107
  

--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,
                        UdxLog, KafkaLog, MemoryReportLog, all.
  -v, --vsql-off        Does -X Query,SystemTable and skips vsql checks.
                        Useful if vertica is running, but slow to respond.
  -s, --local_diags     Gather diagnostics for local machine only
  -d DB, --database=DB  Only report on database <DB>
  -n HOST_LIST, --hosts=HOST_LIST
                        Gather diagnostics for these hosts only. Host list
                        must be a comma-separated list. Ex. host1,host2,host3
                        or 'host1, host2, host3'
  -m MESSAGE, --message=MESSAGE
                        Reason for gathering diagnostics
  -o OUTPUT_DIR, --output_dir=OUTPUT_DIR
                        redirect output to somewhere other than the current
                        directory
  -U USERNAME, --user=USERNAME
                        Specify DB user
  -P PASSWORD, --password=PASSWORD
                        Specify DB user password
  -W, --prompt-password
                        Force Scrutinize to prompt for DB user password
  ...

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

Collect data every second. This is the highest level of granularity when collecting from Data Collector tables.

--by-minute=boolean-value

Collect data every minute (if the value is true) or every hour (if the value is false).

--get-files file-list

Collect the specified additional files, including globs, where file-list is a semicolon-delimited list of files.

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

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.

--log-limit=limit
-l limit

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

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

Collect diagnostics only from the host on which scrutinize was invoked. To collect data from multiple nodes in the cluster, use the --hosts option.

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

Collect diagnostics only from the hosts specified in host-list, 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`

Excludes 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

Gathers 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

Type of diagnostics collection to perform, one of the following:

• profiling: Gather profiling data.

• context: Gather summary information.

--with-active-queries

The default setting, includes 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:

• 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

Note

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

--no-active-queries

Omits 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 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 Vertica license.

--url=url
-u url

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

--message=message
-m message

Includes a message with the scrutinize output, where message is a text string, a path to a text file, or PROMPT to open an input stream in which to compose a message. 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.

The message is written 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.

Examples

The --auth-upload option uses your Vertica to identify yourself:

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

The --url option includes the FTP username and password, supplied by support, in the URL:

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

You can supply a message as a text string or in a text file:

$ scrutinize --message="re: case number #ABC-12345"
$ scrutinize --message="/path/to/msg.txt"

Alternatively, you can open an input stream and type a message:

$ 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
...

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 [ -U value ] [ -w value ] [ -c ]

Arguments

-U value

User name, typically the database administrator account, dbadmin.

-w value

Database password.

-c

Include a compression analysis, resulting in a longer script execution time.

Example

The following command runs the audit script with all arguments:

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