Shulou(Shulou.com)06/01 Report--

Copyright notice: this article is organized by the Tencent Cloud database product team. The original content of the page is from the db weekly English official website. If you reprint it, please indicate the source. The purpose of translation is to convey more information related to the latest global database domain, which does not mean that Tencent Cloud database product team agrees with its view or confirms the authenticity of its content. If it is used by other media, websites or any other form of legal entities or individuals, it must be legally authorized in writing by the copyright owner and bear full legal responsibility. You are not allowed to reprint in the name of Tencent Cloud database team without authorization, or misuse the name of Tencent Cloud database team to publish information.

Driven by the Oracle MySQL team, great changes and changes have taken place in MySQL 8.0.

The physical file has changed. For example, * .frm,* .TRG, * .TRN and * .par no longer exist. A large number of new features have been added, such as general table expressions (Common Table Expressions CTE), window functions (Window Functions), invisible indexes (Invisible Indexes), regular expressions (regexp)-MySQL8.0 now fully support Unicode and have multi-byte security features. The data dictionary has also changed. It is now merged with a transactional data dictionary that stores information about database objects. Unlike previous versions, dictionary data is stored in metadata files and non-transactional tables.

The security has been improved, and caching_sha2_password authentication has replaced the previous mysql_native_password authentication as the default authentication method. It provides greater flexibility and enhances security, that is, it requires that you must use a secure connection or an unencrypted link that supports password exchange through an RSA key pair.

With all these excellent features provided by MySQL 8.0, as well as enhancements and improvements, our team is interested in learning about the performance of the current version of MySQL 8.0. Especially considering that our ClusterControl design for MySQL 8.0.x is in progress (stay tuned). This blog post will not discuss the features of MySQL8.0, but intends to compare its performance with MySQL 5.7to see how it can be improved.

Server Setup and Environment server settings and environment

For this benchmark, I intend to use a system environment based on the minimum configuration of AWS EC2:

Instance type: t2.xlarge instance

Storage: gp2 (SSD storage, 100 IOPS minimum, 16000 IOPS maximum)

Virtual CPU:4

Memory: 16GiB

MySQL5.7 version: MySQLCommunity Server (GPL) 5.7.24

MySQL8.0 version: MySQLCommunity Server-GPL 8.0.14

In this benchmark, I also configured the values of some parameter items, which are:

Innodb_max_dirty_pages_pct= 90 # # this is the default value in MySQL 8.0.

Innodb_max_dirty_pages_pct_lwm= 10 # # this is the default value in MySQL 8.0

Innodb_flush_neighbors=0

Innodb_buffer_pool_instances=8

Innodb_buffer_pool_size=8GiB

The configuration of the remaining parameter items for the two versions (MySQL 5.7 and MySQL 8.0) is tuned with reference to the my.cnf template of ClusterControl.

In addition, I do not use MySQL8.0 's new authentication method, caching_sha2_password authentication, here. Instead, use mysql_native_password in both versions, plus configure innodb_dedicated_serve=OFF (the default), because innodb_dedicated_serve is a new feature of MySQL 8.0.

To simplify my work, I used ClusterControl to configure the MySQL 5.7 Community version node, then removed the node from the cluster, made it a separate host, and shut down the cluster control host, leaving the MySQL 5.7node dormant (without monitoring traffic). Technically, MySQL 5.7and MySQL8.0 are both dormant nodes with no active connectivity on the nodes, so it is basically a pure benchmark.

Commands and scripts used by Commands and Scripts Used

For this task, sysbench is used for both testing and load simulation environments. The commands and scripts used in the following tests:

Sb-prepare.sh #! / bin/bash host=$1#host192.168.10.110port=3306user='sysbench'password='MysqP@55w0rd'table_size=500000rate=20ps_mode='disable'sysbench/usr/share/sysbench/oltp_read_write.lua-- db-driver=mysql-- threads=1--max-requests=0-- time=3600-- mysql-host=$host-- mysql-user=$user--mysql-password=$password-- mysql-port=$port-- tables=10-- report-interval=1--skip-trx=on-- table-size=$table_size-- rate=$rate-- db-ps-mode=$ps_modeprepare

Sb-run.sh

#! / usr/bin/envbash3 host=$1port=3306user= "sysbench" password= "MysqP@55w0rd" table_size=100000tables=10rate=20ps_mode='disable'threads=1events=0time=5trx=100path=$PWD counter=1 echo "thread,cpu" > ${host}-cpu.csv for i in 16 32 64 128 256 512 1024 2048 Do threads=$i mysql-h $host- e "SHOW GLOBAL STATUS" > $host-global-status.logtmpfile=$path/$ {host}-tmp$ {threads} touch $tmpfile/bin/bashcpu-checker.sh $tmpfile $host $threads & / usr/share/sysbench/oltp_read_write.lua--db-driver=mysql-- events=$events-- threads=$threads-- mysql-user=$user-- mysql-password=$password--mysql-port=$port-- report-interval=1-- skip-trx=on-- tables=$tables--table-size=$table_size-- rate=$rate-- delete _ inserts=$trx-order_ranges=$trx--range_selects=on-range-size=$trx-simple_ranges=$trx-db-ps-mode=$ps_mode--mysql-ignore-errors=all run | tee-a $host-sysbench.log echo "${I} "`cat ${tmpfile} | sort-nr | head-1` > ${host}-cpu.csvunlink ${tmpfile} mysql-h $host- e" SHOW GLOBAL STATUS "> > $host-global-status.logdone python $path/innodb-ops-parser.py $host mysql-h $host- e" SHOW GLOBALVARIABLES "> > $host-global-vars.log

