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.
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.
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.
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.
Logon to vsql as the database administrator.
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.
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.
./vsql ‑d database_name ‑p port ‑U username ‑w password
Alternately, you can run vsql with no options to accept the defaults and specify the administrator password at the prompt.
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).
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:
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 /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.
To retain this setting every time the host restarts, copy this line into the /etc/rc.local file.