java - 如果启用了innoDb，如何在java中获取mysql磁盘写入

Question

我正在使用以下代码来检查 mysql 服务器中是否启用了 innoDb，但我想获取 mysql 的磁盘写入总数。请帮助执行以下程序

public class connectToInnoDb {

public static void main(String[] args) {
    // TODO Auto-generated method stub
    try{  
        Class.forName("com.mysql.cj.jdbc.Driver");  
        Connection con=DriverManager.getConnection(  
        "jdbc:mysql://localhost:3310/INFORMATION_SCHEMA","root","root");   
        Statement stmt=con.createStatement();  
        ResultSet rs=stmt.executeQuery("SELECT * FROM ENGINES");  
        while(rs.next())  {
            if(rs.getString(1) == "Innodb")
                System.out.println("Yes");

        }
        con.close(); 
        }catch(Exception e){ System.out.println(e);}  
}

Answer 1

通过SHOW ENGINE INNODB STATUS可以获取大量InnoDB信息，包括I/O计数:

mysql> SHOW ENGINE INNODB STATUS\G

...
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
431 OS file reads, 69 OS file writes, 53 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
...

我在上面看到有 69 个操作系统文件写入。上面的数字很小，因为我从笔记本电脑上运行的沙箱 MySQL 实例中获取了这些信息，而且它运行的时间不长。

正如上面 JimGarrison 所评论的，INNODB STATUS 报告的大部分信息也可以作为 INFORMATION_SCHEMA.INNODB_METRICS 表中的单独行提供给您。这在 Java 中使用比 SHOW ENGINE INNODB STATUS 并尝试解析文本要容易得多。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS
       WHERE NAME = 'os_data_writes'\G

           NAME: os_data_writes
      SUBSYSTEM: os
          COUNT: 69
      MAX_COUNT: 69
      MIN_COUNT: NULL
      AVG_COUNT: 0.0034979215248910067
    COUNT_RESET: 69
MAX_COUNT_RESET: 69
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2017-12-22 10:27:50
  TIME_DISABLED: NULL
   TIME_ELAPSED: 19726
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of writes initiated (innodb_data_writes)

阅读https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-metrics-table.html

我不会展示 Java 代码，您已经知道如何运行查询并获取结果。这些语句可以作为 SQL 语句运行，就像运行 SELECT 查询一样。