MySQLReport分析报告二

最新推荐文章于 2024-10-30 16:20:32 发布

十年彩虹

最新推荐文章于 2024-10-30 16:20:32 发布

阅读量1.1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/k_scott/article/details/8645435

MySQL 专栏收录该内容

45 篇文章

订阅专栏

关于innodb_flush_log_at_trx_commit这个参数默认是1，分析报告一时，我已经修改为2。但是百万级的数据量I/O吞吐量还是大。

0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘; 1 为执行完没执行一条SQL马上commit; 2 代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上. 对速度影响比较大，同时也关系数据完整性

innodb_flush_log_at_trx_commit=0

虽然这个会在服务器宕机后，丢失数据。但是性能非常可观。

__ Key _________________________________________________________________
Buffer used     2.00k of 155.00M  %Used:   0.00
  Current      20.01M            %Usage:  12.91
Write hit       0.00%
Read hit       83.33%

__ Questions ___________________________________________________________
Total          45.30k    76.4/s
  DMS          42.67k    72.0/s  %Total:  94.19
  QC Hits       2.20k     3.7/s            4.85
  Com_            424     0.7/s            0.94
  COM_QUIT         28     0.0/s            0.06
  -Unknown         17     0.0/s            0.04
Slow 5 s            3     0.0/s            0.01  %DMS:   0.01  Log:  ON
DMS            42.67k    72.0/s           94.19
  SELECT       37.61k    63.4/s           83.02         88.14
  UPDATE        4.16k     7.0/s            9.18          9.75
  DELETE          896     1.5/s            1.98          2.10
  INSERT            4     0.0/s            0.01          0.01
  REPLACE           0       0/s            0.00          0.00
Com_              424     0.7/s            0.94
  show_fields     259     0.4/s            0.57
  set_option       68     0.1/s            0.15
  show_create      31     0.1/s            0.07

__ SELECT and Sort _____________________________________________________
Scan              367     0.6/s %SELECT:   0.98
Range             263     0.4/s            0.70
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan          16     0.0/s
Sort range          2     0.0/s
Sort mrg pass       9     0.0/s

__ Query Cache _________________________________________________________
Memory usage  332.55k of  99.00M  %Used:   0.33
Block Fragmnt   9.97%
Hits            2.20k     3.7/s
Inserts         7.77k    13.1/s
Insrt:Prune   7.77k:1    13.1/s
Hit:Insert     0.28:1

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate      12.94k    21.8/s

__ Tables ______________________________________________________________
Open               66 of  512    %Cache:  12.89
Opened             73     0.1/s

__ Connections _________________________________________________________
Max used           11 of 1024      %Max:   1.07
Total              31     0.1/s

__ Created Temp ________________________________________________________
Disk table        273     0.5/s
Table             333     0.6/s    Size: 103.0M
File               23     0.0/s

__ Threads _____________________________________________________________
Running             4 of   11
Cached              0 of    8      %Hit:  64.52
Created            11     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             1     0.0/s
Connects            0       0/s

__ Bytes _______________________________________________________________
Sent            4.66M    7.9k/s
Received        2.33M    3.9k/s

__ InnoDB Buffer Pool __________________________________________________
Usage         305.00M of   1.00G  %Used:  29.79
Read hit       98.81%
Pages
  Free         46.02k            %Total:  70.21
  Data         19.41k                     29.61 %Drty:   1.59
  Misc            114                      0.17
  Latched                                  0.00
Reads           1.54M    2.6k/s
  From file    18.42k    31.1/s            1.19
  Ahead Rnd                 0/s
  Ahead Sql                 0/s
Writes         26.52k    44.7/s
Flushes        16.98k    28.6/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads        19.33k    32.6/s
  Writes        9.25k    15.6/s
  fsync           805     1.4/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created          84     0.1/s
  Read         19.32k    32.6/s
  Written       8.49k    14.3/s

Rows
  Deleted           0       0/s
  Inserted          4     0.0/s
  Read          3.16M    5.3k/s
  Updated       4.16k     7.0/s