java 数据库压力测试 源码

java 数据库压力测试 源码

实验环境:
             数据库:Mysql
             语言:java
原理:

使用java多线程模拟并发环境,不断对数据库进行查询操作,求查询的最大最小平均时间。

简介:

数据库压侧是对数据库的数据查询、修改等操作在面向大量用户发出request的请求下的反应情况。可以使用的工具有很出名的Jmeter，原理就是通过jdbc与数据库交互，通过多线程机制模拟并发，达到压测的目的。针对于Mysql的压测工具有Mysql自带的MySqlSlap,一个很强大的压测工具，可以通过命令行让其自动生成SQl语句进行测试，可以测试不同的存储引擎、支持插入、删除等测试，支持单条、批量查询测试等等，之前想通过python脚本调用这个工具来实现压测的一些功能，但是由于我要达到的目的是在查询语句模板(select* from table where id = #key#)中这个key值是随机的，而且模板是有很多个的，MySQlSlap在自定义查询语句上，貌似只支持固定的查询，即不是随机变换的查询，这样就会出现一个问题，就是查询中数据库可能会产生针对于相同语句的缓存，这样查询的时间就会不准确。可以说MysqlSlap是一个测试数据库的抗压能力的工具，而不是一个测试查询语句执行效率的工具(拙见。。。)；

1. 首先，加载你的sql模板文件，这个模板文件类似：

select * from table where id = #key# and pid=#key2#select * from table where id = #key#select * from table;

2.sql生成代码：

public class SqlGenerator  {   private static final Logger logger = Logger.getLogger(SqlGenerator.class);   public  List<String> GenerateSQl(List<String> arguments, String sqlfiletemplate){//arguments是替换key的真正的值，在模板文件中key只是一个占位符      List<String> returnGenerateSQL = new ArrayList<String>();      try {         BufferedReader br = new BufferedReader(new FileReader(new File(sqlfiletemplate)));//读取模板文件         String line = "";         int lineindex= 0;         String[] sqlparts = null;         try {            while((line = br.readLine()) != null ){               sqlparts = line.split("#");               if (arguments.size() == sqlparts.length / 2 || arguments.size() == (sqlparts.length -1 ) / 2){                  for (int i =0 ;i < sqlparts.length; ++i){                     if (i % 2 != 0){                        sqlparts[i] = arguments.get(i / 2);//替换key值                     }                  }               }else{                  logger.error("The " + lineindex + " line parse error!");               }               ++lineindex;               returnGenerateSQL.add(StrArrayToStr(sqlparts).toString());//返回List,包括所有产生的查询语句            }         } catch (IOException e) {            logger.error("read the template error!");            return null;         }         return returnGenerateSQL;      } catch (FileNotFoundException e) {         logger.error("tempate file not found!");         return null;      }   }   private StringBuffer StrArrayToStr(String[] strarray){      StringBuffer sb = new StringBuffer();      for (String str : strarray){         sb.append(str);      }      return sb;   }} 

3.关于从数据库中随机抽取key值的部分：

public void init(){         if (key_to_replace != null){//key_to_replace是key的数组            totalcounts = new int[key_to_replace.size()];            for (int i  = 0; i < key_to_replace.size(); ++i){               totalcounts[i] = daomysql.getJdbcTemplate().queryForInt("select count(*) from (select distinct  " + key_to_replace.get(i) +"  from xxxxx) b");\\通过JdbcTemplate获取每个key(上面中要替换的值)到底有多少个，这里的key可以包含多个字段，即查询模板中条件有多个占位符            }         }      }      public HashMap<String,List<String>> RandomGetData(int dumpcount){//dumpcount指的是一次从数据库中抓取key多少个         HashMap<String, List<String>> returnHashMap = new HashMap<String, List<String>>();         if (this.totalcounts != null && this.key_to_replace != null){            for (int i = 0; i < totalcounts.length; ++i){               String dump_sql = "SELECT distinct "+ this.key_to_replace.get(i) + " FROM "+ tablename +" ORDER BY  RAND()  LIMIT  "+ (dumpcount > totalcounts[i]?totalcounts[i] : dumpcount);//一次dump一定数量的数据               if (daomysql != null){                  List<String> returnList = new ArrayList<String>();                  List rows = daomysql.getJdbcTemplate().queryForList(dump_sql);                  Iterator iterator = rows.iterator();                  while(iterator.hasNext()){                     Map res = (Map)iterator.next();                     returnList.add(res.get(key_to_replace.get(i)).toString());                  }                  returnHashMap.put(key_to_replace.get(i), returnList);//返回的Hash中，键值是相对应的key值，value即是dump的数据               }            }         }         return returnHashMap;      }

      之所以要先从数据库dump一定的量的key值，主要是考虑到一个问题，即当数据库中key的值很多，这个时候不可能全部都dump下来，也不可能把它写到文本里，因为dump的key到最后是要被随机的抽出去代替template中的占位符的，所以如果全表查询没什么必要，如果写入文本更是效率很低。所以，可以自己定义一部分的dump量，把它放在内存里，设置一个条件，一旦过了这个条件，重新dump相同数据量的数据，这样会增加效率。

 

4.线程：

public class QueryThread extends Thread {public static HashMap<Integer,TimeStatistics> thread_time_statistics = new HashMap<Integer,TimeStatistics>(); //每个线程都可以将数据存储到类变量中，key为线程id，value为TimeStatistics结构数据private RandomDataGenerator dbdump; //随机获取函数RandomGetData的接口private String templatepath; //模板路径private int threadid; //threadidprivate String threadloopcount; //线程循环量，可以控制并发中每个线程的循环量private int dumpcount;        //一次dump多少数据量的keyprivate SqlGenerator sqlgenerate;          // SqlGenerator的接口public SqlGenerator getSqlgenerate() {return sqlgenerate;}public void setSqlgenerate(SqlGenerator sqlgenerate) {this.sqlgenerate = sqlgenerate;}public RandomDataGenerator getDbdump() {return dbdump;}public void setDbdump(RandomDataGenerator dbdump) {this.dbdump = dbdump;}public String getTemplatepath() {return templatepath;}public void setTemplatepath(String templatepath) {this.templatepath = templatepath;}   public QueryThread(int threadid, int dumpcount,String loopcount){this.threadid = threadid;this.threadloopcount = loopcount;this.dumpcount = dumpcount;}@Overridepublic void run() {if (dbdump != null && templatepath != null && threadloopcount != null){System.out.print("the thread id:" + threadid + " begin to query \n");long begin_time = 0;     long end_time = 0;TimeStatistics time_statistics = new TimeStatistics();if ("*".equals(this.threadloopcount)){  \\ threadloopcount如果是*的话，说明是无限循环int querycount = 0;long onequery_begin_time = 0;long onequery_end_time = 0;long onequery_max_time = 0;long onequery_min_time = Long.MAX_VALUE;String max_time_query = "";String min_time_query = "";begin_time = System.currentTimeMillis();try{while(!Thread.currentThread().isInterrupted()){ //判断是否终端线程HashMap<String, List<String>> testData = dbdump.RandomGetData(dumpcount); //获取dumpcount量的数据for (int j = 0; j < 10000; ++j){     //在循环模式中，可以设置每过多少个循环重新dump一定量的数据，保持数据的随机性，这里设为10000List<String> queryList = GenerateQuery(testData);\\ 从dump的数据上，随机获取key，代替sql模板中的占位符，生成真正的查询语句Iterator iterator = queryList.iterator();while(iterator.hasNext()){++querycount;String query = iterator.next().toString();onequery_begin_time = System.nanoTime();dbdump.getDataTemplate().getJdbcTemplate().query(query, new RowCallbackHandler() {public void processRow(ResultSet arg0)throws SQLException {// todo nothing}});//进行查询onequery_end_time = System.nanoTime();if ((onequery_end_time - onequery_begin_time) < onequery_min_time){onequery_min_time = (onequery_end_time - onequery_begin_time);min_time_query = query;}if ((onequery_end_time - onequery_begin_time)> onequery_max_time){onequery_max_time = (onequery_end_time - onequery_begin_time);max_time_query = query;}}}try {//每次查询，sleep0.5秒Thread.sleep(500);} catch (InterruptedException e) { System.out.println("Thread id: " + this.threadid +"is stopping!");}}}catch(RuntimeException ex){//当主线程调用ShutDownNow()时，会ExecutorService会抛出异常，在这里可以捕捉到(使用的Executor框架, ShutDownNow其实就是调用了每个线程的interrupt()函数)end_time = System.currentTimeMillis();time_statistics.setOnequery_max_time(onequery_max_time);time_statistics.setOnequery_min_time(onequery_min_time);time_statistics.setOnequery_average_time((end_time - begin_time)*1000000/(querycount));time_statistics.setQuerytotal_duration_time(end_time - begin_time);time_statistics.setQuerytotalcount(querycount);time_statistics.setMax_time_of_query(max_time_query);time_statistics.setMin_time_of_query(min_time_query);thread_time_statistics.put(this.threadid, time_statistics);}}else{//非无限循环模式，即循环一定的次数int querycount = 0;long onequery_begin_time = 0;long onequery_end_time = 0;long onequery_max_time = 0;long onequery_min_time = Long.MAX_VALUE;String max_time_query = "";String min_time_query = "";begin_time = System.currentTimeMillis();for (int i = 0; i < Long.parseLong(threadloopcount); ++i){HashMap<String, List<String>> testData = dbdump.RandomGetData(dumpcount);List<String> queryList = GenerateQuery(testData);Iterator iterator = queryList.iterator();while(iterator.hasNext()){++querycount;String query = iterator.next().toString();onequery_begin_time = System.nanoTime();dbdump.getDataTemplate().getJdbcTemplate().query(query, new RowCallbackHandler() {public void processRow(ResultSet arg0)throws SQLException {// todo nothing}});onequery_end_time = System.nanoTime();if ((onequery_end_time - onequery_begin_time) < onequery_min_time){onequery_min_time = (onequery_end_time - onequery_begin_time);min_time_query = query;}if ((onequery_end_time - onequery_begin_time)> onequery_max_time){onequery_max_time = (onequery_end_time - onequery_begin_time);max_time_query = query;}}}end_time = System.currentTimeMillis();time_statistics.setQuerytotalcount(querycount);time_statistics.setQuerytotal_duration_time(end_time - begin_time);time_statistics.setOnequery_max_time(onequery_max_time);time_statistics.setOnequery_min_time(onequery_min_time);time_statistics.setMax_time_of_query(max_time_query);time_statistics.setMin_time_of_query(min_time_query);time_statistics.setOnequery_average_time((end_time - begin_time) * 1000000 / querycount);thread_time_statistics.put(this.threadid, time_statistics);}}}private List<String> GenerateQuery(HashMap<String, List<String>> testdata) {//从dump的数据上，随机获取key，代替sql模板中的占位符，生成真正的查询语句List<Integer> key_to_replace_size = null;List<String> args = new ArrayList<String>();Random random = new Random();for (Entry<String, List<String>> entryset : testdata.entrySet()){args.add(entryset.getValue().get(random.nextInt(entryset.getValue().size())));}List<String> queryList = sqlgenerate.GenerateSQl(args, templatepath);return queryList;}}

5.主函数：

 

public class App {                               privatestatic RandomDataGenerator dump;                privatestatic SqlGenerator sqlGenerator;                //privatestatic final Logger logger = Logger.getLogger(App.class);                 publicstatic void main(String[] args) throws Exception {                                if(args.length != 3){                                                System.out.print("usage:Appnumberofthread dumpcount loopcount(num|*)");                                                System.exit(1);                                }                                ApplicationContextctx = new ClassPathXmlApplicationContext("spring.xml");                                dump= (RandomDataGenerator) ctx.getBean("mysqldbdump"); //通过spring配置获取dump对象                                sqlGenerator= (SqlGenerator) ctx.getBean("sqlgenerator");//通过spring配置获取sqlgenerator对象                                ExecutorServiceexecutor = Executors.newCachedThreadPool();                                  System.out.println("threadid :0" + "begin to test db...");                                for(int i = 1 ; i <= Integer.valueOf(args[0]); ++i){                                                \\实例化查询线程,模拟并发                                                QueryThreadqt = new QueryThread(i, Integer.valueOf(args[1]), args[2]);                                                qt.setDbdump(dump);                                                qt.setTemplatepath("stlunion-big-sql.txt");                                                qt.setSqlgenerate(sqlGenerator);                                                executor.execute(qt);                                                //qt.join();                                }                                if("*".equals(args[2])){                                                StringstopFlag = "";                                                Scannerscanner = new Scanner(System.in);                                                while(true){                                                                System.out.println("Input(y|Y) to stop all threads:");                                                                stopFlag= scanner.nextLine().trim().toUpperCase();                                                                if("Y".equals(stopFlag)){ //判断终止条件                                                                                executor.shutdownNow();//终止所有线程                                                                                while(!executor.isTerminated()) {}  //等待所有线程结束                                                                                break;                                                                }                                                }                                }else                                {                                                executor.shutdown();                                                while(!executor.isTerminated()) {}                                }                                                               intstatics_size = 0;                                System.out.println("threadid :0" + "end to test db...");                                StringBuffersb = new StringBuffer();                                longmin = Long.MAX_VALUE;                                longmax = 0;                                longsum = 0;                                intqueryTotalCount = 0;                                Stringmax_query = "";                                Stringmin_query = "";                                for(Entry<Integer,TimeStatistics>entry : QueryThread.thread_time_statistics.entrySet()){                                                TimeStatisticsthreadStatistics = entry.getValue();                                                queryTotalCount+= threadStatistics.getQuerytotalcount();                                                sum+= threadStatistics.getQuerytotal_duration_time();                                                if(threadStatistics.getOnequery_max_time() > max){                                                                max= threadStatistics.getOnequery_max_time();                                                                max_query= threadStatistics.getMax_time_of_query();                                                }                                                if(threadStatistics.getOnequery_min_time() < min){                                                                min= threadStatistics.getOnequery_min_time();                                                                min_query= threadStatistics.getMin_time_of_query();                                                }                                                sb.append("Threadid: " + entry.getKey() + "\n" +                                                                                "Thecount of these queries: " + threadStatistics.getQuerytotalcount() +"\n" +                                                                                "Themax time to use (one piece of query): " +threadStatistics.getMax_time_of_query() + " : " +threadStatistics.getOnequery_max_time() + " nanoseconds" +"\n" +                                                                                "Themin time to use (one piece of query): " +threadStatistics.getMin_time_of_query() + " : " +threadStatistics.getOnequery_min_time() + " nanoseconds" +"\n"+                                                                                "Theaverage time : " + threadStatistics.getOnequery_average_time() + "nanoseconds" + "\n");                                }                                                               sb.append("Summary:\n" + "The max time to use (one piece of query):  " + max_query + ":" + max +"nanoseconds" + "\n"                                +"The min time to use (one piece of query): " + min_query +":" + min + "nanoseconds" + "\n" +                                                                "Theaverage time to use:" +sum/queryTotalCount*1000000 + "nanoseconds" +"\n");                                System.out.println(sb.toString());                }}