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