I am curious.. Are you the only client on this database or or there other
connections doing work in the background? A busy insert/update heavy
application could cause these effects.
- michael dykman
On Thu, Oct 6, 2011 at 12:35 PM, 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
>
>
--
- michael dykman
- mdykman@stripped
May the Source be with you.
