LATEST VERSION: v1.2 - RELEASE NOTES
Pivotal Container Service v1.1

Viewing and Exporting Usage Data

Page last updated:

This topic describes how operators can view and export usage information from their Pivotal Container Service (PKS) deployment. Operators can use this data to calculate billed usage, perform customer chargebacks, and generate usage reports.

The PKS database stores the following pod usage data:

  • Watermark: the number of pods that run at a single time.
  • Consumption: the memory and CPU usage of pods.

To extract either type of usage data from your PKS deployment, you must bosh ssh into your PKS VM.

About Usage Data

This section describes the usage data records you can view and export from the PKS VM. The agent pod collects both watermark and consumption data for the deployment and sends the data to the PKS aggregator agent. The aggregator agent then stores the data in the PKS database. You can access the PKS database from the PKS VM and export the usage data for your deployment.

Watermark Data

The PKS database stores comma-separated watermark data about the number of pods that run simultaneously in your PKS deployment. You can view the watermark data on the command line or export it to a comma-separated values (.csv) file.

The following is an example of a watermark usage data export:

id,collect_time,day,month,year,agent,agent_pod_cnt,total_pod_cnt,high_watermark_since_install_pod_cnt,high_watermark_since_install_date
1,2018-06-08 01:16:22,7,6,2018,Service-instance_61916de4-8abe-4ec7-a67b-e2568c83dbe0,1,1,1,2018-06-08 01:16:22
2,2018-06-09 01:16:24,8,6,2018,service-instance_61916de4-8abe-4ec7-a67b-e2568c83dbe0,1,1,1,2018-06-08 01:16:22
3,2018-06-10 01:16:34,9,6,2018,service-instance_61916de4-8abe-4ec7-a67b-e2568c83dbe0,1,1,1,2018-06-08 01:16:22
4,2018-06-12 01:12:25,11,6,2018,service-instance_61916de4-8abe-4ec7-a67b-e2568c83dbe0,1,1,1,2018-06-12 01:12:25
5,2018-06-26 01:16:38,25,6,2018,service-instance_748709f7-41be-4c5f-9123-78874caeb602,3,3,3,2018-06-26 01:16:38
6,2018-06-28 01:16:59,27,6,2018,service-instance_748709f7-41be-4c5f-9123-78874caeb602,3,3,3,2018-06-28 01:16:59

The following table describes the database fields related to watermark usage data:

Field Name Description
id Unique record identifier
collect_time The date when the agent collects the record
day The day that corresponds to the record
month The month that corresponds to the record
year The year that corresponds to the record
agent The name of the pod that contains the agent
agent_pod_cnt The maximum number of pods in the cluster on the given day
total_pod_cnt The maximum number of pods in the deployment on the given day
high_watermark_since_install_pod_cnt The maximum number of pods in the deployment since installation
high_watermark_since_install_date The date when the agent logs the maximum number of pods in the deployment

Consumption Data

The PKS database stores resource consumption data for all pods in a deployment. You can view the uptime and pod count for each cluster in your PKS deployment as well as memory and CPU usage for each pod by accessing the database on the command line.

The following table describes the database fields related to consumption usage data:

Field Name Description
id Unique record identifier
agent Unique cluster name
collect_time The date when the agent collects the record
pod_id Unique pod identifier
pod_name Unique pod name
memory_used Pod memory usage
cpu_used Pod CPU usage
pod_cnt The number of pods in the cluster

SSH into the PKS VM

