๐ŸงฉWorkload API

The Workload API provides you with the option to download comprehensive metrics in either JSON or CSV format.

To use the Workload API, it's necessary to generate an access token.

A comprehensive toolkit, with practical usage examples, is accessible on GitHub.

This toolkit comes pre-installed as a default component within the on-premises platform, located at /datasentinel/soft/datasentinel_toolkit.

Workload

Session History

POST https://<<platform-server>>/ds-api/activity/session-history

For more details, refer to Session History Feature.

Headers

Request Body

Response

Example
[
    {
        "time": "2023-08-29T00:00:00Z",
        "queryid": "-601699501168248552",
        "application_name": "N/A",
        "query_md5_id": "66edf7db36e8105c9d1a63341ee3b31a",
        "provider": "azure",
        "client_host_name": "127.0.0.1/32",
        "datacenter": "lyon",
        "application": "crm",
        "ha_cluster": "",
        "user_name": "datasentinel",
        "environment": "production",
        "wait_event": "CPU",
        "pid": "3",
        "database": "postgres",
        "pg_instance": "pg-crm-2031@:9342",
        "server": "pg-crm-2031",
        "pg_version": "11.8",
        "command_type": "SELECT",
        "wait_event_type": "CPU"
    },
    {
        "time": "2023-08-29T00:00:00Z",
        "queryid": "7363511080490450112",
        "ha_cluster": "",
        "pid": "1",
        "user_name": "crm_user",
        "client_host_name": "127.0.0.1/32",
        "application_name": "N/A",
        "server": "pg-crm-2031",
        "pg_version": "11.8",
        "environment": "production",
        "wait_event": "WALWriteLock",
        "application": "crm",
        "database": "crm",
        "command_type": "UPDATE",
        "datacenter": "lyon",
        "pg_instance": "pg-crm-2031@:9342",
        "wait_event_type": "LWLock",
        "query_md5_id": "101f8dda390eb1c34a2ebb5e65ec8c46",
        "provider": "azure"
    },
    {
        "time": "2023-08-29T00:00:00Z",
        "queryid": "7363511080490450112",
        "server": "pg-crm-2031",
        "client_host_name": "127.0.0.1/32",
        "datacenter": "lyon",
        "pid": "2",
        "wait_event_type": "CPU",
        "user_name": "crm_user",
        "database": "crm",
        "provider": "azure",
        "wait_event": "CPU",
        "ha_cluster": "",
        "query_md5_id": "101f8dda390eb1c34a2ebb5e65ec8c46",
        "environment": "production",
        "pg_version": "11.8",
        "application_name": "N/A",
        "command_type": "UPDATE",
        "pg_instance": "pg-crm-2031@:9342",
        "application": "crm"
    },
  .../...
]

Queries

Summary

POST https://<<platform-server>>/ds-api/activity/queries-summary

Present an overview of global metrics summarizing all executed queries group by database.

Headers

Request Body

Response

Example
[
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "crm",
        "calls": 280835784.0,
        "local_blks_dirtied": 0.0,
        "local_blks_hit": 0.0,
        "local_blks_read": 0.0,
        "local_blks_written": 0.0,
        "rows": 280836486.0,
        "shared_blks_dirtied": 283218594.0,
        "shared_blks_hit": 18442632991.0,
        "shared_blks_read": 811531769.0,
        "shared_blks_written": 259540171.0,
        "temp_blks_read": 0.0,
        "temp_blks_written": 0.0,
        "total_time": 70683067.0,
        "blk_read_time": 0.0,
        "blk_write_time": 0.0,
        "wal_bytes": 0,
        "min_time": 0,
        "max_time": 0,
        "time_per_call": 0,
        "perc_90": 1.0,
        "perc_95": 1.0,
        "perc_99": 2.0
    },
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "pgbench",
        "calls": 120800235.0,
        "local_blks_dirtied": 0.0,
        "local_blks_hit": 0.0,
        "local_blks_read": 0.0,
        "local_blks_written": 0.0,
        "rows": 100000233.0,
        "shared_blks_dirtied": 21209863.0,
        "shared_blks_hit": 2601734412.0,
        "shared_blks_read": 35596001.0,
        "shared_blks_written": 15782890.0,
        "temp_blks_read": 0.0,
        "temp_blks_written": 0.0,
        "total_time": 27121996.0,
        "blk_read_time": 0.0,
        "blk_write_time": 0.0,
        "wal_bytes": 0,
        "min_time": 0,
        "max_time": 0,
        "time_per_call": 0,
        "perc_90": 2.0,
        "perc_95": 10.0,
        "perc_99": 200026.0
    },
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "postgres",
        "calls": 76931.0,
        "local_blks_dirtied": 0.0,
        "local_blks_hit": 0.0,
        "local_blks_read": 0.0,
        "local_blks_written": 0.0,
        "rows": 730733.0,
        "shared_blks_dirtied": 0.0,
        "shared_blks_hit": 37697.0,
        "shared_blks_read": 13655.0,
        "shared_blks_written": 7956.0,
        "temp_blks_read": 0.0,
        "temp_blks_written": 0.0,
        "total_time": 42913.0,
        "blk_read_time": 0.0,
        "blk_write_time": 0.0,
        "wal_bytes": 0,
        "min_time": 0,
        "max_time": 0,
        "time_per_call": 0,
        "perc_90": 3.0,
        "perc_95": 10.0,
        "perc_99": 11.0
    },
  .../...
]

