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.

Optimization for 4GB Server Dedicated CENTOS 6.5 Xeon 3450

Discussion in 'Workarounds and Optimization' started by zimbahost, Feb 12, 2014.

  1. zimbahost

    zimbahost Active Member

    Joined:
    Feb 17, 2009
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brazil
    Could you help me with a good optimization?
    Server Xeon 3450 4gb RAM

    Code:
    This is the actual my.cnf:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=50000
    

    Code:
    mysqltunner:
     >>  MySQLTuner 1.2.0_1 - 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.5.35-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 23008)
    [--] Data in InnoDB tables: 5G (Tables: 12626)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 577)
    [!!] Total fragmented tables: 231
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 52m 19s (310K q [99.009 qps], 11K conn, TX: 703M, RX: 42M)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (15% of installed RAM)
    [OK] Slow queries: 0% (0/310K)
    [OK] Highest usage of available connections: 11% (18/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/471.1M
    [OK] Key buffer hit rate: 99.9% (39M cached / 56K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 26K sorts)
    [!!] Joins performed without indexes: 1268
    [OK] Temporary tables created on disk: 25% (14K on disk / 56K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 10% (400 open / 3K opened)
    [OK] Open file limit used: 0% (431/50K)
    [OK] Table locks acquired immediately: 99% (385K immediate / 386K locks)
    [!!] InnoDB data size / buffer pool: 5.7G/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        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)
        thread_cache_size (start at 4)
        table_cache (> 400)
        innodb_buffer_pool_size (>= 5G)
    
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    The following thread is a good place to start when obtaining data for MySQL optimization:

    mysqlmymonlite.sh server stats gathering tool for cPanel Server

    There is a variant to the MySQL tuner that is more suited towards newer versions of MySQL. In addition, try to let MySQL run at least 24 hours before using a tuner.

    Thank you.
     
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You have too little RAM memory to fit main buffers

    replace your my.cnf with this

    [mysqld]
    skip-name-resolve

    myisam_use_mmap=1

    max_connections = 100
    max_user_connections = 50

    join_buffer_size=512K
    sort_buffer_size=256K

    table_open_cache = 5000
    table_definition_cache = 3000
    max_allowed_packet = 32M

    thread_cache_size = 16

    query_cache_type = 1
    query_cache_size =50M
    query_cache_limit = 1M

    max_heap_table_size = 30M
    tmp_table_size = 30M

    key_buffer_size = 500M

    innodb_buffer_pool_size = 1000M
    innodb_stats_on_metadata=0

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1



    This will make of course huge improvement, but it still won't fit whole innodb_buffer_pool_size in RAM, innodb buffer
    Make sure to remove databases that are not used and rerun mysqltuner.pl again

    And which tables are used more often, InnoDB or MyISAM ?
     
  4. zimbahost

    zimbahost Active Member

    Joined:
    Feb 17, 2009
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brazil
    Hi Thanks Help

    My costumer use WordPress Joomla, more MyISAM
    What ram for me? 8gb is good?

    Process Manager
    Mysql
    CPU% 5%
    Memory% 12%

    Thanks
    Maison
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    If your all innodb databases are not used, so they will probably fit in memory
    MyiSAM tables all fits in buffers now, so they will work very fast

    You should install munin plugin for WHM to have monitoring of server usage on graphs

    please rerun mysqltuner.pl
     
  6. zimbahost

    zimbahost Active Member

    Joined:
    Feb 17, 2009
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brazil
    Hi Mysql Use 31,2% RAM 1,9% CPU is correct?

    Thanks
    Maison
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You should install something more reliable for server monitoring, like munin plugin in WHM
    Process Manager is very basic tool for showing server utilization
     
  8. zimbahost

    zimbahost Active Member

    Joined:
    Feb 17, 2009
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brazil
    I have 8gb installed on the server now, I need to change some more configuration below?

     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    First please rerun mysqltuner.pl and post results here
     
  10. Julien Mary

    Julien Mary Registered

    Joined:
    Sep 22, 2014
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Hong Kong, Hong Kong
    cPanel Access Level:
    Root Administrator
    That changed the server from turtle style to blazing fast : THANKS
     
Loading...

Share This Page