List:General Discussion« Previous MessageNext Message »
From:Joey L Date:October 6 2011 6:35pm
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
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 <eroomydna@stripped> 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 <wang@stripped>
> 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 <mjh2000@stripped> 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 <eroomydna@stripped>
>>> 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
> <eroomydna@stripped>
>>> > wrote:
>>> >>
>>> >> Joey, does your 'large' table get
>>> >>
>>> >> On Thu, Oct 6, 2011 at 3:22 PM, Joey L <mjh2000@stripped>
> 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
> <major@stripped>
>>> >>>  >>  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 <rik@stripped>
> 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=1
>>> >>> >
>>> >>> >
>>> >>>
>>> >>> --
>>> >>> MySQL General Mailing List
>>> >>> For list archives: http://lists.mysql.com/mysql
>>> >>> To unsubscribe:
>>> >>>  http://lists.mysql.com/mysql?unsub=1
>>> >>>
>>> >>
>>> >
>>> >
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>
>>
>> --
>> Pythian proud winner of Oracle North America Titan Award for Exadata
>> Solution... Read more & see us at OpenWorld bit.ly/pythianoow11
>>
>
>
Thread
4 minute slow on select count(*) from table - myisam typeJoey L2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore2 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeJoey L2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeBruce Ferrell2 Oct
Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
      • Re: 4 minute slow on select count(*) from table - myisam typeBruce Ferrell3 Oct
        • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
          • Re: 4 minute slow on select count(*) from table - myisam typeAndrĂ©s Tello3 Oct
            • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
              • Re: 4 minute slow on select count(*) from table - myisam typeEric Bergen3 Oct
                • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                    • Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
                      • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeJohan De Meersman6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeRik Wasmus6 Oct
                          • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                            • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
                              • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
                                • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                                  • Re: 4 minute slow on select count(*) from table - myisam typeMichael Dykman6 Oct
Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJohan De Meersman7 Oct
Re: 4 minute slow on select count(*) from table - myisam typeJan Steinman7 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L7 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore7 Oct