Previously, I wrote a series of articles on the performance test of several common API s of Redis by the FunTester framework. Today, as promised, I began to write the performance test of common MySQL functions by the FunTester framework.
Let's review some Redis performance test articles:
- Preparation for Redis pressure test of FunTester framework
- Redis performance test practice of FunTester framework
- list operation of Redis performance test of FunTester framework
- Map & incr of Redis performance test of FunTester framework
MySQL series articles are expected to be divided into two phases, namely, add, delete, change and query, and carry out simple SQL statement operations. If they are used in practical work, they need to realize the parameterization of SQL statements to meet business needs.
MySQL is my local service and uses this machine. This test only demonstrates the performance test of common addition, deletion, modification and query functions. It does not test the performance of MySQL service, nor set indexes and other parameters. Later, when I finish learning the gorm framework of Go language, I will write another MySQL performance test article of Go language to share.
If the preparation work is omitted, it is nothing more than starting a service and building a database table, mainly including two fields: name and age, a string and an integer number.
insert
This is used a lot in the work. It is estimated that it will be used more when making data. A single piece of data is used here.
The script uses SQL: insert into funds (name, age) values (\ "fun
Translated into Java: "insert into funds (name, age) values (\" fun "+ stringutil. GetString (10) +" \ "," + getrandomint (100) + ");"
Parameterization: it is composed of random strings and random numbers. Since there is no de duplication requirement in this project, the method of unique variable is not adopted.
Script use case
/**
* MySQL insert Sentence practice
*/
class MysqlInsert extends SqlBase {
static final String url = "jdbc:mysql://localhost:3306/funtester?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true&useSSL=false"
static final int thread = 20
static final int times = 10000
public static void main(String[] args) {
RUNUP_TIME = 0
def task = []
thread.times {
task << new FunTester()
}
new Concurrent(task, "FunTester Frame test MySQL").start()
FunLibrary.testOver()
}
private static class FunTester extends FixedThread {
def connection = getConnection(url, "root", "root123456")
def statement = getStatement(connection)
FunTester() {
super(null, times, true)
}
@Override
protected void doing() throws Exception {
statement.execute("INSERT INTO funtesters (name,age) VALUES (\"fun${StringUtil.getString(10)}\",${getRandomInt(100)});")
}
@Override
protected void after() {
super.after()
close(connection, statement)
}
@Override
FixedThread clone() {
return new FunTester(limit)
}
}
}
test result
~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~
＞ {
＞ ① . "rt":1,
＞ ① . "failRate":0.0,
＞ ① . "threads":20,
＞ ① . "deviation":"13.1%",
＞ ① . "errorRate":0.0,
＞ ① . "executeTotal":198907,
＞ ① . "qps2":17380.898287312128,
＞ ① . "total":198907,
＞ ① . "qps":20000.0,
＞ ① . "startTime":"2021-11-17 15:32:52",
＞ ① . "endTime":"2021-11-17 15:33:03",
＞ ① . "mark":"FunTester Frame test MySQL171532",
＞ ① . "table":"eJzj5VIgCNxK80JSi0tSi54tbHs2b9uzrd0v1k/1rQwO9FEwMlAoyShKTUwhbAovFy9+u4JSiwvy84pTFUIyc1OtFCp0i1OLMhNzFPJKc3UUKnVzU1MyE/MI2UHYHQoKuZl5ChDTrAwUcot1chMrrIyADGL0EvQFOeDRtA4gwkuN2jpq66ito7aO2jpq68iwlQilQ4wa9dnQo0Z9NvSoUZ8NPWrUZ0OPGvXZ0KNGfTb0qFGfDT1qOPsMAApg90I="
＞ }
~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~
Watching QPS is pretty fast, ha ha.
delete
It's troublesome to delete this, because these data will disappear if deleted. If you take How to test the performance of message queue This scheme is a little overqualified. I'll use the random name as the basis for deleting data and execute the test scheme of insert and delete first.
test case
/**
* MySQL insert Sentence practice
*/
class MysqlDelete extends SqlBase {
static final String url = "jdbc:mysql://localhost:3306/funtester?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true&useSSL=false"
static final int thread = 10
static final int times = 100
public static void main(String[] args) {
RUNUP_TIME = 0
def task = []
thread.times {
task << new FunTester()
}
new Concurrent(task, "FunTester Frame test MySQL").start()
FunLibrary.testOver()
}
private static class FunTester extends FixedThread {
def connection = getConnection(url, "root", "root123456")
def statement = getStatement(connection)
FunTester() {
super(null, times, true)
}
@Override
protected void doing() throws Exception {
String name = "fun${StringUtil.getString(10)}"
statement.execute("INSERT INTO funtesters (name,age) VALUES (\"$name\",${getRandomInt(100)});")
statement.execute("DELETE FROM funtesters WHERE name = \"$name\";")
}
@Override
protected void after() {
super.after()
close(connection, statement)
}
@Override
FixedThread clone() {
return new FunTester(limit)
}
}
}
test result
~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~
＞ {
＞ ① . "rt":1354,
＞ ① . "failRate":0.0,
＞ ① . "threads":10,
＞ ① . "deviation":"0.47%",
＞ ① . "errorRate":0.0,
＞ ① . "executeTotal":1000,
＞ ① . "qps2":7.3508872520913275,
＞ ① . "total":1000,
＞ ① . "qps":7.385524372230428,
＞ ① . "startTime":"2021-11-17 15:51:41",
＞ ① . "endTime":"2021-11-17 15:53:57",
＞ ① . "mark":"FunTester Frame test MySQL171551",
＞ ① . "table":"eJwBLwDQ/+aVsOaNrumHj+WkquWwkSzml6Dms5Xnu5jlm74hIOW6lOW9k+Wkp+S6jiAxMDI0/eodgA=="
＞ }
~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~ JSON ~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~~☢~
It can be seen that the performance is greatly degraded by the delete method.
Please look forward to the next issue of select and update and possible future Go language practice. The comparative pressure test of HTTP client and server of Java and Go is already on the way.