This is the 29th day of my participation in the August Wenwen Challenge.More challenges in August

preface

This section describes online tuning methods and pressure testing tools, combined with some actual command parameters, we will introduce the specific meaning of the running results. This section is a general introduction of how to pressure test and how to read parameters. For specific operation effects, you need to deploy a machine to test. The contents of this part will have completely different effects under the influence of different machines.

​

An overview of the

  1. This section describes common performance analysis indicators of the mysql system, including throughput and machine selection
  2. The introduction of pressure testing tools, as well as the actual database pressure testing.
  3. Finally, we will introduce how to read the performance of mysql server according to Linux system commands
  4. Two systems Prometheus and Grafana are briefly introduced.

​

System index analysis

Small system:

Small concurrent systems do not need to consider other conditions, because the system could have every few minutes to a wave of requests to the database, and a table in the database may be hundreds, thousands or tens of thousands of data, the data volume is small, the concurrency value is very small, the operating frequency is low, users is small, the concurrent volume is small, it’s just possible system business logic is very complex, For this kind of system database machine selection, it is not within our consideration.

Usually choose:

Most cases generally 8-core 16 g machine to deploy a MySQL database, resistance to a few thousand concurrent requests per second is no problem, but if the concurrency value higher again, assume that there are thousands of concurrent requests per second, then may database will be a little dangerous, because the database CPU, disk, IO, memory load will be very high, The database will break down if it is under too much pressure.

Throughput:

If a system with a mysql requests need 1 s, then a minute could only handle 100 requests, 4 nuclear 8 g machine deployment ordinary Java application system, usually is resistant to roughly every second, hundreds of concurrent access, but the same configuration of the machine can be controlled requests per second per second to 7800 requests are possible, The key is how long it takes you to process each request.

Solid state drives

Because the biggest complexity of a database is the amount of disk I/O, it requires a lot of disk files to read and write, so if you can use SSDS, your database will be able to handle more concurrent requests per second.

Database stress testing

Once you have a database, the first thing you do is stress test:

What is QPS and what is TPS?

Pressure the database, how many requests per second, how many requests per second, if you want to determine the performance can be measured by the following indicators:

Qps: Qps stands for Query per second, which means how many requests a database can process per second. A request is an SQL statement. In mysql, it means how many SQL statements a second can process.

Tps is used to measure the number of transactions a database completes per second. Some people would interpret Tps as the number of requests a database makes per second.

The number of transactions that can be processed per second. This data is used internally by the database, which means how many transaction commits or rollbacks the database will perform per second.

I/O performance indicators

Concerned about IO related performance indicators, we should also do a understanding with him:

(1) IOPS: This refers to the random I/O concurrent processing capability of the machine. For example, the machine can perform 200 RANDOM I/O read/write requests per second.

(2) Throughput: This refers to how many bytes of data can be read and written per second on the machine’s disk storage

(3) Latency: This metric refers to the latency of writing a piece of data to disk.

Typically, sequential writes to a normal disk can reach 200mb requests, and generally, disk throughput can reach 200mb

In general, the lower the read/write latency of a disk, the higher the performance of the database and the faster the execution of SQL and transactions.Copy the code

Other performance indicators of the stress test

  1. CPU load: An important performance metric, let’s say the database is under 3000, but the CPU load is full, which means it can only handle this much data.

  2. ** Network load: ** pressure to a certain QPS or TPS, every second are machine card input how many MB data, how many MB data output, QPS1000, network load played full, transmission per second 100MB to reach the upper limit is also unable to pressure test.

  3. Memory load: Machine consumption to the limit is not stress tested.

Given a 4-core 8GB machine, can it handle thousands or even tens of thousands of concurrent requests per second?

How many requests to handle depends on the actual CPU, hard disk, memory, network bandwidth, etc. A machine carrying 500+ requests is already very high, if 1000+ requests per second, the base load will be full. The machine may fail. At the same time, memory is almost full, and the JVM’s GC frequency can be very high.

Introduction to pressure testing tools

The tool used for stress testing is the Sysbench tool

Installation tutorial:

Installation tutorial is as follows:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash 
sudo yum -y install sysbench
sysbench --version 
Copy the code

Database pressure test actual combat

Here is a case of the command, can be used when running the results of comparison to understand:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable prepare
Copy the code

Let’s introduce parameters separately:

--db-driver=mysql -- driver=mysql -- driver=mysql -- driver=mysql
-- Time =300: this means 300 seconds of continuous access -- Threads =10: this means 10 threads simulating concurrent access
--report-interval=1: report-interval=1: report-interval=1: report-interval=1: report-interval=1
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user; What's his username and password
--mysql-db=test_db --tables=20 --table_size=1000000 --mysql-db=test_db --tables=20 --table_size=1000000 The name of the test table will be something like sbTest1, sbtest2 olTP_read_write: that is, perform read and write tests on the OLTP database
--db-ps-mode=disable: disables ps mode
-- Prepare automatically creates 20 tables with 1 million data each based on the data required to build the test.
Copy the code

All-round test

Oltp_read_write mode is used to test the TPS of the database using the oltp_read_write mode.

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable run
Copy the code

Test database read-only performance, using oltp_read_only mode (oltp_read_only mode) :

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_only --db-ps-mode=disable run
Copy the code

Test database delete performance using olTP_delete mode:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0. 01. --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run
Copy the code

Using this command, the sysbench tool builds a variety of SQL statements to update or query the data in your 20 test tables and monitor the performance of your database. After the test, you can run the following cleanup command to cleanup the data:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup
Copy the code

Test the performance of the update index field of the database using olTP_update_INDEX mode:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_update_index --db-ps-mode=disable run
Copy the code

