sysbench 高效并发灌输数据 to postgreSQL

1. 背景

    最近在对postgreSQL进行压测数据时，发现当准备大量数据时，由于prepare方法是串行执行的，一条一条SQL插入导致执行速度很慢，也极易受到其他不稳定因素影响，导致数据插入中断。因此，提高并行插入数据的速度是十分必要的。本文结合大量网上文章，sysbench对如何并行灌输数据进行梳理归纳。

2. 并行灌输

2.1. 主要思路

    sysbench可在测试时启动并行线程数即run命令，利用run命令来构造数据，num-thread指定线程数。
    在sysbench中自定义的lua脚本中要求实现以下函数：

function thread_init(thread_id): 此函数在线程创建后只被执行一次
function event(thread_id):       每执行一次就会被调用一次

2.2. 高效生产数据

    要高效生产数据，最好的方案就是通过程序生成数据，再通过管道的形式直接灌输到数据库里，可避免数据需要落磁盘产生IO导致慢的问题。
    虽然通过lua脚本也能生成asbench要的测试数据，但其中的asbench在lua脚本中提供的随机函数sb_rand及sb_rand_uniform还是不够快，这会影响数据的装载速度，为了达到一种极致的性能，可以写一个C语言程序来生成所需要的数据。

#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <stdint.h>
#include <sys/time.h>
#include <string.h>
uint64_t my_rand(struct random_data * r1, struct random_data * r2)
{
    uint64_t rand_max = 100000000000LL;
    uint64_t result;
    uint32_t u1, u2;
    random_r(r1, &u1);
    random_r(r2, &u2);
    result = (int64_t)u1 * (int64_t)u2;
    result = result % rand_max;
    return result;
}

int main(int argc, char *argv[])
{
    struct timeval tpstart;
    struct random_data r1, r2;
    int i;
    int r;
    int max_value;
    char rand_state1[128];
    char rand_state2[128];
    if (argc !=2)
    {
        printf("Usage: %s <rownums>\n", argv[0]);
        return 1;
    }
    max_value = atoi(argv[1]);
    gettimeofday(&tpstart,NULL);
    memset((void*)&r1, 0, sizeof(r1));
    memset((void*)&r2, 0, sizeof(r1));
    memset((void*)rand_state1, 0, sizeof(rand_state1));
    memset((void*)rand_state2, 0, sizeof(rand_state2));
    initstate_r(tpstart.tv_usec,rand_state1,sizeof(rand_state1),&r1);
    srandom_r(tpstart.tv_usec, &r1);
    gettimeofday(&tpstart,NULL);
    initstate_r(tpstart.tv_usec,rand_state2,sizeof(rand_state1),&r2);
    srandom_r(tpstart.tv_usec, &r2);
    for (i=1; i<max_value+1; i++)
    {
        r = my_rand(&r1, &r2) % max_value;
        printf("%d,%d,%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu-%011llu,%011llu-%011llu-%011llu-%011llu-%011llu\n",
                i,
                r,
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2),
                 my_rand(&r1, &r2)
              );
    }
    return 0;
}

    然后编译该文件：gcc gendata.c -o gendata

2.3. 装载数据的Lua脚本

    脚本名为copy.lua

pathtest = string.match(test, "(.*/)") or ""

dofile(pathtest .. "common.lua")

function copydata(table_id)
  local query

  query = [[
CREATE UNLOGGED TABLE sbtest]] .. table_id .. [[ (
id SERIAL NOT NULL,
k INTEGER,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) ]]

  db_query(query)

  os.execute ('export PGPASSWORD=' .. pgsql_password)
  os.execute ('rm -f sbtest' .. table_id .. '.dat')
  os.execute ('mknod sbtest' .. table_id .. '.dat p')
  os.execute ('./gendata ' .. oltp_table_size .. ' >> sbtest'..table_id ..'.dat &')
  os.execute ('cat sbtest' .. table_id .. '.dat | PGPASSWORD=Bd2021_bd psql -h ' .. pgsql_host .. ' -p ' .. pgsql_port .. ' -U ' .. pgsql_user .. ' -d ' .. pgsql_db .. ' -c "copy sbtest' .. table_id .. ' from stdin with csv"')
  os.execute ('rm -f sbtest' .. table_id .. '.dat')
end

function create_index(table_id)
  db_query("select setval('sbtest" .. table_id .. "_id_seq', " .. (oltp_table_size+1) .. ")" )
  db_query("CREATE INDEX k_" .. table_id .. " on sbtest" .. table_id .. "(k)")
end

function thread_init(thread_id)
   set_vars()

   print("thread prepare"..thread_id)

   for i=thread_id+1, oltp_tables_count, num_threads  do
     copydata(i)
     create_index(i)
   end
end

function event(thread_id)
   os.exit()
end

2.4. 并行灌输数据

nohup sysbench 
--test=/usr/share/sysbench/tests/include/oltp_legacy/copy.lua 
--db-driver=pgsql 
--pgsql-db=sysbench 
--pgsql-user=root 
--pgsql-password=test@123 
--pgsql-port=5432 
--pgsql-host=127.0.0.1
--oltp-tables-count=32 
--oltp-table-size=10000000 
--num-threads=16 run > prepare.out 2>&1 &

3. 参考

    https://www.csudata.com/csu_article/10113
    https://bbs.huaweicloud.com/blogs/116437