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