How to calculate pod consumption hours

Products

• VMware Tanzu Kubernetes Grid Integrated Edition

Issue/Introduction

This article describes how an operator can calculate the number of pod hours consumed over a given time period for billing purposes. Connect to the billing database to run queries against your cluster.

Environment

Resolution

To connect to the billing database, follow the instructions listed below:

1. In a browser, navigate to Ops Manager.

2. Select the Pivotal Container Service tile.

3. Select the Status tab. Record the IP address that appears in the IPS column. 

4. Select the Credentials tab.

5. Click the credential link next to Cf Mysql Billing Db Password. Record the MySQL billing database password that appears.

6. SSH into the PKS VM using the following command:

$ bosh -e ENVIRONMENT -d pivotal-container-service/DEPLOYMENT-ID ssh pivotal-container-service/VM-ID

7. Log in to the billing database using the following command:

$ mysql -h IP-ADDRESS -u billing -p billing

When prompted by the command line, enter the MySQL billing database password you recorded earlier.

Viewing raw pod consumption data

To view the raw consumption data for all pods, run the following query: 

MariaDB [billing]> select * from pods;

Calculating total pod consumption for a given time period

To calculate total pod consumption in hours for a given time period, run the following query:

SET @beginning_of_period = '2018-11-26 00:00:00';
SET @end_of_period = '2018-11-27 00:00:00';
SELECT SUM(TIMESTAMPDIFF(HOUR, GREATEST(first_seen, @beginning_of_period),
                         LEAST(last_seen, @end_of_period))) AS pod_hours
FROM pods
WHERE first_seen < @end_of_period
  AND last_seen > @beginning_of_period;

Modify the values for the beginning and end periods as needed. The pod hours calculated are rounded down to the nearest hour. For example, if a pod runs for 1 hour, 59 minutes, and 59 seconds, it is billed for 1 hour only.
 

Calculating pod consumption by pod

To calculate pod consumption in hours by pod, run the following query from the billing database:

SET @beginning_of_period = '2018-11-26 00:00:00';
SET @end_of_period = '2018-11-27 00:00:00';
SELECT id,
       @start_of_billing := GREATEST(first_seen, @beginning_of_period) AS start_of_billing,
       @end_of_billing := LEAST(last_seen, @end_of_period)             AS end_of_billing,
       TIMESTAMPDIFF(HOUR, @start_of_billing, @end_of_billing)         AS pod_hours
FROM pods
WHERE first_seen < @end_of_period
  AND last_seen > @beginning_of_period;

Calculating pod consumption by service instance and namespace

To calculate pod hour consumption in hours by service instance and namespace, run the following query from the billing database:

SET @beginning_of_period = '2018-11-26 00:00:00';
SET @end_of_period = '2018-11-27 00:00:00';
SELECT service_instance_id,
       namespace,
       SUM(TIMESTAMPDIFF(HOUR, GREATEST(first_seen, @beginning_of_period),
                         LEAST(last_seen, @end_of_period))) AS pod_hours
FROM pods
WHERE first_seen < @end_of_period
  AND last_seen > @beginning_of_period
GROUP BY service_instance_id, namespace