Top Queries

POST https://<<platform-server>>/ds-api/activity/top-queries

For more details, refer to Top Queries Feature.

Headers

Request Body

Response

[
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "crm",
        "user": "crm_user",
        "query_md5_id": "9df8b1f6958fe8b095cc6c6a4b1c1d4c",
        "queryid": "7284485607870996447",
        "calls": 56170616,
        "local_blks_dirtied": 0,
        "local_blks_hit": 0,
        "local_blks_read": 0,
        "local_blks_written": 0,
        "rows": 56170616,
        "shared_blks_dirtied": 56510189,
        "shared_blks_hit": 3682523383,
        "shared_blks_read": 163896328,
        "shared_blks_written": 52168594,
        "temp_blks_read": 0,
        "temp_blks_written": 0,
        "total_time": 14292536,
        "blk_read_time": 0,
        "blk_write_time": 0,
        "wal_bytes": 0,
        "min_time": 0,
        "max_time": 0,
        "time_per_call": 0,
        "text": "UPDATE sbtest5 SET k=k+$1 WHERE id=$2"
    },
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "crm",
        "user": "crm_user",
        "query_md5_id": "6fb8e9ad28ffb534804e58afe649a98a",
        "queryid": "7923329922665361682",
        "calls": 56162074,
        "local_blks_dirtied": 0,
        "local_blks_hit": 0,
        "local_blks_read": 0,
        "local_blks_written": 0,
        "rows": 56162074,
        "shared_blks_dirtied": 56816823,
        "shared_blks_hit": 3691919811,
        "shared_blks_read": 162954673,
        "shared_blks_written": 52059177,
        "temp_blks_read": 0,
        "temp_blks_written": 0,
        "total_time": 14202541,
        "blk_read_time": 0,
        "blk_write_time": 0,
        "wal_bytes": 0,
        "min_time": 0,
        "max_time": 0,
        "time_per_call": 0,
        "text": "UPDATE sbtest2 SET k=k+$1 WHERE id=$2"
    },
.../...
]

Query Details

POST https://<<platform-server>>/ds-api/activity/query

Export execution metrics based on a query identifier.

Headers

Request Body

Response

Example
{
    "time": "2023-08-29T00:00:00Z",
    "pg_instance": "",
    "database": "",
    "user": "",
    "query_md5_id": "fb1268a4d4152c97cd79784da490e612",
    "queryid": "3788389275214642998",
    "calls": 1527.0,
    "local_blks_dirtied": 0.0,
    "local_blks_hit": 0.0,
    "local_blks_read": 0.0,
    "local_blks_written": 0.0,
    "rows": 1527.0,
    "shared_blks_dirtied": 0.0,
    "shared_blks_hit": 0.0,
    "shared_blks_read": 0.0,
    "shared_blks_written": 0.0,
    "temp_blks_read": 0.0,
    "temp_blks_written": 0.0,
    "total_time": 57.0,
    "blk_read_time": 0.0,
    "blk_write_time": 0.0,
    "wal_bytes": 0.0,
    "min_time": 0.0,
    "max_time": 1.0,
    "time_per_call": 0
}

PostgreSQL instance

Informations

POST https://<<platform-server>>/ds-api/activity/pg-instance-infos

Headers

Request Body

Response

Example
[
    {
        "pg_instance": "pg-crm-0926@:9342",
        "tags": "{\"datacenter\": \"lyon\", \"environment\": \"production\", \"new_value\": \"\", \"ha_cluster\": \"\", \"provider\": \"azure\", \"application\": \"crm\"}",
        "version": "11.8",
        "version_full": "PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit",
        "start_time": "2023-07-12 12:49:31",
        "uptime": "48 days 13:10:29",
        "server": "pg-crm-0926",
        "wal_size": 24449868376.0
    },
    {
        "pg_instance": "pg-crm-0926@AgentLess1",
        "tags": "{\"environment\": \"production\", \"provider\": \"aws\", \"application\": \"crm\", \"datacenter\": \"paris\", \"ha_cluster\": \"\", \"new_value\": \"\"}",
        "version": "11.8",
        "version_full": "PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit",
        "start_time": "",
        "uptime": "",
        "server": "pg-crm-0926",
        "wal_size": 0
    },
    {
        "pg_instance": "pg-crm-2031@:9342",
        "tags": "{\"provider\": \"azure\", \"ha_cluster\": \"\", \"application\": \"crm\", \"datacenter\": \"lyon\", \"environment\": \"production\", \"new_value\": \"\"}",
        "version": "11.8",
        "version_full": "PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit",
        "start_time": "2023-06-14 11:14:49",
        "uptime": "76 days 14:45:11",
        "server": "pg-crm-2031",
        "wal_size": 362392338432.0
    },
.../...
]

Activity

POST https://<<platform-server>>/ds-api/activity/pg-instance

Overall activity or minute-by-minute detailed activity of one or more clusters, encompassing cache activity, IO operation statistics, transaction metrics, and more.

Headers

Request Body

Response

Example
[
    {
        "time": "2023-08-29T00:00:00Z",
        "pg_instance": "pg-cluster-02@:9342",
        "database": "all",
        "blk_read_time": 0,
        "blk_write_time": 0,
        "blks_hit": 2296,
        "blks_read": 0,
        "num_backends": 2,
        "temp_bytes": 0,
        "temp_files": 0,
        "tup_deleted": 0,
        "tup_fetched": 1206,
        "tup_inserted": 0,
        "tup_returned": 4354,
        "tup_updated": 0,
        "xact_commit": 273,
        "xact_rollback": 6
    },
    {
        "time": "2023-08-29T00:01:00Z",
        "pg_instance": "pg-cluster-02@:9342",
        "database": "all",
        "blk_read_time": 0,
        "blk_write_time": 0,
        "blks_hit": 1599,
        "blks_read": 0,
        "num_backends": 2,
        "temp_bytes": 0,
        "temp_files": 0,
        "tup_deleted": 0,
        "tup_fetched": 959,
        "tup_inserted": 0,
        "tup_returned": 961,
        "tup_updated": 0,
        "xact_commit": 268,
        "xact_rollback": 1
    },
.../...
]

Background Activity

POST https://<<platform-server>>/ds-api/activity/pg-background

Overall background activity or minute-by-minute detailed background activity of one or more clusters

Headers

Request Body

Response

Example
[
    {
        "time": "2023-08-29T00:00:00Z",
        "pg_instance": "pg-cluster-02@:9342",
        "buffers_alloc": 0,
        "buffers_backend": 0,
        "buffers_backend_fsync": 0,
        "buffers_checkpoint": 0,
        "buffers_clean": 0,
        "checkpoint_sync_time": 0,
        "checkpoint_write_time": 0,
        "checkpoints_req": 0,
        "checkpoints_timed": 5757
    },
    {
        "time": "2023-08-29T00:00:00Z",
        "pg_instance": "pg-cluster-03@:9342",
        "buffers_alloc": 0,
        "buffers_backend": 0,
        "buffers_backend_fsync": 0,
        "buffers_checkpoint": 0,
        "buffers_clean": 0,
        "checkpoint_sync_time": 0,
        "checkpoint_write_time": 0,
        "checkpoints_req": 0,
        "checkpoints_timed": 5761
    },
.../...
]

Data Size

POST https://<<platform-server>>/ds-api/activity/data-size

Headers

Request Body

Response

Example
[
    {
        "time": "2023-08-29T00:00:00Z",
        "pg_instance": "pg-crm-0926@:9342",
        "database": "crm",
        "data_size": 5042205343.0
    },
    {
        "time": "2023-08-29T01:00:00Z",
        "pg_instance": "pg-crm-0926@:9342",
        "database": "crm",
        "data_size": 5042279071.0
    },
    {
        "time": "2023-08-29T02:00:00Z",
        "pg_instance": "pg-crm-0926@:9342",
        "database": "crm",
        "data_size": 5042320031.0
    },
    {
        "time": "2023-08-29T03:00:00Z",
        "pg_instance": "pg-crm-0926@:9342",
        "database": "crm",
        "data_size": 5042303647.0
    },
.../...
]

Relations

Tables

POST https://<<platform-server>>/ds-api/activity/tables

For more details, refer to Top Tables Feature.

Headers

Request Body

Response

Example
[
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "crm",
        "schema_name": "public",
        "rel_name": "sbtest1",
        "heap_blks_hit": 3023938125,
        "heap_blks_read": 116350512,
        "idx_blks_hit": 551059060,
        "idx_blks_read": 48145354,
        "idx_scan": 53632940,
        "idx_tup_fetch": 53632940,
        "n_tup_del": 0,
        "n_tup_hot_upd": 0,
        "n_tup_ins": 0,
        "n_tup_upd": 53632940,
        "relid": 16395,
        "relkind": "table",
        "relpages": 31112,
        "reltuples": 99544,
        "seq_scan": 0,
        "seq_tup_read": 0,
        "size": 254869504,
        "tidx_blks_hit": 0,
        "tidx_blks_read": 0,
        "toast_blks_hit": 0,
        "toast_blks_read": 0,
        "vacuum_count": 0,
        "autovacuum_count": 69,
        "analyze_count": 0,
        "autoanalyze_count": 69,
        "last_autoanalyze": "2023-08-30 01:23:49",
        "last_analyze": " ",
        "last_vacuum": " ",
        "last_autovacuum": "2023-08-30 01:20:38",
        "n_live_tup": 99544,
        "n_dead_tup": 1493913,
        "n_mod_since_analyze": 793410,
        "relfrozenxid": 1035344526,
        "age_txid": 76482132
    },
    {
        "pg_instance": "pg-crm-2031@:9342",
        "database": "crm",
        "schema_name": "public",
        "rel_name": "sbtest4",
        "heap_blks_hit": 3023275199,
        "heap_blks_read": 113318090,
        "idx_blks_hit": 551119131,
        "idx_blks_read": 48417561,
        "idx_scan": 53621251,
        "idx_tup_fetch": 53621251,
        "n_tup_del": 0,
        "n_tup_hot_upd": 0,
        "n_tup_ins": 0,
        "n_tup_upd": 53621251,
        "relid": 16431,
        "relkind": "table",
        "relpages": 31345,
        "reltuples": 99983,
        "seq_scan": 0,
        "seq_tup_read": 0,
        "size": 256778240,
        "tidx_blks_hit": 0,
        "tidx_blks_read": 0,
        "toast_blks_hit": 0,
        "toast_blks_read": 0,
        "vacuum_count": 0,
        "autovacuum_count": 67,
        "analyze_count": 0,
        "autoanalyze_count": 47,
        "last_autoanalyze": "2023-08-30 01:37:05",
        "last_analyze": " ",
        "last_vacuum": " ",
        "last_autovacuum": "2023-08-30 01:34:32",
        "n_live_tup": 99983,
        "n_dead_tup": 642400,
        "n_mod_since_analyze": 382455,
        "relfrozenxid": 1009884107,
        "age_txid": 101942551
    },
.../...
]

Indexes

POST https://<<platform-server>>/ds-api/activity/indexes

For more details, refer to Top Tables Feature.

Headers

Request Body

Response

Example
[
    {
        "pg_instance": "pg-sales-0616@:9342",
        "database": "pgbench",
        "schema_name": "public",
        "rel_name": "pgbench_accounts",
        "index_rel_name": "pgbench_accounts_pkey",
        "idx_blks_hit": 507350381,
        "idx_blks_read": 69028628,
        "idx_scan": 191979268,
        "idx_tup_fetch": 191979268,
        "idx_tup_read": 192194347,
        "relpages": 27422,
        "size": 224641024
    },
    {
        "pg_instance": "pg-sales-3127@:9342",
        "database": "pgbench",
        "schema_name": "public",
        "rel_name": "pgbench_accounts",
        "index_rel_name": "pgbench_accounts_pkey",
        "idx_blks_hit": 405799074,
        "idx_blks_read": 55249560,
        "idx_scan": 153597144,
        "idx_tup_fetch": 153597144,
        "idx_tup_read": 153708316,
        "relpages": 27422,
        "size": 224641024
    },
    {
        "pg_instance": "pg-sales-3928@:9342",
        "database": "pgbench",
        "schema_name": "public",
        "rel_name": "pgbench_accounts",
        "index_rel_name": "pgbench_accounts_pkey",
        "idx_blks_hit": 304273074,
        "idx_blks_read": 41423773,
        "idx_scan": 115181486,
        "idx_tup_fetch": 115181486,
        "idx_tup_read": 115256646,
        "relpages": 27422,
        "size": 224641024
    },
]

Server

Activity

POST https://<<platform-server>>/ds-api/activity/server

Overall activity or minute-by-minute detailed server activity

Headers

Request Body

Response

Example
[
    {
        "time": "2023-08-29T00:00:00Z",
        "server": "pg-cluster-01",
        "cpu_model": "AMD EPYC 7281 16-Core Processor",
        "cores": 3,
        "cpus": 3,
        "os_idle": 80.54,
        "os_iowait": 0.05,
        "os_nice": 0.0,
        "os_system": 11,
        "os_user": 8.17,
        "load_avg_1m": 1,
        "mem_active": 436539392,
        "mem_available": 3258077184,
        "mem_buffers": 137588736,
        "mem_cached": 254132224,
        "mem_free": 3337527296,
        "mem_inactive": 105390080,
        "mem_perc": 18,
        "mem_shared": 199053312,
        "mem_total": 3967737856,
        "mem_used": 247914496,
        "bytes_recv": 19483038082,
        "bytes_sent": 2410831905,
        "packets_recv": 17318740,
        "packets_sent": 18680889,
        "read_bytes": 0,
        "read_count": 0,
        "read_time": 0,
        "write_bytes": 366497792,
        "write_count": 42201,
        "write_time": 148376,
        "swap_free": 0,
        "swap_in": 0,
        "swap_out": 0,
        "swap_perc": 0,
        "swap_total": 0,
        "swap_used": 0
    },
    {
        "time": "2023-08-29T00:00:00Z",
        "server": "pg-cluster-02",
        "cpu_model": "AMD EPYC 7282 16-Core Processor",
        "cores": 3,
        "cpus": 3,
        "os_idle": 89.28,
        "os_iowait": 6.84,
        "os_nice": 0.0,
        "os_system": 2,
        "os_user": 2.3,
        "load_avg_1m": 1,
        "mem_active": 1458298880,
        "mem_available": 2368622592,
        "mem_buffers": 139497472,
        "mem_cached": 3053953024,
        "mem_free": 393117696,
        "mem_inactive": 1804337152,
        "mem_perc": 41,
        "mem_shared": 791859200,
        "mem_total": 3967729664,
        "mem_used": 422379520,
        "bytes_recv": 8022400491,
        "bytes_sent": 4718467675,
        "packets_recv": 38419490,
        "packets_sent": 39699631,
        "read_bytes": 2150400,
        "read_count": 85,
        "read_time": 26,
        "write_bytes": 27719852032,
        "write_count": 8819151,
        "write_time": 21503264,
        "swap_free": 0,
        "swap_in": 0,
        "swap_out": 0,
        "swap_perc": 0,
        "swap_total": 0,
        "swap_used": 0
    },
.../...
]

Analytics

Top Consumers

POST https://<<platform-server>>/ds-api/activity/top-consumers

Present the foremost users of a specific resource.

Metrics are grouped according to a particular dimension, which is characterized by any defined tag. Refer to Tags section for more details.

Headers

Request Body

Response

Example
[
    {
        "tag": "crm",
        "instances": 5,
        "db_time": 153852340.0,
        "wal_size": 1236151027544.0,
        "memory_size": 0,
        "data_size": 14445673041.0,
        "cache_blocks_hit": 23498692312.0,
        "disk_blocks_read": 1077888688.0
    },
    {
        "tag": "sales",
        "instances": 7,
        "db_time": 140888413.0,
        "wal_size": 1837793596424.0,
        "memory_size": 0,
        "data_size": 12995168401.0,
        "cache_blocks_hit": 5562268950.0,
        "disk_blocks_read": 485838177.0
    },
.../...
]

Last updated