mysql压测和优化

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120

MySQL 5.6.35-log uptime 0 22:21:16 Thu Feb 1 20:42:24 2018 __ Key _________________________________________________________________ Buffer used 8.00k of 128.00M %Used: 0.01 Current 23.35M %Usage: 18.24 Write hit 0.00% Read hit 99.63% __ Questions ___________________________________________________________ Total 8.68M 107.8/s Com_ 5.14M 63.8/s %Total: 59.20 QC Hits 3.05M 37.9/s 35.16 DMS 1.54M 19.2/s 17.79 -Unknown 1.09M 13.5/s 12.52 COM_QUIT 32.28k 0.4/s 0.37 Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: DMS 1.54M 19.2/s 17.79 SELECT 1.18M 14.7/s 13.65 76.69 UPDATE 218.86k 2.7/s 2.52 14.17 INSERT 141.03k 1.8/s 1.63 9.13 DELETE 16 0.0/s 0.00 0.00 REPLACE 0 0/s 0.00 0.00 Com_ 5.14M 63.8/s 59.20 change_db 4.28M 53.2/s 49.31 begin 359.90k 4.5/s 4.15 commit 359.90k 4.5/s 4.15 __ SELECT and Sort _____________________________________________________ Scan 478.37k 5.9/s %SELECT: 40.40 Range 31.40k 0.4/s 2.65 Full join 1 0.0/s 0.00 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 182.86k 2.3/s Sort range 17.39k 0.2/s Sort mrg pass 0 0/s __ Query Cache _________________________________________________________ Memory usage 160.81M of 256.00M %Used: 62.82 Block Fragmnt 18.20% Hits 3.05M 37.9/s Inserts 1.00M 12.4/s Insrt:Prune 6.55:1 10.5/s Hit:Insert 3.05:1 __ Table Locks _________________________________________________________ Waited 0 0/s %Total: 0.00 Immediate 1.53M 19.0/s __ Tables ______________________________________________________________ Open 179 of 1024 %Cache: 17.48 Opened 186 0.0/s __ Connections _________________________________________________________ Max used 34 of 1024 %Max: 0.33 Total 32.44k 0.4/s __ Created Temp ________________________________________________________ Disk table 48 0.0/s Table 195.06k 2.4/s Size: 256.0M File 6 0.0/s __ Threads _____________________________________________________________ Running 2 of 24 Cached 10 of 120 %Hit: 99.90 Created 34 0.0/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 307 0.0/s Connects 27 0.0/s __ Bytes _______________________________________________________________ Sent 7.27G 90.4k/s Received 890.98M 11.1k/s __ InnoDB Buffer Pool __________________________________________________ Usage 895.88M of 1.00G %Used: 87.50 Read hit 99.99% Pages Free 8.19k %Total: 12.50 Data 53.25k 81.27 %Drty: 0.00 Misc 4084 6.23 Latched 0.00 Reads 1.84G 22.9k/s From file 126.66k 1.6/s 0.01 Ahead Rnd 0 0/s Ahead Sql 0/s Writes 2.79M 34.7/s Flushes 947.95k 11.8/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 157.26k 2.0/s Writes 1.41M 17.6/s fsync 751.64k 9.3/s Pending Reads 0 Writes 0 fsync 0 Pages Created 1.38k 0.0/s Read 157.18k 2.0/s Written 947.95k 11.8/s Rows Deleted 16 0.0/s Inserted 140.66k 1.7/s Read 13.54G 168.2k/s Updated 160.91k 2.0/s