批量写入tidb提高写入效率

shy_snow

已于 2022-07-22 17:19:02 修改

阅读量3.1k

点赞数 3

分类专栏：数据库代码示例文章标签： tidb 数据库

于 2022-05-30 15:27:26 首次发布

本文链接：https://blog.csdn.net/shy_snow/article/details/125047226

版权

数据库同时被 2 个专栏收录

68 篇文章 1 订阅

订阅专栏

代码示例

22 篇文章 0 订阅

订阅专栏

本文详细介绍了如何通过批量提交、优化集群配置、设置随机主键和预分区、调整提交量、并发写入以及使用SSD硬盘来提升TiDB集群的写入效率。经过一系列优化，单线程写入效率从几十条每秒提升到了2万条每秒以上，并且探讨了不同优化措施的效果和适用场景。

摘要由CSDN通过智能技术生成

在url中增加allowMultiQueries=true&rewriteBatchedStatements=true&useConfigs=maxPerformance&useServerPrepStmts=true开启批量提交sql,并在代码中使用批量提交的方式提高写入效率;适当增大提交条数减少网络交换次数，以及使用多线程并发写入提高集群写入效率也可以提高写入效率。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TidbBatchInsertTest
{
    
    Connection conn = null;
    
    PreparedStatement stmt;
    
    ResultSet rs;
    
    public Connection getConnection()
        throws SQLException
    {
        try
        {
            String userName = "root";
            String password = "123456";
            String hostName = "10.37.62.111";
            hostName = "192.168.129.107";
            int port = 4000;
            String dbName = "pv_gray_pro";;
            String driverClass = "com.mysql.jdbc.Driver";
            // 允许批量提交sql之后效率大大提升
            String conUrl = "jdbc:mysql://10.37.62.111:4000,10.37.62.112:4000,10.37.62.113:4000,10.37.62.114:4000/"
                + dbName + "?useUnicode=true&characterEncoding=utf-8";
            conUrl = "jdbc:mysql://192.168.129.106:4000,192.168.129.108:4000,10.37.62.113:4000,192.168.129.109:4000/"
                + "?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&rewriteBatchedStatements=true&useConfigs=maxPerformance&useServerPrepStmts=true";
//            conUrl = "jdbc:mysql://" + hostName + ":" + port + "/" + dbName + "";
//            conUrl = "jdbc:mysql://" + hostName + ":" + port + "/" + dbName
//                + "?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&rewriteBatchedStatements=true&useConfigs=maxPerformance&useServerPrepStmts=true";
            Class.forName(driverClass);
            System.out.println("数据库连接:驱动[" + driverClass + "],url[" + conUrl + "]");
            conn = DriverManager.getConnection(conUrl, userName, password);
            return conn;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            conn.close();
            return conn;
        }
    }
    
    public String batchInsert(int totalSize, int batchSize)
        throws SQLException
    {
        try
        {
            /*
            CREATE TABLE  tb1 (
                aid BIGINT PRIMARY KEY AUTO_RANDOM, 
                `a` varchar(11) DEFAULT NULL,
                `b` varchar(256) DEFAULT NULL,
                `c` varchar(256) DEFAULT NULL,
                `d` varchar(256) DEFAULT NULL,
                `e` varchar(256) DEFAULT NULL,
                `f` varchar(256) DEFAULT NULL
              );
              
              CREATE TABLE tb1 (
                
                `id` varchar(256) DEFAULT NULL,
                `a` varchar(11) DEFAULT NULL,
                `b` varchar(256) DEFAULT NULL,
                `c` varchar(256) DEFAULT NULL,
                `d` varchar(256) DEFAULT NULL,
                `e` varchar(256) DEFAULT NULL
              ) shard_row_id_bits = 4 pre_split_regions= 3;
               */
            long start = System.currentTimeMillis();
            long time = start;
            long end ;
            String sql =
                "insert INTO default.tb1(id,a,b,c,d,e) VALUES (?,'268310','bb','20120601','06','551')";
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 1; i <= totalSize; i++)
            {
                ps.setObject(1, "" + i);
                ps.addBatch();
                if (i % batchSize == 0)
                {
                    ps.executeBatch();
                    ps.clearBatch();
                    end = System.currentTimeMillis();
                    System.out.println(new Date()+"本批次insert " + batchSize + "条,耗时" + (System.currentTimeMillis() - time) + "毫秒"+((batchSize*1000)/(end - time))+"条/秒");
                    time = end;
                }
            }
            end = System.currentTimeMillis();
            System.out.println("insert " + totalSize + "条总花费的时间：" + (end - start) + "毫秒 "+((totalSize*1000)/(end - start))+"条/秒");
        }
        finally
        {
            try
            {
                if (rs != null)
                {
                    rs.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        return null;
    }
    
    public static void main(String[] args)
        throws Exception
    {
        TidbBatchInsertTest t = new TidbBatchInsertTest();
        Connection connect = t.getConnection();
        System.out.println("获取连接" + connect);
        t.batchInsert(1000000, 5000);
    }
}

在这里插入图片描述

tiup cluster edit-config tidb-test

修改配置格式如下

server_configs:
  tidb: {}
  tikv:
    readpool.storage.normal-concurrency: 16
    rocksdb.compaction-readahead-size: "2MB"
    raftstore.region-split-check-diff: "32MB"
    rocksdb.defaultcf.disable-auto-compactions: TRUE
    raftstore.region-max-size: "384MB"
    raftstore.region-split-size: "256MB"
    raftstore.split-region-check-tick-interval: "300s"
    rocksdb.defaultcf.max-write-buffer-number: 10
    rocksdb.writecf.max-write-buffer-number: 10
  pd:
    replication.enable-placement-rules: true
  tiflash: {}
  tiflash-learner: {}
  pump: {}
  drainer: {}
  cdc: {}

保存之后滚动重启更新配置

tiup cluster reload tidb-test

参数	调优前（默认设置）	调优后	备注
raftstore.region-split-check-diff	region 大小的 1/16	32MB	允许 region 数据超过指定大小的最大值
rocksdb.defaultcf.disable-auto-compactions	FALSE	TRUE	开启自动 compaction 的开关。
raftstore.region-max-size	144MB	384MB	Region 容量空间最大值，超过时系统分裂成多个 Region
raftstore.region-split-size	96MB	256MB	分裂后新 Region 的大小，此值属于估算值。
raftstore.split-region-check-tick-interval	10s	300s	检查 region 是否需要分裂的时间间隔，0 表示不启用。
rocksdb.defaultcf.max-write-buffer-number	5	10	指最大 memtable 个数。
rocksdb.writecf.max-write-buffer-number	5	10	指最大 memtable 个数。
rocksdb.compaction-readahead-size	0	2MB	指异步 Sync 限速速率

# 通过mysql客户端在线修改为试验阶段功能,修改的是toml文件,会被reload覆盖丢失,不建议生产使用
mysql>
set config tikv `readpool.unified.max-thread-count`= 51;  -- 51  
set config tikv `readpool.storage.normal-concurrency`= 16;            -- 8 16
set config tikv `storage.block-cache.capacity`= "209317MB";        -- 209317MiB
set config tikv `raftstore.region-split-check-diff`= "32MB"; -- 6MiB  32MB
set config tikv `raftstore.region-max-size`= "384MB";   --         144MB  384MB
set config tikv `raftstore.region-split-size`= "256MB";    -- 96MiB  256MB
set config tikv `raftstore.split-region-check-tick-interval`= "300s";         -- 10s 300s
set config tikv `rocksdb.defaultcf.disable-auto-compactions`= TRUE;  -- false TRUE
set config tikv `rocksdb.defaultcf.max-write-buffer-number`= 10;   -- 5 10
set config tikv `rocksdb.writecf.max-write-buffer-number`= 10;      -- 5 10
set config tikv `rocksdb.compaction-readahead-size`= "2MB";               -- 0 2MB

小结:

Tidb集群写入效率比较低只有几十条每秒,需要提升写入效率
提高措施:
1.使用批量提交
在tidb的数据库连接url中增加配置开启批量提交allowMultiQueries=true&rewriteBatchedStatements=true&useConfigs=maxPerformance&useServerPrepStmts=true并在代码中使用批量提交的方式(每200条提交一次),提高写入效率
效果：单线程jdbc写入都提高到1千多条每秒
2.优化集群配置
#关闭日志同步
set config tikv raftstore.sync-log= false;
内存及线程数配置调优
效果：单线程jdbc写入都写入达到7000/s。

3.设置随机主键以及预分区避免写热点
– 设置随机主键
aid BIGINT PRIMARY KEY AUTO_RANDOM,
– 设置预分区
SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 ;
效果: 设置预分区时对程序刚启动时的写入速度有一定提升,但是对整体写入效率没有明显效果。设置随机主键对写入效率没有明显效果。

4.增加tidb扩容
在将tidb节点从4节点集群增加到5节点集群
效果: 理论上会提示集群能力,但测试没有明显效果,集群能力提升并未提升写入效率,瓶颈可能不在集群能力上。

5.程序端增大提交量,减少网络交换时间
因为写入的时候整个集群的cpu内存使用也很低，怀疑是程序端每次提交数据太少，频繁网络通信耗时导致单位时间提交数据量不足，所以提高每次提交的数据量从200条到5000条。
效果：使用jdbc单线程写入测试效果大大提高到17000条/秒左右。增加大提交批次到10000每次,jdbc写入效率仍有一定提升,达到20000多条/秒,增加到20000每批次时,写入效率下降到10000条/秒;

6.并发写入
本地验证2线程jdbc并发写入一个表中,效率提升明显,3线程并发达到2万,但是没法继续提高了。

7.将集群建立在ssd固态硬盘上
效果: 效率翻倍,单线程写效率3万多每秒

参考:
https://blog.csdn.net/huaxia2002/article/details/119911888
https://blog.csdn.net/symong888/article/details/111561071
https://www.bookstack.cn/read/TiDB-4.0/tune-tikv-thread-performance.md
https://www.bookstack.cn/read/TiDB-5.3-zh/tune-tikv-memory-performance.md