tencent cloud

SQL Database Performance Testing
Download PDF
Last updated: 2025-03-10 16:46:55
SQL Database Performance Testing
Last updated: 2025-03-10 16:46:55
Download PDF
This document introduces how to orchestrate a script for the PTS SQL database, which is used for PTS to support relational databases that use SQL, such as MySQL.

Basic Usage

By using the APIs provided by the pts/sql module, you can create a client to connect to the SQL database, send DDL and DML requests, and perform basic CRUD operations on the database.

Database Connection

The new sql.Database(driverName: string, dataSourceName: string) method can be called to establish a database connection. Where, driverName
specifies the database driver, and dataSourceName specifies the data source.
The script example is as follows:
import sql from 'pts/sql';

const db = new sql.Database(sql.MySQL, "user:passwd@tcp(ip:port)/database")

export default function () {
    // Send a request to the database.
}
Note:
If the database table contains the date or time field, it is recommended to add the parseTime parameter (for example, user:passwd@tcp(ip:port)/database?parseTime=true) in the connection string when establishing the database connection to avoid time parsing errors.
It is recommended to put the above database connection establishment statement as a global variable outside the main function (as shown in the above example) so that the same VU can reuse the connection when iterating the main function, avoiding unnecessary resource consumption from repeatedly creating database connections.

SQL Query

The db.query(sql string) method can be called for SQL query, which returns an array of database records that meet the conditions. Where, sql specifies the SQL query statement passed in.
The script example is as follows:
import sql from 'pts/sql';

const db = new sql.Database(sql.MySQL, "user:passwd@tcp(ip:port)/database")

export default function () {
    let rows = db.query("SELECT * FROM user");
    console.log(JSON.stringify(rows)); // [{"id":1,"name":"zhangsan","age":23},{"id":2,"name":"lisi","age":2}]
}

SQL Execution

The db.exec(sql string) method can be called for SQL execution. After the execution statement is padded in, this method can return the impact of the execution on the database (return fields include the ID of the last inserted row and the number of all affected rows).
Where, sql specifies the SQL execution statement passed in.
The script example is as follows:
import sql from 'pts/sql';

const db = new sql.Database(sql.MySQL, "user:passwd@tcp(ip:port)/database")

export default function () {
    // Modify data.
    let result = db.exec("UPDATE user SET age=? WHERE name='zhangsan'", Math.floor(Math.random() * 100));
    console.log(JSON.stringify(result)); // {"lastInsertId":0,"rowsAffected":1}
    
    // Insert data.
    let result1 = db.exec("insert into user (name, age) values ('wanger', 18)");
    console.log(JSON.stringify(result)); // {"lastInsertId":66,"rowsAffected":1}
}
Note:
The db.exec method supports common DDL commands (such as create, drop, and alter) and common DML commands (such as insert, update, and delete).

Script Examples

An example of a complete script containing basic database operations and checkpoints is as follows:
import sql from 'pts/sql';
import { sleep, check } from 'pts';

const db = new sql.Database(sql.MySQL, "user:passwd@tcp(ip:port)/database")

export default function () {
    // Query data.
    let rows = db.query("SELECT * FROM user");
    console.log(JSON.stringify(rows)); // [{"id":1,"name":"zhangsan","age":23},{"id":2,"name":"lisi","age":2}]
    
    // Add data.
    let result = db.exec("insert into user (name, age) values ('wanger', 18)");
    console.log(JSON.stringify(result)); // {"lastInsertId":66,"rowsAffected":1}

    // Delete data.
    let result1 = db.exec("delete from user where id > 8");
    console.log(JSON.stringify(result)); // {"lastInsertId":0,"rowsAffected":2}
    
    // Modify data.
    let result2 = db.exec("UPDATE user SET age=? WHERE name='zhangsan'", Math.floor(Math.random() * 100));
    console.log(JSON.stringify(result)); // {"lastInsertId":0,"rowsAffected":1}
    
    // Set checkpoints.
    check("1 row returned", () => result.rowsAffected === 1);

    sleep(1)
}

Script Verification

To verify the script execution results, you can use the PTS debugging feature to quickly verify whether the results meet expectations before the formal performance testing.
For more details, see Debug Scenarios.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback