“DBmarlin provides second-by-second insights into what your databases are doing and helps you identify issues before they become disasters.” - Rob Reid, Technical Evangelist at Cockroach Labs

Monitoring a distributed database like CockroachDB can be complex, but effective tools make it manageable. In this post, we’ll explore how to monitor CockroachDB using DBmarlin, based on insights shared by Rob Reid, Technical Evangelist at Cockroach Labs. This guide will cover everything from initial configuration to diagnosing transaction contention and leveraging DBmarlin’s time comparison feature for workload analysis.

Configuring DBmarlin for CockroachDB

Start by setting up DBmarlin to work with your CockroachDB instance:

1. Add a Database Instance: Navigate to the DBmarlin console and add a new database instance. Name it (e.g., “CRDB”) and set the host address as the Fully Qualified Domain Name (FQDN) for your private CockroachDB service.
2. Specify Database Details: Choose CockroachDB as the instance type, input the default port number (26257), and use the root user for an insecure cluster.
3. Enable Advanced Monitoring: Toggle the advanced mode to collect transaction contention data. Save the configuration and start the agent.

Once the agent shows a green status, you’re ready to begin monitoring.

Simulating Workloads to Diagnose Contention

With DBmarlin configured, run a workload that simulates high contention to see real-time monitoring in action:

1. Initial High-Contention Scenario: Launch a multi-threaded application simulating bank account transfers. This setup, involving four workers attempting updates at 10-millisecond intervals within a small pool of 10 accounts, is designed to create high contention.
2. Monitor Metrics: Check the CockroachDB console’s metrics page to observe SQL statement volume and contention events. DBmarlin complements this view by providing deeper insights into contention causes.

Using DBmarlin for Detailed Analysis

Navigate to DBmarlin’s console to explore activity and identify contention issues:

• Transaction Contention Tab: This tab reveals which transactions and SQL statements are involved in contention. For example, if a workload contains a SELECT FOR UPDATE followed by an UPDATE, and another transaction waits on the same data, DBmarlin highlights these as blocking and waiting SQL statements.
• Schema Insights: If the contention involves primary keys in the account table or another index, DBmarlin represents this clearly.

Adjusting Workload Parameters

To test DBmarlin’s responsiveness to different workload intensities:

• Run a Lower-Contention Scenario: Modify the workload to interact with a larger pool of accounts, which naturally reduces contention. DBmarlin’s interface will reflect this with an increase in successful transaction volume and a drop in contention events.
• Visualise Database Performance: The sharp rise in transaction counts in DBmarlin demonstrates how a less contended workload allows for smoother operations.

Time Comparison for Long-Term Monitoring

DBmarlin’s time comparison feature helps assess performance changes over time. This is particularly useful for:

• Database Migrations: Compare workloads before and after migrating to CockroachDB or upgrading its version.
• Performance Trends: Visualise how transaction wait times evolve, ensuring that optimisations yield expected results. In one example, reduced wait times for SELECT FOR UPDATE statements indicated a successful workload adjustment.

Benefits of Using DBmarlin with CockroachDB

DBmarlin enhances your monitoring experience by offering:

• Granular Insights: View second-by-second database activity.
• Detailed Contention Analysis: Identify and resolve blocking transactions efficiently.
• Time-Based Comparisons: Track database performance across snapshots to evaluate the impact of upgrades and changes.

Get Started with DBmarlin

Interested in monitoring CockroachDB with DBmarlin? Users can deploy DBmarlin to monitor a CockroachDB cluster for free for three months. This trial provides an excellent opportunity to explore the tool’s robust features and improve your database performance.

For more information and to get started, visit DBmarlin’s CockroachDB page.