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