mysqlreport show very high +Unknown value


I’m having performance and memory usage issues with a live server and have been using mysqlreport to attempt to diagnose.

I think I have Key Buffer issues (buffer not big enough) and my Query Cache could probably be reduced without any problems, but I have very high RAM usage for the Unknown variable in Questions.

Is this normal? I need to reduce the amount of RAM in use and this seems like a major culprit.

Can anyone shed some light? I’d be grateful for any help!

MySQL 5.0.22-log uptime 45 21:22:38__ Key _______________________________________________________________Buffer used 56.63M of 64.00M %Used: 88.49 Current 64.00M %Usage: 100.00Write hit 94.02%Read hit 88.48% Questions _______________________________________________________Total 1.09G 275.3/s +Unknown 946.07M 238.6/s %Total: 86.67 QC Hits 142.63M 36.0/s 13.07 COM_QUIT 2.92M 0.7/s 0.27 Com 1 0.0/s 0.00 DMS 0 0/s 0.00Slow 1 s 0 0/s 0.00 %DMS: 0.00 Log: ONDMS 0 0/s 0.00 REPLACE 0 0/s 0.00 0.00 DELETE 0 0/s 0.00 0.00 INSERT 0 0/s 0.00 0.00 SELECT 0 0/s 0.00 0.00 UPDATE 0 0/s 0.00 0.00Com 1 0.0/s 0.00 show_status 1 0.0/s 0.00 SELECT and Sort ___________________________________________________Scan 1 0.0/s %SELECT: 100.00Range 0 0/s 0.00Full join 0 0/s 0.00Range check 0 0/s 0.00Full rng join 0 0/s 0.00Sort scan 0 0/sSort range 0 0/sSort mrg pass 0 0/s Query Cache _______________________________________________________Memory usage 107.20M of 384.00M %Used: 27.92Block Fragmnt 19.15%Hits 142.63M 36.0/sInserts 43.37M 10.9/sInsrt:Prune 43.37M:1 10.9/sHit:Insert 3.29:1 Table Locks _______________________________________________________Waited 321.06k 0.1/s %Total: 0.03Immediate 1.11G 280.5/s Tables ____________________________________________________________Open 1024 of 1024 %Cache: 100.00Opened 0 0/s Connections _______________________________________________________Max used 157 of 400 %Max: 39.25Total 2.92M 0.7/s Created Temp ______________________________________________________Disk table 0 0/sTable 1 0.0/s Size: 32.0MFile 734 0.0/s Threads ___________________________________________________________Running 2 of 2Cached 48 of 50 %Hit: 99.93Created 1.99k 0.0/sSlow 0 0/s Aborted ___________________________________________________________Clients 358 0.0/sConnects 11 0.0/s Bytes _____________________________________________________________Sent 71 0.0/sReceived 58 0.0/s InnoDB Buffer Pool ________________________________________________Usage 8.00M of 8.00M %Used: 100.00Read hit 71.63%Pages Free 0 %Total: 0.00 Data 511 99.80 %Drty: 0.00 Misc 1 0.20 Latched 0 0.00Reads 1.02G 256.3/s From file 288.35M 72.7/s 28.37 Ahead Rnd 25415216 6.4/s Ahead Sql 49543763 12.5/sWrites 1.62M 0.4/sFlushes 256.24k 0.1/sWait Free 0 0/s InnoDB Lock _______________________________________________________Waits 320 0.0/sCurrent 0Time acquiring Total 4744 ms Average 14 ms Max 206 ms InnoDB Data, Pages, Rows ____________________________________________Data Reads 392.26M 98.9/s Writes 1.12M 0.3/s fsync 1.08M 0.3/s Pending Reads 0 Writes 0 fsync 0Pages Created 1.61k 0.0/s Read 1.29G 326.0/s Written 256.24k 0.1/sRows Deleted 97.08k 0.0/s Inserted 98.35k 0.0/s Read 1.47G 371.0/s Updated 47.78k 0.0/s

That doesn’t represent memory usage. I’d recommend reading through Daniel’s docs for mysqlreport. This is advice given in a vacuum, but a) I would decrease the query cache size greatly or even disable it, and b) you should not have InnoDB’s buffer pool set at the default 8MB. This server is very badly configured.

Thanks for the feedback - very helpful!

I’ve made a few changes and it has improved performance (and reduced memory usage) so far, but I’m taking small steps each time and carefully monitoring the results so there is a little way to go until performance is 100% acceptable.