Operating the database

• 1: Starting the database
• 2: Stopping the database
• 3: CRC and sort order check
• 3.1: Evaluating CRC errors
• 3.2: Evaluating sort order errors

1 - Starting the database

You can start a database through one of the following:

• Management Console

• Administration Tools

• Command Line

Administration tools

You can start a database with the Vertica Administration Tools:

1. Open the Administration Tools and select View Database Cluster State to make sure all nodes are down and no other database is running.

2. Open the Administration Tools. See Using the administration tools for information about accessing the Administration Tools.

3. On the Main Menu, select Start Database,and then select OK.

4. Select the database to start, and then click OK.

   Caution

   Start only one database at a time. If you start more than one database at a given time, results can be unpredictable: users are liable to encounter resource conflicts, or perform operations on the wrong database.

5. Enter the database password and click OK.

6. When prompted that the database started successfully, click OK.

7. Check the log files to make sure that no startup problems occurred.

Command line

You can start a database with the command line tool start_db:

$ /opt/vertica/bin/admintools -t start_db -d db-name
     [-p password]
     [-s host1[,...] | --hosts=host1[,...]]
     [--timeout seconds]
     [-i | --noprompts]
     [--fast]
     [-F | --force]

The following example uses start_db to start a single-node database:

$ /opt/vertica/bin/admintools -t start_db -d VMart
Info:
no password specified, using none
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (UP)
Database VMart started successfully

Eon Mode database node startup

On starting an Eon Mode database, you can start all primary nodes, or a subset of them. In both cases, pass start_db the list of the primary nodes to start with the -s option.

The following requirements apply:

• Primary node hosts must already be up to start the database.
• The start_db tool cannot start stopped hosts such as cloud-based VMs. You must either manually start the hosts or use the MC to start the cluster.

The following example starts the three primary nodes in a six-node Eon Mode database:

$ admintools -t start_db -d verticadb -p 'password' \
   -s 10.11.12.10,10.11.12.20,10.11.12.30
    Starting nodes:
        v_verticadb_node0001 (10.11.12.10)
        v_verticadb_node0002 (10.11.12.20)
        v_verticadb_node0003 (10.11.12.30)
    Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (UP) v_verticadb_node0002: (UP) v_verticadb_node0003: (UP)
Syncing catalog on verticadb with 2000 attempts.
Database verticadb: Startup Succeeded.  All Nodes are UP

After the database starts, the secondary subclusters are down. You can choose to start them as needed. See Starting a Subcluster.

Starting the database with a subset of primary nodes

As a best pratice, Vertica recommends that you always start an Eon Mode database with all primary nodes. Occasionally, you might be unable to start the hosts for all primary nodes. In that case, you might need to start the database with a subset of its primary nodes.

If start_db specifies a subset of database primary nodes, the following requirements apply:

• The nodes must comprise a quorum: at least 50% + 1 of all primary nodes in the cluster.
• Collectively, the nodes must provide coverage for all shards in communal storage. The primary nodes you use to start the database do not attempt to rebalance shard subscriptions while starting up.

If either or both of these conditions are not met, start_db returns an error. In the following example, start_db specifies three primary nodes in a database with nine primary nodes. The command returns an error that it cannot start the database with fewer than five primary nodes:

$ admintools -t start_db -d verticadb -p 'password' \
    -s 10.11.12.10,10.11.12.20,10.11.12.30
    Starting nodes:
        v_verticadb_node0001 (10.11.12.10)
        v_verticadb_node0002 (10.11.12.20)
        v_verticadb_node0003 (10.11.12.30)
Error: Quorum not satisfied for verticadb.
    3 < minimum 5  of 9 primary nodes.
Attempted to start the following nodes:
Primary
        v_verticadb_node0001 (10.11.12.10)
        v_verticadb_node0003 (10.11.12.30)
        v_verticadb_node0002 (10.11.12.20)
Secondary

 hint: you may want to start all primary nodes in the database
Database start up failed.  Cluster partitioned.

If you try to start the database with fewer than the full set of primary nodes and the cluster fails to start, Vertica processes might continue to run on some of the hosts. If so, subsequent attempts to start the database will return with an error like this:

Error: the vertica process for the database is running on the following hosts:
10.11.12.10
10.11.12.20
10.11.12.30
This may be because the process has not completed previous shutdown activities. Please wait and retry again.
Database start up failed.  Processes still running.
Database verticadb did not start successfully: Processes still running.. Hint: you may need to start all primary nodes.

Before you can start the database, you must stop the Vertica server process on the hosts listed in the error message, either with the admintools menus or the admintools command line's stop_host tool:

$ admintools -t stop_host -s 10.11.12.10,10.11.12.20,10.11.12.30

2 - Stopping the database

There are many occasions when you must stop a database, for example, before an upgrade or performing various maintenance tasks. You can stop a running database through one of the following:

• Management Console

• Administration Tools interface

• Vertica functions

• Command line

You cannot stop a running database if any users are connected or Database Designer is building or deploying a database design.

Administration tools

To stop a running database with admintools:

1. Verify that all cluster nodes are up. If any nodes are down, identify and restart them.

