Storage Planning of TigerGraph DB

TigerGraph DB is a powerful and high-performance graph database that excels in processing and analyzing complex relationships within vast datasets. To ensure optimal functionality and performance, proper storage planning is crucial. In this blog post, we will delve into the ideal allocation of disk types and storage types for TigerGraph DB.

Below disk/mounts provides the information for ideal allocation of disk types and storage types for TigerGraph DB to function properly. 

Disk/mount allocation:

Mount	Disk Type	Usage	Owner	Comments
/home/tigergraph	Attached SSD	Home directory for tigergraph user	tigergraph:tigergraph
/tigergraph/app	Attached SSD	To store Tigergraph DB binaries	tigergraph:tigergraph
/tigergraph/data	Attached SSD	To store Tigergraph Data	tigergraph:tigergraph
/tigergraph/kafka	Attached SSD	To store Tigergraph Kafka data	tigergraph:tigergraph	As Kafka is write intensive, create a soft link to this from /tigergraph/data/kafka
/tigergraph/logs	Attached SSD	To store Tigergraph DB logs	tigergraph:tigergraph	Can be a SAN storage but it is better to have Attached SSD as there are many components that actively writes logs.
/tigergraph/tmp	Attached SSD	To store Tigergraph DB temporary files	tigergraph:tigergraph
/tigergraph/backup	Any	To store backup	tigergraph:tigergraph	Depends on organization requirement. Usually requires 3X of data directory
/tigergraph/fileoutput	Attached SSD	To store output files from GSQL query	tigergraph:tigergraph
/tigergraph/toolbox	Any	To store general files for monitoring, log collection etc.	tigergraph:tigergraph	Described in file staging location for more details.

Below are the details for file staging locations for backup and scripts from above mount points described.

Staging type	Location	Owner
Backup staging	/tigergraph/backup/staging	tigergraph:tigergraph
Scripts	/tigergraph/toolbox/scripts	tigergraph:tigergraph
Support collection	/tigergraph/toolbox/support_collection	tigergraph:tigergraph
UDF files	/tigergraph/toolbox/udf	tigergraph:tigergraph
UDF files backup	/tigergraph/toolbox/udf_backup	tigergraph:tigergraph
Python binaries	/tigergraph/toolbox/oel7python38	tigergraph:tigergraph
Downloads	/tigergraph/toolbox/downloads	tigergraph:tigergraph

The above can be created with the below command.

mkdir -p /tigergraph/backup/staging /tigergraph/toolbox/scripts /tigergraph/toolbox/support_collection /tigergraph/toolbox/udf /tigergraph/toolbox/udf_backup /tigergraph/toolbox/oel7python38 /tigergraph/toolbox/downloads

***

Patching and Upgrading TigerGraph Database

Keeping your TigerGraph database up-to-date is crucial for ensuring optimal performance, security, and access to the latest features. In this guide, will walk you through the essential steps of patching and upgrading your TigerGraph database to the latest version.

Before upgrade make sure if all the services are up and normal and then take full backup and UDF backups.

$ gadmin status -v
$ gadmin backup create full_upgradebackup --staging-path=/tigergraph/backups/backup_staging &
$ gadmin backup list

Backup UDF files

> GET ExprFunctions TO "/tigergraph/backups/UDF/udf_backup/ExprFunctions_20240229.hpp"
> GET ExprUtil TO "/tigergraph/backups/UDF/udf_backup/ExprUtil_20240229.hpp"

Download the official binaries and extract and run upgrade.

$ tar -xvf tigergraph-3.9.3-offline.tar
$ bash tigergraph-3.9.3-offline/install.sh -U

Applying Subsequent Patches:

$ tar -xvf tigergraph-3.9.3-2-offline.tar
$ bash tigergraph-3.9.3-2-offline/install.sh -U

***

Query to generate and print the current time in UTC - Tigergraph DB

 Create a TigerGraph distributed query to generate and print the current time in UTC.

> CREATE GRAPH graph_name()
> CREATE DISTRIBUTED QUERY time_utc(/* Parameters here */) FOR GRAPH graph_name { DATETIME start_overall_time; start_overall_time = NOW(); print start_overall_time; }
> INSTALL QUERY time_utc
> RUN QUERY time_utc()

This should print the current UTC time in the console.

***

Automatic Page Refresh with JavaScript

 1) Run this code in the Chrome browser console

Right-click on the newtab/window and select "Inspect" or simply press Ctrl + Shift + I (Windows/Linux) or Cmd + Opt + I (Mac) to open the Chrome Developer Tools.

