Heater
Posts: 13863
Joined: Tue Jul 17, 2012 3:02 pm

Cockroach database and pi64 on Pi 3 cluster

Tue Dec 26, 2017 9:40 am

I notice the Cockroach database has never been mentioned on this forum. Which is odd because it does run on the Pi and there are those here who are interested in tinkering with Pi clusters.

Cockroachdb is a free and open source SQL database with a twist. It has been built from the ground up to be a fault tolerant distributed database. It automatically replicates and spreads data around multiple nodes such that node failure does not cause data loss or stop the service. Normally such databases are "nosql", they give up SQL consistency for replication because it's hard to do. Not Cockroach, which offers all the ACID reliability of SQL.

There are instructions for using Cockroach on Raspberry Pi here: https://www.cockroachlabs.com/blog/run- ... pberry-pi/ but that is making a slightly tweaked buid for 32 bit ARM. I wanted an out of the box 64 bit Cockroach.

Luckily there is a 64 bit Debian, pi64 by bamarni, here: https://github.com/bamarni Which installs and runs perfectly.

So, here is how I got Cockroachdb running on pi64 on a Pi 3:


Install pi64, a 64 bit version of Debian for the Pi 3, to your Pi 3 using the instructions here: https://github.com/bamarni/pi64

When you have that done, on your Pi 3 do the following:

Be sure Debian is up to date

Code: Select all

$ sudo apt-get update
$ sudo apt-get upgrade
Install vim

Code: Select all

$ sudo apt-get install vim
Install node.js`. Not necessary but I want to test cockroach from node.js later.

Code: Select all

 
$ wget -qO- https://raw.githubusercontent.com/creationix/nvm/v0.33.8/install.sh | bash
$ nvm install 9.3.0
$ node -v
Install go for arm64

Code: Select all

$ wget https://storage.googleapis.com/golang/go1.9.2.linux-arm64.tar.gz
$ sudo tar -C /usr/local -xzf go1.9.2.linux-arm64.tar.gz 
Add GOPATH to profile

Code: Select all

$ sudo vim ~/.profile     # and add the line export GOPATH=/home/pi
$ .~/.profile
$ echo $GOPATH
$ go version
Install autoconf

Code: Select all

$ sudo apt-get install autoconf`
Install the yarn package manager

Code: Select all

$ curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | sudo apt-key add -
$ echo "deb https://dl.yarnpkg.com/debian/ stable main" | sudo tee /etc/apt/sources.list.d/yarn.list
$ sudo apt-get install apt-transport-https
$ sudo apt-get update
$ sudo apt-get install yarn
Make a nice big swap file, building cockroach will need it.

Code: Select all

 
$ sudo dd if=/dev/zero of=/swap bs=1024 count=1M
$ chmod 600 /swap 
$ sudo chmod 600 /swap 
$ sudo mkswap /swap
$ sudo swapon /swap
Build and install cockroachdb

Code: Select all

$ cd $GOPATH                # Or ~/go if you have no GOPATH
$ go get -d github.com/cockroachdb/cockroach
$ cd src/github.com/cockroachdb/cockroach
$ make build -j1 TAGS='v1.1.3'
That takes about an hour and half…

Code: Select all

$ ls -lh          # should see cockroach executable there
Install cockroach on your PATH and check it runs:

Code: Select all

$ sudo cp cockroach /usr/local/bin/
$ cockroach version
$ cockroach start --insecure --host=localhost
You should now be able to browse to the cockroach dashboard on localhost:8080

If all is well it’s time to check the documentation at https://www.cockroachlabs.com/docs/stable/

Next up, getting a cluster of cockroaches working...
Memory in C++ is a leaky abstraction .

ejolson
Posts: 3792
Joined: Tue Mar 18, 2014 11:47 am

Re: Cockroach database and pi64 on Pi 3 cluster

Tue Dec 26, 2017 5:53 pm

Heater wrote:
Tue Dec 26, 2017 9:40 am
Cockroachdb is a free and open source SQL database with a twist. It has been built from the ground up to be a fault tolerant distributed database. It automatically replicates and spreads data around multiple nodes such that node failure does not cause data loss or stop the service.
Thanks for posting a detailed guide about an interesting software. I may have a go at it (sorry about the pun) in 32-bit with my Zero cluster after I'm done with slurm and MPI.