2. Close all user sessions:

   • Identify all users with active sessions by querying the SESSIONS system table. Notify users of the impending shutdown and request them to shut down their sessions.

   • Prevent users from starting new sessions by temporarily resetting configuration parameter MaxClientSessions to 0:

     => ALTER DATABASE DEFAULT SET MaxClientSessions = 0;
     

   • Close all remaining user sessions with Vertica functions CLOSE_SESSION and CLOSE_ALL_SESSIONS.

   Note

   You can also force a database shutdown and block new sessions with the function SHUTDOWN.

3. Open Vertica Administration Tools.

4. From the Main Menu:

   • Select Stop Database

   • Click OK

5. Select the database to stop and click OK.

6. Enter the password (if asked) and click OK.

7. When prompted that database shutdown is complete, click OK.

Vertica functions

You can stop a database with the SHUTDOWN function. By default, the shutdown fails if any users are connected. To force a shutdown regardless of active user connections, call SHUTDOWN with an argument of true:

=> SELECT SHUTDOWN('true');
      SHUTDOWN
-------------------------
Shutdown: sync complete
(1 row)

In Eon Mode databases, you can stop subclusters with the SHUTDOWN_SUBCLUSTER and SHUTDOWN_WITH_DRAIN functions. SHUTDOWN_SUBCLUSTER shuts down subclusters immediately, whereas SHUTDOWN_WITH_DRAIN performs a graceful shutdown that drains client connections from subclusters before shutting them down. For more information, see Starting and stopping subclusters.

The following example demonstrates how you can shut down all subclusters in an Eon Mode database using SHUTDOWN_WITH_DRAIN:

=> SELECT SHUTDOWN_WITH_DRAIN('', 0);
NOTICE 0:  Begin shutdown of subcluster (default_subcluster, analytics)
                SHUTDOWN_WITH_DRAIN
-----------------------------------------------------------------------
Shutdown message sent to subcluster (default_subcluster, analytics)

(1 row)

Command line

You can stop a database with the admintools command stop_db:

$ $ admintools -t stop_db --help
Usage: stop_db [options]

Options:
  -h, --help            Show this help message and exit.
  -d DB, --database=DB  Name of database to be stopped.
  -p DBPASSWORD, --password=DBPASSWORD
                        Database password in single quotes.
  -F, --force           Force the database to shutdown, even if users are
                        connected.
  -z, --if-no-users     Only shutdown if no users are connected. If any users
                        are connected, exit with an error.
  -n DRAIN_SECONDS, --drain-seconds=DRAIN_SECONDS
                        Eon db only: seconds to wait for user connections to
                        close. Default value is 60 seconds. When the time
                        expires, connections will be forcibly closed and the
                        db will shut down.
  --timeout=NONINTERACTIVE_TIMEOUT
                        Set a timeout (in seconds) to wait for actions to
                        complete ('never') will wait forever (implicitly sets
                        -i).
  -i, --noprompts       Do not stop and wait for user input(default false).
                        Setting this implies a timeout of 20 min.

stop_db behavior depends on whether it stops an Eon Mode or Enterprise Mode database.

Stopping an Eon Mode database

In Eon Mode databases, the default behavior of stop_db is to call SHUTDOWN_WITH_DRAIN to gracefully shut down all subclusters in the database. This graceful shutdown process drains client connections from subclusters before shutting them down.

The stop_db option -n (--drain-seconds) lets you specify the number of seconds to wait—by default, 60—before forcefully closing client connections and shutting down all subclusters. If you set a negative -n value, the subclusters are marked as draining but do not shut down until all active user sessions disconnect.

In the following example, the database initially has an active client session, but the session closes before the timeout limit is reached and the database shuts down:

$ admintools -t stop_db -d verticadb --password password --drain-seconds 200
Shutdown will use connection draining.
Shutdown will wait for all client sessions to complete, up to 200 seconds
Then it will force a shutdown.
Poller has been running for 0:00:00.000025 seconds since 2022-07-27 17:10:08.292919


------------------------------------------------------------
client_sessions     |node_count          |node_names
--------------------------------------------------------------
0                   |5                   |v_verticadb_node0005,v_verticadb_node0006,v_verticadb_node0003,v_verticadb_node0...
1                   |1                   |v_verticadb_node0001
STATUS: vertica.engine.api.db_client.module is still running on 1 host: nodeIP as of 2022-07-27 17:10:18. See /opt/vertica/log/adminTools.log for full details.
Poller has been running for 0:00:11.371296 seconds since 2022-07-27 17:10:08.292919

...

------------------------------------------------------------
client_sessions     |node_count          |node_names
--------------------------------------------------------------
0                   |5                   |v_verticadb_node0002,v_verticadb_node0004,v_verticadb_node0003,v_verticadb_node0...
1                   |1                   |v_verticadb_node0001
Stopping poller drain_status because it was canceled
Shutdown metafunction complete. Polling until database processes have stopped.
Database verticadb stopped successfully

If you use the -z (--if-no-users) option, the database shuts down immediately if there are no active user sessions. Otherwise, the stop_db command returns an error:

