docs.daveops.net

Snippets for yer computer needs

Databases

Michael Stonebreaker

http://slideshot.epfl.ch/play/suri_stonebraker

Column store

50-100 times faster (according to Stonebraker) (data?) Only reads the columns needed, not the entire store

OLTP (Online Transaction Processing)

High-Availability

CAP theorem - consistency, availability, partition tolerance

Log-Structured Merge Trees

Record data and changes in immutable segments. When data is inserted/changed, the top level fills up and its data is copied into a new segment (compaction) LevelDB, Apache HBase, Hyperdex, Cassandra, RocksDB, WiredTiger, Riak

Advantages:

DBA Responsibilities

Interesting DBA problems

https://docs.microsoft.com/en-us/sql/odbc/microsoft-open-database-connectivity-odbc

Cassandra

Based on the Amazon Dynamo paper

Ring topology peer-to-peer, gossip protocol no special nodes Distributed Hash Table eventually consistent, tunable

Use vnodes whenever possible to avoid issues with topology changes, node rebuilds, hotspots, and heterogeneous clusters.

vnodes mean more ranges, which makes it easier to give a range to a new node

Keyspaces

List keyspaces

DESCRIBE KEYSPACES;

Create a keyspace

CREATE KEYSPACE "my_space"
WITH REPLICATION = {
  'class': 'SimpleStrategy', 'replication_factor': 1
};

Use a keyspace

USE "my_space";

Tables

List tables

DESCRIBE TABLES;

Create table

CREATE TABLE "users" (
  "user" text PRIMARY KEY,
  "email" text,
  "avatar" blob
);

Insert into table

INSERT INTO "users"
("avatar", "email", "avatar")
VALUES (
  'admin',
  'admin@example.org',
  0xf00badc0ff33
);

Partitioners

ports

desc port
cqlsh 9042

nodetool

https://docs.datastax.com/en/cassandra/2.1/cassandra/tools/toolsNodetool_r.html

#  Checking node repair
nodetool netstats
nodetool compactionstats

Topologies

Uses ‘snitches’

Per-Query Consistency

ANY/ONE/QUORUM/LOCAL_QUORUM/ALL

Quick Docker dev setup

# Run server instance
docker run --name some-cassandra -v ~/my/own/datadir:/var/lib/cassandra -d cassandra:latest

Types

name desc
text UTF8
ascii ASCII
int 32-bit integer
bigint 64-bit integer
varint arbitrary size
float 32-bit float
double 64-bit float
decimal variable-precision decimal
boolean boolean
timestamp ‘yyyy-mm-dd HH:mm:ssZ’
uuid UUID v1 and v4
blob binary (prefix with 0x)

Resources

get list of databases

curl localhost:5984/_all_dbs

get database details

curl localhost:5984/db_name

access futon

curl localhost:5984/_utils

Running in Docker

docker pull docker.elastic.co/elasticsearch/elasticsearch:7.10.0

# single node
docker run -p 9200:9200 -p 9300:9300 -e "discovery.type=single-node" docker.elastic.co/elasticsearch/elasticsearch:7.10.0

Queries

// Show current operations
db.currentOp()
// Show long running queries
db.currentOp()['inprog'].filter(function(x) {return x.secs_running > 10})
// Kill a query
db.killOp(12345)

Administration

// Show database list
show dbs

// Create/switch to database
use myDb

// Drop database
db.dropDatabase();

Rotate logs

kill -SIGUSR1

http://orientdb.com/