Sorry, hit send by accident there! *face palm* Just had a quick scan of the report. You've got 2 1GB disks in software raid - RAID1 or RAID5? I can also see you're creating a lot of temporary files on disk. I think in your previous email that your biggest table's index(s) were larger then the keybuffer size. I would suspect that you're disk bound with limited IO performance through 2 disks and effectively 1 if in a mirrored configuration. The stats show that you're configured for MyISAM and that you're tables are taking reads and writes (read heavy though), MyISAM doesn't like high concurrency mixed workloads such as yours, it will cause locking and maybe thats why your count has such a delay. Such activity may be better suited to InnoDB engine (you must configure and tune for this, not JUST change the engine).
HTH Andy On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore <eroomy...@gmail.com> wrote: > 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 >> >> >