$ admintools -t stop_db -d verticadb --password password --if-no-users
Running shutdown metafunction. Not using connection draining

             Active session details
| Session id                        | Host Ip        | Connected User |
| ------- --                        | ---- --        | --------- ---- |
| v_verticadb_node0001-107720:0x257 | 192.168.111.31 | analyst        |
Database verticadb not stopped successfully for the following reason:
Shutdown did not complete. Message: Shutdown: aborting shutdown
Active sessions prevented shutdown.
Omit the option --if-no-users to close sessions. See stop_db --help.

You can use the -F (or --force) option to shut down all subclusters immediately, without checking for active user sessions or draining the subclusters.

Stopping an Enterprise Mode database

In Enterprise Mode databases, the default behavior of stop_db is to shut down the database only if there are no active sessions. If users are connected to the database, the command aborts with an error message and lists all active sessions. For example:

$ /opt/vertica/bin/admintools -t stop_db -d VMart
Info: no password specified, using none

        Active session details
| Session id                     | Host Ip       | Connected User |
| ------- --                     | ---- --       | --------- ---- |
| v_vmart_node0001-91901:0x162   | 10.20.100.247 | analyst        |
Database VMart not stopped successfully for the following reason:
Unexpected output from shutdown: Shutdown: aborting shutdown
NOTICE: Cannot shut down while users are connected

You can use the -F (or --force) option to override user connections and force a shutdown.

3 - CRC and sort order check

As a superuser, you can run the Index tool on a Vertica database to perform two tasks:

• Run a cyclic redundancy check (CRC) on each block of existing data storage to check the data integrity of ROS data blocks.

• Check that the sort order in ROS containers is correct.

If the database is down, invoke the Index tool from the Linux command line. If the database is up, invoke from VSQL with Vertica meta-function RUN_INDEX_TOOL:

If invoked from the command line, the Index tool runs only on the current node. However, you can run the Index tool on multiple nodes simultaneously.

Result output

The Index tool writes summary information about its operation to standard output; detailed information on results is logged in one of two locations, depending on the environment where you invoke the tool:

For information about evaluating output for possible errors, see:

• Evaluating CRC errors
• Evaluating sort order errors

Optimizing performance

You can optimize meta-function performance by narrowing the scope of the operation to one or more projections, and specifying the number of threads used to execute the function. For details, see RUN_INDEX_TOOL.

3.1 - Evaluating CRC errors

Vertica evaluates the CRC values in each ROS data block each time it fetches data disk to process a query. If CRC errors occur while fetching data, the following information is written to the vertica.log file:

CRC Check Failure Details:File Name:
File Offset:
Compressed size in file:
Memory Address of Read Buffer:
Pointer to Compressed Data:
Memory Contents:

The Event Manager is also notified of CRC errors, so you can use an SNMP trap to capture CRC errors:

"CRC mismatch detected on file <file_path>. File may be corrupted. Please check hardware and drivers."

If you run a query from vsql, ODBC, or JDBC, the query returns a FileColumnReader ERROR. This message indicates that a specific block's CRC does not match a given record as follows:

hint: Data file may be corrupt.  Ensure that all hardware (disk and memory) is working properly.
Possible solutions are to delete the file <pathname> while the node is down, and then allow the node
to recover, or truncate the table data.code: ERRCODE_DATA_CORRUPTED

3.2 - Evaluating sort order errors

If ROS data is not sorted correctly in the projection's order, query results that rely on sorted data will be incorrect. You can use the Index tool to check the ROS sort order if you suspect or detect incorrect query results. The Index tool evaluates each ROS row to determine whether it is sorted correctly. If the check locates a row that is not in order, it writes an error message to the log file with the row number and contents of the unsorted row.

Reviewing errors

1. Open the indextool.log file. For example:

   $ cd VMart/v_check_node0001_catalog
   

2. Look for error messages that include an OID number and the string Sort Order Violation. For example:

   <INFO> ...on oid 45035996273723545: Sort Order Violation:
   

3. Find detailed information about the sort order violation string by running grep on indextool.log. For example, the following command returns the line before each string (-B1), and the four lines that follow (-A4):

   [15:07:55][vertica-s1]: grep -B1 -A4 'Sort Order Violation:' /my_host/databases/check/v_check_node0001_catalog/indextool.log
   2012-06-14 14:07:13.686 unknown:0x7fe1da7a1950 [EE] <INFO> An error occurred when running index tool thread on oid 45035996273723537:
   Sort Order Violation:
   Row Position: 624
   Column Index: 0
   Last Row: 2576000
   This Row: 2575000
   --
   2012-06-14 14:07:13.687 unknown:0x7fe1dafa2950 [EE] <INFO> An error occurred when running index tool thread on oid 45035996273723545:
   Sort Order Violation:
   Row Position: 3
   Column Index: 0
   Last Row: 4
   This Row: 2
   --
   

4. Find the projection where a sort order violation occurred by querying system table STORAGE_CONTAINERS. Use a storage_oid equal to the OID value listed in indextool.log. For example:

   => SELECT * FROM storage_containers WHERE storage_oid = 45035996273723545;