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!

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)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #1 zimbahost, Feb 12, 2014
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    46,400
    Likes Received:
    2,049
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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?

     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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. leisegang

    MySQL slow and server optimization

    leisegang, Nov 19, 2018, in forum: Workarounds and Optimization
    Replies:
    3
    Views:
    604
    cPanelMichael
    Nov 21, 2018
  2. georget777

    server optimization for wordpress

    georget777, Jan 6, 2018, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    1,020
    georget777
    Jan 6, 2018
  3. Larkolag

    Suggestions for new server optimizations?

    Larkolag, Oct 18, 2017, in forum: Workarounds and Optimization
    Replies:
    9
    Views:
    838
    Larkolag
    Oct 19, 2017
  4. RadWebHosting

    Apache Optimizations for Single Domain on Server

    RadWebHosting, Jun 15, 2017, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    813
    cPanelMichael
    Jun 16, 2017
  5. wonder_wonder

    MySQL optimization

    wonder_wonder, Feb 14, 2019, in forum: Workarounds and Optimization
    Replies:
    14
    Views:
    340
    wonder_wonder
    Mar 25, 2019 at 2:20 PM

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.
    Accept Learn More...
    Dismiss Notice