Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the method of collecting the running state data of MySQL database". In the daily operation, I believe that many people have doubts about the method of collecting the running state data of MySQL database. The editor has consulted all kinds of materials and sorted out the simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "the method of collecting MySQL database running state data". Next, please follow the editor to study!

Status data item

The MySQL database system provides commands for data acquisition: SHOW STATUS; or directly read the data of the virtual database informat_schema object GLOBAL_STATUS (Note: 5.1 and above). Our team needs to focus on the data collection for some classification.

1) number of SQL statements executed

(1)。 Queries

The number of all SQL statements executed by the MySQL server, including the number of SQL statements executed by stored procedures, but excluding the number of times the commands COM_PING and COM_STATISTICS were executed

(2)。 Questions

The number of all SQL statements sent by the client to the server for execution, but does not include the number of SQL statements executed by stored procedures and the number of times the commands COM_PING and COM_STATISTICS are executed

(3)。 Com_**** statistics

Com_insert-record INSERT INTO TABLE tablename VALUES (…) ... The number of times the statement was executed

Com_insert_select-record INSERT INTO TABLE tablename SELECT … The number of times the statement was executed

Com_delete-record DELETE [FROM] tablename... The number of times the statement was executed

Com_delete_multi-record DELETE [FROM] tablename1,tablename2... The number of times the statement was executed

Com_select-record SELECT … FROM tablename1... The number of times the statement was executed

Com_update-record UPDATE tablename1 SET … The number of times the statement was executed

Com_update_multi-record UPDATE tablename1,tabkename2 … SET... The number of times the statement was executed

2) query cache

Qcache_hits-the number of times the query statement hit the query cache

Qcache_inserts-number of query statements and recordsets added to the query cache

Qcache_lowmem_prunes-number of cache records that had to be deleted from the query cache due to insufficient query cache memory capacity

Qcache_not_cached-No number of SELECT query statements corresponding to cached SQL statements were found in the query cache

Qcache_queries_in_cache-how many query statements are being cached in the query cache

3) MyISAM engine

Key_blocks_used-Block usage in the index cache, which can be found by recording historical data

Key_blocks_unused-the amount of unused memory in the index cache, which can be inferred from the utilization of the indexed cache

Key_read_requests-get the corresponding data directly through the index cache, that is, the amount of IO that the database logically reads

Key_reads-data cannot be obtained through the index cache, data on the file system or disk must be read, and

That is, the amount of physical reading of the database.

Key_write_requests-the amount of IO written directly by modifying the logic of the index value in the index cache

Key_writes-cannot be done by directly modifying the index value in the index cache, you must modify the file system or magnetic directly

The amount of physical write IO of data on the disk

4) InnoDB engine

The number of IO generated by the random reading of the background reading thread of the Innodb_buffer_pool_read_ahead_rnd-InnoDB engine, which usually occurs when scanning a table data in a random manner

The number of IO generated by sequential reading of Innodb_buffer_pool_read_ahead_seq-InnoDB engine background reading threads, which usually occurs in sequential full table scans

The amount of logical read IO of Innodb_buffer_pool_read_requests-InnoDB engine data

The amount of physical read IO of Innodb_buffer_pool_reads-InnoDB engine data

Innodb_buffer_pool_wait_free-when there is no free memory block available in Innodb_buffer_pool and a memory block needs to be read or created, a page needs to be refreshed and re-allocated to the required thread

When a write operation is performed, a wait-and-count occurs. Because the data of the InnoDB engine table is read and written in the InnoDB memory cache.

The background thread is then responsible for writing the data to disk.

The amount of logical write IO of Innodb_buffer_pool_write_requests-InnoDB engine data written to InnoDB_buffer_pool_size

Number of rows deleted from Innodb_rows_deleted-InnoDB engine table data

Innodb_rows_inserted-number of record rows added to the InnoDB engine table

Number of rows that have been modified for Innodb_rows_updated-InnoDB engine table data

Innodb_rows_read-read the number of rows of data in the InnoDB engine table, in which UPDATE, DELETE, OPTIMIZE, etc., will read data from the data table object.

5) transaction related

Com_commit-engine that supports transactions, number of transaction commits made

Com_rollback-engine that supports transactions, number of transaction rollbacks performed

6) temporary tables or documents

The total number of temporary files created by the Created_tmp_files-MySQL system does not include the temporary tables that are displayed. In addition, if temporary files are converted to temporary tables, they may not necessarily correspond one to one.

It doesn't have to be 1/3. If the temporary table created by the hidden trouble of the system is in MyISAM format, there will be three temporary files; if specified

Temporary table storage engine is heap, then it is a temporary file

Created_tmp_disk_tables-MySQL system creates temporary tables based on the number of temporary tables on disk. Temporary default creation is based on memory, if it exceeds tmp_table_size or

The size of the max_heap_table_size is converted to a disk-based temporary table

Created by the Created_tmp_tables-MySQL system, whether disk-based or memory-based, this value increases

7) other

Bytes_sent-the number of bytes sent to the client by the MySQL server

Bytes_received-number of bytes sent by all clients to the MySQL server

The number of Slow_queries-SQL statements execution time greater than the long_query_ time value

The number of ranges sorted by Sort_range-SQL statements

The total number of rows that the Sort_rows-SQL statement caused the data to sort

Sort_scan-the number of times the SQL scanned the table to sort the data

Aborted_connects-the number of database connection threads that failed to close properly when the client crashed abnormally

Connections-the number of attempts by the client to establish a connection with the MySQL server, including the number of failures

Uptime-the length of time that the mysqld service process runs (in second)

Data acquisition mode

No matter what method is used to collect the state performance data, the SHOW GLOBAL STATUS; is executed and then the required data items are obtained by shell script.

Or

Double-click the code to select 1SELECT VARIABLE_NAME, VARIABLE_VALUE FROM information_schema. GLOBAL_STATUS WHERE VARIABLE_NAME IN (…)

We also need to classify the collected data. One kind of data item is only the state value of a certain point in time, and the other kind of data item is the cumulative value.

Data display and analysis

In view of the data collected automatically by the script program, make a screenshot display of part of the data, and import part of the parameter data into CVS format, and then draw the curve under the Windows environment, so as to intuitively compare and analyze. In addition, you can also develop a set of programs or draw trend charts with the help of other drawing tools.

For the collected data, we show the effect of graphical analysis of some parameters (three parameters: Com_update, Innodb_buffer_pool_reads, Innodb_buffer_pool_read_requests). We can generate data in cvs format through SQL statements, such as deriving SQL statements for the average number of times of execution per second of Com_update data:

Double click the code 12 3 4 5 6SELECT M.CreateDateM.totalroomnum INTO OUTFILE 'com_update.cvs'FROM performance_innodb M INNER JOIN (SELECT total_num,CreateDate FROM performance_innodb WHERE statu_item='Uptime' AND CreateDate > =' 2011-10-12 'AND CreateDate='2011-10-12' AND M.CreateDate)

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.