Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Optimization

Discussion in 'Workarounds and Optimization' started by Pianoface, Aug 19, 2012.

  1. Pianoface

    Pianoface Registered

    Aug 19, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Could anyone help me and suggest what lines to change or add to my my.cnf? I've pasted my tuner log and existing my.cnf below. The SQL server has been running for 165 days.

    My current my.cnf
    set-variable = max_connections=500
    SQLTuner Log
     >>  MySQLTuner 1.2.0 - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.95-community-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 11G (Tables: 2218)
    [--] Data in InnoDB tables: 145M (Tables: 170)
    [--] Data in MEMORY tables: 0B (Tables: 5)
    [!!] Total fragmented tables: 303
    -------- Security Recommendations  -------------------------------------------
    [!!] User 'catsresc_wp219@localhost' has no password set.
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 165d 20h 52m 20s (1B q [115.340 qps], 99M conn, TX: 198B, RX: 2B)
    [--] Reads / Writes: 86% / 14%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.3G (11% of installed RAM)
    [OK] Slow queries: 0% (56K/1B)
    [OK] Highest usage of available connections: 19% (96/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/3.1G
    [OK] Key buffer hit rate: 99.5% (1642B cached / 8B reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 2% (1M temp sorts / 88M sorts)
    [!!] Joins performed without indexes: 179177
    [!!] Temporary tables created on disk: 29% (16M on disk / 56M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 125M opened)
    [OK] Open file limit used: 3% (89/2K)
    [OK] Table locks acquired immediately: 99% (2B immediate / 2B locks)
    [!!] InnoDB data size / buffer pool: 145.6M/8.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        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
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 145M)
  2. syndicated

    syndicated Member

    Jun 1, 2010
    Likes Received:
    Trophy Points:
    Hi Piano as I have just finished tuning MYSQL for a week or two now here are my findings about the Tuner and MYSQL

    Query Cache Size = this is the size of the pool for query cache. Your query cache is disabled so you need to turn it on and set a size. It says > 8 megs a good start for your server would be 256M , I found I need at least that much on my 2gig and 8gig servers. Its best to start low and tune up then make giant jumps, but sometimes your wasting time as I have already noticed over a week of upping it over and over some settings.

    Join Buffer Size = you can go up to 256kb, a lot of people like to push this up to 2MB

    Know that some of these buffers are PER connection, so if you get a lot of users that can really eat up ram FAST and crash your server. Also based on my research, the type of ram used once you pass 256kb is 37x slower then that first bit of ram.
    So MORE is not always better for MYSQL, dont overtune your server way out of sync with norms or risk loss of performance.

    More on understanding sort_buffer_size « MySQL Expert | MySQL Performance | MySQL Consulting


    From Monty Taylor “if buffer is set to over 256K, it uses mmap() instead of malloc() for memory allocation. Actually – this is a libc malloc thing and is tunable, but defaults to 256k. From the manual:” . He goes on in a further to shows that impact > 256K for a buffer is 37x slower. This applies to all per session buffers, not just sort buffer.

    So I tried this at up to 2M dropped it back to 128 (default) or 256kb

    Also the Tuner will ask you for ENDLESS RAM for join_buffer_size, you can keep going up to 30megs etc and it will not stop because your tables will never use Index with joins, and this is not a way to fix that, you would need to recode your mysql not jack up JOINS ram. 179177 joins without is just WAY too many to try to buffer this.

    tmp_table_size (> 32M) = this one is a basic pool of cache, jack it up no problem
    max_heap_table_size = should be set the same as it

    thread_cache_size (start at 4) = I am at 50 so id at least goto 20 for this

    table_cache (> 64) = I am at 5000 on a 2gig server so I am sure you can use that much at least

    innodb_buffer_pool_size (>= 145M) = simple pool, you can juice this one no problem also follows its directions

    Your on 5.0 so this syntax will work to add to your config:


    key_buffer_size = 25M
    sort_buffer_size = 128kb
    read_buffer_size = 128kb
    join_buffer_size = 256kb
    read_rnd_buffer_size = 128kb
    thread_cache_size = 20
    table_cache = 5000
    query_cache_size = 256M
    query_cache_limit = 1M
    max_connections = 500
    tmp_table_size = 256M
    max_heap_table_size = 256M
    innodb_buffer_pool_size = 150M

    key_buffer_size = 128M
    sort_buffer_size = 128M
    read_buffer_size = 2M
    write_buffer_size = 2M

    That gives you everything the tuner asked for and a jump start to tune.

    Run it for 24 hours, rerun the tuner and post your results here.
  3. cactus0sahara

    cactus0sahara Registered

    Sep 6, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator
    Thanks syndicated. I need this share
  4. qwerty

    qwerty Well-Known Member

    Jan 21, 2003
    Likes Received:
    Trophy Points:
    too large a query cache can be more of a negative than positive as mysql needs to check the entire buffer everytime a new query is sent its way. Start low ie. 32mb or 64mb and see how it goes. I've only just started looking into mysql optimization and this is the first thing I learn-ed today :D

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice