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:
    56
    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)
     
    #1 zimbahost, Feb 12, 2014
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    35,652
    Likes Received:
    1,138
    Trophy Points:
    363
    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.
     
    #2 cPanelMichael, Feb 12, 2014
  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 ?
     
    #3 thinkbot, Feb 13, 2014
  4. zimbahost

    zimbahost Active Member

    Joined:
    Feb 17, 2009
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    56
    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
     
    #4 zimbahost, Feb 14, 2014
  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
     
    #5 thinkbot, Feb 14, 2014
  6. zimbahost

    zimbahost Active Member

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

    Thanks
    Maison
     
    #6 zimbahost, Feb 15, 2014
  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
     
    #7 thinkbot, Feb 15, 2014
  8. zimbahost

    zimbahost Active Member

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

     
    #8 zimbahost, Feb 20, 2014
  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
     
    #9 thinkbot, Feb 20, 2014
  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
     
    #10 Julien Mary, Sep 22, 2014
(You must log in or sign up to post here.)
Loading...
Similar Threads - Optimization Server Dedicated
  1. NEK4TE

    MySQL Optimization - Dedicated Server

    NEK4TE, Aug 8, 2015, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    1,770
    NEK4TE
    Aug 10, 2015
  2. RadWebHosting

    Apache Optimizations for Single Domain on Server

    RadWebHosting, Jun 15, 2017, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    60
    cPanelMichael
    Jun 16, 2017
  3. icandoit

    Concerns About Server Optimization

    icandoit, Oct 4, 2016, in forum: Workarounds and Optimization
    Replies:
    7
    Views:
    387
    Infopro
    Oct 7, 2016
  4. Amir Askari

    Server Optimization question

    Amir Askari, Jun 1, 2016, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    682
    24x7server
    Jun 5, 2016
  5. Ajeje989

    Server optimization assistance requested

    Ajeje989, Jan 30, 2016, in forum: General Discussion
    Replies:
    1
    Views:
    302
    cPanelMichael
    Feb 1, 2016

Share This Page