Test the performance of database update non-index fields using olTP_UPDATE_non_INDEX mode:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_update_non_index --db-ps-mode=disable run
Copy the code

Test the performance of database update non-index fields using olTP_UPDATE_non_INDEX mode:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_update_non_index --db-ps-mode=disable run
Copy the code

Test database insert performance using olTP_INSERT mode:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_insert --db-ps-mode=disable run
Copy the code

Test database write performance using olTP_write_only mode:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_write_only --db-ps-mode=disable run
Copy the code

When finally done, run the following cleanup command to cleanup the data:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup
Copy the code

Analysis of test results

Here is a sysbench run:

[22s] THDS: 10 TPS: 380.99 QPS: 7312.66 (R/W/O: 5132.99/1155.86/1321.35) LAT (MS, 95%): 21.33 Err /s: 0.00 Reconn /s: 0.00Copy the code

Analysis:

This is what happens in 22s and what the other fields mean:

Tdhs:10One thread Tps: executed per second380Four transaction Qps: executed per second7312A request (r/w/o)... : This paragraph indicates that7312.66Among the requests, there are5132.99One is the read request.1155.86Is to write the request,1321Other types of requests. That is, for the whole QPSJ you want to split the Lat(ms,95%): Means,95% delay in21.33Milliseconds below Err/s: second0A failure, happened0The secondary network is reconnected.Copy the code

Interpretation of manometry report

According to the pressure test command above, at the end of the result is the whole report of the pressure test. The content of the report is explained as follows:

SQL statistics: Queries performed: read: 1480084 298457 // This means more than 290,000 write requests were executed during the pressure test. Other: 325436 // This means more than 300,000 other requests were executed during the pressure test. Total: 2103977 // This indicates that more than 2.1 million transactions have been executed // this indicates that more than 350 transactions have been executed per second: Ignored errors: 2103977 (7013.26 per SEC.) // This means that more than 2.1 million requests were executed. In the process, 0 (0.00 per SEC.) reconnects: 0 (0.00 per SEC.) 300.0052s Total number of events: 105180 Latency (ms): min: 4.32 avG: 13.42 Max: 45.56 // The request with the highest latency is 45.56ms 95th Percentile: 21.33 // 95% of requests have latency less than 21.33msCopy the code

In fact, the pressure test is to use the command to observe the corresponding situation by adding threads to test the limits of the machine, and the other is to use the Linux command to see if it is normal.

Command to view the pressure test performance:

Top:

The top command is the most intuitive display of CPU load. Executing the top command on Linux, we can see the following:

Top 15:52:00 UP 42:35, 1 User, Load Average: 0.15, 0.05, 0.01Copy the code

Here is a presentation of the results above:

Here, the system runs for 42:35 minutes, 15:52 is the current time, and 1 user is used by a userLoad averatge 0.15.0.05.0.01The CPU is in1Minutes.5Minutes.15Minute load case, let's say one4Core CPU when loaded0.15, that is to say,4Not one core is full. If your load is1,4One of the cores is a little busyCopy the code

How to observe the memory load of the machine during measurement?

  • Mem: 33554432k total, 20971520k used, 12268339 free, 307200k buffers

  • The Top command ends with the following: Total memory 32GB, 20GB used, and 10 more free.

How to view disk I/O

Using the dstat -d command, you see the following:

-dsk/total read writ 103k 211k
0 11 
Copy the code

Within a reasonable load range of hardware, the QPS of the database is increased to the maximum, which is the most reasonable limit QPS value of the database pressure measurement.

Two systems, Prometheus and Grafana

These two systems please consult baidu to understand and learn:

Prometheus is a monitoring data acquisition and storage system that periodically collects monitoring data from mysql.

Granfana: for auxiliary use with Prometheus, a visual monitoring action for mysql.

Consider:

  1. Suppose the Java system developed is deployed on a 4-core 8G machine. Let’s assume that the Java system can process a request very quickly, and each request can be processed only 0.01MS. Do you think the Java system deployed on this machine can resist thousands of concurrent requests per second? Can it withstand tens of thousands of concurrent requests per second?

MySQL: 5 Database machine configuration _136.la

** ANSWER: ** each request is handled by 0.01ms and should be a pure memory operation that does not involve disks

On a 4-core, 8-GIGAByte machine, it is best to have 4 threads at the same time, but any more will result in frequent context switches due to race issues, wasting performance.

The theoretical number of concurrent requests

There are three threads per database and one CPU core thread for other processes.

Without considering network card resources, it is theoretically possible to achieve 3 * 1s/0.01ms = 300,000 requests per second.

Factors affecting production environment

In a real production environment, there are many factors to consider. There must be more than four threads working in a production environment, which results in CPU thread race switching times; When the concurrency is high, the STW time of YGC and Full GC is also counted. When the concurrency is high and the load on the CPU is high, the processing is slow and the TPS is long. There are also disk IO, network cards and other factors to consider.

At this point, the average time required for each request may reach about 100ms.

  1. QPS and TPS. If a trading system is split into many services, is the number of concurrent requests per second received by each service QPS or TPS?

This is obviously QPS, because each service is responsible for doing its own thing, and the number of requests per second is QPS for it.

conclusion

In fact, there are many parameters that affect the performance of a mysql service, including memory, processor, IO performance, and network bandwidth. Therefore, we cannot completely guess how much pressure the database can bear according to rational configuration. After the actual pressure test of the database, the pressure limit of the mysql server can be found by increasing the pressure. Finally, through two questions, we can see that the performance of a mysql server needs to be considered in many aspects, even under ideal circumstances, it can handle only a few requests.

Write in the last

The content of this article is basic and simple, and I hope this article will lead to more thinking about mysql performance.