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