Question

postgresql 怎么添加聚合索引

Answer 1

sysbench原来自带的lua数据装载脚本是使用以下方式串行装载的，速度比较慢(比单条insert快，但是比COPY慢)。insertintotable1values(),(),().insertintotable2values(),(),().insertintotablenvalues(),(),().使用prepare导入数据的用法举例./sysbench_pg--test=lua/oltp.lua--db-driver=pgsql--pgsql-host=127.0.0.1--pgsql-port=1921--pgsql-user=postgres--pgsql-password=postgres--pgsql-db=postgres--oltp-tables-count=64--oltp-table-size=1000000--num-threads=64prepareprepare表示装载数据，但是它串行的。sysbench0.5中可以在命令行中指定测试时启动的并行线程数，这个测试过程是使用run命令，而且是多线程并发的，所以我们可以使用sysbench的run命令来造数据，而不再使用其提供的prepare命令的方法来造数据。run命令会根据命令行参数--num-threads来指定并发线程数的多少。在sysbench中自定义的lua脚本中要求实现以下几个函数：functionthread_init(thread_id):此函数在线程创建后只被执行一次functionevent(thread_id):每执行一次就会被调用一次。由上可以知道，本次造数据的脚本我们只需要实现thread_init()函数就可以了。生成测试数据的脚本沿用老唐提供的代码：#include#include#include#include#includeuint64_tmy_rand(structrandom_data*r1,structrandom_data*r2){uint64_trand_max=100000000000LL;uint64_tresult;uint32_tu1,u2;random_r(r1,&u1);random_r(r2,&u2);result=(int64_t)u1*(int64_t)u2;result=result%rand_max;returnresult;}intmain(intargc,char*argv[]){structtimevaltpstart;structrandom_datar1,r2;inti;intr;intmax_value;charrand_state1[128];charrand_state2[128];if(argc!=2){printf("Usage:%s\n",argv[0]);return1;}max_value=atoi(argv[1]);gettimeofday(&tpstart,NULL);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>sbtest'..table_id..'.dat&')os.execute('catsbtest'..table_id..'.dat|psql-h'..pgsql_host..'-p'..pgsql_port..'-U'..pgsql_user..'-d'..pgsql_db..'-c"copysbtest'..table_id..'fromstdinwithcsv"')os.execute('rm-fsbtest'..table_id..'.dat')endfunctioncreate_index(table_id)db_query("selectsetval('sbtest"..table_id.."_id_seq',"..(oltp_table_size+1)..")")db_query("CREATEINDEXk_"..table_id.."onsbtest"..table_id.."(k)")endfunctionthread_init(thread_id)set_vars()print("threadprepare"..thread_id)fori=thread_id+1,oltp_tables_count,num_threadsdocopydata(i)create_index(i)endendfunctionevent(thread_id)os.exit()end用法，必须把psql放到路径中，因为lua中需要用到psql命令exportPATH=/home/digoal/pgsql9.5/bin:$PATH生成数据，速度比以前快多了./sysbench_pg--test=lua/copy.lua\--db-driver=pgsql\--pgsql-host=127.0.0.1\--pgsql-port=1921\--pgsql-user=postgres\--pgsql-password=postgres\--pgsql-db=postgres\--oltp-tables-count=64\--oltp-table-size=1000000\--num-threads=64\run清除数据,droptable./sysbench_pg--test=lua/copy.lua\--db-driver=pgsql\--pgsql-host=127.0.0.1\--pgsql-port=1921\--pgsql-user=postgres\--pgsql-password=postgres\--pgsql-db=postgres\--oltp-tables-count=64\--oltp-table-size=1000000\--num-threads=64\cleanuplua全局变量代码：sysbench/scripting/lua/src/lua.h:#definelua_register(L,n,f)(lua_pushcfunction(L,(f)),lua_setglobal(L,(n)))sysbench/scripting/lua/src/lua.h:#definelua_setglobal(L,s)lua_setfield(L,LUA_GLOBALSINDEX,(s))sysbench/scripting/lua/src/lbaselib.c:lua_setglobal(L,"_G");sysbench/scripting/lua/src/lbaselib.c:lua_setglobal(L,"_VERSION");/*setglobal_VERSION*/sysbench/scripting/lua/src/lbaselib.c:lua_setglobal(L,"newproxy");/*setglobal`newproxy'*/sysbench/scripting/script_lua.c:lua_setglobal(state,opt->name);sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_uniq");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rnd");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_str");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_uniform");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_gaussian");sysbench/scripting/script_lua.c:lua_setglobal(state,"sb_rand_special");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_connect");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_disconnect");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_query");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bulk_insert_init");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bulk_insert_next");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bulk_insert_done");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_prepare");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bind_param");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_bind_result");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_execute");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_close");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_store_results");sysbench/scripting/script_lua.c:lua_setglobal(state,"db_free_results");sysbench/scripting/script_lua.c:lua_setglobal(state,"DB_ERROR_NONE");sysbench/scripting/script_lua.c:lua_setglobal(state,"DB_ERROR_DEADLOCK");sysbench/scripting/script_lua.c:lua_setglobal(state,"DB_ERROR_FAILED");sysbench/scripting/script_lua.c:lua_setglobal(L,"db_driver");传入参数，可以把sysbench_pg的参数-替换成_在lua脚本中使用这些变量，例子--pgsql-host=127.0.0.1->对应lua中的变量名pgsql_host--pgsql-port=1921->对应lua中的变量名pgsql_port--pgsql-user=postgres->对应lua中的变量名pgsql_user--pgsql-password=postgres->对应lua中的变量名pgsql_password--pgsql-db=postgres->对应lua中的变量名pgsql_db--oltp-tables-count=64->对应lua中的变量名oltp_tables_count--oltp-table-size=1000000->对应lua中的变量名oltp_table_size--num-threads=64->对应lua中的变量名num_threads