关于数据库:MySQL监控Innodb信息

<article class=“article fmt article-content”><h2>Innodb监控</h2><p>Innodb因为反对事务操作，是mysql中应用最多的存储引擎，所以如何监控Innodb存储引擎以进行性能优化是在应用mysql过程中遇到最多的，那么如何进行监控呢？</p><h3>show engine</h3><pre><code class=“sql”>– 显示innodb存储引擎状态的统计和配置信息show engine innodb status;展现的次要内容有—————–BACKGROUND THREAD –后盾线程—————–srv_master_thread loops: 19610306 srv_active, 0 srv_shutdown, 9705136 srv_idle –统计Innodb启动后的流动srv_master_thread log flush and writes: 29312902 –写入和刷新日志的次数———-SEMAPHORES –信号量蕴含了线程期待互斥锁或读写锁的信息———-OS WAIT ARRAY INFO: reservation count 52795642 –os期待数组信息，调配插槽的次数OS WAIT ARRAY INFO: signal count 57522728 –os期待数组信息，线程通过数组失去信号的次数RW-shared spins 0, rounds 77349143, OS waits 9180114 –共享锁期间，读写锁存器上自旋期待个数，自旋循环迭代次数以及操作系统调用的期待个数RW-excl spins 0, rounds 179767865, OS waits 2534243 –排他锁期间，读写锁存器上自旋期待个数，自旋循环迭代次数以及操作系统调用的期待个数RW-sx spins 2068750, rounds 40171680, OS waits 844522 –共享排他锁期间，读写锁存器上自选期待个数，自旋循环迭代次数以及操作系统调用的期待个数Spin rounds per wait: 77349143.00 RW-shared, 179767865.00 RW-excl, 19.42 RW-sx –对于每一个互斥锁，操作系统调用期待的每一个自旋循环迭代个数————TRANSACTIONS –蕴含所有以后正在执行的事务的信息————Trx id counter 1888483436 –以后事务idPurge done for trx s n:o < 1888483436 undo n:o < 0 state: running but idle –所有编号小于1888483436的事务都曾经从历史记录列表中革除了，革除旧的MVCC行时所用的事务id，这个值与以后事务ID进行比拟，就能够晓得有多少老版本的数据未被革除History list length 17 –历史列表的长度，位于Innodb数据文件的撤销空间里的页面的数目，如果事务执行了更新并提交，该数目就会减少，当清理过程移除旧版本数据时，该数目会缩小LIST OF TRANSACTIONS FOR EACH SESSION: 以后事务列表—TRANSACTION 422068961001072, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068960999248, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961005632, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961013840, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961012016, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961010192, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961001984, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961000160, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961017488, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961011104, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961012928, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961004720, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961002896, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961003808, not started0 lock struct(s), heap size 1136, 0 row lock(s)—TRANSACTION 422068961007456, not started0 lock struct(s), heap size 1136, 0 row lock(s)——–FILE I/O –各种IO操作的Innodb外部线程以及执行了多少次IO操作———- 有四个线程– insert buffer thread 负责插入缓冲合并– log thread 负责异步刷日志– read thread 执行预读操作以尝试事后读取Innodb预感须要的数据– write thread 刷脏缓冲I/O thread 0 state: waiting for completed aio requests (insert buffer thread) –IO线程的状态I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: waiting for completed aio requests (write thread)Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o’s:, sync i/o’s:Pending flushes (fsync) log: 0; buffer pool: 0 – 挂起操作的信息，aio是指异步io83934578288 OS file reads, 282688772 OS file writes, 190348192 OS fsyncs –innodb启动后的总统计信息984.40 reads/s, 16384 avg bytes/read, 10.15 writes/s, 9.12 fsyncs/s –最初一次显示后的总统计信息————————————-INSERT BUFFER AND ADAPTIVE HASH INDEX –插入缓冲区与自适应散列统计信息————————————-Ibuf: size 1, free list len 3078, seg size 3080, 8815726 merges –在页中插入缓冲索引树的以后大小，闲暇列表的长度，在蕴含插入缓冲树与头信息的文件段中已调配页的个数，被合并页的个数merged operations: insert 6898371, delete mark 38430046, delete 1226485 –通过类型辨别，索引页被执行合并操作的次数discarded operations: insert 1019, delete mark 0, delete 0 –毋庸合并抛弃操作的数量Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 74 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 2 buffer(s)Hash table size 34673, node heap has 28 buffer(s)Hash table size 34673, node heap has 7 buffer(s)5203.54 hash searches/s, 128.14 non-hash searches/s –胜利应用自适应散列索引查找的数量，当不能应用自适应索引时向下搜寻B树的次数—LOG –Innodb日志中流动信息—Log sequence number 319041331834 –以后日志序号Log flushed up to 319041331699 – 日志曾经刷到的地位Pages flushed up to 319033170877 Last checkpoint at 319033170877 – 上一个检查点，以后日志序列号LSN0 pending log flushes, 0 pending chkp writes 169033177 log i/o’s done, 8.92 log i/o’s/second –挂起的日志写入次数，挂起的检查点写入个数，innodb启动后的IO操作个数，从最近一次显示之后的每秒IO操作个数———————-BUFFER POOL AND MEMORY –Innodb缓冲池与内存应用状况———————-Total large memory allocated 137428992 –调配的内存Dictionary memory allocated 1204989 –被数据字典表与索引对象所占空间的字节数Buffer pool size 8191 –缓冲池个数Free buffers 1024 –闲暇缓冲区个数Database pages 7052 –以后缓冲区LRU队列的长度(调配用来存储数据库页的页数)Old database pages 2583 –旧的LRU队列的长度Modified db pages 530 –须要刷新的页面的数量(脏数据库页数)Pending reads 0 –挂起读操作的个数Pending writes: LRU 0, flush list 0, single page 0 –通过LRU算法，期待刷新的页数Pages made young 983912385, not young 304833753259 –因为最近第一次被拜访时，变成新页面的数目和没有变成新页面的数目1.54 youngs/s, 16246.04 non-youngs/s – 上述两个值每秒的速率Pages read 83934649301, created 4135172, written 103030852 –读操作的页面数目，在缓冲区中创立然而没有读取的页面数目，写操作的页面数目984.40 reads/s, 0.17 creates/s, 1.15 writes/s – 上述值美好的速率Buffer pool hit rate 972 / 1000, young-making rate 0 / 1000 not 478 / 1000 –读取到的页面数与取得的缓冲池页面的比例，变为新页面的页面数与取得缓冲池页面的比例，没有变为新页面的页面数与取得缓冲池页面的比例Pages read ahead 913.79/s, evicted without access 5.60/s, Random read ahead 0.00/s –预读的速率与不通过拜访剔除的预读页面的个数LRU len: 7052, unzip_LRU len: 0 –LRU列表的长度，unzip_LRU列表的长度I/O sum[4121]:cur[0], unzip sum[0]:cur[0] –IO操作的次数：以后距离的IO————–ROW OPERATIONS –行操作————–0 queries inside InnoDB, 0 queries in queue –以后有多少个正在执行的查问，在innodb_thread_concurrency队列中的查问个数0 read views open inside InnoDB –只读视图的数量Process ID=1543, Main thread ID=140593683990272, state: sleeping –线程id以及状态Number of rows inserted 56092883, updated 133093048, deleted 40729879, read 477150639699 –从innodb启动后，插入、更新、删除、读取的行数0.19 inserts/s, 7.73 updates/s, 0.00 deletes/s, 138100.85 reads/s – 速率</code></pre><p><!– more –></p><pre><code class=“sql”>– 展现Innodb的互斥体信息show engine innodb mutex;Type Name StatusInnoDB rwlock: dict0dict.cc:2782 waits=4InnoDB rwlock: dict0dict.cc:1228 waits=80InnoDB rwlock: log0log.cc:846 waits=75InnoDB sum rwlock: buf0buf.cc:1460 waits=11– name列显示了创立互斥体的源文件和行号– status列显示了互斥体在操作系统上的期待次数</code></pre><h3>show status</h3><h4>通过查看日志文件</h4><pre><code class=“sql”>show status like ‘innodb%log%‘Variable_name ValueInnodb_log_waits 0 日志文件太小时，操作必须期待日志刷新的等待时间计数器，该值如果长期大于0，能够适当减少日志文件大小Innodb_log_write_requests 4539 日志写入申请的数量Innodb_log_writes 22 数据被写入日志的次数Innodb_os_log_fsyncs 1020 操作系统文件同步的数量(fsync()办法调用)Innodb_os_log_pending_fsyncs 0 阻塞的文件同步申请的数量，如果该值开始增长并长期大于0，须要查看磁盘拜访问题Innodb_os_log_pending_writes 0 阻塞的日志写申请的次数，如果该值开始增长并长期大于0，须要查看磁盘拜访问题Innodb_os_log_written 2855424 写入日志中的字节总量Innodb_available_undo_logs 128</code></pre><h4>缓冲池信息</h4><p>缓冲池是Innodb缓存频繁拜访数据的中央，对缓冲池内数据的任何更新也会被缓存</p><pre><code class=“sql”>– 能够查看存储引擎的统计信息，其中蕴含有缓冲池的信息show engine innodb status;截取出缓冲池的信息来进行剖析———————-BUFFER POOL AND MEMORY———————-Total large memory allocated 137428992Dictionary memory allocated 223164Buffer pool size 8191Free buffers 7374 空的且可用于缓冲数据的缓冲段个数Database pages 809 Old database pages 299Modified db pages 0 发生变化的页数Pending reads 0 期待中的读申请个数Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 503, created 306, written 25340.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 809, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]</code></pre><p>在查看一下缓冲区相干的变量</p><pre><code class=“sql”>show status like ‘innodb%buf%‘Variable_name ValueInnodb_buffer_pool_pages_data 809 含有数据的页数，包含不变和扭转的页Innodb_buffer_pool_bytes_data 13254656 含有数据的字节数Innodb_buffer_pool_pages_dirty 0 扭转的字节数Innodb_buffer_pool_bytes_dirty 0 扭转的页的数目Innodb_buffer_pool_pages_flushed 2525 缓冲池页面被刷新的次数Innodb_buffer_pool_pages_free 7374 空页面的数目Innodb_buffer_pool_pages_misc 8 用于管理工作的页数，公式为’Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free-Innodb_buffer_pool_pages_data’Innodb_buffer_pool_pages_total 8191 缓冲池中的总页数Innodb_buffer_pool_read_ahead_rnd 0 扫描大块数据时产生随机读头的数量Innodb_buffer_pool_read_ahead 0 Innodb_buffer_pool_read_ahead_evicted 0Innodb_buffer_pool_read_requests 107632 逻辑读申请的次数Innodb_buffer_pool_reads 504 间接从磁盘中逻辑读取的次数(没有从缓冲池中读)Innodb_buffer_pool_wait_free 0 如果缓冲池忙碌且没有空页，innodb须要期待页面刷新，该值示意期待次数，若始终大于0，可适当减少缓冲池大小Innodb_buffer_pool_write_requests 47403 写入innodb缓冲池的次数</code></pre><h4>线程和连贯统计信息</h4><p>应用<code>show status like ‘变量’</code>来查问，这些变量用来跟踪尝试的连贯、退出的连贯、网络流量和线程统计</p><ul><li>Connections</li><li>Max_used_connections</li><li>Threads_connected</li><li>Aborted_clients</li><li>Aborted_connects 如果不为0，示意有人尝试连贯失败</li><li>Bytes_received</li><li>Bytes_sent</li><li>Slow_launch_threads</li><li>Threads_cached</li><li>Threads_created</li><li>Threads_running</li></ul><h4>二进制日志状态</h4><ul><li>Binlog_cache_use和Binlog_cache_disk_use示意在二进制日志缓存中有多少事务被存储过，以及多少事务因为超过二进制日志缓存而被存储到一个临时文件中</li><li>Binlog_stmt_cache_use和Binlog_stmt_cache_disk_use示意非事务语句对应的度量值</li></ul><h4>命令计数器</h4><p>Com_*变量统计了每种类型的SQL发动的次数</p><h4>临时文件和长期表</h4><p>通过Create_tmp%来查看隐式临时文件和长期表的统计</p><h4>select类型</h4><p>select_*变量统计select查问的计数器</p><ul><li>Select_full_join 穿插连贯或并没有条件匹配表中行的连贯的数目，如果存在，须要查看sql语句</li><li>Select_full_range_join 应用在表t1中的一个值来从表t2中通过参考索引的区间内获取行所做的连接数，比Select_scan开销大些</li><li>Select_range 扫描表的一个索引区间的连贯数目</li><li>Select_range_check 在表t2中从新评估表t1中的每一行的索引是否开销最小所做的连接数，意味着表t2中对该连贯而言并没有应用索引，这种查问应该防止，开销很大</li><li>Select_scan 扫描整张表的连贯数目</li></ul><h4>排序</h4><ul><li>Sort_merge_passes 依赖于sort_buffer_size服务器变量，sort_buffer_size来包容排序的行块，当实现排序后，会将这些排序后的行合并到后果集中，此时就会减少Sort_merge_passes值</li><li>Sort_scan和Sort_range 当mysql从文件排序后果中读取曾经排好序的行并返回给客户端会导致这两个变量的增长，如果是当Select_scan减少时Sort_scan减少；如果是Select_range减少时Sort_range减少</li></ul><h3>information_schema数据库中对于innodb的表</h3><p>information_schema数据库中有几个对于innodb的非凡表，能够用于监控压缩、事务和锁</p><ul><li>INNODB_CMP表 显示压缩表的详细信息和统计信息</li><li>INNODB_CMP_RESET表 与INNODB_CMP信息雷同，然而会在查问表时将重置统计信息，能够定期跟踪统计信息</li><li>INNODB_CMPMEM表 显示在缓冲池中应用压缩的详细信息和统计信息</li><li>INNODB_CMPMEM_RESET表 与INNODB_CMPMEM信息雷同，然而会在查问表时将重置统计信息，能够定期跟踪统计信息</li><li>INNODB_TRX表 显示所有事务的详细信息和统计信息，包含事务状态和以后正在运行的查问信息</li><li>INNODB_LOCKS表 显示事务申请的锁的详细信息和统计信息，形容每个锁的状态、模式、类型等信息</li><li>INNODB_LOCK_WAITS表 显示被阻塞的事务申请的锁的详细信息和统计信息，形容每个锁的状态、模式、类型和阻塞事务</li></ul><blockquote>https://zhhll.icu/2021/数据库/关系型数据库/MySQL/进阶/29.Innodb监控/</blockquote><p>本文由mdnice多平台公布</p></article>