扩展2：数据库性能测试m6g vs. m5

We uses HammerDB to benchmark and compare between different database engines. We have got their results of RDS MySQL m6g and m5 instances. Here is a summary of results and findings from them.

HammerDB Benchmark Results of RDS MySQL m6g vs. m5

DBS Benchmarking team uses HammerDB to benchmark and compare between different database engines. We have got their results of RDS MySQL m6g and m5 instances. Here is a summary of results and findings from them.

Overall Throughput

According to the benchmark results from DBS Benchmarking team:

• For peak throughput, m6g is up to 20% better than m5 (excluding 24xl).
  • Note: this also include certain level of performance improvements due to OS/kernel version upgrade, from 4.4 to 4.14.
  • The advantage of m6g vs. m5 at 16xlarge is also partially due to single CPU socket vs. 2x CPU sockets.
  • As the biggest single CPU socket instance type of m5, at 12xlarge, m6g is about 12% better than m5.
• From large to 12xlarge, the advantage of m6g at peak throughput is around 12% better than according m5 instance type.

Throughput by Concurrency

• For **large, xlarge and 4xlarge instance sizes, m6g shows consistent higher throughput compare to m5, in some cases up to about 50%. **

• The benchmark results of 12xlarge and above are not as consistent as smaller instance sizes. This should also be looked into.

large

xlarge

4xlarge

12xlarge

16xlarge and above

Appendix

Benchmark configurations

• HammerDB version: 3.1
• Driver instance type: “m5.4xlarge”
• Number of warehouses

• Custom DB parameters

• DB instances configurations

实验步骤

create-client-instance

aws ec2 run-instances --image-id ami-0c2b8ca1dad447f8a --count 1 --instance-type c5.2xlarge \
--key-name "jiade" --subnet-id subnet-b9581997 --security-group-ids sg-ed5108ab \
--user-data file://userdata.txt --tag-specifications 'ResourceType=instance,Tags=[{Key=AZ,Value=us-east-1b}]' \
--query 'Instances[*].InstanceId' --output text
# output i-0d0560975f5225caa

aws ec2 describe-instances --instance-ids i-0d0560975f5225caa --query 'Reservations[*].Instances[*].PublicIpAddress' --output text
# output 3.84.60.45

ssh to your client-instance

yum update -y
yum install htop -y
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | bash
yum -y install sysbench
sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ff> $x/rps_cpus; done'
sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"
cat << EOF > /etc/security/limits.conf
*                              soft    nofile  65536
*                              hard    nofile  65536
*                              soft    nproc  65536
*                              hard    nproc  65536
EOF

create-db

db.r5.2xlarge

aws rds create-db-cluster --db-cluster-identifier aurora-r5 \
  --database-name sysbench \
  --db-cluster-parameter-group-name aurora-mysql57-tuned \
  --engine aurora-mysql --engine-version 5.7.mysql_aurora.2.10.0 \
  --master-username aurora --master-user-password wujiade4444 \
  --db-subnet-group-name default --vpc-security-group-ids sg-ed5108ab

aws rds create-db-instance --db-instance-identifier aurora-r5 \
  --db-cluster-identifier aurora-r5 --engine aurora-mysql --db-instance-class db.r5.2xlarge \
  --availability-zone us-east-1b --enable-performance-insights

db.R6g.2xlarge

aws rds create-db-cluster --db-cluster-identifier aurora-r6 \
  --database-name sysbench \
  --db-cluster-parameter-group-name aurora-mysql57-tuned \
  --engine aurora-mysql --engine-version 5.7.mysql_aurora.2.10.0 \
  --master-username aurora --master-user-password wujiade4444 \
  --db-subnet-group-name default --vpc-security-group-ids sg-ed5108ab

aws rds create-db-instance --db-instance-identifier aurora-r6 \
  --db-cluster-identifier aurora-r6 --engine aurora-mysql --db-instance-class db.R6g.2xlarge \
  --availability-zone us-east-1b --enable-performance-insights

使用sysbench开始压力测试

oltp_read_only

oltp_read_only 生成测试表

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only prepare

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only prepare

oltp_read_only 测试只读性能

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-port=3306 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --max-time=600 --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_only run >> r5_read.log

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-port=3306 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --max-time=600 --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_only run >> r6g_read.log

oltp_read_only 清除测试数据

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only cleanup

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only cleanup

oltp_write_only

oltp_write_only 生成测试表

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_write_only prepare

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_write_only prepare

oltp_write_only 测试只读性能

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-port=3306 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --max-time=600 --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_write_only run >> r5_read.log

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-port=3306 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --max-time=600 --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_write_only run >> r6g_read.log

oltp_write_only 清除测试数据

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_write_only cleanup

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_write_only cleanup

oltp_read_write

oltp_read_write 生成测试表

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_write prepare

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_write prepare

oltp_read_write 测试只读性能

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-port=3306 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --max-time=600 --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_write run >> r5_read.log

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-port=3306 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --max-time=600 --threads=32 --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=20 oltp_read_write run >> r6g_read.log

oltp_read_write 清除测试数据

sysbench --db-driver=mysql --mysql-host=aurora-r5.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_write cleanup

sysbench --db-driver=mysql --mysql-host=aurora-r6.cmq96p5vz1ps.us-east-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=aurora --mysql-password=wujiade4444 --mysql-db=sysbench --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_write cleanup