Overview and Quick Start

Effective resource management is crucial for maintaining optimal performance and controlling costs in Civis Platform. This guide provides comprehensive information about monitoring, understanding, and managing both compute and database resources across your organization.

You'll learn how to:

• Monitor compute usage and costs across your organization
• Track database performance and health metrics
• Identify and resolve resource bottlenecks before they impact your work
• Implement best practices for resource optimization
• Make informed decisions about resource allocation

Resource Management Tools

1. Click "Admin" on the Top Navigation Bar
2. Select "Platform Usage Overview"
3. Use the tabs to switch between:
   • Compute Usage: View EC2 instance utilization, compute hours, and workload details
   • Database Status: Monitor database performance metrics and health

Quick Start

If you're experiencing immediate resource issues:

• For queued or slow jobs: Go to the Compute Usage tab to check resource utilization and active workloads
• For slow database queries: Check the Database Status tab for CPU usage, disk usage, and query queue length
• For detailed troubleshooting steps: See the Troubleshooting Guide sections below

EC2 Compute Resources Overview

The Compute Usage Overview provides the user with a summary of compute resource utilization in their organization. Compute resources refer to the EC2 instances that run Jobs, Notebooks, and Services in Platform. This page will help you answer questions like:

• How many compute hours has my organization used over a given month?
• How many compute resources are available to my organization?
• How many resources are currently available?
• How many resources are currently in use?
• What does resource usage look like over time?

Key Terms and Concepts

• EC2 (Elastic Cloud Compute): Cloud compute used to run everything on Platform, including Scripts, Notebooks, and Services
• Workload: A Job, Notebook, or Service that runs on EC2. The primary workload types are:
  • Jobs: Python, R, and Container scripts, plus script-based Imports/Exports and most Custom scripts. (Note: This does not include 'Classic' Jobs, such as SQL and Javascript scripts. An easy way to check if an import or export uses compute resources is to check for /scripts/ in the URL.)
  • Notebooks: Interactive coding environments in Python or R.
  • Services: User-defined applications, such as an R Shiny application.
• Compute Instance: The type of EC2 used to run a workload. Each instance type has CPU, memory, and storage limits which constrain the number of workloads that can run on one instance. Important: A single workload cannot run on multiple instances, thus the maximum CPU and memory of an instance dictates the maximum CPU and memory of a Job, Notebook, or Service
• Partition: A collection of compute instances dedicated to running a particular workload type. Resources for each of your organization’s partitions are shown beneath a partition header, e.g.: “Jobs Compute Instances”. Note that partitions are mutually exclusive, meaning that if one partition is maxed out, work queued to run on that partition will queue until there is space available, but other partitions will be unaffected.
• Compute Hours: The number of EC2 hours used by an organization across all partitions and instance types. Instances that run in parallel count as separate hours. For example, if an organization is using 10 instances in parallel for 100 hours, they will use a total of 1,000 compute hours
• Compute Instance Maximum: The maximum number of EC2 instances an organization can use in parallel, specified for each partition and instance type

Compute Usage Summary

The Compute Usage summary is the landing page when you navigate to the Platform Usage Overview. It provides a real-time snapshot of your organization's compute utilization, broken down into the following areas:

• Monthly Compute Hours: The card at the top of the page shows the cumulative number of compute hours used by your organization for the current month across all partitions and instance types.
• Partitions: Resources are grouped into partitions based on the type of work. The primary partitions are Jobs Compute Instances and Notebooks/Services Compute Instances. These groups are mutually exclusive, meaning that if the Jobs partition is at maximum capacity, it won't affect available resources for Notebooks.
• Instance Types: Within each partition, usage is further broken down by the specific instance type (e.g., M4.2XLARGE, C5.18XLARGE). Each box represents one of these types and displays its current utilization percentage (% used) and the maximum number of parallel instances your organization can run. The bar chart within each box provides a quick visual of this utilization, filling up as more resources are consumed.

To see more details about a specific instance and partition type, including which workloads are running and who they belong to, click on the instance type.

Monthly Compute Hours

The Monthly Compute Hours page provides a 12-month history of your organization's compute usage, allowing you to track and review your Platform resource utilization over time.

1. On the Compute page of Platform Usage Overview, click on “Hours Used”

2. This will take you to the Monthly Compute Hours page:

Instance Detail Page

The Instance Detail Page provides compute usage information for a specific instance type. 

1. Click on one of the instance type boxes. For example, the m4.xlarge instance type within “Notebooks/Services” partition:

Instance Types - Each box on the landing page represents a specific partition and instance type, though some organizations may only have one instance type available. The box displays the Max Instances that can be run in parallel for that resource. The Status Bar shows the current utilization percentage; however, it's important to understand this number represents the total usage averaged across all servers, while new jobs require contiguous resources on a single server. This means it's possible for a new job to be queued even when the status bar shows available capacity. 

For example, consider an environment with two servers, each with 10GB of memory. If small jobs leave 5GB free on Server A and 5GB free on Server B, the overview will show 50% total usage. However, if a new job arrives that needs 8GB of memory, it cannot run. Although 10GB are free in total, no single server has the required 8GB block of space. In such cases, new jobs will queue until currently running jobs complete or are canceled, freeing up a large enough block of resources on a single server.

Current Activity

This page shows a breakdown of workloads currently running on the selected partition and instance type combination.This information can be manually refreshed by clicking “Refresh” located in the top right corner. 

Memory and CPU Status Bars -  Each instance type has specific memory and CPU limits. These status bars show the current utilization of memory and CPU across all available instances for the selected partition and instance type. The utilization of memory and CPU are not necessarily correlated. The memory across all instances of a specific instance type may be maxed out but there might be available CPU (and vice versa). If one of these two resources is maxed out, the entire instance type is maxed out. 

Running - The total number of workloads currently running across all instances for the specific partition and instance type. 

Pending - The total number of queued workloads for the selected partition and instance type. Jobs/Notebooks/Services that are queued will be stuck with a “dedicating resources” log message in Platform and will not be able to run until resources become available. 

Top User Activity - Table that gives insight into the users who are consuming the most resources across the organization. 

Active Workloads - Table that identifies all active workloads utilizing resources in the given partition and instance type. Certain fields (e.g. workload name, ID) may be hidden to users if they don’t have permission to access the workload. Users have the ability to cancel any active workloads which they either own or have been shared on as an editor/manager.

Over Time

These graphs show both CPU and memory usage over time. Users can toggle between last day and last week to look at the data with different levels of granularity. Last Day is the past 24 hours (rolling) and Last Week is the past 7 days (rolling). 

Reading the Graph

• Capacity - the total resource amount available for a particular partition and instance type at that period in time. This capacity can scale up based on Platform autoscaling logic if an organization does not have their maximum number of instances set to always on.
• Requested - the total amount of resources requested by all workloads on that partition and instance type at a certain period in time. Resource requested is the memory or CPU set on each workload under Settings. These numbers constrain the scheduling of new workloads.
• Used - the actual amount of resources used by the workloads running on that partition and instance type at a certain period in time. 

Interpreting the Graph

• If the Resources Used line is much lower than the Resources Requested line, users may be over-provisioning their workloads. To be more efficient, users can reduce the amount of memory or CPU which Jobs, Notebooks, or Services request to leave more resources for other workloads.
• If the Resource Requested line is near or above the Resource Capacity line, the organization is at its maximum capacity. New workloads will be queued until running Jobs have finished or other Notebooks and Services are turned off. 

Troubleshooting and Optimizing Compute Usage

This section addresses common user pain points with immediate steps and links to more detailed, long-term solutions.

Addressing High Compute Usage and Queued Jobs

When compute resources reach maximum capacity, new jobs will be queued. Here’s how to diagnose and resolve the issue:

1. Identify the Bottleneck: Navigate to Admin > Platform Usage Overview. The status bars for each instance type show current utilization. A full bar indicates that a resource is at capacity.
2. Review Active Workloads: Click on the maxed-out instance type to go to the Instance Detail Page. The "Active Workloads" table details every running job, the user running it, and the resources it consumes.
3. Take Immediate Action:
   • Cancel Unneeded Workloads: You can cancel jobs you own directly from the "Active Workloads" table.
   • Coordinate with Top Users: The "Top User Activity" table helps identify who is using the most resources so you can coordinate with them.
   • Monitor the Queue: The "Pending" count shows how many workloads are waiting. As running jobs finish, queued jobs will start automatically.

Best Practices for Resource Management

1. Understanding Resource Requests

Memory Requests

• These are hard limits - if your workload exceeds the requested memory, it will be terminated
• Always set memory requests slightly higher than your expected usage
• If you see jobs failing with exit code 137, this typically indicates you need to increase the memory allocation
• Monitor memory usage through the Platform Usage Overview to optimize requests

CPU Requests

• These are soft limits - your workload can potentially use more CPU if available
• For non-parallel processing tasks, stick to 1000m (1 core) or less
• For parallel processing tasks, request multiple cores based on your parallelization strategy
• Use the "Over Time" graphs to compare requested vs actual usage and adjust accordingly
• CPU usage affects job speed but is not the only factor

2. Choosing the Right Instance Type

Civis Platform offers several instance types to match your workload requirements. Choose the smallest instance type that meets your needs to optimize resource usage and costs.

If you are using larger instance types for standard data processing tasks, you could consider downgrading to a smaller instance type to conserve resources and improve efficiency. To see if you can use a smaller instance, check the "Over Time" graphs. If your 'Used' resources are consistently much lower than the 'Capacity,' you may be able to use a smaller instance type to improve efficiency.

Note: Available instance types may vary by organization. Contact support@civisanalytics.com if you have questions about which instance types are available to your organization.

3. Notebook and Service Management

 Notebooks

• Shut down notebooks when not actively using them - they continue consuming resources even when idle
• Idle notebooks (no running processes) will automatically shut down after 3 hours of inactivity
• Running notebooks automatically shut down after 3 days of continuous uptime to prevent resource waste

Services

• For services, use "On Demand" or "Multischedule" instead of "Always On" when possible.
• To change a service's schedule, press the "Multischedule" button on the top-right panel of the Service configuration page. Choose from three options:
  • "On Demand": Service sleeps after 1 hour of inactivity and wakes when accessed
  • "Specific Times": Service runs during specified times and operates on-demand outside those times
  • "Always On": Service runs continuously (use only when quick startup time is critical)

Database Status Overview

The purpose of the Database Status Overview is to give users visibility into usage and performance details of their Civis-managed Redshift database. The page should help users answer questions like:

• Why are queries running slowly?
• How much disk space is available on my database?
• Has my database experienced downtime recently?

1. Click “Admin” on the Top Navigation Bar
2. Select "Platform Usage Overview"

3. Select the “Database” tab on the left

4. View your database metrics

If you have multiple databases, you can choose which one to view metrics for via the top navigation database dropdown. Select the name of the database to load metrics for it.

Resource Usage

This section contains graphs about CPU Usage and Disk Space Usage, see the Troubleshooting section for tips and more information. . 

CPU Usage

Shows CPU usage by node. As nodes near 100% CPU usage, queries will run slower than usual. 

Disk Space Usage

Shows disk space used by each node. Please note that the available disk space displayed here may be higher than your contractual limit. If you have questions about your contractual disk usage limit please reach out to Support@CivisAnalytics.com.

Query Performance

Queue Length

Indicates how many queries are currently queued.

Query duration by phase

Indicates average amount of time (in milliseconds) queries spend in each phase at a given time. This gives you an idea of what types of queries (copy, unload, ctas, etc.) are taking up the most time on your cluster.

Availability

Status

Shows whether you can currently connect to and use the database

• Status = 1: Database is active and available for use
• Status = 0: Database is down and cannot be accessed. If this occurs, please contact support@civisanalytics.com 

Maintenance

Indicates planned maintenance periods

• Maintenance = 1: Database is undergoing scheduled maintenance and may have limited functionality
• Maintenance = 0: No maintenance is occurring, database is operating normally

Troubleshooting and Resolving Slow Database Performance

Slow database performance is typically caused by high CPU usage, high disk usage (from temporary files), or a long queue of queries. To check database health, go to the Database tab in the Platform Usage Overview.

Review Key Metrics:

CPU Usage

If compute nodes are showing high CPU usage, there may be some non-performant queries on your database. These queries may not show up in the UI, as they could be the result of a script or other job. You can use this code to check for long-running and expensive queries:

SELECT * FROM stv_recents WHERE status='Running' ORDER BY starttime ASC;

To cancel a query, run:

CANCEL <PID>;

If tables are well-distributed, then you should expect the compute-[x] lines to approximately match up, meaning each node of your cluster is doing a similar amount of work. If these lines are considerably different from each other, that is an indicator that all of the data involved in a query is on a single node, and you should consider a different distribution key. To identify queries involved with poorly-distributed (skewed) data, you can use this query:

SELECT * FROM svl_query_metrics_summary
WHERE cpu_skew is not null
  AND query_cpu_time > 100 --filter out short queries
ORDER BY cpu_skew desc;

If your cpu_skew query shows a significant imbalance, it indicates that data for the query is not distributed evenly across the database's compute nodes. This forces a single node to do most of the work. The solution is to change the table's DISTKEY (Distribution Key) to ensure a more even spread of data. 

1. Select a Distribution Key
   • Choose a column with many unique values (like user_id or transaction_id)
   • Avoid columns with few distinct values (like state or boolean fields)
2. Update Distribution Style

-- For distribution by column:
ALTER TABLE your_table
ALTER DISTSTYLE KEY DISTKEY your_new_column_name;

-- For even distribution:
ALTER TABLE your_table
ALTER DISTSTYLE EVEN;

3. Important: Plan for a Maintenance Window
   • ALTER TABLE is a resource-intensive operation that will temporarily lock the table. It can take a significant amount of time for large tables and should be run during a planned outage.
4. Verify Changes
   • Rerun the cpu_skew query to confirm improvement
   • Run these commands to optimize the table's performance:

VACUUM your_table;  -- Reclaims space and re-sorts rows

ANALYZE your_table; -- Updates table statistics used by the query planner

Note: If you're unsure about making these changes, contact support@civisanalytics.com for assistance.

Disk Usage

When disk space usage increases (especially when accompanied by high CPU and queuing), this indicates that there may be a query running that is spilling temporary results out of memory and onto disk. When this occurs, you should refactor problem queries. You can use this code to check for which queries are using the most temp space:

SELECT * FROM svl_query_metrics_summary
WHERE query_temp_blocks_to_disk is not null
ORDER BY query_temp_blocks_to_disk desc;

Queue Length 

A high queue length number indicates the database is overloaded with requests. When there are large spikes in queue length, queries will take longer to start. You can use this code to check for long-running queries:

SELECT * FROM stv_recents WHERE status='Running' ORDER BY starttime ASC;

To cancel a query, run:

CANCEL <PID>;

Query Duration

Excessive time in the “query planning” phase may indicate table locks. Use the below query to investigate table locks:

SELECT * FROM pg_catalog.pg_locks;

Database Status Indicators

If you see the database is down or in maintenance mode, you may want to:

• Plan your database jobs around maintenance windows
• Save your work and retry connections later
• Contact support if you have time-sensitive needs during an outage