Heater
Posts: 13863
Joined: Tue Jul 17, 2012 3:02 pm

Re: Cockroach database and pi64 on Pi 3 cluster

Tue Dec 26, 2017 6:13 pm

Be warned. I think the instructions for building cockroach for the Pi on the cockroachlabs web site are a bit old and out of date w.r.t. the latest cockroach versions.

I had a look at it, can't remember the details but it looked like the files you are instructed to hack have been moved around and I could not find at least one of the definitions that should be changed.

I started a thread about this on the cockroachlabs forum so perhaps we will find out soon.

I'm impressed with cockroach so far. I have had it running on three nodes in the google cloud for some time. Just logging sensor data. It's very easy to set up and use. Only a single executable to worry about and no messing with config files. The developers have been very responsive and quick to fix the few issues discovered.

It's an interesting technology under the hood. Uses the "raft" consensus algorithm to ensure that all nodes are in sync with each other and handle failed nodes and network disconnects. So there is no old fashioned "master", "slave" replication going on. All nodes are of equal standing.
Memory in C++ is a leaky abstraction .

W. H. Heydt
Posts: 11063
Joined: Fri Mar 09, 2012 7:36 pm
Location: Vallejo, CA (US)

Re: Cockroach database and pi64 on Pi 3 cluster

Tue Dec 26, 2017 6:35 pm

Sounds interesting and I will have to experiment with it for my ConReg system.The auto-replication would make life easier. How similar is the C interface to the one for MySQL?

Heater
Posts: 13863
Joined: Tue Jul 17, 2012 3:02 pm

Re: Cockroach database and pi64 on Pi 3 cluster

Tue Dec 26, 2017 7:01 pm

I have no idea. I have only ever used MySql in PHP and that was back in 1999 or so. An experience I promised myself never to repeat.

But I can say that Cockroach uses the PostgreSQL wire protocol. So all the client libraries for Postgres can be used with Cockroach, in whatever language you like.

The SQL syntax is somewhat different to MySQL here and there. Cockroach aims to implement as much of the Postgres style SQL as makes sense for them. It's pretty complete already. Even so I have noticed a few minor differences between Cockroach and Postgres SQL syntax. Nothing major and it did not bother me as I have never used Postgres before.

Below is some C++ code I use to record sensor data to a simple table of "source_id", "timestamp" and "value". I'd wager the C version looks similar.

Code: Select all

#include <iostream>
#include <pqxx/pipeline>
#include <pqxx/pqxx>
#include <signal.h>
#include <stdexcept>
#include <tuple>

#include "Persist.h"

using namespace std;

Persist::Persist() {
  cout << "Persist::Persist..." << endl;

  // libpq docs say we might get SIGPIPE on connection failure.
  signal(SIGPIPE, SIG_IGN);

  try {
    // Connect to the time_series database.
    string uri = ("postgresql://[email protected]:26257/"
                  "time_series?password=dwlcb8212bda&sslmode=verify-ca&"
                  "sslrootcert=/home/rsm/mqtt-umrr-radar/src/ca.crt");

    auto cp = new pqxx::connection(uri);

    // Save pointer to connection
    auto ctemp = std::unique_ptr<pqxx::connection>(cp);
    connection = std::move(ctemp);

    cerr << "Persist::Persist: OK" << endl;
  } catch (const exception &e) {
    cerr << "Persist::Persist: FAIL" << endl;
  }
}

int Persist::persist(int source_id, string timestamp, int value) {
  try {
    // Must have a transaction (even nontransaction)
    pqxx::nontransaction transaction(*connection);

    // Insert row into table.
    pqxx::tablewriter tableWriter(transaction, "series_1");

    vector<string> rowData(3);
    rowData[0] = std::to_string(source_id);
    rowData[1] = timestamp;
    rowData[2] = std::to_string(value);
    tableWriter.insert(rowData);
    tableWriter.complete();

    // Note this doesn't doesn't do anything
    // for a nontransaction, but is still required.
    transaction.commit();
  } catch (const exception &e) {
    //cerr << e.what() << endl;
    return 1;
  }
  return (0);
}
Memory in C++ is a leaky abstraction .

Return to “General discussion”