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