SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Mar 2007
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My forum is slow here's my MySQL stats

    What can I tune to get things improved?


    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.95-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 17G (Tables: 1421)
    [--] Data in InnoDB tables: 33M (Tables: 1907)
    [--] Data in MEMORY tables: 2M (Tables: 6)
    [!!] BDB is enabled but isn't being used
    [!!] Total fragmented tables: 113
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17d 5h 8m 41s (146M q [98.768 qps], 6M conn, TX: 4896B, RX: 58B)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 666.0M global + 16.4M per thread (750 max threads)
    [!!] Maximum possible memory usage: 12.6G (162% of installed RAM)
    [OK] Slow queries: 0% (4K/146M)
    [!!] Highest connection usage: 100%  (751/750)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/4.4G
    [OK] Key buffer hit rate: 100.0% (19B cached / 5M reads)
    [OK] Query cache efficiency: 72.5% (74M cached / 102M selects)
    [!!] Query cache prunes per day: 109463
    [OK] Sorts requiring temporary tables: 0% (31K temp sorts / 4M sorts)
    [!!] Joins performed without indexes: 97125
    [OK] Temporary tables created on disk: 4% (131K on disk / 3M total)
    [OK] Thread cache hit rate: 93% (439K created / 6M connections)
    [OK] Table cache hit rate: 43% (6K open / 14K opened)
    [OK] Open file limit used: 7% (5K/65K)
    [OK] Table locks acquired immediately: 97% (82M immediate / 84M locks)
    [!!] InnoDB data size / buffer pool: 33.1M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-bdb to MySQL configuration to disable BDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        max_connections (> 750)
        query_cache_size (> 128M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        innodb_buffer_pool_size (>= 33M)
    Thanks for your help!

  2. #2
    SitePoint Member
    Join Date
    Mar 2007
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My forum is slow here's my MySQL stats

    Mods can you delete/merge this post:
    http://www.sitepoint.com/forums/show...my-MySQL-stats

    I have not been here in years and got a bit mixed up and post in what looks to be the wrong forum first.

    What can I tune to get things improved?

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.95-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 17G (Tables: 1421)
    [--] Data in InnoDB tables: 33M (Tables: 1907)
    [--] Data in MEMORY tables: 2M (Tables: 6)
    [!!] BDB is enabled but isn't being used
    [!!] Total fragmented tables: 113
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 17d 5h 8m 41s (146M q [98.768 qps], 6M conn, TX: 4896B, RX: 58B)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 666.0M global + 16.4M per thread (750 max threads)
    [!!] Maximum possible memory usage: 12.6G (162% of installed RAM)
    [OK] Slow queries: 0% (4K/146M)
    [!!] Highest connection usage: 100%  (751/750)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/4.4G
    [OK] Key buffer hit rate: 100.0% (19B cached / 5M reads)
    [OK] Query cache efficiency: 72.5% (74M cached / 102M selects)
    [!!] Query cache prunes per day: 109463
    [OK] Sorts requiring temporary tables: 0% (31K temp sorts / 4M sorts)
    [!!] Joins performed without indexes: 97125
    [OK] Temporary tables created on disk: 4% (131K on disk / 3M total)
    [OK] Thread cache hit rate: 93% (439K created / 6M connections)
    [OK] Table cache hit rate: 43% (6K open / 14K opened)
    [OK] Open file limit used: 7% (5K/65K)
    [OK] Table locks acquired immediately: 97% (82M immediate / 84M locks)
    [!!] InnoDB data size / buffer pool: 33.1M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-bdb to MySQL configuration to disable BDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        max_connections (> 750)
        query_cache_size (> 128M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        innodb_buffer_pool_size (>= 33M)
    Thanks for your help!

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi

    Do you see the recommendations in the report? Namely:


    • Run OPTIMIZE TABLE to defragment tables for better performance
    • Reduce your overall MySQL memory footprint for system stability
    • Enable the slow query log to troubleshoot bad queries
    • Adjust your join queries to always utilize indexes


    Then


    • *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables
    • *** max_connections (> 750) query_cache_size (> 128M) join_buffer_size (> 128.0K, or always use indexes with joins) innodb_buffer_pool_size (>= 33M)


    The most obvious things to look at first is your using 162% of your 100% or RAM. Anytime you pass your maximum threshold of RAM then the server needs to SWAP. Swapping causes it to use the hard drive as a VERY SLOW type of RAM. This is further compounded if the drive is not defragmented. You are advised to bump up the RAM more than double what you have now; or optimize how your forum is utilizing RAM; however this is far more difficult then just adding more RAM.

    Then the next culprit could be that you have a large number of non-indexed JOINS. JOINS slow down queries and they really slow them down if you don't put indexes on columns that are being searched or lookup. You could add indexes to your JOINED database tables.

    You may want to enable the slow query log to see if any of the actual queries could be optimized.

    Personally I would go with the RAM first, especially if optimizing MySQL and Servers are not your forte, next I would index my joined tables.

    Hope this helps,
    Steve
    ictus==""

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's one culprit:

    Joins performed without indexes: 97125

    Look in your slow queries log. For any queries that show up repeatedly provide us the query as well as table(s) structure including indexes.
    We'd know more info in order to help you.

  5. #5
    Life is not a malfunction gold trophysilver trophybronze trophy
    TechnoBear's Avatar
    Join Date
    Jun 2011
    Location
    Argyll, Scotland
    Posts
    6,421
    Mentioned
    273 Post(s)
    Tagged
    5 Thread(s)
    Off Topic:

    Quote Originally Posted by purehonda View Post
    Mods can you delete/merge this post:
    http://www.sitepoint.com/forums/show...my-MySQL-stats

    I have not been here in years and got a bit mixed up and post in what looks to be the wrong forum first.
    No problem. If you do post in the wrong forum, the best solution is to click the red flag next to your post and ask a moderator to move it for you. Saves confusion.

  6. #6
    SitePoint Member
    Join Date
    Mar 2007
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's been tuned. Tuning included disabling persistent connections, greatly reducing connection timeout limits, etc. The site is faster and it now looks like this (24 hours has not passed yet):

    Code:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5h 3m 44s (1M q [76.885 qps], 62K conn, TX: 33B, RX: 676M)
    [--] Reads / Writes: 66% / 34%
    [--] Total buffers: 5.2G global + 9.2M per thread (400 max threads)
    [!!] Maximum possible memory usage: 8.8G (112% of installed RAM)
    [OK] Slow queries: 0% (30/1M)
    [OK] Highest usage of available connections: 14% (59/400)
    [OK] Key buffer size / total MyISAM indexes: 4.9G/4.4G
    [OK] Key buffer hit rate: 99.8% (154M cached / 289K reads)
    [OK] Query cache efficiency: 60.6% (580K cached / 958K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (1K temp sorts / 99K sorts)
    [!!] Joins performed without indexes: 1050
    [OK] Temporary tables created on disk: 3% (1K on disk / 39K total)
    [OK] Thread cache hit rate: 99% (59 created / 62K connections)
    [OK] Table cache hit rate: 97% (3K open / 3K opened)
    [OK] Open file limit used: 38% (3K/8K)
    [OK] Table locks acquired immediately: 98% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 33.1M/40.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 2.0M, or always use indexes with joins)
    Does RAM need to be added or can I just leave it like this? There's 4GB or swap available.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by purehonda View Post
    It's been tuned. Tuning included disabling persistent connections, greatly reducing connection timeout limits, etc.
    and declaring some indexes, right? because the "Joins performed without indexes" number went right down
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Mar 2007
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also the key buffer previously was set to:
    [OK] Key buffer size / total MyISAM indexes: 512.0M/4.4G

    If all mysql indexes are not within RAM it affects joins as well.

    Key buffer is now set to 5000M:
    [OK] Key buffer size / total MyISAM indexes: 4.9G/4.4G


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •