Tsurugi DB、sqlのinsertとkvsのputの性能を比較する(tsubakuro)

2024年12月25日 12:02

公式
tsurugidb
tsubakuro

gradle環境を用意

~$ docker build -t kvs .
dockerfileは以下

FROM ubuntu:22.04
RUN apt update -y && apt install -y git build-essential cmake libboost-system-dev openjdk-11-jdk build-essential gcc gdb curl unzip zip

RUN curl -s "https://get.sdkman.io" | bash
# Source SDKMAN script using bash
RUN bash -c "source /root/.sdkman/bin/sdkman-init.sh"
ENV SDKMAN_DIR /root/.sdkman
ENV PATH $SDKMAN_DIR/bin:$PATH
RUN bash -c "source $SDKMAN_DIR/bin/sdkman-init.sh && sdk install gradle"
RUN mkdir tsurugi
WORKDIR tsurugi
RUN bash -c "source $SDKMAN_DIR/bin/sdkman-init.sh && gradle init --type java-application --dsl groovy --project-name tsurugi  --test-framework junit --java-version 21 --no-split-project --no-incubating"

docker-compseで環境構築

docker-compose.yml

docker-compose up -d

services:
  db:
    image: ghcr.io/project-tsurugi/tsurugidb
    container_name: tsurugidb_1
    networks:
      - tsurugi_net
    ports:
      - 12345:12345
  kvs:
    image: kvs
    container_name: kvs_1
    networks:
      - tsurugi_net
    tty: true
    stdin_open: true
    depends_on:
      - db

networks:
  tsurugi_net:
    name: tsurugi_network

kvsにvscodeからログイン

~$ code .
リモートエクスプローラー -> 開発コンテナー -> kvs_1
ファイルを開く(Ctrl + o) /tsurugi/app/build.gradle

dependenciesに以下の記述を追加する

dependencies {
    def tsubakuroVersion = '1.7.0'
    implementation "com.tsurugidb.tsubakuro:tsubakuro-session:${tsubakuroVersion}"
    implementation "com.tsurugidb.tsubakuro:tsubakuro-connector:${tsubakuroVersion}"
    implementation "com.tsurugidb.tsubakuro:tsubakuro-kvs:${tsubakuroVersion}"
    implementation 'org.slf4j:slf4j-simple:1.7.32'
}

ファイルを開く(Ctrl + o) /tsurugi/app/src/test/java/org/example/AppTest.java

自動生成されるテストは不要なので中身は空にする。

import org.junit.Test;
import static org.junit.Assert.*;

public class AppTest {

}

ファイルを開く(Ctrl + o) 　/tsurugi/app/src/main/java/org/example/App.java

package org.example;

import java.util.stream.IntStream;

import com.tsurugidb.tsubakuro.common.Session;
import com.tsurugidb.tsubakuro.common.SessionBuilder;
import com.tsurugidb.tsubakuro.sql.SqlClient;
import com.tsurugidb.tsubakuro.sql.Transaction;
import com.tsurugidb.tsubakuro.util.FutureResponse;
import com.tsurugidb.tsubakuro.sql.Placeholders;
import com.tsurugidb.tsubakuro.sql.PreparedStatement;
import com.tsurugidb.tsubakuro.sql.ResultSet;
import com.tsurugidb.tsubakuro.sql.Parameters;
import com.tsurugidb.tsubakuro.kvs.KvsClient;
import com.tsurugidb.tsubakuro.kvs.RecordBuffer;
import com.tsurugidb.tsubakuro.kvs.TransactionHandle;

public class App {
    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        String tableName = "test_table";
        String drop = String.format("DROP TABLE %s", tableName);
        String create = String.format("create table %s (id int primary key , name int , note int)", tableName);
        String select = String.format("select name,note,count(*) from %s group by name,note", tableName);

        try (Session session = SessionBuilder.connect("tcp://localhost:12345").create();
                SqlClient sql = SqlClient.attach(session);
                KvsClient kvs = KvsClient.attach(session)) {
            try (Transaction transaction = sql.createTransaction().get()) {
                transaction.executeStatement(drop).await();
                transaction.commit().await();
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
            try (Transaction transaction = sql.createTransaction().await()) {
                transaction.executeStatement(create).await();
                transaction.commit().await();
                transaction.close();
            }
            long kvsstartTime = System.nanoTime();
            try (TransactionHandle tx = kvs.beginTransaction().await()) {
                IntStream.range(0, 100000).forEach(i -> {
                    RecordBuffer record = new RecordBuffer();
                    record.add("id", i);
                    record.add("name", 1);
                    record.add("note", 1);
                    try {
                        kvs.put(tx, "test_table", record).await();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
                kvs.commit(tx).await();
                tx.close();
                kvs.close();
            }
            long kvsendTime = System.nanoTime();
            System.out.println("kvs " + (kvsendTime - kvsstartTime) + " nm");
            long sqlstartTime = System.nanoTime();
            try (Transaction transaction = sql.createTransaction().await()) {
                IntStream.range(100000, 200000).forEach(i -> {
                    String insert = String.format("insert into %s (id, name, note) values (%d, 1, 1)", tableName, i);
                    try {
                        transaction.executeStatement(insert).await();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
                /*
                 * for (int i = 10; i < 20; i++) {
                 * String insert =
                 * String.format("insert into %s (id, name, note) values (%d, 1, 1)", tableName,
                 * i);
                 * transaction.executeStatement(insert).await();
                 * }
                 */
                transaction.commit().await();
                transaction.close();
            }
            long sqlendTime = System.nanoTime();
            System.out.println("sql " + (sqlendTime - sqlstartTime) + " nm");

            PreparedStatement preparedStatement = sql.prepare(select, Placeholders.of("id", int.class),
                    Placeholders.of("name", int.class), Placeholders.of("note", int.class)).get();
            try (Transaction transaction = sql.createTransaction().get()) {
                try (FutureResponse<ResultSet> resultSet = transaction.executeQuery(preparedStatement,
                        Parameters.of("id", (int) 999), Parameters.of("name", (int) 999),
                        Parameters.of("note", (int) 999))) {
                    ResultSet r = resultSet.await();
                    while (r.nextRow()) {
                        while (r.nextColumn()) {
                            if (!r.isNull()) {
                                System.out.print(r.fetchInt4Value() + " ");
                            }
                        }
                        System.out.println("");
                    }
                }
                sql.close();
                transaction.close();
            }
            session.close();
        }
    }
}

build & run

コマンドライン上で行う場合は
cd /tsurugi
./gradlew build
./gradlew run
vscodeの機能を使いたい場合は
ターミナル->タスクの構成->テンプレートからtasks.jsonを生成->others

tasks.jsonを以下で上書き

{
    "version": "2.0.0",
    "tasks": [
        {
            "label": "build",
            "type": "shell",
            "command": "./gradlew build",
            "problemMatcher": [],
            "group": {
                "kind": "build",
                "isDefault": true
            }
        },
        {
            "label": "run",
            "type": "shell",
            "command": "./gradlew run",
            "group": "build",
            "problemMatcher": []
        },
        {
            "label": "clean",
            "type": "shell",
            "command": "./gradlew clean"
        },
        {
            "label": "check",
            "type": "shell",
            "command": "./gradlew check"
        }
    ]
}

ターミナル->タスクの実行
build,run,clean,checkを用途ごとに使い分ける
buildだけは既定として設定しているため(Ctrl + Shift + B )のショートカットで実行可

結果

kvsのputはsqlのinsertより三倍速い

kvs 12696379942 nm
sql 39114751219 nm　

insert文のみ(KVS)

package org.example;

import java.util.stream.IntStream;

import com.tsurugidb.tsubakuro.common.Session;
import com.tsurugidb.tsubakuro.common.SessionBuilder;
import com.tsurugidb.tsubakuro.kvs.KvsClient;
import com.tsurugidb.tsubakuro.kvs.RecordBuffer;
import com.tsurugidb.tsubakuro.kvs.TransactionHandle;

public class App {
    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        String tableName = "test_table";
        try (Session session = SessionBuilder.connect("tcp://localhost:12345").create();
                KvsClient kvs = KvsClient.attach(session)) {

            try (TransactionHandle tx = kvs.beginTransaction().await()) {
                IntStream.range(0, 1).forEach(i -> {
                    RecordBuffer record = new RecordBuffer();
                    record.add("id", i);
                    try {
                        kvs.put(tx, tableName, record).await();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
                kvs.commit(tx).await();
                tx.close();
                kvs.close();
            }
            session.close();
        }
    }
}