这篇文章主要介绍“mysql5.6性能监控表innodb_metrics的用法”,在日常操作中,相信很多人在mysql5.6性能监控表innodb_metrics的用法问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”mysql5.6性能监控表innodb_metrics的用法”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

前言
oracle有dba_hist_active_session_history,mysql自5.6也提供性能监控指标表innodb_metrics,可以定制监控多种不同监控类别;如何使用得当,对于mysql
性能评估,比如锁的占用分析,事务的吞吐量,元数据的性能,操作系统读写的性能,服务器的性能,不一而足。

操作明细

1,innodb_metrics表mysql>descinformation_schema.innodb_metrics;+-----------------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------------+--------------+------+-----+---------+-------+|NAME|varchar(193)|NO|||||SUBSYSTEM|varchar(193)|NO|||||COUNT|bigint(21)|NO||0|||MAX_COUNT|bigint(21)|YES||NULL|||MIN_COUNT|bigint(21)|YES||NULL|||AVG_COUNT|double|YES||NULL|||COUNT_RESET|bigint(21)|NO||0|||MAX_COUNT_RESET|bigint(21)|YES||NULL|||MIN_COUNT_RESET|bigint(21)|YES||NULL|||AVG_COUNT_RESET|double|YES||NULL|||TIME_ENABLED|datetime|YES||NULL|||TIME_DISABLED|datetime|YES||NULL|||TIME_ELAPSED|bigint(21)|YES||NULL|||TIME_RESET|datetime|YES||NULL|||STATUS|varchar(193)|NO|||||TYPE|varchar(193)|NO|||||COMMENT|varchar(193)|NO||||+-----------------+--------------+------+-----+---------+-------+17rowsinset(0.04sec)2,表的数据,非常有价值,可用于监控分析mysql>select*frominformation_schema.innodb_metrics;+------------------------------------------+---------------------+-------------+-------------+-----------+-------------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+----------+----------------+-------------------------------------------------------------------------------------------------------------------+|NAME|SUBSYSTEM|COUNT|MAX_COUNT|MIN_COUNT|AVG_COUNT|COUNT_RESET|MAX_COUNT_RESET|MIN_COUNT_RESET|AVG_COUNT_RESET|TIME_ENABLED|TIME_DISABLED|TIME_ELAPSED|TIME_RESET|STATUS|TYPE|COMMENT|+------------------------------------------+---------------------+-------------+-------------+-----------+-------------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+----------+----------------+-------------------------------------------------------------------------------------------------------------------+|metadata_table_handles_opened|metadata|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftablehandlesopened||metadata_table_handles_closed|metadata|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftablehandlesclosed||metadata_table_reference_count|metadata|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Tablereferencecounter||metadata_mem_pool_size|metadata|8388608|8388608|8388608|NULL|8388608|8388608|8388608|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|SizeofamemorypoolInnoDBusestostoredatadictionaryandinternaldatastructuresinbytes||lock_deadlocks|lock|0|NULL|NULL|0|0|NULL|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|counter|Numberofdeadlocks||lock_timeouts|lock|7|7|NULL|0.00015228647261019014|7|7|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|counter|Numberoflocktimeouts||lock_rec_lock_waits|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimesenqueuedintorecordlockwaitqueue||lock_table_lock_waits|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimesenqueuedintotablelockwaitqueue||lock_rec_lock_requests|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofrecordlocksrequested||lock_rec_lock_created|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofrecordlockscreated||lock_rec_lock_removed|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofrecordlocksremovedfromthelockqueue||lock_rec_locks|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Currentnumberofrecordlocksontables||lock_table_lock_created|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftablelockscreated||lock_table_lock_removed|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftablelocksremovedfromthelockqueue||lock_table_locks|lock|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Currentnumberoftablelocksontables||lock_row_lock_current_waits|lock|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrowlockscurrentlybeingwaitedfor(innodb_row_lock_current_waits)||lock_row_lock_time|lock|356363|356363|NULL|7.75275203411217|356363|356363|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Timespentinacquiringrowlocks,inmilliseconds(innodb_row_lock_time)||lock_row_lock_time_max|lock|51062|51062|51062|NULL|51062|51062|51062|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Themaximumtimetoacquirearowlock,inmilliseconds(innodb_row_lock_time_max)||lock_row_lock_waits|lock|7|7|NULL|0.00015228647261019014|7|7|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoftimesarowlockhadtobewaitedfor(innodb_row_lock_waits)||lock_row_lock_time_avg|lock|50909|50909|50909|NULL|50909|50909|50909|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Theaveragetimetoacquirearowlock,inmilliseconds(innodb_row_lock_time_avg)||buffer_pool_size|server|134217728|134217728|134217728|NULL|134217728|134217728|134217728|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Serverbufferpoolsize(allbufferpools)inbytes||buffer_pool_reads|buffer|56917|56917|NULL|1.238241308793456|56917|56917|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofreadsdirectlyfromdisk(innodb_buffer_pool_reads)||buffer_pool_read_requests|buffer|688876467|688876467|NULL|14986.652460514293|688876467|688876467|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoflogicalreadrequests(innodb_buffer_pool_read_requests)||buffer_pool_write_requests|buffer|232689742|232689742|NULL|5062.214288822173|232689742|232689742|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofwriterequests(innodb_buffer_pool_write_requests)||buffer_pool_wait_free|buffer|148|148|NULL|0.003219771135186877|148|148|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoftimeswaitedforfreebuffer(innodb_buffer_pool_wait_free)||buffer_pool_read_ahead|buffer|281470|281470|NULL|6.123439063655746|281470|281470|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpagesreadasreadahead(innodb_buffer_pool_read_ahead)||buffer_pool_read_ahead_evicted|buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Read-aheadpagesevictedwithoutbeingaccessed(innodb_buffer_pool_read_ahead_evicted)||buffer_pool_pages_total|buffer|8191|8191|8191|NULL|8191|8191|8191|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Totalbufferpoolsizeinpages(innodb_buffer_pool_pages_total)||buffer_pool_pages_misc|buffer|491|491|491|NULL|491|491|491|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Bufferpagesformiscusesuchasrowlocksortheadaptivehashindex(innodb_buffer_pool_pages_misc)||buffer_pool_pages_data|buffer|6676|6676|6676|NULL|6676|6676|6676|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Bufferpagescontainingdata(innodb_buffer_pool_pages_data)||buffer_pool_bytes_data|buffer|109379584|109379584|109379584|NULL|109379584|109379584|109379584|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Bufferbytescontainingdata(innodb_buffer_pool_bytes_data)||buffer_pool_pages_dirty|buffer|0|0|0|NULL|0|0|0|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Bufferpagescurrentlydirty(innodb_buffer_pool_pages_dirty)||buffer_pool_bytes_dirty|buffer|0|0|0|NULL|0|0|0|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Bufferbytescurrentlydirty(innodb_buffer_pool_bytes_dirty)||buffer_pool_pages_free|buffer|1024|1024|1024|NULL|1024|1024|1024|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Bufferpagescurrentlyfree(innodb_buffer_pool_pages_free)||buffer_pages_created|buffer|258153|258153|NULL|5.616172823391202|258153|258153|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpagescreated(innodb_pages_created)||buffer_pages_written|buffer|567903|567903|NULL|12.354849236392116|567903|567903|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpageswritten(innodb_pages_written)||buffer_pages_read|buffer|338386|338386|NULL|7.361658617238828|338386|338386|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpagesread(innodb_pages_read)||buffer_data_reads|buffer|5544202240|5544202240|NULL|120615.2860810164|5544202240|5544202240|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Amountofdatareadinbytes(innodb_data_reads)||buffer_data_written|buffer|28202428416|28202428416|NULL|613549.7632162903|28202428416|28202428416|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Amountofdatawritteninbytes(innodb_data_written)||buffer_flush_batch_scanned|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|Totalpagesscannedaspartofflushbatch||buffer_flush_batch_num_scan|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Numberoftimesbufferflushlistflushiscalled||buffer_flush_batch_scanned_per_call|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Pagesscannedperflushbatchscan||buffer_flush_batch_rescan|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimesrescanofflushlistforced||buffer_flush_batch_total_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|Totalpagesflushedaspartofflushbatch||buffer_flush_batches|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Numberofflushbatches||buffer_flush_batch_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Pagesqueuedasaflushbatch||buffer_flush_neighbor_total_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|Totalneighborsflushedaspartofneighborflush||buffer_flush_neighbor|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Numberoftimesneighborsflushingisinvoked||buffer_flush_neighbor_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Pagesqueuedasaneighborbatch||buffer_flush_n_to_flush_requested|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofpagesrequestedforflushing.||buffer_flush_avg_page_rate|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Averagenumberofpagesatwhichflushingishappening||buffer_flush_lsn_avg_rate|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Averageredogenerationrate||buffer_flush_pct_for_dirty|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|PercentofIOcapacityusedtoavoidmaxdirtypagelimit||buffer_flush_pct_for_lsn|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|PercentofIOcapacityusedtoavoidreusableredospacelimit||buffer_flush_sync_waits|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimesawaithappensduetosyncflushing||buffer_flush_adaptive_total_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|Totalpagesflushedaspartofadaptiveflushing||buffer_flush_adaptive|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Numberofadaptivebatches||buffer_flush_adaptive_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Pagesqueuedasanadaptivebatch||buffer_flush_sync_total_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|Totalpagesflushedaspartofsyncbatches||buffer_flush_sync|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Numberofsyncbatches||buffer_flush_sync_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Pagesqueuedasasyncbatch||buffer_flush_background_total_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|Totalpagesflushedaspartofbackgroundbatches||buffer_flush_background|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Numberofbackgroundbatches||buffer_flush_background_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|Pagesqueuedasabackgroundbatch||buffer_LRU_batch_scanned|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|TotalpagesscannedaspartofLRUbatch||buffer_LRU_batch_num_scan|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|NumberoftimesLRUbatchiscalled||buffer_LRU_batch_scanned_per_call|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|PagesscannedperLRUbatchcall||buffer_LRU_batch_total_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|TotalpagesflushedaspartofLRUbatches||buffer_LRU_batches|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|NumberofLRUbatches||buffer_LRU_batch_pages|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|PagesqueuedasanLRUbatch||buffer_LRU_single_flush_scanned|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|TotalpagesscannedaspartofsinglepageLRUflush||buffer_LRU_single_flush_num_scan|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|NumberoftimessinglepageLRUflushiscalled||buffer_LRU_single_flush_scanned_per_call|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|PagescannedpersingleLRUflush||buffer_LRU_single_flush_failure_count|Buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberoftimesattempttoflushasinglepagefromLRUfailed||buffer_LRU_get_free_search|Buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofsearchesperformedforacleanpage||buffer_LRU_search_scanned|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|TotalpagesscannedaspartofLRUsearch||buffer_LRU_search_num_scan|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|NumberoftimesLRUsearchisperformed||buffer_LRU_search_scanned_per_call|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|PagescannedpersingleLRUsearch||buffer_LRU_unzip_search_scanned|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_owner|TotalpagesscannedaspartofLRUunzipsearch||buffer_LRU_unzip_search_num_scan|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|NumberoftimesLRUunzipsearchisperformed||buffer_LRU_unzip_search_scanned_per_call|buffer|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|set_member|PagescannedpersingleLRUunzipsearch||buffer_page_read_index_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofIndexLeafPagesread||buffer_page_read_index_non_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofIndexNon-leafPagesread||buffer_page_read_index_ibuf_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferIndexLeafPagesread||buffer_page_read_index_ibuf_non_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferIndexNon-LeafPagesread||buffer_page_read_undo_log|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofUndoLogPagesread||buffer_page_read_index_inode|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofIndexInodePagesread||buffer_page_read_ibuf_free_list|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferFreeListPagesread||buffer_page_read_ibuf_bitmap|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferBitmapPagesread||buffer_page_read_system_page|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofSystemPagesread||buffer_page_read_trx_system|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofTransactionSystemPagesread||buffer_page_read_fsp_hdr|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofFileSpaceHeaderPagesread||buffer_page_read_xdes|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofExtentDescriptorPagesread||buffer_page_read_blob|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofUncompressedBLOBPagesread||buffer_page_read_zblob|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofFirstCompressedBLOBPagesread||buffer_page_read_zblob2|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofSubsequentCompressedBLOBPagesread||buffer_page_read_other|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofother/unknown(oldversionofInnoDB)Pagesread||buffer_page_written_index_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofIndexLeafPageswritten||buffer_page_written_index_non_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofIndexNon-leafPageswritten||buffer_page_written_index_ibuf_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferIndexLeafPageswritten||buffer_page_written_index_ibuf_non_leaf|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferIndexNon-LeafPageswritten||buffer_page_written_undo_log|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofUndoLogPageswritten||buffer_page_written_index_inode|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofIndexInodePageswritten||buffer_page_written_ibuf_free_list|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferFreeListPageswritten||buffer_page_written_ibuf_bitmap|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofInsertBufferBitmapPageswritten||buffer_page_written_system_page|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofSystemPageswritten||buffer_page_written_trx_system|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofTransactionSystemPageswritten||buffer_page_written_fsp_hdr|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofFileSpaceHeaderPageswritten||buffer_page_written_xdes|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofExtentDescriptorPageswritten||buffer_page_written_blob|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofUncompressedBLOBPageswritten||buffer_page_written_zblob|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofFirstCompressedBLOBPageswritten||buffer_page_written_zblob2|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofSubsequentCompressedBLOBPageswritten||buffer_page_written_other|buffer_page_io|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofother/unknown(oldversionInnoDB)Pageswritten||os_data_reads|os|338399|338399|NULL|7.3619414349736765|338399|338399|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofreadsinitiated(innodb_data_reads)||os_data_writes|os|822181|822181|NULL|17.88672061958839|822181|822181|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofwritesinitiated(innodb_data_writes)||os_data_fsyncs|os|257327|257327|NULL|5.5982030196232|257327|257327|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoffsync()calls(innodb_data_fsyncs)||os_pending_reads|os|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofreadspending||os_pending_writes|os|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofwritespending||os_log_bytes_written|os|9592861184|9592861184|NULL|208694.71313579602|9592861184|9592861184|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Bytesoflogwritten(innodb_os_log_written)||os_log_fsyncs|os|224155|224155|NULL|4.876539181133881|224155|224155|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoffsynclogwrites(innodb_os_log_fsyncs)||os_log_pending_fsyncs|os|0|0|0|0|0|0|0|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpendingfsyncwrite(innodb_os_log_pending_fsyncs)||os_log_pending_writes|os|0|0|0|0|0|0|0|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpendinglogfilewrites(innodb_os_log_pending_writes)||trx_rw_commits|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofread-writetransactionscommitted||trx_ro_commits|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofread-onlytransactionscommitted||trx_nl_ro_commits|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofnon-lockingauto-commitread-onlytransactionscommitted||trx_commits_insert_update|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftransactionscommittedwithinsertsandupdates||trx_rollbacks|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftransactionsrolledback||trx_rollbacks_savepoint|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftransactionsrolledbacktosavepoint||trx_rollback_active|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofresurrectedactivetransactionsrolledback||trx_active_transactions|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofactivetransactions||trx_rseg_history_len|transaction|923|923|923|NULL|923|923|923|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|LengthoftheTRX_RSEG_HISTORYlist||trx_undo_slots_used|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofundoslotsused||trx_undo_slots_cached|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofundoslotscached||trx_rseg_current_size|transaction|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|Currentrollbacksegmentsizeinpages||purge_del_mark_records|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofdelete-markedrowspurged||purge_upd_exist_or_extern_records|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofpurgesonupdatesofexistingrecordsandupdatesondeletemarkedrecordwithexternallystoredfield||purge_invoked|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimespurgewasinvoked||purge_undo_log_pages|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofundologpageshandledbythepurge||purge_dml_delay_usec|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|MicrosecondsDMLtobedelayedduetopurgelagging||purge_stop_count|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|Numberoftimespurgewasstopped||purge_resume_count|purge|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|Numberoftimespurgewasresumed||log_checkpoints|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofcheckpoints||log_lsn_last_flush|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|LSNofLastflush||log_lsn_last_checkpoint|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|LSNatlastcheckpoint||log_lsn_current|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|CurrentLSNvalue||log_lsn_checkpoint_age|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|CurrentLSNvalueminusLSNatlastcheckpoint||log_lsn_buf_pool_oldest|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|TheoldestmodifiedblockLSNinthebufferpool||log_max_modified_age_async|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|MaximumLSNdifference;whenexceeded,startasynchronouspreflush||log_max_modified_age_sync|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|value|MaximumLSNdifference;whenexceeded,startsynchronouspreflush||log_pending_log_writes|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Pendinglogwrites||log_pending_checkpoint_writes|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Pendingcheckpoints||log_num_log_io|recovery|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberoflogI/Os||log_waits|recovery|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoflogwaitsduetosmalllogbuffer(innodb_log_waits)||log_write_requests|recovery|18571124|18571124|NULL|404.0187094809207|18571124|18571124|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoflogwriterequests(innodb_log_write_requests)||log_writes|recovery|225060|225060|NULL|4.896227646521342|225060|225060|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberoflogwrites(innodb_log_writes)||compress_pages_compressed|compression|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofpagescompressed||compress_pages_decompressed|compression|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofpagesdecompressed||compression_pad_increments|compression|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimespaddingisincrementedtoavoidcompressionfailures||compression_pad_decrements|compression|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimespaddingisdecrementedduetogoodcompressibility||index_page_splits|index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofindexpagesplits||index_page_merge_attempts|index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofindexpagemergeattempts||index_page_merge_successful|index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofsuccessfulindexpagemerges||index_page_reorg_attempts|index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofindexpagereorganizationattempts||index_page_reorg_successful|index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofsuccessfulindexpagereorganizations||index_page_discards|index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofindexpagesdiscarded||adaptive_hash_searches|adaptive_hash_index|48920480|48920480|NULL|1064.2753339424792|48920480|48920480|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|NumberofsuccessfulsearchesusingAdaptiveHashIndex||adaptive_hash_searches_btree|adaptive_hash_index|2550311|2550311|NULL|55.48255232128095|2550311|2550311|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|NumberofsearchesusingB-treeonanindexsearch||adaptive_hash_pages_added|adaptive_hash_index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofindexpagesonwhichtheAdaptiveHashIndexisbuilt||adaptive_hash_pages_removed|adaptive_hash_index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofindexpageswhosecorrespondingAdaptiveHashIndexentrieswereremoved||adaptive_hash_rows_added|adaptive_hash_index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofAdaptiveHashIndexrowsadded||adaptive_hash_rows_removed|adaptive_hash_index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofAdaptiveHashIndexrowsremoved||adaptive_hash_rows_deleted_no_hash_entry|adaptive_hash_index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofrowsdeletedthatdidnothavecorrespondingAdaptiveHashIndexentries||adaptive_hash_rows_updated|adaptive_hash_index|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofAdaptiveHashIndexrowsupdated||file_num_open_files|file_system|6|6|6|NULL|6|6|6|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|Numberoffilescurrentlyopen(innodb_num_open_files)||ibuf_merges_insert|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofinsertedrecordsmergedbychangebuffering||ibuf_merges_delete_mark|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofdeletedrecordsmergedbychangebuffering||ibuf_merges_delete|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpurgerecordsmergedbychangebuffering||ibuf_merges_discard_insert|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofinsertmergedoperationsdiscarded||ibuf_merges_discard_delete_mark|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofdeletedmergedoperationsdiscarded||ibuf_merges_discard_delete|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpurgemergedoperationsdiscarded||ibuf_merges|change_buffer|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofchangebuffermerges||ibuf_size|change_buffer|1|1|NULL|0.000021755210372884305|1|1|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Changebuffersizeinpages||innodb_master_thread_sleeps|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimes(seconds)masterthreadsleeps||innodb_activity_count|server|29366418|29366418|NULL|638.8726014880564|29366418|29366418|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Currentserveractivitycount||innodb_master_active_loops|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimesmasterthreadperformsitstaskswhenserverisactive||innodb_master_idle_loops|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftimesmasterthreadperformsitstaskswhenserverisidle||innodb_background_drop_table_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spenttoprocessdroptablelist||innodb_ibuf_merge_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spenttoprocesschangebuffermerge||innodb_log_flush_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spenttoflushlogrecords||innodb_mem_validate_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spenttodomemoryvalidation||innodb_master_purge_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spentbymasterthreadtopurgerecords||innodb_dict_lru_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spenttoprocessDICTLRUlist||innodb_checkpoint_usec|server|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Time(inmicroseconds)spentbymasterthreadtodocheckpoint||innodb_dblwr_writes|server|8627|8627|NULL|0.1876821998868729|8627|8627|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofdoublewriteoperationsthathavebeenperformed(innodb_dblwr_writes)||innodb_dblwr_pages_written|server|567903|567903|NULL|12.354849236392116|567903|567903|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofpagesthathavebeenwrittenfordoublewriteoperations(innodb_dblwr_pages_written)||innodb_page_size|server|16384|16384|16384|NULL|16384|16384|16384|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|value|InnoDBpagesizeinbytes(innodb_page_size)||innodb_rwlock_s_spin_waits|server|287836|287836|NULL|6.261932732889527|287836|287836|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrwlockspinwaitsduetosharedlatchrequest||innodb_rwlock_x_spin_waits|server|32494|32494|NULL|0.7069138058565027|32494|32494|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrwlockspinwaitsduetoexclusivelatchrequest||innodb_rwlock_s_spin_rounds|server|9534135|9534135|NULL|207.4171126484793|9534135|9534135|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrwlockspinlooproundsduetosharedlatchrequest||innodb_rwlock_x_spin_rounds|server|1799257|1799257|NULL|39.143214549884696|1799257|1799257|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrwlockspinlooproundsduetoexclusivelatchrequest||innodb_rwlock_s_os_waits|server|294666|294666|NULL|6.410520819736327|294666|294666|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|NumberofOSwaitsduetosharedlatchrequest||innodb_rwlock_x_os_waits|server|57256|57256|NULL|1.2456163251098638|57256|57256|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|NumberofOSwaitsduetoexclusivelatchrequest||dml_reads|dml|346466423|346466423|NULL|7537.4499195057215|346466423|346466423|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrowsread||dml_inserts|dml|26065444|26065444|NULL|567.059217682635|26065444|26065444|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrowsinserted||dml_deletes|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrowsdeleted||dml_updates|dml|75506799|75506799|NULL|1642.6662968280903|75506799|75506799|NULL|NULL|2019-11-0519:23:01|NULL|45966|NULL|enabled|status_counter|Numberofrowsupdated||ddl_background_drop_indexes|ddl|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofindexeswaitingtobedroppedafterfailedindexcreation||ddl_background_drop_tables|ddl|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberoftablesinbackgrounddroptablelist||ddl_online_create_index|ddl|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofindexesbeingcreatedonline||ddl_pending_alter_table|ddl|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|NumberofALTERTABLE,CREATEINDEX,DROPINDEXinprogress||icp_attempts|icp|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Numberofattemptsforindexpush-downconditionchecks||icp_no_match|icp|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Indexpush-downconditiondoesnotmatch||icp_out_of_range|icp|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Indexpush-downconditionoutofrange||icp_match|icp|0|NULL|NULL|NULL|0|NULL|NULL|NULL|NULL|NULL|NULL|NULL|disabled|counter|Indexpush-downconditionmatches|+------------------------------------------+---------------------+-------------+-------------+-----------+-------------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+----------+----------------+-------------------------------------------------------------------------------------------------------------------+214rowsinset(0.00sec)mysql>3,表的数据分为几个大类:bufferbuffer_page_ioserverlockrecoverytransactionosadaptive_hash_indexchange_bufferpurgeindexdmlddlcompressionmetadataicp不同的大类,又包括具体的监控指标,非常有价值mysql>selectsubsystem,count(*)frominformation_schema.innodb_metricsgroupbysubsystemorderby2desc;+---------------------+----------+|subsystem|count(*)|+---------------------+----------+|buffer|60||buffer_page_io|32||server|21||lock|16||recovery|14||transaction|12||os|9||adaptive_hash_index|8||change_buffer|8||purge|7||index|6||dml|4||ddl|4||compression|4||metadata|4||icp|4||file_system|1|+---------------------+----------+17rowsinset(0.01sec)4,压测前mysql>select*frominformation_schema.innodb_metricswheresubsystem='dml';+-------------+-----------+-----------+-----------+-----------+-------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|NAME|SUBSYSTEM|COUNT|MAX_COUNT|MIN_COUNT|AVG_COUNT|COUNT_RESET|MAX_COUNT_RESET|MIN_COUNT_RESET|AVG_COUNT_RESET|TIME_ENABLED|TIME_DISABLED|TIME_ELAPSED|TIME_RESET|STATUS|TYPE|COMMENT|+-------------+-----------+-----------+-----------+-----------+-------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|dml_reads|dml|346466423|346466423|NULL|7484.045945478896|346466423|346466423|NULL|NULL|2019-11-0519:23:01|NULL|46294|NULL|enabled|status_counter|Numberofrowsread||dml_inserts|dml|26065444|26065444|NULL|563.041517259256|26065444|26065444|NULL|NULL|2019-11-0519:23:01|NULL|46294|NULL|enabled|status_counter|Numberofrowsinserted||dml_deletes|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|46294|NULL|enabled|status_counter|Numberofrowsdeleted||dml_updates|dml|75506799|75506799|NULL|1631.027757376766|75506799|75506799|NULL|NULL|2019-11-0519:23:01|NULL|46294|NULL|enabled|status_counter|Numberofrowsupdated|+-------------+-----------+-----------+-----------+-----------+-------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+4rowsinset(0.00sec)mysql>select*frominformation_schema.innodb_metricswheresubsystem='dml';+-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|NAME|SUBSYSTEM|COUNT|MAX_COUNT|MIN_COUNT|AVG_COUNT|COUNT_RESET|MAX_COUNT_RESET|MIN_COUNT_RESET|AVG_COUNT_RESET|TIME_ENABLED|TIME_DISABLED|TIME_ELAPSED|TIME_RESET|STATUS|TYPE|COMMENT|+-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|dml_reads|dml|346466423|346466423|NULL|7482.429660504492|346466423|346466423|NULL|NULL|2019-11-0519:23:01|NULL|46304|NULL|enabled|status_counter|Numberofrowsread||dml_inserts|dml|26065444|26065444|NULL|562.9199205252246|26065444|26065444|NULL|NULL|2019-11-0519:23:01|NULL|46304|NULL|enabled|status_counter|Numberofrowsinserted||dml_deletes|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|46304|NULL|enabled|status_counter|Numberofrowsdeleted||dml_updates|dml|75506799|75506799|NULL|1630.6755139944714|75506799|75506799|NULL|NULL|2019-11-0519:23:01|NULL|46304|NULL|enabled|status_counter|Numberofrowsupdated|+-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-----------压测[root@standbygtid~]#mysqlslap-uroot--auto-generate-sql--concurrency=80--iterations=100-psystemWarning:Usingapasswordonthecommandlineinterfacecanbeinsecure.可清晰看到DML不同操作类型的变化mysql>select*frominformation_schema.innodb_metricswheresubsystem='dml';+-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|NAME|SUBSYSTEM|COUNT|MAX_COUNT|MIN_COUNT|AVG_COUNT|COUNT_RESET|MAX_COUNT_RESET|MIN_COUNT_RESET|AVG_COUNT_RESET|TIME_ENABLED|TIME_DISABLED|TIME_ELAPSED|TIME_RESET|STATUS|TYPE|COMMENT|+-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|dml_reads|dml|347750604|347750604|NULL|7503.3574418504295|347750604|347750604|NULL|NULL|2019-11-0519:23:01|NULL|46346|NULL|enabled|status_counter|Numberofrowsread||dml_inserts|dml|26070656|26070656|NULL|562.5222457169982|26070656|26070656|NULL|NULL|2019-11-0519:23:01|NULL|46346|NULL|enabled|status_counter|Numberofrowsinserted||dml_deletes|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0519:23:01|NULL|46346|NULL|enabled|status_counter|Numberofrowsdeleted||dml_updates|dml|75506799|75506799|NULL|1629.1977516937816|75506799|75506799|NULL|NULL|2019-11-0519:23:01|NULL|46346|NULL|enabled|status_counter|Numberofrowsupdated|+-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+4rowsinset(0.00sec)7,重启mysql8,可见重启后不会持久化保存这些数据mysql>select*frominformation_schema.innodb_metricswheresubsystem='dml';+-------------+-----------+-------+-----------+-----------+-----------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|NAME|SUBSYSTEM|COUNT|MAX_COUNT|MIN_COUNT|AVG_COUNT|COUNT_RESET|MAX_COUNT_RESET|MIN_COUNT_RESET|AVG_COUNT_RESET|TIME_ENABLED|TIME_DISABLED|TIME_ELAPSED|TIME_RESET|STATUS|TYPE|COMMENT|+-------------+-----------+-------+-----------+-----------+-----------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+|dml_reads|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0608:34:23|NULL|29|NULL|enabled|status_counter|Numberofrowsread||dml_inserts|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0608:34:23|NULL|29|NULL|enabled|status_counter|Numberofrowsinserted||dml_deletes|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0608:34:23|NULL|29|NULL|enabled|status_counter|Numberofrowsdeleted||dml_updates|dml|0|0|NULL|0|0|0|NULL|NULL|2019-11-0608:34:23|NULL|29|NULL|enabled|status_counter|Numberofrowsupdated|+-------------+-----------+-------+-----------+-----------+-----------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+4rowsinset(0.00sec)9,由下可知,innodb_metrics是内存表mysql>showcreatetableinformation_schema.innodb_metrics\G;***************************1.row***************************Table:INNODB_METRICSCreateTable:CREATETEMPORARYTABLE`INNODB_METRICS`(`NAME`varchar(193)NOTNULLDEFAULT'',`SUBSYSTEM`varchar(193)NOTNULLDEFAULT'',`COUNT`bigint(21)NOTNULLDEFAULT'0',`MAX_COUNT`bigint(21)DEFAULTNULL,`MIN_COUNT`bigint(21)DEFAULTNULL,`AVG_COUNT`doubleDEFAULTNULL,`COUNT_RESET`bigint(21)NOTNULLDEFAULT'0',`MAX_COUNT_RESET`bigint(21)DEFAULTNULL,`MIN_COUNT_RESET`bigint(21)DEFAULTNULL,`AVG_COUNT_RESET`doubleDEFAULTNULL,`TIME_ENABLED`datetimeDEFAULTNULL,`TIME_DISABLED`datetimeDEFAULTNULL,`TIME_ELAPSED`bigint(21)DEFAULTNULL,`TIME_RESET`datetimeDEFAULTNULL,`STATUS`varchar(193)NOTNULLDEFAULT'',`TYPE`varchar(193)NOTNULLDEFAULT'',`COMMENT`varchar(193)NOTNULLDEFAULT'')ENGINE=MEMORYDEFAULTCHARSET=utf81rowinset(0.00sec)ERROR:Noqueryspecified10,所以需要对此表的数据进行持久化保存,便于重启数据库前后的性能纵向对比

到此,关于“mysql5.6性能监控表innodb_metrics的用法”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!