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
>
>
