Top 5 Useful Commands with CockroachDB
The Top 5 Commands used to help you become more proficient in administering and using 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 [email protected]_auto_index_fk_city_ref_users; # Show Zone configurations for a Partition SHOW ZONE CONFIGURATION FROM PARTITION us_west OF INDEX [email protected];
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 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
DROP TABLE, and
- User-created table statistics created for use by the cost-based optimizer
- The automatic table statistics are not displayed on running the
SHOW JOBSstatement. To view the automatic table statistics, use
SHOW AUTOMATIC JOBS
- Scheduled backups
The below example is all schema change jobs
# 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 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
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.