I am curious.. Are you the only client on this database or or there other connections doing work in the background? A busy insert/update heavy application could cause these effects.
- michael dykman On Thu, Oct 6, 2011 at 12:35 PM, Joey L <mjh2...@gmail.com> wrote: > i did google search - myisam is faster...i am not really doing any > transaction stuff. > thanks > > On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore <eroomy...@gmail.com> wrote: > > 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 > >>> > >> > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com May the Source be with you.
