Three key performance indicators of mysql database-TPS\QPS\IOPS

Overview

Today we mainly introduce the MySQL database, or three key performance indicators of all databases:

• qps The number of queries processed per second
• tps The number of transactions processed per second
• IOPS The number of I/O operations performed by the disk per second

1. TPS (applicable to innodb)

1, concept

Transactions Per Second (transactions per second), that is, the number of transactions processed by the server per second.

TPS includes one message in and one message out, plus one user database access. (Business TPS = CAPS × Average TPS per call)

TPS is the measurement unit of software test results. A transaction is a process in which a client sends a request to the server and the server responds. The client starts timing when sending a request, and ends timing after receiving a response from the server to calculate the time used and the number of completed transactions.

Generally, the performance of the evaluation system is measured by the number of technical transactions completed per second. The overall processing capacity of the system depends on the TPS value of the module with the lowest processing capacity.

2, TPS calculation

2.1, method one

Com_commit = SHOW GLOBAL STATUS LIKE &x27;Com_commit&x27;;
Com_rollback = SHOW GLOBAL STATUS; LIKE&x27&x27; Uptime = SHOW GLOBAL STATUS LIKE &x27;Uptime&x27;;
TPS=(Com_commit + Com_rollback)/Uptime

2.2、Method two

use information_schema;
select VARIABLE VARIABLE from where_num = STAT_COMABLE_VALUE into @_num &x27;COM_COMMIT&x27;;
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME =&x27;COM_ROLLBACK&x27;;
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME =&x27;UPTIME&x27; uptime;

2. QPS (applicable to InnoDB and MyISAM engines at the same time)

1, concept

Query rate per second QPS is for a specific query server A measure of how much traffic is processed within a specified period of time. On the Internet, the performance of a machine serving as a domain name system server is often measured by the query rate per second.

corresponds to fetches/sec, which is the number of response requests per second, which is the maximum throughput capacity.

2, QPS calculation

2.1, method one

Questions = SHOW GLOBAL STATUS LIKE &x27;Questions&x27;;
Uptime = SHOW GLOBAL STATUS LIKE &x27;Uptime&x27;;
QPS=Questions/Uptime

3, IOPS

1, concept

IOPS (Input/Output Per Second) is the input and output per second (or the number of reads and writes), which is one of the main indicators to measure disk performance . IOPS refers to the number of I/O requests that the system can process per unit of time. Generally, the unit is the number of I/O requests processed per second. I/O requests are usually read or write data operation requests. For applications with frequent random reads and writes, such as OLTP (Online Transaction Processing), IOPS is a key measure. Another important indicator is data throughput (Throughput), which refers to the amount of data that can be successfully transmitted per unit time. For a large number of sequential read and write applications, such as VOD (Video On Demand), more attention is paid to throughput indicators.

Traditional disks are essentially a mechanical device, such as FC, SAS, and SATA disks. The speed is usually 5400/7200/10K/15K rpm. The key factor affecting the disk is the disk service time, that is, the time it takes for the disk to complete an I/O request. It consists of three parts: seek time, rotation delay and data transmission time.

Seek time Tseek refers to the time required to move the read/write head to the correct track. The shorter the seek time, the faster the I/O operation. At present, the average seek time of the disk is generally 3-15ms.

Rotation delay Trotation refers to the time required for the disk rotation to move the sector where the requested data is located under the read/write head. The rotation delay depends on the rotation speed of the disk, and it is usually expressed by 1/2 of the time required for the disk to rotate once. For example, the average rotation delay of a 7200 rpm disk is about 60*1000/7200/2 = 4.17ms, while the average rotation delay of a disk with a rotation speed of 15000 rpm is about 2ms.

Data transfer time Ttransfer refers to the time required to complete the data transfer requested. It depends on the data transfer rate, and its value is equal to the data size divided by the data transfer rate. At present, IDE/ATA can reach 133MB/s, and SATA II can reach the interface data transfer rate of 300MB/s. The data transfer time is usually much shorter than the previous two parts.

IOPS can be subdivided into the following indicators:

Toatal IOPS, disk IOPS under mixed read-write and sequential random I/O load, this is the most consistent with the actual I/O situation, most applications Pay attention to this indicator.

• Random Read IOPS, IOPS under 100% random read load.
• Random Write IOPS, IOP under 100% random write loadS.
• Sequential Read IOPS, IOPS under 100% sequential load read.
• Sequential Write IOPS, IOPS under 100% sequential write load.

IOPS test benchmark tools mainly include Iometer, IoZone, FIO, etc., which can be used to test the IOPS of the disk in different situations. For the application system, it is necessary to first determine the load characteristics of the data, and then select a reasonable IOPS index for measurement and comparative analysis, and select the appropriate storage medium and software system accordingly.

2, IOPS calculation

theoretically can calculate the maximum IOPS of the disk, that is, IOPS = 1000 ms/ (Tseek + Troatation), ignoring the data transmission time. Assuming that the average physical seek time of the disk is 3ms and the disk rotation speed is 7200, 10K, 15K rpm, the theoretical maximum disk IOPS are respectively,

IOPS = 1000 / (3 + 60000/7200/2) = 140

IOPS = 1000 / (3 + 60000/10000/2) = 167

IOPS = 1000 / (3 + 60000/15000/2) = 200

3, case

requirements: 20TB storage space meets 4500 IOPS+RAID 5 at the same time, how should I calculate? How many hard drives are required for RAID 5 or RAID 1/0?

First of all, you need to know the percentage of the read operation (Read) and the write operation (Write) in the I/O. Then use the following formula to convert the IOPS demand of the host into the actual IOPS load of the hard disk:

Assuming that the read/write ratio in 4500 IOPS is 2:1, different RAID types Drive IOPS The requirements are as follows:

RAID 1/0: (2/3)*4500 + 2*(1/3)*4500 = 6000 IOPS
RAID 5: (2/3)*4500 + 4*(1/ 3)*4500 = 9000 IOPS
RAID 6: (2/3)*4500 + 6*(1/3)*4500 = 12000 IOPS

Then refer to the IOPS parameters of different types of hard disks in the table below to obtain How many hard drives are needed:

Assuming FC 15K RPM hard drives are selected, then:

RAID 1/0: 6000/180 = 34 blocks 
RAID 5: 9000 /180 = 50 blocks 
RAID 6: 12000/180 = 67 blocks 

Note: Vault Drivers (5 in total) and Hot Spares (recommended for every 30 hard drives) One).

Finally, if you choose a 600GB FC hard drive to achieve 20TB of usable space, then RAID 1/0 requires 78 blocks and RAID 5 requires 42 blocks.

Friends who find it useful, please help forward! I will share more devops and DBA content later, interested friends can follow~