Query regression on v0.7 standalone on Windows (compared with v0.6)

[zengfanlin]

1. Insert 17280 points within 11000 time-series, 1 point / 5 seconds.
2. Query all the time-series data

v0.6 takes about 3.6 minutes, but v0.7 takes 30 minutes.

Version info: v0.7.0 Windows standalone

@Data 
@Metric(name = "tag_hits")
public class GreptimeTagEntity {
    @Column(name = "name" , tag = true, datatype = DataType.String)
    private String name;
    @Column(name = "ts", timestamp=true, datatype = DataType.TimestampMillisecond)
    private Long ts;
    @Column(name = "quality", datatype = DataType.UInt32)
    private int quality;
    @Column(name = "value", datatype = DataType.Float64)
    private double value;
}

package com.example.benchmark;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.file.FileReader;
import com.example.benchmark.greptimedb.GrepTimeTagEntity;
import com.example.benchmark.greptimedb.MysqlConnManager;
import io.greptime.GreptimeDB;
import io.greptime.models.Err;
import io.greptime.models.Result;
import io.greptime.models.WriteOk;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;

/**
 * @author zengfanlin
 * @create 2023-12-14 19:17
 */
@SpringBootTest
@Slf4j
public class greapTimeTest500_1 {

    @Autowired
    GreptimeDB greptimeDB;

    String startdt = "2024-02-19 00:00:00";
    String enddt = "2024-02-20 00:00:00";
    int one_loop_insert = 207360;

    /**
     * 模拟历史值插入 1000回路
     *
     * @throws IOException
     */
    @Test
    public void TestInsert() throws IOException, ExecutionException, InterruptedException {
        LocalDateTime start = LocalDateTimeUtil.of(DateUtil.parse(startdt));
        List<String> loops = getLoopList().subList(0, 500);
        List<List<String>> loopsPartition = ListUtil.partition(loops, 20);
        List<String> suffixList = getSuffixList();
        int count = 0;
        List<LocalDateTime> ldtList = new ArrayList<>();
        for (int i = 1; i <= 17280; i++) {
            LocalDateTime offset = LocalDateTimeUtil.offset(start, 5 * i, ChronoUnit.SECONDS);
            ldtList.add(offset);
        }
        TimeInterval timer = DateUtil.timer();
        //按分段切成两分钟一段，共720段
        List<List<LocalDateTime>> partition = ListUtil.partition(ldtList, 24);
        for (List<LocalDateTime> timeSegment : partition) {//两分钟段一组

            for (List<String> loopSegment : loopsPartition) {//20个回路一组

                List<GrepTimeTagEntity> entityList = new ArrayList<>();
                List<String> taglist = new ArrayList<>();
                for (String loop : loopSegment) {
                    for (String fix : suffixList) {
                        taglist.add(loop + fix);
                    }
                }
                for (String tag : taglist) {
                    for (LocalDateTime localDateTime : timeSegment) {
                        GrepTimeTagEntity tg = Gentag(tag, LocalDateTimeUtil.toEpochMilli(localDateTime));
                        entityList.add(tg);
                        count++;
                    }
                }
                CompletableFuture<Result<WriteOk, Err>> resultCompletableFuture = greptimeDB.writePOJOs(entityList);
                Result<WriteOk, Err> result = resultCompletableFuture.get();
                if (result.isOk()) {
                    log.info("Write result: {}", result.getOk());
                } else {
                    log.error("Failed to write: {}", result.getErr());
                }
                double tt = ((double) timer.intervalSecond()) / 60;
                System.out.println("已经耗时：" + tt);
                System.out.println("预估时间(min):" + (double) one_loop_insert * loops.size() * tt / count);
                System.out.println("----count----" + count);
            }
        }


        //System.out.println("本条插入时间" + timerInt.interval());
//            System.out.println("平均插入时间" + timer.interval() / i);
//            System.out.println("采集时间:" + LocalDateTimeUtil.format(start, DatePattern.NORM_DATETIME_PATTERN) + "传感器数量：" + loops.size() * suffixList.size());
//            System.out.println("已经耗时：" + timer.intervalMinute());
//            System.out.println("预估时间(min):" + timer.intervalMinute() * 17280 / (double) i);

    }