To SSH into your PKS VM using BOSH, follow the steps below:

  1. Gather credential and IP address information for your BOSH Director, SSH into the Ops Manager VM, and use BOSH CLI to log in to the BOSH Director from the Ops Manager VM. For more information, see Advanced Troubleshooting with the BOSH CLI.
  2. Identify the name of your PKS deployment by running bosh -e ENVIRONMENT deployments, where ENVIRONMENT is the alias you set in Set a BOSH Environment Alias.

    For example:
    $ bosh -e pks deployments
    Your PKS deployment name begins with pivotal-container-service and includes a BOSH-generated hash.
  3. Identify the name of your PKS VM by running bosh -e ENVIRONMENT -d DEPLOYMENT vms, where DEPLOYMENT is your PKS deployment name.

    For example:
    $ bosh -e pks -d pivotal-container-service/a1b2c333d444e5f66a77 vms
    Your PKS VM name begins with pivotal-container-service and includes a BOSH-generated hash. This value is different from the hash in your PKS deployment name.
  4. SSH into the PKS VM by running bosh -e ENVIRONMENT -d DEPLOYMENT ssh PKS-VM, where PKS-VM is your PKS VM name.

    For example:
    $ bosh -e pks \
     -d pivotal-container-service/a1b2c333d444e5f66a77 \
     ssh pivotal-container-service/000a1111-222b-3333-4cc5-de66f7a8899b

View and Export Watermark Usage Data

To view and export the watermark usage data for your PKS deployment, follow the steps below:

  1. From the PKS VM, create a new file named print-watermark.sh.

  2. Paste the following contents into the print-watermark.sh file:

    #! /bin/bash
    
    set -e
    
    db_username="$(grep 'DBName: telemetry' -A2 /var/vcap/jobs/mysql/config/mariadb_ctl_config.yml | grep 'User' | tr -d ' ' | cut -d':' -f2)"
    db_password="$(grep 'DBName: telemetry' -A2 /var/vcap/jobs/mysql/config/mariadb_ctl_config.yml | grep 'Password' | tr -d ' ' | cut -d':' -f2)"
    
    mysql_cmd="/var/vcap/packages/mariadb/bin/mysql -u$db_username -h 127.0.0.1 -p$db_password"
    
    watermark_select_result="$($mysql_cmd --execute="use telemetry; select * from pkswatermark order by collect_time")"
    
    watermark_csv="$(echo "$watermark_select_result" | tr '\t' ',')"
    
    echo "$watermark_csv"
    
  3. To print all watermark data to the terminal window, run the following command:

    bash print-watermark.sh

    Note: To print only the most recent watermark data entries, append | tail -nNUMBER to the above command. For example, to display the five most recent watermarks, run bash print-watermark.sh | tail -n5.

  4. (Optional) To write the data to a .csv file, run the following command:

    bash print-watermark.sh > watermarks-$(date -u +"%Y-%m-%dT%H:%M:%SZ").csv

View Consumption Usage Data

To view the consumption data for your PKS deployment, follow the steps below:

  1. On the command line, connect to your PKS database. You can locate your database credentials in /var/vcap/jobs/mysql/config/mariadb_ctl_config.yml.

  2. To view the pksdata table, run describe pksdata;.

    For example:

    MariaDB [telemetry]> describe pksdata;
    +--------------+------------+------+-----+-------------------+-----------------------------+
    | Field        | Type       | Null | Key | Default           | Extra                       |
    +--------------+------------+------+-----+-------------------+-----------------------------+
    | id           | int(11)    | NO   | PRI | NULL              | auto_increment              |
    | agent        | char(253)  | YES  |     | NULL              |                             |
    | collect_time | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    | pod_id       | char(253)  | YES  |     | NULL              |                             |
    | pod_name     | char(253)  | YES  |     | NULL              |                             |
    | memory_used  | bigint(20) | NO   |     | NULL              |                             |
    | cpu_used     | bigint(20) | NO   |     | NULL              |                             |
    | pod_cnt      | bigint(20) | NO   |     | NULL              |                             |
    +--------------+------------+------+-----+-------------------+-----------------------------+
    

  3. Continue to the following sections to run specific queries.

View Pod Counts by Cluster in a Given Time Window

To view the pod counts by cluster for a given time window, run the following query:

select agent,count(distinct pod_name) from pksdata where collect_time between
'BEGINNING-TIMESTAMP' and 'ENDING-TIMESTAMP' group by agent;

Where BEGINNING-TIMESTAMP and ENDING-TIMESTAMP represent the beginning and ending times for your search window. Use the YYYY-MM-DD HH:MM:SS format for both timestamps.

For example:

MariaDB [telemetry]> select agent,count(distinct pod_name) from pksdata where collect_time between '2018-08-14 00:00:00' and '2018-08-14 01:00:00' group by agent;
+-------------------------------------------------------+--------------------------+
| agent                                                 | count(distinct pod_name) |
+-------------------------------------------------------+--------------------------+
| service-instance_1a8617eb-a582-45b5-8749-3b18fb1d661c | 9                        |
| service-instance_1c025915-0ef7-4621-b1eb-aff1c549feb9 | 8                        |
| service-instance_209caeb3-87da-47b7-81db-8b978249f80a | 7                        |
| service-instance_3eb8c734-dce5-4de3-b971-88792d5262d0 | 12                       |
| service-instance_483fa035-c2ee-47c4-b2bd-79329155d6b2 | 3                        |
| service-instance_666ded63-7265-4af8-bfbb-b51f4b1e3f0a | 2                        |
| service-instance_789f7c3f-33f6-4914-88f5-0359c39bf856 | 24                       |
| service-instance_998f7e11-940e-4fdd-9abb-117370dcaaf3 | 12                       |
| service-instance_e3139ecc-5567-4232-9707-1c16e3cdf571 | 10                       |
+-------------------------------------------------------+--------------------------+

View Running Pod Hours for the Current Day

To view the running pod count by cluster for the current day, run the following query:

select agent,timestampdiff(HOUR,min(collect_time), max(collect_time)) + 1 as
"hours today", pod_name from pksdata where collect_time > curdate() -1 group by pod_name;

For example:

MariaDB [telemetry]> select agent,timestampdiff(HOUR,min(collect_time), max(collect_time)) + 1 as "hours today", pod_name from pksdata where collect_time > curdate() -1 group by pod_name;
+-------------------------------------------------------+-------------+-------------------------------------------------+
| agent                                                 | hours today | pod_name                                        |
+-------------------------------------------------------+-------------+-------------------------------------------------+
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-create-bucket-mtlpr                  |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-minio-7d6647dcdd-f62wx               |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |           1 | my-release-spinnaker-clouddriver-c4d56c6b-fd227 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-spinnaker-deck-5f7c94d6b8-qm49n      |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-spinnaker-echo-75655dd8bb-hkc2t      |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-spinnaker-front50-8f58449b6-pwpdq    |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |           1 | my-release-spinnaker-gate-c66c8996-xxt5h        |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-spinnaker-igor-59d69c6c69-2xcrp      |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |           1 | my-release-spinnaker-orca-7c8ffd56d9-pmr7d      |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-spinnaker-rosco-7759ffcc65-jsw66     |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | my-release-upload-build-image-d4f9f             |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |           1 | nginx-77v62                                     |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |           1 | nginx-kkrm8                                     |
| service-instance_db568933-c59d-409e-a488-a716641e55cd |           1 | nginx-mqr9x                                     |
| service-instance_db568933-c59d-409e-a488-a716641e55cd |           1 | nginx-rbnlm                                     |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |          21 | wordpress-mysql-bcc89f687-vjfvc                 |
+-------------------------------------------------------+-------------+-------------------------------------------------+

View Pods by Running Time

To view the running pod count by cluster for each hour in the current day, run the following query:

select agent,timestampdiff(HOUR,min(collect_time), max(collect_time)) + 1 as
"hours today", pod_name from pksdata where collect_time > curdate() -1 group by pod_name;

For example:

MariaDB [telemetry]> select agent,hour(collect_time) as hour,count(distinct pod_name) from pksdata group by agent,hour;
+-------------------------------------------------------+------+--------------------------+
| agent                                                 | hour | count(distinct pod_name) |
+-------------------------------------------------------+------+--------------------------+
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    0 |                       12 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    1 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    2 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    3 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    4 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    5 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    6 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    7 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    8 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |    9 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   10 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   11 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   12 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   13 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   14 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   15 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   16 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   17 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   18 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   19 |                        9 |
| service-instance_b43c891c-6a4d-42ef-8bcd-402c0433f89e |   20 |                       11 |
| service-instance_db568933-c59d-409e-a488-a716641e55cd |   20 |                        2 |
+-------------------------------------------------------+------+--------------------------+

Please send any feedback you have to pks-feedback@pivotal.io.

Create a pull request or raise an issue on the source for this page in GitHub