oky..you guys are much more advanced then me! I am glad i am asking for your help...here is show processlist of mysql below. One thing to point out - the locks are happening to the 9gig table like i thought. I would like to know what i can do - tuning wise to mysql to help this locking issue. It seems when i have the server up for an extended period of time like 3-6 hours - this locking starts to affect other sites/dbs. I am using a cms and it has a database backend...i have multiple sites running the same code but the site that has all the traffic is the one with the biggest table - 9 gig. Can anyone suggest tuning parameters for this locking issue ?? thanks ysql> show processlist ;
mysql> show processlist ; +------+----------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+----------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | 103 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 2507 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2508 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemid=484&lan | | 2509 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2521 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_jfbconnect&lang | | 2522 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_jfbconnect&lang | | 2523 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2529 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2535 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2536 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2537 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemid=484&lan | | 2538 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2556 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&airportid | | 2557 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2558 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Casc | | 2567 | p_092211 | localhost | p_092211 | Query | 11 | Sending data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2568 | p_092211 | localhost | p_092211 | Query | 69 | Sending data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2569 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemid=484&lan | | 2572 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2576 | p_092211 | localhost | p_092211 | Query | 7 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2577 | p_092211 | localhost | p_092211 | Query | 7 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2578 | p_092211 | localhost | p_092211 | Query | 7 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2579 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2580 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2581 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2582 | p_092211 | localhost | p_092211 | Query | 69 | Locked | update `w6h8a_sh404sef_urls` set cpt=(cpt+1) where `oldurl` = 'Camargo-Illinois-Holiday_Light_Tour-H | | 2583 | p_092211 | localhost | p_092211 | Query | 5 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND ( oldurl like ' | | 2584 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Milt | | 2585 | p_092211 | localhost | p_092211 | Query | 5 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND ( oldurl like ' | | 2586 | p_092211 | localhost | p_092211 | Query | 7 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2587 | p_092211 | localhost | p_092211 | Query | 7 | Locked | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where newurl <> "" AND soundex(oldurl) | | 2588 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2591 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2592 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2593 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2594 | p_092211 | localhost | p_092211 | Query | 4 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2595 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Milt | | 2596 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2598 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Boyn | | 2599 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_community&Itemi | | 2600 | p_092211 | localhost | p_092211 | Query | 6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_jfbconnect&lang | | 2601 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Oxfo | | 2602 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Milt | | 2603 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Shar | | 2604 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls WHERE `oldurl`='O52/index.php/' ORDER | | 2605 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLS/index.php/' ORDER | | 2606 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT * FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLD/index.php' | | 2607 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Milt | | 2608 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&airportid=5892&lan | | 2609 | p_092211 | localhost | p_092211 | Query | 6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Shar | | 2610 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Fish | | 2611 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&airportid=7705&lan | | 2612 | p_092211 | localhost | p_092211 | Query | 5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Fish | | 2613 | p_092211 | localhost | p_092211 | Query | 6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Milt | | 2614 | p_092211 | localhost | p_092211 | Query | 6 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limos&city=Armo | | 2615 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLF/index.php/' ORDER | | 2616 | p_092211 | localhost | p_092211 | Query | 7 | Locked | SELECT * FROM w6h8a_sh404sef_urls WHERE `oldurl`='MLF/index.php' | | 2617 | p_092211 | localhost | p_092211 | Query | 7 | Locked | INSERT INTO `w6h8a_sh404sef_urls` (`cpt`, `rank`, `oldurl`, `newurl`, `dateadd`) VALUES (1, 0, '2O1/ | +------+----------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ 58 rows in set (0.00 sec) On Thu, Oct 6, 2011 at 1:17 PM, Andrew Moore <eroomy...@gmail.com> wrote: > 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 >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org