sql - 如何提高 CockroachDB 的 INSERT 性能(每秒行数)(与 PostgreSQL 相比大约慢 20 倍)

Question

本文末尾附加的 Python3 脚本创建了一个包含 5 个 INT 列的简单表，其中 3 个带有索引。

然后它使用 multi-row inserts 来填充表格。

一开始，它每秒能够插入大约 10000 行。

Took   0.983 s to INSERT 10000 rows, i.e. performance =  10171 rows per second.
Took   0.879 s to INSERT 10000 rows, i.e. performance =  11376 rows per second.
Took   0.911 s to INSERT 10000 rows, i.e. performance =  10982 rows per second.
Took   1.180 s to INSERT 10000 rows, i.e. performance =   8477 rows per second.
Took   1.030 s to INSERT 10000 rows, i.e. performance =   9708 rows per second.
Took   1.114 s to INSERT 10000 rows, i.e. performance =   8975 rows per second.

但是当表已包含大约 1000000 行时，性能会下降到每秒大约 2000 行。

Took   3.648 s to INSERT 10000 rows, i.e. performance =   2741 rows per second.
Took   3.026 s to INSERT 10000 rows, i.e. performance =   3305 rows per second.
Took   5.495 s to INSERT 10000 rows, i.e. performance =   1820 rows per second.
Took   6.212 s to INSERT 10000 rows, i.e. performance =   1610 rows per second.
Took   5.952 s to INSERT 10000 rows, i.e. performance =   1680 rows per second.
Took   4.872 s to INSERT 10000 rows, i.e. performance =   2053 rows per second.

作为比较:当使用 PostgreSQL 而不是 CockroachDB 时，性能始终约为每秒 40000 行。

Took   0.212 s to INSERT 10000 rows, i.e. performance =  47198 rows per second.
Took   0.268 s to INSERT 10000 rows, i.e. performance =  37335 rows per second.
Took   0.224 s to INSERT 10000 rows, i.e. performance =  44548 rows per second.
Took   0.307 s to INSERT 10000 rows, i.e. performance =  32620 rows per second.
Took   0.234 s to INSERT 10000 rows, i.e. performance =  42645 rows per second.
Took   0.262 s to INSERT 10000 rows, i.e. performance =  38124 rows per second.

Took   0.301 s to INSERT 10000 rows, i.e. performance =  33254 rows per second.
Took   0.220 s to INSERT 10000 rows, i.e. performance =  45547 rows per second.
Took   0.260 s to INSERT 10000 rows, i.e. performance =  38399 rows per second.
Took   0.222 s to INSERT 10000 rows, i.e. performance =  45136 rows per second.
Took   0.213 s to INSERT 10000 rows, i.e. performance =  46950 rows per second.
Took   0.211 s to INSERT 10000 rows, i.e. performance =  47436 rows per second.

使用 CockroachDB 时有没有办法提高性能？

由于表是连续填充的，因此不能先填充表，然后再添加索引。

---

db_insert_performance_test.py:

import random
from timeit import default_timer as timer
import psycopg2


def init_table(cur):
    """Create table and DB indexes"""
    cur.execute("""
        CREATE TABLE entities (a INT NOT NULL, b INT NOT NULL,
                               c INT NOT NULL, d INT NOT NULL,
                               e INT NOT NULL);""")
    cur.execute('CREATE INDEX a_idx ON entities (a);')
    cur.execute('CREATE INDEX b_idx ON entities (b);')
    cur.execute('CREATE INDEX c_idx ON entities (c);')
    # d and e does not need an index.


def create_random_event_value():
    """Returns a SQL-compatible string containing a value tuple"""
    def randval():
        return random.randint(0, 100000000)
    return f"({randval()}, {randval()}, {randval()}, {randval()}, {randval()})"


def generate_statement(statement_template, rows_per_statement):
        """Multi-row insert statement for 200 random entities like this:
        INSERT INTO entities (a, b, ...) VALUES (1, 2, ...), (6, 7, ...), ...
        """
        return statement_template.format(', '.join(
                create_random_event_value()
                for i in range(rows_per_statement)))


def main():
    """Write dummy entities into db and output performance."""

    # Config
    database = 'db'
    user = 'me'
    password = 'pwd'
    host, port = 'cockroach-db', 26257
    #host, port = 'postgres-db', 5432

    rows_per_statement = 200
    statements_per_round = 50
    rounds = 100
    statement_template = 'INSERT INTO entities (a, b, c, d, e) VALUES {}'

    # Connect to DB
    conn = psycopg2.connect(database=database, user=user, password=password,
                            host=host, port=port)
    conn.set_session(autocommit=True)
    cur = conn.cursor()

    init_table(cur)

    for _ in range(rounds):
        # statements_per_round multi-row INSERTs
        # with rows_per_statement rows each
        batch_statements = [generate_statement(statement_template,
                                               rows_per_statement)
                            for _ in range(statements_per_round)]

        # Measure insert duration
        start = timer()
        for batch_statement in batch_statements:
            cur.execute(batch_statement)
        duration = timer() - start

        # Calculate performance
        row_count = rows_per_statement * statements_per_round
        rows_per_second = int(round(row_count / duration))
        print('Took {:7.3f} s to INSERT {} rows, '
            'i.e. performance = {:>6} rows per second.'
            ''.format(duration, row_count, rows_per_second), flush=True)

    # Close the database connection.
    cur.close()
    conn.close()


if __name__ == '__main__':
    main()

为了快速重现我的结果，这里有一个docker-compose.yml:

version: '2.4'

services:

  cockroach-db:
    image: cockroachdb/cockroach:v2.0.3
    command: start --insecure --host cockroach-db --vmodule=executor=2
    healthcheck:
      test: nc -z cockroach-db 26258

  cockroach-db-init:
    image: cockroachdb/cockroach:v2.0.3
    depends_on:
     - cockroach-db
    entrypoint: /cockroach/cockroach sql --host=cockroach-db --insecure -e "CREATE DATABASE db; CREATE USER me; GRANT ALL ON DATABASE db TO me;"

  postgres-db:
    image: postgres:10.4
    environment:
      POSTGRES_USER: me
      POSTGRES_PASSWORD: pwd
      POSTGRES_DB: db
    healthcheck:
      test: nc -z postgres-db 5432

  db-insert-performance-test:
    image: python:3.6
    depends_on:
     - cockroach-db-init
     - postgres-db
    volumes:
     - .:/code
    working_dir: /
    entrypoint: bash -c "pip3 install psycopg2 && python3 code/db_insert_performance_test.py"

要开始测试，只需运行docker-compose up db-insert-performance-test。

Answer 1

CockroachDB 将数据存储在“范围”内，当范围达到 64MB 时，范围就会被分割。最初，该表适合一个范围，因此每次插入都是单范围操作。 Range拆分后，每次插入都需要涉及多个Range来更新表和索引；因此，预计性能会下降。