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.

MySQL Tuner and VPS Settings

Discussion in 'Workarounds and Optimization' started by Astral God, May 12, 2012.

  1. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    Hi.

    I'm running cPanel under a VPS (1Gb RAM / 2Gb Burst).

    Here is my.cnf content:

    Code:
    [mysqld]
    skip-networking
    skip-innodb
    slow_query_log
    safe-show-database
    local-infile = 0
    
    max_connections = 150
    max_delayed_threads = 20
    max_tmp_tables = 32
    query_cache_type = 1
    query_cache_size = 64M
    thread_cache_size = 4
    tmp_table_size = 128M
    max_heap_table_size = 128M
    join_buffer_size = 8M
    table_open_cache = 64
    innodb_file_per_table=1
    wait_timeout = 14400
    interactive_timeout = 14400
    
    Here is my latest MySQL Tuner report:

    Code:
     >>  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.62-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 49M (Tables: 533)
    [--] Data in MEMORY tables: 0B (Tables: 1)
    [!!] Total fragmented tables: 2
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 13d 19h 13m 29s (84M q [70.702 qps], 164K conn, TX: 25B, RX: 15B)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 200.0M global + 10.6M per thread (150 max threads)
    [!!] Maximum possible memory usage: 1.8G (87% of installed RAM)
    [OK] Slow queries: 0% (82/84M)
    [OK] Highest usage of available connections: 67% (101/150)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/16.0M
    [OK] Key buffer hit rate: 99.1% (15M cached / 137K reads)
    [OK] Query cache efficiency: 99.2% (82M cached / 83M selects)
    [!!] Query cache prunes per day: 4982
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 83K sorts)
    [!!] Joins performed without indexes: 6072
    [!!] Temporary tables created on disk: 29% (28K on disk / 96K total)
    [OK] Thread cache hit rate: 96% (6K created / 164K connections)
    [!!] Table cache hit rate: 0% (64 open / 85K opened)
    [OK] Open file limit used: 12% (127/1K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        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:
        query_cache_size (> 64M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
        table_cache (> 64)
    
    Under Parallels Power Panel, Resources tab, i can see this kind of alerts:

    [​IMG]

    So, i would like to optimize the system to have the best RAM usage possible, because when i try to follow MySQL Tuner, RAM is always more and more requested...

    Should i disable some functions (like table_open_cache) ?
     

    Attached Files:

    • ppp.png
      ppp.png
      File size:
      9.5 KB
      Views:
      117
  2. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    Nobody can help me ?
    Thanks.
     
  3. -GR-

    -GR- Active Member

    Joined:
    May 2, 2012
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator

    Um, I think that might be the problem. You are on the Cpanel forum but asking questions for a Plesk setup. Might want to head over to the Plesk forums and ask there.
     
  4. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    He is probably also using some Plesk control panel for his vps account, like me.
     
  5. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    I'm having those alerts because my.cnf is not correctly optimized. If i disable all cache functions, i have no more alerts. They might be optimized i think, and this is why i'm requesting some help.

    PPP os only a VPS panel, nothing more.
     
Loading...

Share This Page