How to Capture Table Size Metrics on YugabyteDB Anywhere

Generally, it’s good to keep a record of the growing size of databases and tables. This data can be helpful to report the growth of table and database size over time, determine why there was a sudden drop or increase in table size, or infer table create and drop dates. YugabyteDB Anywhere provides an API to capture table-level size metrics, which can be used to capture the details daily in a permanent table.

So now, let’s walk through how to capture table size metrics for YugabyteDB Anywhere using this API.

Instructions to Capture Table Size Metrics

Before we get started, let’s review the five high-level steps:

1. Create the required database objects
   1. Create a database
   2. Create a table
   3. Create a sequence
2. Configure Python modules
3. Configure environment variables
4. Capture data in a persistent table
   1. Capture metrics
   2. Store data in the table
5. Validate

Step 1: Create Required Database Objects

1. Create a database
   Create a database to store the data.

   create database yb_stats_db;

2. Create a sequence
   Use the following DDL to create a sequence within the database created in Step 1a.

   create sequence table_stats_run_id_sequence start 1 increment 1;

3. Create a table
   Use the following DDL to create a table to capture the table metrics in a persistent table within the database created in Step 1a.

   create table table_stats (run_id bigint,current_dt date,tableUUID text, dbname text,tableType text,tableName text,relationType text,sizeBytes float,isIndexTable text, primary key((run_id,current_dt,tableUUID) ));

   Result: You have successfully created the required database objects to store table metrics data.

Step 2: Configure Python Modules

# (Tested with Python version 2.7.18)

import httplib
import json
import psycopg2
from datetime import datetime

Step 3: Configure Environment Variables

## YB Anywhere platform IP Address. 
yb_platform_addr="10.9.123.49"

## Replace Customer UUID
cUUID="3a69de7a-f74e-4124-adcd-de19484006da"

## Replace Universe UUID
uniUUID="7e848e2a-f7e1-47bc-8517-ec4a64b4e285"

yb_user_token="<< Replace YB user token>>"

#Define Variables for connecting to the Database
yb_db_addr="10.9.124.17"
yb_stats_db="yb_stats_db"
yb_db_port="5433"
yb_db_user="yugabyte"
yb_db_password="<< Replace with your password >>"
yb_db_ssl_path="<< Replace with SSL cert path>>"

Step 4: Capture Data in a Persistent Table

1. Capture metrics

   conn = httplib.HTTPConnection(yb_platform_addr)
   
   headers = {
       'Content-Type': "application/json",
       'X-AUTH-YW-API-TOKEN': yb_user_token
   }
   
   conn.request("GET", "/api/v1/customers/"+cUUID+"/universes/"+uniUUID+"/tables", headers=headers)
   
   res = conn.getresponse()
   data = json.loads(res.read())

   Visit our docs to read more about tables API.

2. Store data in table

   try:
     conn = psycopg2.connect(dbname=yb_stats_db,host=yb_db_addr,port=yb_db_port,user=yb_db_user,password=yb_db_password)
   
     cur = conn.cursor()
   except Exception as error:
     print ("Oops! An exception has occurred:", error)
   
   select_sql="SELECT nextval('table_stats_run_id_sequence')"
   cur.execute(select_sql)
   run_id = cur.fetchall()
   
   # Capture current date on which data was inserted into the table
   now = datetime.now()
   
   # convert to string
   date_time_str = now.strftime("%Y-%m-%d")
   
   # Data insertion in the table
   insert_sql="insert into table_stats values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
   
   for iter in data:
   params=(run_id[0][0],date_time_str,iter['tableUUID'],iter['keySpace'],iter['tableType'],iter['tableName'],iter['relationType'],iter['sizeBytes'],iter['isIndexTable'])
     cur.execute(insert_sql,params)
     conn.commit()
   
   conn.close()

Step 5: Validate

Now it’s time to verify that the data is getting captured in the table.

yb_stats_db=# select * from table_stats where dbname='yugabyte' and tablename='test' order by current_dt,run_id;
 run_id | current_dt |          	tableuuid           	|  dbname  |	tabletype 	| tablename |	relationtype 	| sizebytes | isindextable
--------+------------+--------------------------------------+----------+------------------+-----------+---------------------+-----------+--------------
  101 | 2022-09-21 | 000033e8-0000-3000-8000-00000000400f | yugabyte | PGSQL_TABLE_TYPE | test  	| USER_TABLE_RELATION |	341652 | false
  301 | 2022-09-21 | 000033e8-0000-3000-8000-00000000400f | yugabyte | PGSQL_TABLE_TYPE | test  	| USER_TABLE_RELATION | 100972763 | false
(2 rows)
yb_stats_db=#

We can schedule to run the above mentioned steps on a daily basis to capture the metrics.

Conclusion

In this blog, we have successfully captured the table metrics data in persistent tables.

Visit the YugabyteDB Docs site to read more about alerts and YugabyteDB Anywhere APIs.