In the Developer Tools, navigate to the "Console" tab.

Copy and paste the provided JavaScript code into the console.

Press Enter to execute the below code.

2) Code is as below

bypramod = window.open("http://www.bypramod.blogspot.com");
timer1 = setInterval(function() {
    bypramod.location.href = "http://www.bypramod.blogspot.com";
}, 5000); // 5000 milliseconds (5 seconds) interval

 

Explanation:

bypramod = window.open("http://www.bypramod.blogspot.com");: This line opens a new browser window or tab with the specified URL ("http://www.bypramod.blogspot.com") and assigns it to the variable bypramod. The window.open() method is commonly used to open a new browser window or tab via JavaScript.

timer1 = setInterval(function() { bypramod.location.href = "http://www.bypramod.blogspot.com"; }, 5000);: This line sets up an interval that triggers a function at regular intervals. In this case, the function is responsible for changing the location (URL) of the bypramod window/tab to "http://www.bypramod.blogspot.com". The interval is set to 5000 milliseconds, which is equivalent to 5 seconds.

setInterval() is a JavaScript function that repeatedly calls a provided function at specified time intervals.

The provided function inside setInterval() is an anonymous function (a function without a name) that changes the href property of the bypramod window/tab, effectively refreshing the content by loading the specified URL.

---

Note:

The timing interval may be too short (every second). Some websites or servers may restrict frequent requests to prevent abuse. 

***

Basic SQLite Database commands

SQLite Commands for Database Management

1. Invoke sqlite3 for a specific database:

   sqlite3 /tigergraph/tigergraph/data/ts3/db

2. Attach a database in the sqlite3 shell:

   sqlite> ATTACH DATABASE "/tigergraph/tigergraph/data/ts3/db" as db;

3. List tables:

   sqlite> .tables

   Output:

   datapoints

4. Describe schema for a table (e.g., "datapoints"):

   sqlite> .schema datapoints

   Output:

   
   CREATE TABLE "datapoints" ("id" integer not null primary key autoincrement, "when" integer, "where" varchar(255), "who" varchar(255), "what" varchar(255), "detail" varchar(255), "submitted" integer, "written" integer);
   CREATE INDEX whenidx on datapoints ("when");
           

5. Get the max_page_count of the database "db":

   sqlite> PRAGMA db.max_page_count;

   Output:

   1073741823

6. Exit out of sqlite3 shell:

   sqlite> .quit

Clean up ts3 db file in Tigergraph DB

What's TS3:

TigerGraph System Service State (TS3) is a TigerGraph sub-system which helps monitor the TigerGraph system. It serves as backend of TigerGraph Admin Portal.

Minimum space required for auto cleanup:

Usually ts3's db file requires twice the space to get cleanup, and if for some reason if the db file is not cleaned up automatically, follow below steps to clean up old metrics.

Default clean up schedule:

By default clean up is done 0:0:0 every day

Processes:

ts3serv and ts3 are corresponding process for the DB file that was under 

File location:

$ (gadmin config get System.DataRoot)/ts3/

Verify retention:

$ gadmin config get TS3.RetentionPeriodDay

Change retention if required as below:

$ gadmin config set TS3.RetentionPeriodDay 8

$ gadmin config apply

$ gadmin restart ts3serv,ts3

Main steps:

a) Stop ts3 and ts3serv

$ gadmin stop ts3serv ts3 -y

b) Verify the size and move the file to a desired location where there is atlease double to free space.

$ du -sh /tigergraph/data/ts3/db

$ mv /tigergraph/data/ts3/db /temp/location/

c) Download sqlite3 tool from official repository.

https://www.sqlite.org/download.html

File: sqlite-tools-linux-x64-3450100.zip (https://www.sqlite.org/2024/sqlite-tools-linux-x64-3450100.zip)

or

$ wget https://www.sqlite.org/2024/sqlite-tools-linux-x64-3450100.zip

d) $ unzip sqlite-tools-linux-x64-3450100.zip

e) Execute the below command by passing the temporary location of ts3 db file. (Don't use sqlite3 full path as it is throwing some error in our case, instead execute staying at unzipped location)

sqlite3 /temp/location/db -cmd "delete from datapoints where \"when\" < $(($(date +%s) - 8 * 24 * 3600)); VACUUM" .quit

f) Verify the size after above step and move back the "db" file to original location.

$ du -sh /temp/location/db

$ mv /temp/location/db /tigergraph/data/ts3/

g) Start first ts3serv and then ts3

$ gadmin start ts3serv ts3 -y

***