Joey, does your 'large' table get On Thu, Oct 6, 2011 at 3:22 PM, Joey L <mjh2...@gmail.com> wrote:
> here is mysqlreport --- > ------------------------------------------------ > root@rider:~/tmp# ./mysqlreport --user root --password barakobomb > Use of uninitialized value $is in multiplication (*) at ./mysqlreport line > 829. > Use of uninitialized value in formline at ./mysqlreport line 1227. > MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49 2011 > > __ Key _________________________________________________________________ > Buffer used 727.43M of 2.00G %Used: 35.52 > Current 963.24M %Usage: 47.03 > Write hit 29.41% > Read hit 99.79% > > __ Questions ___________________________________________________________ > Total 50.20k 33.4/s > QC Hits 32.56k 21.6/s %Total: 64.87 > DMS 12.28k 8.2/s 24.46 > Com_ 3.21k 2.1/s 6.39 > COM_QUIT 2.89k 1.9/s 5.76 > -Unknown 745 0.5/s 1.48 > Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log: OFF > DMS 12.28k 8.2/s 24.46 > SELECT 11.09k 7.4/s 22.10 90.36 > UPDATE 539 0.4/s 1.07 4.39 > INSERT 384 0.3/s 0.77 3.13 > DELETE 260 0.2/s 0.52 2.12 > REPLACE 0 0/s 0.00 0.00 > Com_ 3.21k 2.1/s 6.39 > set_option 1.10k 0.7/s 2.20 > show_fields 1.03k 0.7/s 2.05 > admin_comma 707 0.5/s 1.41 > > __ SELECT and Sort _____________________________________________________ > Scan 1.65k 1.1/s %SELECT: 14.87 > Range 493 0.3/s 4.44 > Full join 310 0.2/s 2.79 > Range check 339 0.2/s 3.06 > Full rng join 0 0/s 0.00 > Sort scan 887 0.6/s > Sort range 628 0.4/s > Sort mrg pass 0 0/s > > __ Query Cache _________________________________________________________ > Memory usage 5.96M of 16.00M %Used: 37.25 > Block Fragmnt 5.17% > Hits 32.56k 21.6/s > Inserts 5.66k 3.8/s > Insrt:Prune 5.66k:1 3.8/s > Hit:Insert 5.76:1 > > __ Table Locks _________________________________________________________ > Waited 513 0.3/s %Total: 3.62 > Immediate 13.65k 9.1/s > > __ Tables ______________________________________________________________ > Open 1024 of 1024 %Cache: 100.00 > Opened 14.96k 9.9/s > > __ Connections _________________________________________________________ > Max used 70 of 100 %Max: 70.00 > Total 2.89k 1.9/s > > __ Created Temp ________________________________________________________ > Disk table 1.34k 0.9/s > Table 2.35k 1.6/s Size: 32.0M > File 5 0.0/s > > __ Threads _____________________________________________________________ > Running 32 of 37 > Cached 0 of 8 %Hit: 93.26 > Created 195 0.1/s > Slow 0 0/s > > __ Aborted _____________________________________________________________ > Clients 0 0/s > Connects 2 0.0/s > > __ Bytes _______________________________________________________________ > Sent 100.33M 66.7k/s > Received 12.48M 8.3k/s > > __ InnoDB Buffer Pool __________________________________________________ > Usage 1.67M of 8.00M %Used: 20.90 > Read hit 99.70% > Pages > Free 405 %Total: 79.10 > Data 107 20.90 %Drty: 0.00 > Misc 0 0.00 > Latched 0.00 > Reads 26.18k 17.4/s > From file 78 0.1/s 0.30 > Ahead Rnd 2 0.0/s > Ahead Sql 1 0.0/s > Writes 3 0.0/s > Flushes 3 0.0/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 96 0.1/s > Writes 12 0.0/s > fsync 11 0.0/s > Pending > Reads 0 > Writes 0 > fsync 0 > > Pages > Created 0 0/s > Read 107 0.1/s > Written 3 0.0/s > > Rows > Deleted 0 0/s > Inserted 0 0/s > Read 20.98k 13.9/s > Updated 0 0/s > root@rider:~/tmp# > > and the mysqltuner.pl report : > ------------------------------------------------------------- > > root@rider:~/tmp# perl mysqltuner.pl > > >> MySQLTuner 1.2.0 - Major Hayden <ma...@mhtx.net> > >> Bug reports, feature requests, and downloads at > http://mysqltuner.com/ > >> Run with '--help' for additional options and output filtering > Please enter your MySQL administrative login: root > Please enter your MySQL administrative password: > > -------- General Statistics > -------------------------------------------------- > [--] Skipped version check for MySQLTuner script > [OK] Currently running supported MySQL version 5.1.49-3-log > [OK] Operating on 64-bit architecture > > -------- Storage Engine Statistics > ------------------------------------------- > [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster > [--] Data in MyISAM tables: 7G (Tables: 2408) > [--] Data in InnoDB tables: 1M (Tables: 37) > [!!] Total fragmented tables: 49 > > -------- Security Recommendations > ------------------------------------------- > [OK] All database users have passwords assigned > > -------- Performance Metrics > ------------------------------------------------- > [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M) > [--] Reads / Writes: 90% / 10% > [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads) > [OK] Maximum possible memory usage: 2.3G (19% of installed RAM) > [OK] Slow queries: 0% (69/54K) > [OK] Highest usage of available connections: 70% (70/100) > [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G > [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads) > [OK] Query cache efficiency: 76.1% (36K cached / 47K selects) > [OK] Query cache prunes per day: 0 > [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) > [!!] Joins performed without indexes: 689 > [!!] Temporary tables created on disk: 36% (1K on disk / 4K total) > [OK] Thread cache hit rate: 93% (198 created / 2K connections) > [!!] Table cache hit rate: 6% (1K open / 14K opened) > [!!] Open file limit used: 89% (1K/2K) > [OK] Table locks acquired immediately: 96% (14K immediate / 14K locks) > [OK] InnoDB data size / buffer pool: 1.2M/8.0M > > -------- Recommendations > ----------------------------------------------------- > General recommendations: > Run OPTIMIZE TABLE to defragment tables for better performance > MySQL started within last 24 hours - recommendations may be inaccurate > Enable the slow query log to troubleshoot bad queries > Adjust your join queries to always utilize indexes > When making adjustments, make tmp_table_size/max_heap_table_size equal > Reduce your SELECT DISTINCT queries without LIMIT clauses > Increase table_cache gradually to avoid file descriptor limits > Variables to adjust: > join_buffer_size (> 8.0K, or always use indexes with joins) > tmp_table_size (> 32M) > max_heap_table_size (> 32M) > table_cache (> 1024) > open_files_limit (> 2158) > > root@rider:~/tmp# > > > > On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus <r...@grib.nl> wrote: > >> thanks for the response - but do not believe queries are the issue > >> because - Like I said - i have other websites doing the same exact > >> queries as I am doing on the site with the 9gig table. > > > > Contrary to popular believe, size DOES matter... And having a table large > > enough so it doesn't fit in memory could require another approach > entirely for > > query optimization. > > > > Another good start would be to examine the output of mysqlreport, it will > tel > > you a lot. > > -- > > Rik Wasmus > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2...@gmail.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com > >
