RDS for MySQL并发性能测试
最近由于工作需要，需要对阿里云数据库-RDS for MySQL进行性能测试，通过MySQL自带的mysqlslap工具可以进行并发性能测试，但是输出显示总感觉有问题，所以就萌生想法自己开发代码通过JDBC连接RDS，测试RDS的并发性能。本文是自己写的Java程序，模拟mysqlslap进行并发测试。打印了每个并发执行的时间，以及并发测试中执行最长时间、最短时间、以及平均时长。
RDS for MySQL版本：MySQL8.0
1、pom.xml配置
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0modelVersion> <groupId>org.examplegroupId> <artifactId>RdsConcurrencyTestartifactId> <version>1.0-SNAPSHOTversion> <properties> <maven.compiler.source>8maven.compiler.source> <maven.compiler.target>8maven.compiler.target> properties> <dependencies> <dependency> <groupId>mysqlgroupId> <artifactId>mysql-connector-javaartifactId> <version>8.0.16version> dependency> dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.pluginsgroupId> <artifactId>maven-shade-pluginartifactId> <version>2.3version> <executions> <execution> <phase>packagephase> <goals> <goal>shadegoal> goals> <configuration> <minimizeJar>falseminimizeJar> <shadedArtifactAttached>trueshadedArtifactAttached> <artifactSet> <includes> <include>*:*include> includes> artifactSet> <filters> <filter> <artifact>*:*artifact> <excludes> <exclude>META-INF/*.SFexclude> <exclude>META-INF/*.DSAexclude> <exclude>META-INF/*.RSAexclude> <exclude>**/log4j.propertiesexclude> excludes> filter> filters> <transformers> <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer"> <mainClass>com.mysql.test.RdsConcurrencyTestmainClass> transformer> <transformer implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer"> <resource>reference.confresource> transformer> <transformer implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer"> <resource> META-INF/ resource> transformer> transformers> configuration> execution> executions> plugin> <plugin> <groupId>net.alchim31.mavengroupId> <artifactId>scala-maven-pluginartifactId> <version>3.3.2version> <executions> <execution> <id>scala-compile-firstid> <phase>process-resourcesphase> <goals> <goal>compilegoal> goals> execution> <execution> <id>scala-test-compile-firstid> <phase>process-test-resourcesphase> <goals> <goal>testCompilegoal> goals> execution> executions> plugin> plugins> build> project>
2、工具类
package com.mysql.test; import java.sql.*; import java.util.List; public class DBUtil implements Runnable{ private final String host; private final String port; private final String user; private final String password; private final String database; private final String sql; private String url = null; private long costTime; private Listlist; public long getCostTime() { return costTime; } public DBUtil(String host1,String port1,String database1,String user1,String password1,String sql1,List list){ this.host = host1; this.port = port1; this.user = user1; this.password = password1; this.database = database1; this.sql = sql1; this.list = list; } //获取Connection连接对象的方法,使用static方便之后在其他类中调用 public Connection getConn() { Connection conn = null; try { String name = "com.mysql.cj.jdbc.Driver"; Class.forName(name); conn = DriverManager.getConnection(url, user, password);//获取连接 } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } return conn; } //关闭资源的方法 public void close(ResultSet rs,PreparedStatement ps,Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Override public void run() { // 开始时间 long startTime = 0; startTime = System.currentTimeMillis(); url = "jdbc:mysql://" + host + ":" + port + "/" + database + "?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT"; PreparedStatement ps = null; ResultSet rs = null; Connection myConn = getConn(); try { ps = myConn.prepareStatement(sql); rs = ps.executeQuery(); // 结束时间 long endTime = System.currentTimeMillis(); costTime = endTime - startTime; list.add(costTime); int fieldCount = rs.getMetaData().getColumnCount(); while (rs.next()) { int i; StringBuilder result = new StringBuilder(); for (i=1;i<=fieldCount;i++){ result.append(rs.getString(i)).append(","); } //System.out.println(result); } } catch (SQLException throwable) { throwable.printStackTrace(); } finally { close(rs,ps,myConn); } } }
3、并发测试类
package com.mysql.test; import java.sql.SQLOutput; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collections; import java.util.Date; import java.util.List; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; public class RdsConcurrencyTest { public static String host = null; public static String port = null; public static String user = null; public static String password = null; public static String database = null; public static String sql = null; public static String concurrent = null; // 处理Main方法传入的参数 private static boolean parseArgs(String[] args) { for(int i = 0; i < args.length;) { if (args[i].equals("--host")) { host = args[i+1]; i += 2; } else if (args[i].equals("--port")) { port = args[i+1]; i += 2; } else if (args[i].equals("--database")) { database = args[i+1]; i += 2; } else if (args[i].equals("--user")) { user = args[i+1]; i += 2; } else if (args[i].equals("--password")) { password = args[i+1]; i += 2; } else if (args[i].equals("--sql")) { sql = args[i+1]; i += 2; } else if (args[i].equals("--concurrent")) { concurrent = args[i+1]; i += 2; } else { return false; } } return true; } //如果没有传参或者传参错误，打印传参的格式 private static void printUsage() { System.err.println("Usage: RdsConcurrencyTest [options]"); System.err.println("\t--host\thost ip"); System.err.println("\t--port\tport"); System.err.println("\t--database\tdatabase"); System.err.println("\t--user\tuser"); System.err.println("\t--password\tpassword"); System.err.println("\t--sql\tsql"); System.err.println("\t--concurrent\t[Num]"); } // Main方法入口 public static void main(String[] args) { if (!parseArgs(args)) { printUsage(); System.exit(1); } if (host == null || port == null || database == null || user == null || password == null || sql == null || concurrent == null) { printUsage(); System.exit(1); } //开始时间 Date startDt = new Date(); SimpleDateFormat ft = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss"); // 设置并发数(线程数) int concurrentNum = Integer.parseInt(concurrent); ExecutorService executorService = Executors.newFixedThreadPool(concurrentNum); DBUtil thread ; ListreList = new ArrayList (); for(int i = 0; i< concurrentNum; i++) { thread = new DBUtil(host,port,database,user,password,sql,reList); executorService.execute(thread); } //关闭线程池 executorService.shutdown(); while (true) { //所有的线程都结束 if (executorService.isTerminated()) { //结束时间 Date endDt = new Date(); long maxTime = Collections.max(reList); long minTime = Collections.min(reList); long allTime = 0L; for(Long cost:reList){ allTime += cost; } long avgTime = allTime/reList.size(); System.out.println("Milliseconds to run all queries : " + reList); System.out.println("Maximum of milliseconds to run all queries : " + maxTime + "ms."); System.out.println("Minimum of milliseconds to run all queries : " + minTime + "ms."); System.out.println("Average of milliseconds to run all queries : " + avgTime + "ms."); System.out.println("Number of concurrent : " + concurrentNum); System.out.println("Run begin time : " + ft.format(startDt)); System.out.println("Run end time : " + ft.format(endDt)); System.out.println("--------------------------------------------------------------------------------"); break; } } } }
4、使用编译好的jar包在ECS上进行测试。
java -jar RdsConcurrencyTest.jar --host xxx.mysql.rds.xxx.com.cn --port 3306 --database lgb_test --user root --password Root@1234 --sql "select * from lgb_test" --concurrent 70