Test Method
MySQL is one of the most popular open-source relational databases. It works with the Linux, Apache, and Perl/PHP/Python to establish a LAMP model for efficient web solutions. It solves problems such as poor database performance, long data replication delay, and slow fault recovery in high-concurrency scenarios.
RDS for MySQL is ready for immediate use, and provides backup and restoration, data migration, security protection, high availability, and elastic scalability. You can obtain a production database with high performance and scalability in a few minutes after simple configurations while your data integrity and service continuity are guaranteed.
Test Environment
- Elastic Cloud Server (ECS): general computing | c3.2xlarge.2 | 8 vCPUs | 16 GB, CentOS7.4 64 bit image. Bind an elastic IP (EIP) to the ECS because additional compilation tools need to be installed on stress testing tools.
RDS for MySQL 8.0 test environment is as follows:
- ECS: general computing-plus | c6.4xlarge.2 | 16 vCPUs | 32 GB, CentOS 7.6 (64 bit). Bind an EIP to the ECS because additional compilation tools need to be installed on stress testing tools.
Test Tool
Sysbench is a multi-threaded benchmark tool based on LuaJIT, allowing you to quickly get an impression of system performance by using a built-in database test model. For details, visit https://github.com/akopytov/sysbench.
Sysbench 1.0.18 is used in this test. Run the following commands to install it:
# wget -c https://github.com/akopytov/sysbench/archive/1.0.18.zip
# yum install autoconf libtool mysql mysql-devel vim unzip
# unzip 1.0.18.zip
# cd sysbench-1.0.18
# ./autogen.sh
# ./configure
# make
# make install
Test Procedure
Replace the database name, connection IP address, and user password based on the site requirements.
- Import data.
- Run the following command to log in to a database and create the test database loadtest:
mysql -u root -P 3306 -h <host> -p -e "create database loadtest"
- Run the following command to import the test background data to the loadtest database:
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password=<password> --mysql-port=3306 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --num-threads=20 prepare
- Run the following command to log in to a database and create the test database loadtest:
- Run the following command to perform a stress testing:
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password=<password> --mysql-port=3306 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=200 --report-interval=3 --forced-shutdown=1 run
- Run the following command to delete the test data:
sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --mysql-db=loadtest --mysql-user=root --mysql-password=<password> --mysql-port=3306 --mysql-host=<host> --oltp-tables-count=64 --oltp-table-size=10000000 --max-time=3600 --max-requests=0 --num-threads=200 cleanup
Testing Model
- Table structure:
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`k` INTEGER UNSIGNED DEFAULT '0' NOT NULL,
`c` CHAR(120) DEFAULT '' NOT NULL,
`pad` CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
- Read/write ratio:
The default transaction submitted by sysbench contains 18 SQL statements. The details are as follows:
- Ten primary key SELECT statements:
- Four range SELECT statements:
SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};
SELECT SUM(K) FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end};
SELECT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;
SELECT DISTINCT c FROM ${rand_table_name} WHERE id BETWEEN ${rand_id_start} AND ${rand_id_end} ORDER BY c;
- Two UPDATE statements:
UPDATE ${rand_table_name} SET k=k+1 WHERE id=${rand_id}
UPDATE ${rand_table_name} SET c=${rand_str} WHERE id=${rand_id}
- One DELETE statement:
- One INSERT statement:
INSERT INTO ${rand_table_name} (id, k, c, pad) VALUES (${rand_id},${rand_k},${rand_str_c},${rand_str_pad})
Test Metrics
- Transactions per second (TPS) refers to the number of transactions executed per second by a database. Each transaction contains 18 SQL statements.
- Queries per second (QPS) refers to the number of SQL statements, including INSERT, SELECT, UPDATE, and DELETE statements, executed per second.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot