Precisely my point Singer. There's a workload here that isn't friendly with table level locking and I would hazard a guess that there's some fights over IO due to load vs resources. The count is going to be queued as you describe.
A On Thu, Oct 6, 2011 at 6:09 PM, Singer X.J. Wang <w...@singerwang.com>wrote: > Okay, lets hold on for a minute here and go back. We're side tracking too > much. > > Lets state the facts here: > > 1) MyISAM stores the row count internally, a 'select count(*) from table' > DOES NOT DO A FULL TABLE SCAN > 2) hell, a software RAID6 of 2 MFM drives could do a seek to the metadata > faster then 4 minutes.. > > But lets remember that if another thread is writing or updating the MyISAM > table, the count(*) must wait.. > > So I recommend this: > > run a select count(*) from the table that you see is long.. if it is taking > a long time open another session, do a show processlist > > I bet you that you will see another process updating or deleting or > inserting into the MyISAM table. > > > On Thu, Oct 6, 2011 at 12:35, 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=w...@singerwang.com >> >> > -- > Pythian proud winner of Oracle North America Titan Award for Exadata > Solution... Read more & see us at OpenWorld bit.ly/pythianoow11 > >
