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.

Optimize MySQL service

Discussion in 'Workarounds and Optimization' started by MasterGberry, Jan 1, 2013.

  1. MasterGberry

    MasterGberry Member

    Joined:
    Sep 4, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hey. So I was running some intense scripts earlier that was using 9 worker threads accessing around 25k rows each and doing UPDATEs on them after getting some information from an API and I noticed my server load shot up above 2. Obviously this is some heavy data work and I need to optimize MySQL to work better.

    I was hoping to get some help here on optimizing MySQL to better handle this kind of load. I'm not even sure how I can check how many queries per second and such are being done. The specs on my server are the XL 6 Server Premium XL - 1&1 Dedicated Server

    I also have this screenshot of munin with MySQL InnoDB stuff and it was saying the amount of free space was critical?

    6XwRTt.png

    I have the following my.cnf file but I feel like it could use with some additions...

    Code:
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
    
    I am happy to answer any questions and hope to tweak the settings to handle this kind of load at nights. Thanks.
     
    #1 MasterGberry, Jan 1, 2013
    Last edited by a moderator: Jan 1, 2013
  2. bbrink68

    bbrink68 Active Member

    Joined:
    Nov 27, 2012
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Yikes, max connections at 500 is pretty high... You would need a fairly large amount of memory to accommodate that.

    You should download and run MySQL tuner(google). That will give you accurate recommendations

    Also if you get space critical warnings you should check te MySQL were or log, might have /var filling up
     
    #2 bbrink68, Jan 1, 2013
    Last edited: Jan 1, 2013
  3. MasterGberry

    MasterGberry Member

    Joined:
    Sep 4, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    /var seems to have plenty of space, and I will take a quick look at MySQL tuner. Thanks. Will post back with results.

    Code:
    root@server1 [~]# perl mysqltuner.pl
    
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.66-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 636M (Tables: 2142)
    [--] Data in InnoDB tables: 35M (Tables: 80)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 258
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 14d 2h 48m 16s (10M q [8.861 qps], 1M conn, TX: 16B, RX: 845M)
    [--] Reads / Writes: 46% / 54%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (8% of installed RAM)
    [OK] Slow queries: 0% (360/10M)
    [OK] Highest usage of available connections: 32% (160/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/706.2M
    [OK] Key buffer hit rate: 97.0% (44M cached / 1M reads)
    [!!] Query cache is disabled
    [!!] Sorts requiring temporary tables: 11% (7K temp sorts / 68K sorts)
    [OK] Temporary tables created on disk: 19% (70K on disk / 365K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 301K opened)
    [OK] Open file limit used: 3% (77/2K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] InnoDB data size / buffer pool: 35.5M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        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)
        sort_buffer_size (> 1M)
        read_rnd_buffer_size (> 256K)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 35M)
    
    Open to further suggestions/instructions...i really havent played with mysql that much before and definitely need to learn more about it.
     
    #3 MasterGberry, Jan 1, 2013
    Last edited: Jan 1, 2013
Loading...

Share This Page