In a project, the database will generally become the main performance and load bottleneck,Then the monitoring and corresponding optimization of various performance indicators of the database are the main tasks that developers and operation and maintenance personnel need to face,And this part of the work will run through the entire cycle of the project from development to operation.

In this article we are rightMySqlThe calculation and statistics of several main performance indicators in the database are explained and summarized below.

在MySql中通过show global statusA lot of performance indicators can be obtained,From this, we can extract the index items that need attention：

1、CPU、内存使用率、磁盘占用空间

CPU、内存、These three items of disk are the most intuitive indicators we can see,也是MySqlAn overall representation of the operating state,Once our database server these three items fluctuate abnormally,It will inevitably affect the stability and performance of the project,Thus we need to project andMySqlCarry out reasonable configuration and optimization.

2 、创建的连接数、The number of connections to connect、Number of connection caches、Number of active connections

Threads_created：

通过show global status like "Threads_created"You can view the number of connections that have been created,这个顾名思义就是MySqlThe total number of connections that have been created;

Threads_connectd：

通过show global status like "Threads_connectd"You can view the number of connected connections,这个值等于show processlist;

Threads_catched：

通过show global status like "Threads_catched"可以查看当前MySqlThe current number of cached connections,我们知道MySqlEstablishing a connection is very resource intensive,After an existing connection is no longer in use,MySqlNo direct disconnection,Instead, connections are cached in a buffer pool,so that there will be next timecreate thread的需求时,So as to achieve a multiplexed connection,The goal of improving performance,所以Threads_catchedThe value in is the number of connections available in the current buffer pool,由此我们可以得出 Threads_created的值应该是Threads_connectd与Threads_catched之和, Of course, if you already have middleware or other connection pool management,Then this parameter is not so important,But if there is no other connection pool management,Then it is necessary to optimize this parameter.

Threads_running：

通过show global status like "Threads_running"You can view the number of currently active connections,This value represents the number of connection threads that are currently active,This value must be less thanThreads_connectd的.

3、IOPS、QPS、TPS

IOPS：(Input/Output operations Per Second,既每秒处理I/O的请求次数)

We know that the processing of databases is essentiallyIO的读写处理,你可以简单的把IOPSunderstood as a storage medium（一般是磁盘）can be processed in unit timeIO读写请求数量,因为所有对MySqlThe access and operation of the database will eventually fall on the read and write to the disk,那么IOPSThe higher the value is, the more database processing requests can be accepted within a certain period of time,IOPS的计算公式：IOPS=1000ms/(寻道时间+旋转延迟时间),It can be seen that the calculation of this indicator is closely related to the performance of your storage medium,So replace the normal mechanical hard drive with SSDSSDs are improvedMySqlA more effective means of processing power.

QPS(Query Per Second,That is, the number of queries requested per second)

QPS是MySqlA very important indicator of the database,它代表MySqlThe number of request queries received by the database per second,我们可以通过show global status like “Questions” to view the current databaseQuestionsThat is, the number of query requests,But it should be noted that this value is a global cumulative value,也就是自MySqlThe number of all queries since the service started.If we are counting database servicesQPSan average of performance,The following calculation method can be directly used：

questions = show global status like “questions”; – MySqlThe total number of query requests since launch

uptimes = show global status like “uptime”; – MySql本次启动后的运行时间(单位：秒)

QPS = questions/uptimes
If you want to calculate the system peak timeQPS指标,This requires us to calculate manually,We can pass unit time apartshow global status like "Questions"command to query this value,And subtract two adjacent values ​​and divide by the time apart,得到的就是精确的每一秒的实际请求数了.

TPS(Transcantion Per Second,既每秒事务数)

Since it is transaction data per second,在InnoDBThis indicator data will only be available under the engine,TPSInvolves committing and rolling back transactions,所以计算TPS时需要把show global status like “Com_commit” 与 show global status like “Com_rollback”,The calculation method also requires us to sample every time interval,然后把Com_commit与Com_rollbackThe value is added and divided by the unit timeTPS值了,公式如下：

commit = show global status like “Com_commit”;

rollback = show global status like “Com_rollback”;

TPS= (commit+rollback)/seconds(单位时间,单位：秒)

4、InnoDBCache read hit rate、使用率、脏块率（%）

在MySql的使用中,由于InnoDBThe advantages and characteristics of the engine,We generally choose to do itMySql的数据引擎,InnoDBPossessing caching features can be greatly improvedMySql的查询性能,因为Innodb 不仅仅缓存索引,At the same time, the actual cache is also cached international data,By configuring we canInnodb More memory is used in the storage engine to cache data,Then the hit rate of its cache in this process、Usage rate and dirty block rate must be important performance indicators.

关于InnodbThere are many parameter indicators：

//innodbFile read and write times

innodb_data_reads：innodbAverage number of reads from the file per second

innodb_data_writes：innodbAverage number of writes per second from the file

innodb_data_fsyncs：innodbOn average every secondfsync()操作的次数

//innodb读写量

innodb_data_read：innodbAverage amount of data read per second,单位为KB

innodb_data_written：innodbAverage amount of data written per second,单位为KB

//innodb缓冲池状态

innodb_buffer_pool_reads: 平均每秒从物理磁盘读取页的次数 

innodb_buffer_pool_read_requests: 平均每秒从innodb缓冲池的读次数（The number of logical read requests）

innodb_buffer_pool_write_requests: 平均每秒向innodb缓冲池的写次数

innodb_buffer_pool_pages_dirty: 平均每秒innodb缓存池中脏页的数目

innodb_buffer_pool_pages_flushed: 平均每秒innodb缓存池中刷新页请求的数目
复制代码
而InnoDBCache read hit rate、使用率、脏块率（%）It is mainly calculated based on the above parameters

innodb缓冲池的读命中率 = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

Innodb缓冲池的利用率 =  ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100

Innodb缓冲池的脏块的百分率 = (innodb_buffer_pool_pages_dirty / innodb_buffer_pool_pages_total) * 100

5. 网络流量（Average input and output per second）

MySqlData monitoring of network traffic can be done by calculating unit timeBytes_received与Bytes_sent获取.

Bytes_received/s：Average number of bytes received from all clients per second,单位KB

Bytes_sent/s：The average number of bytes sent to all clients per second,单位KB

6、MySql中InnoDBEngine queries per second、插入次数、删除次数、更新次数的统计

MySqlDatabase global query、插入、删除、The number of updates can be passedshow global status like "%innodb_rows%"命令来查看,The above indicators can be obtained by calculating the unit timeInnoDBEngine queries per second、插入次数、删除次数、更新次数

InnoDBEngine queries per second = Innodb_rows_read/s

InnoDBEngine inserts per second = Innodb_rows_inserted/s

InnoDBEngine deletes per second = Innodb_rows_deleted/s

InnoDBEngine deletes per second = Innodb_rows_updated/s

以上就是MySqlCalculation and statistical methods of some main performance indicators in the database,Monitor and count these performance indicators,一方面可以对MySqlThe running status of the database is monitored,On the other hand, it is also possible to find and locate the problem through the abnormality of a certain performance index,Hope to everyone in the projectMySqlThe use of a database helps,其中如有不足与不正确的地方还望指出与海涵.