The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

join_buffer_size (> 1.0M, or always use indexes with joins) - MYSQL tuning

Discussion in 'Workarounds and Optimization' started by syndicated, Aug 8, 2012.

  1. syndicated

    syndicated Member

    Joined:
    Jun 1, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    I have been tuning MYSQL for a few days now, it's using some huge cache's

    key_buffer_size = 128M
    sort_buffer_size = 1M
    read_buffer_size = 1M
    join_buffer_size = 1M
    thread_cache_size = 50
    table_open_cache = 1500
    query_cache_size= 164M
    query_cache_type = 1
    query_cache_limit = 1M
    max_connections = 200
    tmp_table_size = 256M
    max_heap_table_size = 256M

    The tmp table sizes seem to be pretty huge, but that's what the tuner wanted

    So the results are:

    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 1.0M, or always use indexes with joins)

    This will ask me to up JOIN BUFFER to over 6M, and keeps asking for more. Join Buffer adds to the PER CONNECTION ram used, so adding 4M x 200 connections = 800 megs more ram. I read that some files are always going to cause this to fire off, so what is a reasonable amount to put this at. I have it set at 1M already up from I think 128k or whatever the default it.

    Also is 256m of temp table too much even tho the tuner set it there?

    Thanks
     
  2. syndicated

    syndicated Member

    Joined:
    Jun 1, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Re: join_buffer_size (> 1.0M, or always use indexes with joins) - MYSQL tun

    Here is the full post

    Code:
    ------- General Statistics --------------------------------------------------
    --] Skipped version check for MySQLTuner script
    OK] Currently running supported MySQL version 5.1.63-cll
    OK] Operating on 64-bit architecture
    
    ------- Storage Engine Statistics -------------------------------------------
    --] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    --] Data in MyISAM tables: 450M (Tables: 680)
    --] Data in InnoDB tables: 8M (Tables: 147)
    --] Data in MEMORY tables: 0B (Tables: 4)
    !!] Total fragmented tables: 39
    
    ------- Security Recommendations  -------------------------------------------
    OK] All database users have passwords assigned
    
    ------- Performance Metrics -------------------------------------------------
    --] Up for: 14h 3m 49s (10M q [215.581 qps], 36K conn, TX: 76B, RX: 2B)
    --] Reads / Writes: 83% / 17%
    --] Total buffers: 559.0M global + 7.2M per thread (200 max threads)
    !!] Maximum possible memory usage: 2.0G (98% of installed RAM)
    OK] Slow queries: 0% (218/10M)
    OK] Highest usage of available connections: 34% (69/200)
    OK] Key buffer size / total MyISAM indexes: 128.0M/147.6M
    OK] Key buffer hit rate: 100.0% (2B cached / 17K reads)
    OK] Query cache efficiency: 94.0% (9M cached / 10M selects)
    OK] Query cache prunes per day: 0
    OK] Sorts requiring temporary tables: 0% (13 temp sorts / 98K sorts)
    !!] Joins performed without indexes: 5561
    OK] Temporary tables created on disk: 23% (25K on disk / 111K total)
    OK] Thread cache hit rate: 99% (113 created / 36K connections)
    OK] Table cache hit rate: 96% (1K open / 1K opened)
    OK] Open file limit used: 45% (1K/4K)
    OK] Table locks acquired immediately: 98% (1M immediate / 1M locks)
    OK] InnoDB data size / buffer pool: 8.5M/9.0M
    
    ------- Recommendations -----------------------------------------------------
    eneral 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
       Adjust your join queries to always utilize indexes
    ariables to adjust:
     *** MySQL's maximum memory usage is dangerously high ***
     *** Add RAM before increasing MySQL buffer variables ***
       join_buffer_size (> 1.0M, or always use indexes with joins)
     
  3. Eric

    Eric Administrator
    Staff Member

    Joined:
    Nov 25, 2007
    Messages:
    746
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Texas
    cPanel Access Level:
    Root Administrator
    Re: join_buffer_size (> 1.0M, or always use indexes with joins) - MYSQL tun

    Howdy,

    I run this in a cron each morning after I optimize every table. Some of this you're just to have to accept as the most you're willing to offer MySQL. Also some apps are not well written with MySQL so they never use indexes with their joins. I run this right after I Optimize ever table but still it insists there are some that are not correct. It's a since of /good enough/ and if your MySQL server is performing well for your users.

    Thanks!
     
  4. syndicated

    syndicated Member

    Joined:
    Jun 1, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Re: join_buffer_size (> 1.0M, or always use indexes with joins) - MYSQL tun

    So is it safe to run

    mysqlcheck -o -A

    in a Cron every night?

    Thanks
     
  5. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Re: join_buffer_size (> 1.0M, or always use indexes with joins) - MYSQL tun

    Yes you can run a cron, but it's not going to do much. Check the nr of fragmented tables. Run mysqlcheck -o -A, wait 12 hours and check the nr of fragmented tables. I don't think that nr is going to decrease that much.

    "Maximum possible memory usage: 2.0G (98% of installed RAM)"

    Using 98% of your memory is not good! You need to reduce global usage, per thread use, max connections or increase your ram. 2GB is really not that much. Memory is not that expensive.

    Also you might want to increase your innodb from 8mb to 16mb.
     
Loading...

Share This Page