Install the database

The Vertica database, which consists of one or more database hosts, contains the cluster operations data for reporting and analysis. You must install and configure the Vertica database properly before you install Platform Analytics.

You can install the Vertica database on a single host or on a cluster of multiple hosts. Installing Vertica on multiple hosts improves the performance and reliability of your database.

What you need to do

The intended database host (or hosts in the cluster) must meet the Platform Analytics and Vertica system requirements. See Prepare for the Platform Analytics installation.

  1. Install Vertica onto the intended database host (or hosts in the cluster).

    For more details on installing Vertica, refer to the Vertica’s documentation Installation and Configuration Guide for the Vertica Analytic Database. Follow the steps described in the Before You Install and Installing Vertica chapters.

    Note:

    You can improve the performance and reliability of your database by installing Vertica on multiple hosts. For best results, Vertica recommends that disk utilization per host should not be more than sixty percent (60%) for a K-Safe=1 database.

  2. Change the create_pa8.0.2_schema.sql manually in case that the K-Safe level of your Vertica database is not 1. K-Safe level of your Vertica database is decided by the number of Vertica database nodes:

    Number of Vertica database nodes <3: K-Safe = 0

    3<= number of Vertica database nodes <5: K-Safe = 1

    Number of Vertica database nodes >5: K-Safe = 2

    Based on the above settings, check the actual K-Safe level of your vertica database and set it accordingly.

  3. Log into a database host as the Vertica database administrator.
  4. Create a new user and grant schema creation privileges.
    1. Create a new user.
      • Logon to vsql as the database administrator.

        /opt/vertica/bin/vsql -w password

        Where password is the database administrator password.

      • create user username identified by ’password’;

        Where username is the name of the user with which to connect to the database, instead of the default user (the database administrator) and password is the password assigning it to the new user.

    2. Grant the schema creation privileges to the created user.

      grant create on database database_name to username

      Where database_name is the name of the database and username is the name of the created user.

    3. Create a schema as a new user.
      • Logon to vsql as the new user

        /opt/vertica/bin/vsql ‑U username ‑w password

        Where username and password are new user credentials.

      • create schema username

        Where username should be the same username created for the new user.

  5. Extract the database schema package as the database administrator.
  6. Launch the vsql command line.
    1. Navigate to the bin subdirectory of the Vertica installation directory.

      By default, this is /opt/vertica/bin.

    2. Run vsql to connect to the database.

      ./vsql ‑d database_name ‑p port ‑U username ‑w password

      where

      • database_name is the name of the database

      • port is the TCP port number or the local socket file extension in which the server is listening for connections. The default is port number 5433.

      • username is the name of the user with which to connect to the database, instead of the default user (the database administrator).

      • password is the password for the database user.

      Alternately, you can run vsql with no options to accept the defaults and specify the administrator password at the prompt.

  7. install_support_hosts.html#v5079471Create the Vertica database schema.

    From the vsql command line, run the create_pa8.0.2_schema.sql script to create a database schema.

    \i file_path/create_pa8.0.2_schema.sql

    where file_path is the file path to the create_pa_schema.sql file (schema/Vertica from the directory where you extracted the database schema package).

  8. Increase the maximum number of client sessions.

    You need to increase the maximum number of client sessions for the database to communicate with the Platform Analytics nodes. In Vertica, this is the SESSIONS parameter, which you can change from the vsql command line by using the SET_CONFIG_PARAMETER function:

    SELECT SET_CONFIG_PARAMETER(’MaxClientSessions’, SESSIONS_value);

    Each Platform Analytics node needs approximately 30 sessions and the Platform Analytics server needs approximately 10 sessions. In addition, each intended user that will be concurrently accessing the live Platform Analytics reports will also need a client session.

    Therefore, for optimal performance of the database, calculate the optimal value of the SESSIONS parameter in Vertica as follows:

    SESSIONS_value = (# of Platform Analytics nodes) × 30 + 10 + (# of concurrent users browsing live reports)

    For example, if you have three Platform Analytics nodes, and will have an extra ten users concurrently access live Platform Analytics reports, increasing the maximum number of client sessions to 110 should be sufficient. From the vsql command line, run the following command:

    SELECT SET_CONFIG_PARAMETER(’MaxClientSessions’, 110);

  9. Modify the default queue timeout of the tm resource pool to 90 seconds.

    From the vsql command line, run the following command:

    ALTER RESOURCE POOL tm queuetimeout 90;

  10. To obtain optimal performance, store TEMP and DATA in different storage locations. Temp data is distributed across available storage locations based on available storage space. However, data can be stored on different storage locations based on predicted or measured access patterns.
  11. For each database host in the cluster, set the blockdev size.

    The block device (blockdev) is the physical storage device on the database. Set the blockdev size to obtain optimal performance by running the following commands for the drives in which your data directory is located:

    sudo blockdev ‑‑getss drive

    sudo blockdev ‑‑getra drive

    For example,

    sudo blockdev ‑‑getss /dev/md0

    sudo blockdev ‑‑getra /dev/md0

    By default, the first command should return 512. The second command should return 2048, which means the readahead parameter is set to 1 MB.

    If the readahead parameter is set too high, the database host may experience a slow mergeout.

  12. Optional. If the readahead parameter is set too high, set the blockdev size to correct this problem.

    sudo blockdev ‑‑setra 2048

    To retain this setting every time the host restarts, copy this line into the /etc/rc.local file.