MySQLReport分析报告二
关于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.91Write 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.04Slow 5 s 3 0.0/s 0.01 %DMS: 0.01 Log: ONDMS 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.00Com_ 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.98Range 263 0.4/s 0.70Full join 0 0/s 0.00Range check 0 0/s 0.00Full rng join 0 0/s 0.00Sort scan 16 0.0/sSort range 2 0.0/sSort mrg pass 9 0.0/s__ Query Cache _________________________________________________________Memory usage 332.55k of 99.00M %Used: 0.33Block Fragmnt 9.97%Hits 2.20k 3.7/sInserts 7.77k 13.1/sInsrt:Prune 7.77k:1 13.1/sHit:Insert 0.28:1__ Table Locks _________________________________________________________Waited 0 0/s %Total: 0.00Immediate 12.94k 21.8/s__ Tables ______________________________________________________________Open 66 of 512 %Cache: 12.89Opened 73 0.1/s__ Connections _________________________________________________________Max used 11 of 1024 %Max: 1.07Total 31 0.1/s__ Created Temp ________________________________________________________Disk table 273 0.5/sTable 333 0.6/s Size: 103.0MFile 23 0.0/s__ Threads _____________________________________________________________Running 4 of 11Cached 0 of 8 %Hit: 64.52Created 11 0.0/sSlow 0 0/s__ Aborted _____________________________________________________________Clients 1 0.0/sConnects 0 0/s__ Bytes _______________________________________________________________Sent 4.66M 7.9k/sReceived 2.33M 3.9k/s__ InnoDB Buffer Pool __________________________________________________Usage 305.00M of 1.00G %Used: 29.79Read hit 98.81%Pages Free 46.02k %Total: 70.21 Data 19.41k 29.61 %Drty: 1.59 Misc 114 0.17 Latched 0.00Reads 1.54M 2.6k/s From file 18.42k 31.1/s 1.19 Ahead Rnd 0/s Ahead Sql 0/sWrites 26.52k 44.7/sFlushes 16.98k 28.6/sWait Free 0 0/s__ InnoDB Lock _________________________________________________________Waits 0 0/sCurrent 0Time 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 0Pages Created 84 0.1/s Read 19.32k 32.6/s Written 8.49k 14.3/sRows Deleted 0 0/s Inserted 4 0.0/s Read 3.16M 5.3k/s Updated 4.16k 7.0/s
