List:General Discussion« Previous MessageNext Message »
From:Joey L Date:October 6 2011 4:35pm
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
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
>>>
>>
>
>
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