Therefore, the script simply prepares the sbtestschema and populates the tables and records. Then, it uses the

The / usr/share/sysbench/oltp_read_write.lua script performs a read / write load test. The script dumps the global state and MySQL variables, collects CPU utilization, and parses InnoDB line operations handled by the script innodb-ops-parser.py. The script generates * .csv files based on the dump logs collected during the benchmark, and here I use the Excel spreadsheet to generate charts from the * .csv files. Please check the code submitted in github.

Now, let's get back to the chart results!

InnoDB line operation

Basically here, I only extract the InnoDB line operation, which performs lookup (read), delete, insert, and update. MySQL 8.0 is significantly better than MySQL 5.7when the number of threads increases! No personalized changes have been made to the configuration items in either version, only the parameter items that I have configured uniformly. So almost all configurations in both versions use default values.

Interestingly, the MySQL team's statement about read and write performance in the new release indicates a significant improvement in performance, especially on high-load servers. Considering the difference between MySQL 5.7 and MySQL 8.0 in InnoDB row operations, there is a big difference, especially when the number of threads increases. MySQL 8.0 shows that it can run efficiently regardless of the workload.

Transaction processing

As shown in the figure above, the resulting trend of MySQL 8.0 shows a significant change in the time it takes to process transactions. The lower the value of the vertical axis, the better the performance, which means that the transaction is processed faster. The transaction statistics table processed (the second table) also shows that there is no difference in the number of transactions handled by the two versions. This means that the two versions handle almost the same number of transactions, but they complete at different speeds. Although MySQL 5.7 can handle a large number of transactions under lower loads, the actual load, especially in production, may be higher-- especially during the busiest periods.

The figure above still shows the number of transactions that can be handled by the two versions, just separating read from write. However, there are actually outliers in the figure, and I did not include these values because they are a small part of the exception that distorts the graph.

MySQL 8.0 represents a big improvement, especially for reads. It is shown in the efficiency of write operations, especially for servers with high workloads. In version 8.0, an important new support that affects MySQL read performance is the ability to create indexes in descending order (or forward index scan). Previous versions only had ascending or reverse index scans, and MySQL must perform filesort if descending order is needed (check the value of max_length_for_sort_data if filesort is needed). When the most efficient scanning order mixes the ascending order of some columns with the descending order of others, the descending index also enables the optimizer to use multi-column indexes. For more information, see here.

CPU resources

In this benchmark, I decided to test some hardware resources, especially CPU utilization.

Let me first explain how to get CPU usage in a benchmark. When benchmarking the database, the sysbench test results do not include statistics on the hardware resources used in the process. So what I did was create the identity by creating a file, connect to the target host through SSH, then collect the data with the Linux command "top" and parse it before the end of the test, and then collect it again. Then analyze the maximum CPU usage occupied by the mysqld process, and finally delete the identity file. You can check my code on github.

Let's discuss the chart results again, which seems to show that MySQL 8.0 consumes a lot of CPU, more than MySQL 5.7. However, MySQL 8.0 may have to consume additional CPU on the new variable configuration. For example, these variables may affect your MySQL 8.0:

Innodb_log_spin_cpu_abs_lwm = 80

Innodb_log_spin_cpu_pct_hwm = 50

Innodb_log_wait_for_flush_spin_hwm = 400

Innodb_parallel_read_threads = 4

In this benchmark, variables with default values retain their default values. Because MySQL 8.0 redesigned the way InnoDB writes to REDO logs (an improvement), the first three variables configure the CPU resources used to handle redo logs. For example, the variable innodb_log_spin_cpu_pct_hwm has CPU affinity, which means that if mysqld is bound to only four kernels, it will ignore other CPU kernels. For parallel read threads, a new variable has been added in MySQL 8.0, and you can adjust the number of threads to use.

However, I did not study this issue in depth. You can improve performance by taking advantage of the features provided by MySQL8.0.

Conclusion

There are many improvements in MySQL 8.0. Benchmark results show that MySQL 8.0 has made remarkable improvements not only in handling read loads, but also under high mixed read and write loads compared to MySQL 5.7s.

Looking at the new features of MySQL 8.0, it seems that it takes advantage of not only the latest software technologies (such as Memcached improvements, remote management for better DevOps performance, etc.), but also hardware. For example, replace latin1 with UTF8MB4 as the default character encoding. This means that it requires more disk space because UTF8 requires 2 bytes on non-US-ASCII characters. Although this benchmark does not take advantage of the new authentication method that uses caching_sha2_password, whether it uses encryption does not affect performance. Once authenticated, it is stored in the cache, which means that authentication occurs only once. Therefore, if you use only one user on the client, there will be no problem and it will be more secure than the previous version.

Because MySQL takes advantage of the latest hardware and software, it changes its default variables. You can read more details here.

Overall, the performance of MySQL 8.0 has far exceeded that of MySQL 5.7.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.