    @Test
    public void TestQuery() {
        List<String> loops = getLoopList().subList(0, 500);
        List<String> suffixList = getSuffixList();

        int count = 0;
        try (Connection conn = MysqlConnManager.getConnection()) {
            Statement statement = conn.createStatement();
            ResultSet rs = null;
            try {
                TimeInterval timer = DateUtil.timer();
                GrepTimeTagEntity entity = null;
                for (String loop : loops) {
                    for (String s : suffixList) {
                        rs = statement.executeQuery(String.format("SELECT name,ts,quality,value FROM tag_his where name='%s' and ts>='%s' and ts<'%s' ORDER BY ts ASC ", loop + s, startdt, enddt));
                        while (rs.next()) {
                            entity = new GrepTimeTagEntity();
                            entity.setName(rs.getString("name"));
                            entity.setTs(rs.getTimestamp("ts").getTime());
                            entity.setQuality(rs.getInt("quality"));
                            entity.setValue(rs.getDouble("value"));
                        }
                    }
                    count++;
                    log.info("----count----{}/{}", count, loops.size());
                    double dd = (double) loops.size() / count;
                    double v = (timer.intervalSecond() / 60D) * dd;
                    log.info("----已经耗时----{} min", timer.intervalMinute());
                    log.info("----预计耗时----{} min", v);
                }
            } finally {
                if (null != rs) {
                    rs.close();
                }
            }
            

        } catch (SQLException exception) {
            exception.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }
    /***
     * 获取tag前缀列表
     * @return
     */
    public static List<String> gettagList() {
        /**
         *
         * TAG0001
         * TAG0002
         * TAG0003
         * TAG0004
         * TAG0005
         * TAG0006
         * TAG0007
         * TAG0008
         * TAG0009
         * TAG0010
         * TAG0011
         * TAG0012
         * TAG0013
         * TAG0014
         * TAG0015
         * TAG0016
         * TAG0017
         * TAG0018
         * TAG0019
         * TAG0020
         * TAG0021
         * TAG0022
         * TAG0023
         * TAG0024
         * TAG0025
         * TAG0026
         * TAG0027
         * TAG0028
         * TAG0029
         * TAG0030
         * .....
         * ....
         * ...
         * ..
         * .
         */
        FileReader fileReader = new FileReader("tags.txt");
        List<String> strings = fileReader.readLines();
        return strings;
    }

    /***
     * 获取回路列表
     * @return
     */
    public static List<String> getLoopList() {
        return gettagList();
    }

    /**
     * 获取后缀列表
     *
     * @return
     */
    public static List<String> getSuffixList() {
        /**
         *
         * .PV
         * .MV
         * .SV
         * .AUTO
         * .CAS
         * .PB
         * .TI
         * .TD
         * .MVSCH
         * .MVSCL
         * .SVSCH
         * .SVSCL
         *
         *
         */
        FileReader fileReader = new FileReader("suffix.txt");
        List<String> strings = fileReader.readLines();
        return strings;
    }
    public static GrepTimeTagEntity Gentag(String tagname, Long timestamp) {
        GrepTimeTagEntity entity = new GrepTimeTagEntity();
        //tag名称
        entity.setName(tagname);
        //采样时间
        entity.setTs(timestamp);
        //值
        entity.setValue(GenValue());

        entity.setQuality(192);
//        entity.setSource(entity.getValue() + "," + entity.getQuality() + "," + entity.getPeriod());
        return entity;
    }

    static Random random = new Random(100000);

    public static double GenValue() {
        double d = random.nextDouble() * 10.0 - 5.0;
        return Double.parseDouble(String.format("%.2f", d));
    }
}

[tisonkun]

@zengfanlin 请问你的启动配置有特别修改吗？还是默认配置起起来的呢？

[evenyag]

We observe the same regression in v0.7 that enables an experimental memtable by default. I will address this issue in v0.7.1 or later. It might take some time.

You could switch to the old memtable via the configuration file:

greptimedb/config/standalone.example.toml

Lines 247 to 251 in 3ee5336

	[region_engine.mito.memtable]
	# Memtable type.
	# - "experimental": experimental memtable
	# - "time_series": time-series memtable (deprecated)
	type = "experimental"

Here is an example, you could add it to your configuration file.

[[region_engine]]

[region_engine.mito.memtable]
type = "time_series"

Note that you have to put [region_engine.mito.memtable] after the [[region_engine]] entry.

To start with a configuration file, run

greptime standalone start -c /your/path/to/config.toml

[tisonkun]

Confirmed by @zengfanlin offline that applying this config resolves the regression. We should instead create an issue to fix the default config's regression.

[tisonkun]

@evenyag Let's create an issue at https://github.com/greptimeteam/greptimedb and linked here, or remember to tag this topic when creating the PR so that we hold the cross-reference.

[evenyag]

I tracked the issue in #3467