CockroachDB slow inserts compared to MariaDB?

Question

Im doing some tests to see if i can use CockroachDB for a small project of mine. I created two identical VPS'es in the same DC and installed CockroachDB on one of them and MariaDB on the other, then created two test databases to store the data. Doing 1M inserts at a time in batches of 5K. I did no change on neither of VPS/db servers, they just run as they are after install.

CockroachDB schema:

root@:26257/test> show create table customers;
+-----------+------------------------------------------------------------------+
|   Table   |                           CreateTable                            
|
+-----------+------------------------------------------------------------------+
| customers | CREATE TABLE customers (␤                                        
|
|           |     id INT NOT NULL DEFAULT unique_rowid(),␤                     
|
|           |     first_name STRING NULL,␤                                     
|
|           |     last_name STRING NULL,␤                                      
|
|           |     age INT NULL,␤                                               
|
|           |     CONSTRAINT "primary" PRIMARY KEY (id ASC),␤                  
|
|           |     FAMILY "primary" (id, first_name, last_name, age)␤ |
|           | )                                                                
|
+-----------+------------------------------------------------------------------+

MariaDB:

MariaDB [test]> show create table customers \G;
*************************** 1. row ***************************
   Table: customers
Create Table: CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(250) DEFAULT NULL,
  `last_name` varchar(250) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7000001 DEFAULT CHARSET=latin1

The insert script is something pretty dumb, just quickly put together to insert a bunch of data into database, using golang (it's the same for both CockroachDB and MariaDB with small differences, i.e: connection string):

package main

import "database/sql"
import _ "github.com/lib/pq"
import "fmt"
import "strings"
import "math/rand"
import "time"

func init() {
    rand.Seed(time.Now().UnixNano())
}

var letterRunes = []rune("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")

func RandStringRunes(n int) string {
    b := make([]rune, n)
    for i := range b {
        b[i] = letterRunes[rand.Intn(len(letterRunes))]
    }
    return string(b)
}


func main() {
    db, err := sql.Open("postgres", "postgresql://root@localhost:26257/test?sslmode=disable")
    if err != nil {
        panic(err)
    }

        sqlStr := "INSERT INTO customers(first_name, last_name, age) VALUES"
    insert := ""
    for i := 0; i < 1000000; i++ {
        fname := RandStringRunes(30)
        lname := RandStringRunes(39)
        age := rand.Intn(100)
        insert += fmt.Sprintf("('%s', '%s', %d),", fname, lname, age)

        if i%5000 == 0 {
            sql := sqlStr + strings.Trim(insert, ",")
            insert = ""
            _, err := db.Exec(sql)
            if err != nil {
                panic(err)
            }
        }
    }

    if len(insert) > 0 {
        sql := sqlStr + strings.Trim(insert, ",")
        insert = ""
        _, err := db.Exec(sql)
        if err != nil {
            panic(err)
        }
    }

}

The results:

MariaDB:

[root@mariadb ~]# time ./insert

real    0m40.650s
user    0m33.784s
sys 0m0.112s
[root@mariadb ~]#

CockroachDB:

[root@cockroachdb ~]# time ./insert

real    3m42.909s
user    0m35.620s
sys 0m0.144s

I'm using MariaDB:

[root@mariadb ~]# mysql --version
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

and CockroachDB:

[root@cockroachdb ~]# cockroach version
Build Tag:    v1.0.1
Build Time:   2017/05/25 15:17:49
Distribution: CCL
Platform:     linux amd64
Go Version:   go1.8.1
C Compiler:   gcc 6.3.0
Build SHA-1:  50fd18dcf8da75a0f4980344a885fe6105d0cf23
Build Type:   release
[root@cockroachdb ~]#

I am aware that using random data might affect the numbers, but still, i get these numbers almost all the times, so there is enough consistency.

Are these numbers correct for CockroachDB, are the inserts slower in the above version?

Also, counting all the records in table is slow too(select count(*) from customers), not sure exactly by how much since i don't get a timer in the result set as i get from MariaDB server, but the MariaDB server returns the result in under 5 seconds, while CockroachDB takes well over 10 seconds from my counting.

I like that altering the table to add a new column for example is just instant with CockroachDB while with MariDB took almost 30 seconds on a table with 5M records.

Any thoughts are highly appreciated.
Thanks.

Peter Mattis Peter Mattis · Accepted Answer · 2017-05-31T15:16:40

CockroachDB 1.0.1 (and 1.0) had a debug assertion left on which causes a massive slowdown for bulk operations. This assertion will be removed in 1.0.2 (scheduled to be released in a few weeks). For comparison, here is what I see when running against 1.0.1:

$ time ./insert

real    2m3.125s
user    0m35.942s
sys     0m6.659s

And here is (the unreleased) 1.0.2:

$ time ./insert

real    0m48.145s
user    0m36.638s
sys     0m6.692s

Despite the improvement, I expect performance will still be lower than MariaDB. Part of the reason for this is the architecture of CockroachDB. Even when running a single node, it is going through lots of code paths that are needed for distributed execution. And part of the reason is maturity. MariaDB has been optimized for years while we're only in the early stages of that work for CockroachDB.

CockroachDB slow inserts compared to MariaDB?

1 Answers