Edit

Share via

How to review tenant statistics using multi-tenant monitoring in Azure Cosmos DB for PostgreSQL

  • Applies to: ✅ PostgreSQL

Important

Azure Cosmos DB for PostgreSQL is no longer supported for new projects. Don't use this service for new projects. Instead, use one of these two services:

Important

Applicable to Citus 11.3 & newer versions

This article shows how to gain insights into resource usage by tenants, by using the citus_stat_tenants view. The view tracks listed metrics for tenants

  • Count of read queries (SELECT queries).
  • Count of total queries (SELECT, INSERT, DELETE, and UPDATE queries).
  • Total CPU usage in seconds.

You learn how to use the citus_stat_tenants view for making informed decisions and how to configure the feature to best fit your application.

Note

  • Privileges (execute, select) on view is granted to the role pg_monitor.

Monitor your top tenants with citus_stat_tenants

When you enable this feature, accounting is activated for SQL commands such as INSERT, UPDATE, DELETE, and SELECT. This accounting is specifically designed for a single tenant. A query qualifies to be a single tenant query, if the query planner can restrict the query to a single shard or single tenant.

You can control the number of tenants tracked with the citus.stat_tenants_limit parameter. Additionally using citus.stat_tenants_period, you can define the time bucket of monitoring. Once a period ends, its statistics are stored in the last period, providing you with the ongoing and last completed period of measurement.

Note

  • Default for citus.stat_tenants_period is 60 seconds.

  • Default for citus.stat_tenants_limit is 100.

Learn more by reviewing a sample multi-tenant application, which helps companies run their ad-campaigns.

CREATE TABLE companies (company_id BIGSERIAL PRIMARY KEY, name TEXT);
SELECT create_distributed_table ('companies', 'company_id');

CREATE TABLE campaigns (id BIGSERIAL, company_id BIGINT, name TEXT, PRIMARY KEY (id, company_id));
SELECT create_distributed_table ('campaigns', 'company_id');

companies & campaigns tables both are sharded on a common tenant key company_id. You can now add companies and the ad campaigns data using commands:

INSERT INTO companies (company_id, name) VALUES (1, 'GigaMarket');
INSERT INTO campaigns (id, company_id, name) VALUES (1, 1, 'Crazy Wednesday'), (2, 1, 'Frozen Food Frenzy');
INSERT INTO campaigns (id, company_id, name) VALUES (3, 1, 'Spring Cleaning'), (4, 1, 'Bread&Butter');
INSERT INTO campaigns (id, company_id, name) VALUES (5, 1, 'Personal Care Refresh'), (6, 1, 'Lazy Lunch');

INSERT INTO companies (company_id, name) VALUES (2, 'White Bouquet Flowers');
INSERT INTO campaigns (id, company_id, name) VALUES (7, 2, 'Bonjour Begonia'), (8, 2, 'April Selection'), (9, 2, 'May Selection');

INSERT INTO companies (company_id, name) VALUES (3, 'Smart Pants Co.');
INSERT INTO campaigns (id, company_id, name) VALUES (10, 3, 'Short Shorts'), (11, 3, 'Tailors Cut');
INSERT INTO campaigns (id, company_id, name) VALUES (12, 3, 'Smarter Casual');

Let's run a few more SELECT and UPDATE queries and see the changes to the citus_stat_tenants view upon executing individual command.

SELECT COUNT(*) FROM campaigns WHERE company_id = 1;

count
-------
     6
(1 row)

SELECT name FROM campaigns WHERE company_id = 2 AND name LIKE '%Selection';

      name
-----------------
 April Selection
 May Selection
(2 rows)

UPDATE campaigns SET name = 'Tailor''s Cut' WHERE company_id = 3 AND name = 'Tailors Cut';

SELECT tenant_attribute,
       read_count_in_this_period,
       query_count_in_this_period,
       cpu_usage_in_this_period
FROM citus_stat_tenants;

Let's now review the resultset captured in the citus_stat_tenants view. For tenant_attribute 1, during this ongoing period, there were five queries executed, resulting in a relatively low CPU usage of 0.000299. Additionally, there was one read count recorded. We observed queries in last 60 seconds for the three tenants, which appear in resultset. Ordering of Top N tenants depends on query_count_in_this_period field.

tenant_attribute | read_count_in_this_period | query_count_in_this_period | cpu_usage_in_this_period
------------------+---------------------------+----------------------------+--------------------------
 1                |                         1 |                          5 |               0.000299
 3                |                         0 |                          3 |               0.000314
 2                |                         2 |                          4 |               0.000295
(3 rows)

Important

  • Tracking tenant level statistics adds an overhead, and by default is disabled.

  • set citus.stat_tenants_track = 'all' to enable tracking.

Next steps

Learn about the concepts related to multi-tenant monitoring and rebalancing active tenants.

Multi-tenant monitoring

Zero-Downtime rebalancing

  • Last updated on