Search

Top 60 Oracle Blogs

Recent comments

distributed

CockroachDB troubleshooting series… define the process

After working with customers for about 18 months now, I am starting a blog series to write up the common issues seen while running CockroachDB. Diagnosis and treatment of issues when running on distributed database architectures like CockroachDB, closely mirror the process used in the medical community.

There are observed symptoms which leads to a diagnosis and finally a treatment to resolve the condition. Good troubleshooting methodology can help frame the problem which leads to better overall outcomes. This process is outlined below:

Scaling CockroachDB key generation… uuid, serial, and sequences

Overview

Primary keys are critical in any RDBMS in order to ensure the validity of data. Unlike other distributed SQL databases, CockroachDB is not sharded by primary key to distribute the data. Data is divided to ranges and distributed automatically among nodes in the cluster. CockroachDB ranges are sorted by the primary key values. So, while the value doesn’t define the distribution, the sorting of the values can have implications if you use sequences that increment values in a counter-like fashion. This will put stress on a single RANGE of data which scaling of a distributed application.

Join Performance for UUID, STRING, and INTEGER with CockroachDB

overview

To continue on the UUID performance thread, I was recently asked by a customer how joins perform with various data types. I had not run a specific test, but suspected perform would be driven mostly by the size of the data types.

I wanted to verify my assumptions with real test data that shows the core performance of joins with CockroachDB.

the schema, data, and queries

For this test, two tables were created. The first table had one million rows and the second table had 200k matching primary keys for UUID, STRING, and INTEGER data types.

schema:

create table u1 (id uuid primary key);
create table u2 (id uuid primary key);

create table s1 (id string primary key);
create table s2 (id string primary key);

create table i1 (id integer primary key);
create table i2 (id integer primary key);

data load:

Ingest format performance with UUID using CockroachDB

Recently, I have been working with customers that have been concerned about the performance of various UUID formats. Other products have various performance characteristics for inserting, generating and presenting UUID data.

For this blog, I ran a quick series of tests using jmeter insert data along with some simple SQL tests to generate UUID values. Hopefully, this will be helpful to better your understanding of UUID with CockroachDB.

UUID formats

Cockroach DB has four different ways data can be formatted for use with the UUID data type.

String format
'63616665-6630-3064-6465-616462656564'

Curly Brace format
'{63616665-6630-3064-6465-616462656564}'

Working Remotely Isn’t Just About the Work

With COVID-19, the business world has come to a fork in the road: Down one route, shuttered offices. Down the other, companies embracing remote work, showing us how businesses can survive — and thrive! — with a fully remote workforce. And that includes fostering strong team bonds and employee relationships; just because there’s no physical break room or water cooler doesn’t mean companies can’t create opportunities for colleagues to connect.

On Working Remotely: An Automattic Reader

How does a distributed company — a group of people with shared business goals but spread out around the world, representing different cultures, family settings, and local health considerations — stick together during a major health crisis like the COVID-19 pandemic?

We don’t intend to make it sound easy. And we are aware — from our families, our communities, the businesses we support, and our customers — that many, if not most companies cannot actually work 100 percent remotely because of the nature of their business.

For those who can transition to distributed work in the wake of this evolving crisis, we wanted to suggest ideas that might help colleagues work well together even when you’re no longer all sharing the same physical space.

Developing Applications with CockroachDB… end-to-end example with SpringBoot, Java, Hibernate, Gradle

overview

Application development frameworks like SpringBoot have done a good job a giving developers a quick start to developing applications with Java. Layering object-relational mapping to SQL to remove proprietary syntax has further simplified the development of database applications.

CockroachDB allows developers to take advantage in the evolution of globally distributed data with a simple SQL interface. But each database can bring it’s own specific syntax and cause developers to hesitate when getting started with a new database.

How do you get started?

Demystifying JSON with CockroachDB… Import, Index, and Computed Columns

Overview

Recently, I created and delivered an "Advanced Developer Workshop" for CockroachLabs. One of the topics dove into how to ingest and use JSON data.

Like many databases, CockroachDB has the ability to use JSON data type for columns within a table. Basically, you insert a JSONB object into a row, and then can filter and extract the desired data with SQL. The following simple example shows how this is done:

So it is pretty straight forward to use JSONB objects within tables, but how do you load those HUGE json files into CockroachDB?

Importing geo-partitioned data… the easy way

 

setting the stage

I started at Cockroach labs back in June 2019 to help others learn how to architect and develop applications using a geo-distributed database.  There has been a resurgence in distributed database technology, but the focus on geo-distributed is quite unique to CockroachDB.  While the underlying technology is unique, developers and DBAs that come with a wealth of experience, need to know how to best use this innovative technology.  Given this situation, I thought it would be good to start a blog series to explore various topics facing anyone beginning to architect database solutions with CockroachDB.

To start using a database, the first step is to IMPORT table data so you can begin to see how the database performs and responds.  And thus the IMPORT series has started!

DB links

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It’s a good question as it’s fairly common knowledge that distributed queries can be very much slower than the same query when executed on an equivalent set of local tables, so it’s worth having a little poke around to see if there’s anything in the way the data moves across the link that could be tweaked to improve performance.