Best Practices for Database Management

1. Regular Maintenance

• Monitor and optimize long-running queries
  • Check the CPU Usage and Queue Length graphs for spikes. You can find specific long-running queries using the diagnostic SQL provided in the Troubleshooting section.
• Review and clean up temporary tables
• Check for and resolve table locks promptly
  • Excessive time in the 'query planning' phase of the Query Duration graph is a key indicator. The Troubleshooting section has a query to find active locks.

2. Query Optimization

• Use appropriate distribution and sort keys
• Monitor for skewed data distribution
  • Unbalanced load on the CPU Usage graph is a sign of data skew. The Troubleshooting section provides a detailed guide on how to find and fix this by choosing an appropriate distribution key.
• Break down complex queries into smaller, more manageable parts

3. Storage Management

• Identify and manage large or old tables. The below query helps identify candidates for archival by showing:
  • Table size in megabytes
  • Row count
  • Creation date
  • Schema and table names

-- Find largest tables by size (in MB)
SELECT 
    trim(pgn.nspname) as schema,
    trim(a.name) as table,
    b.mbytes as size_mb,
    a.rows as row_count,
    ti.create_time
FROM 
    (SELECT db_id, id, name, sum(rows) as rows 
     FROM stv_tbl_perm a 
     GROUP BY db_id, id, name) as a 
JOIN pg_class as pgc ON pgc.oid = a.id
JOIN pg_namespace as pgn ON pgn.oid = pgc.relnamespace
LEFT OUTER JOIN 
    (SELECT tbl, count(*) as mbytes 
     FROM stv_blocklist 
     GROUP BY tbl) b ON a.id = b.tbl
LEFT JOIN svv_table_info ti 
    ON trim(pgn.nspname) = ti.schema 
    AND trim(a.name) = ti.table
WHERE mbytes is not null
ORDER BY mbytes DESC;

• Monitor temporary space usage
  • Spikes in the Disk Usage graph can indicate that queries are writing large temporary files. A query to identify these specific jobs is available in the Troubleshooting section.
• Use appropriate compression settings for large tables. See the below for details.

4. Choosing Appropriate Compression Settings

Properly compressing large tables is one of the most effective ways to improve database performance. Compression reduces storage costs and, more importantly, speeds up queries by minimizing the amount of data that needs to be read from the disk.

How to Apply Compression

Redshift can automatically analyze your data and suggest the best compression settings. This is the recommended first step for any uncompressed table.

• Let Redshift Analyze Your Table
  After your table is populated with data, run the ANALYZE COMPRESSION command:

ANALYZE COMPRESSION your_table_name;

This command will return a list of suggested compression "encodings" for each column based on the data it contains.

• Recreate Your Table with the New Encodings
  Use the suggestions from the analysis to write a new CREATE TABLE statement that includes the recommended ENCODE type for each column.
  Example:

CREATE TABLE sales (
  sale_id      INT ENCODE AZ64,
  product_name VARCHAR(255) ENCODE ZSTD,
  region_code  VARCHAR(2) ENCODE BYTEDICT
);

Common Compression Types

While letting the Platform analyze the table is best, it's helpful to understand the most common encoding types and what they're for:

• AZ64: The modern, high-performance standard for numeric and date/time columns.
• ZSTD: The best all-around choice for compressing text columns (VARCHAR, CHAR). It provides an excellent balance of speed and compression ratio.
• BYTEDICT: Perfect for columns with a very small number of unique values (e.g., a country_code column, a status field, or boolean true/false values).
• RAW: This means no compression. It should only be used if a column's data is truly random and cannot be compressed effectively.

Managing Users and Understanding Seat Counts

1. View Active Users: You can see all users in your organization in the Admin Center.
2. Inquire About Seat Allocation: For specific questions about how users are counted towards your contracted seats, please contact our team at support@civisanalytics.com for assistance.