Top 5 Useful Commands with CockroachDB

In this Blog i explain commands I find myself using more and more. I consider these to be the top 5 and most useful commands to keep in your toolbelt when working with and administering CockroachDB.

SHOW CLUSTER SETTING

SHOW CLUSTER SETTING <setting>

This command allows you to quickly shoe a cluster setting. This is super useful for finding the value of any cluster or configuration setting. A full list of settings can be found on the CockroachDB documentation website.

The example below shows retrieving the CockroachDB version using this command

SHOW ALL CLUSTER SETTINGS;

Another variation of this command and is super useful when debugging issues. This command simply outputs all cluster settings.

SHOW ZONE CONFIGURATIONS

# Show ALl Configurations
SHOW ALL ZONE CONFIGURATIONS;
# The default configuration values
SHOW ZONE CONFIGURATION FROM RANGE default;
# The Configuration for a given database
SHOW ZONE CONFIGURATION FROM DATABASE movr;
# The Configuration for a given table
SHOW ZONE CONFIGURATION FROM TABLE users;
# The configurations for a given index
SHOW ZONE CONFIGURATION FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;
# Show Zone configurations for a Partition
SHOW ZONE CONFIGURATION FROM PARTITION us_west OF INDEX vehicles@primary;

As you can see there is multiple ways to view zone configurations. Zone configurations are settings that control replication settings. Things like lease Preference, Replica Constrains and such. For a full list of zone configuration variables the Cockroach Docs are a good place to start.

The below example shows the output of the command for the movr database

I use this for keeping an eye on settings such as Garbage collection timings, replication factors and any constraints put on the data.

SHOW JOBS

# Show all jobs (not including auto stats)
SHOW JOBS;
# Show system generated jobs
SHOW AUTOMATIC JOBS;
# Filter Jobs
SELECT * FROM [SHOW JOBS] WHERE job_type = 'RESTORE' AND status IN ('running', 'failed') ORDER BY created DESC;
# Filter Automatic jobs
SELECT * FROM [SHOW AUTOMATIC JOBS] WHERE status = ('succeeded') ORDER BY created DESC;
# Show Schema Change Jobs
SELECT * FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE';
# Show Jobs when complete (need to include job_id)
SHOW JOB WHEN COMPLETE 27536791415282;
# Show jobs from a backup scheudule (include schedule_id)
SHOW JOBS FOR SCHEDULE 590204387299262465;

A job in CockroachDB is considered as any long running task. Some examples of this are

• Schema changes through ALTER TABLE, DROP DATABASE, DROP TABLE, and TRUNCATE
• IMPORT
• Enterprise BACKUP and RESTORE
• User-created table statistics created for use by the cost-based optimizer
• The automatic table statistics are not displayed on running the SHOW JOBS statement. To view the automatic table statistics, use SHOW AUTOMATIC JOBS
• Scheduled backups

The below example is all schema change jobs

SHOW STATEMENTS

# Show all active statements
SHOW CLUSTER STATEMENTS;
# Show active statements on a local node
SHOW LOCAL STATEMENTS;
# Show Statements for a given node
SELECT * FROM [SHOW CLUSTER STATEMENTS] WHERE node_id = 2;
# Show statements from a user and location
SELECT * FROM [SHOW CLUSTER STATEMENTS] WHERE client_address = '127.0.0.1:65196' AND user_name = 'maxroach';
# Exlude ad hoc queries from built in sql client
SELECT * FROM [SHOW CLUSTER STATEMENTS] WHERE application_name != '$ cockroach sql'; 
# Cancel a query (Using Query ID from above commands)
CANCEL QUERY '15f92c745fe203600000000000000001';

As you can see there is a lot of ways you can retrieve a lot of really useful information about the queries that are running against the cluster. This is useful if you notice or get alerted on resource issues such as CPU and memory over a certain threshold. You can find quickly what queries are running against the cluster. Capturing the queries and running EXPLAIN on those queries will greatly help in debugging and performance tuning.

EXPLAIN (ANALYSE)

EXPLAIN ANALYZE (DEBUG) Statement

The EXPLAIN ANALYZE (DEBUG) command is my go to command when performance tuning problematic and slow running queries. It is by far my favourite command when helping developers tune queries as it provides everything you need including execution plans and statistics to be able to effectively tune the queries to be performant. There are other options that can be included to get more specific with EXPLAIN ANALYZE which can be found on the CockroachDB docs

For more in depth training on performance tuning and many more educational resources check out the free Cockroach University that Cockroach labs offer

Summary

I hope you found this blog useful with just these 5 commands in your toolbelt you will will become a lot more proficient in administering and monitoring CockroachDB.