0

I have a cockroachdb node I am playing with. My box has Ubuntu 16.4, 64 GB of RAM, 4 physical cores (Quad Core Intel Xeon 3.3 GHZ).

Inserts to this DB are very slow. On a 100000 sample set, average insert time is 65 ms. I ran the same script on a EC2 t2.large box (EBS, no SSD, 8 GB RAM, 2 VCPU). On this EC2 box, the average insert time is 2.5 ms. So my box is 25 times slower.

Further checks I did on my box while the script was running:

  1. No swapping
  2. Memory usage 0.4%
  3. CPU usage 6%

Disk: hdparm -t /dev/xvda


My Box:

Timing buffered disk reads: 586 MB in  3.01 seconds = 194.76 MB/sec

EC2 Box:

Timing buffered disk reads: 490 MB in  3.02 seconds = 162.45 MB/sec

All parameters I have looked at indicate that my box should outperform the EC2 box.

Something else I have to mention is that on this very same box of mine, H2 database is delivering an average insert of 4 ms. H2 is written in Java and Cockroachdb is written in GO.

What else should I be looking at?

EDIT

Same question is posted in cockroachdb forum: https://forum.cockroachlabs.com/t/cockroach-write-performance/1741/6

My Box OS: Ubuntu 16.04.1 LTS

EC2 t2.large: Ubuntu 16.04.4 LTS

Cockroach version across the board: 2.0.2

Cockroahc logs on my box: https://a.rokket.space/imwb4b.log

Cache and SQL memory size are default configurations.

The whole scripts is included here as well:

import psycopg2
import time
import uuid

conn = psycopg2.connect(user='root', host='localhost', port=26257)
conn.set_session(autocommit=True)

cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS test")
cur.execute('''
DROP TABLE IF EXISTS test.transaction;
CREATE TABLE IF NOT EXISTS test.transaction (
  id STRING(255) PRIMARY KEY,
  address STRING(255),
  arrival_time BIGINT NOT NULL,
  bundle STRING(255),
  current_index BIGINT NOT NULL,
  hash STRING(255),
  last_index BIGINT NOT NULL,
  last_modified_date_millis BIGINT NOT NULL,
  tag STRING(255),
  timestamp BIGINT NOT NULL,
  value REAL NOT NULL,
  INDEX idx_transaction_address (id, address),
  INDEX idx_transaction_bundle (id, bundle)
)
''')
start = time.time()
print "Start time was {0}".format(start)
for i in range(1,10000):
    id = str(uuid.uuid4()) # For testing UUID performance
    #id = str(i) # For testing serial integers and testing for transaction contention.
    cur.execute('''
UPSERT INTO test.transaction
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''',
        (id, 'abc', 123, 'abc', 123, 'abc', 123, 123, 'abc', 123, 123.456))
end= time.time()
print "End time was {0}. Total time was {1}. Average time was {2}".format(end, end - start, (end - start)/10000)
  • Please provide CockroachDB versions, your database schema, your insert query, a sample of cluster execution logs, your cache and SQL memory size configuration, and the operating system of the EC2 node. – John Mahowald Jun 17 '18 at 1:14

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.