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.

Server optimization

Discussion in 'Workarounds and Optimization' started by nunoxico, Apr 14, 2014.

  1. nunoxico

    nunoxico Registered

    Joined:
    Apr 14, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi,
    This is my first tread in this forum, hopping being at the right place.
    I’m trying to optimize my new VPS cPanel purchase:

    AMD Opteron(tm) Processor 4284
    2Vcores
    2GB RAM
    Speed
    3000.000 MHz
    Cache
    2048 KB

    After install MySQLTuner I get this results:

    Code:
    [OK] Key buffer hit rate: 99.5% (404K cached / 2K reads)
    [OK] Query cache efficiency: 92.3% (735K cached / 797K selects)
    [!!] Query cache prunes per day: 7655
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15K sorts)
    [OK] Temporary tables created on disk: 22% (5K on disk / 23K total)
    [OK] Thread cache hit rate: 99% (12 created / 17K connections)
    [!!] Table cache hit rate: 4% (665 open / 15K opened)
    [OK] Open file limit used: 2% (304/10K)
    [OK] Table locks acquired immediately: 99% (188K immediate / 189K locks)
    [!!] InnoDB  buffer pool / data size: 8.0M/23.1M
    [!!] InnoDB log waits: 18
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: [http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/[/url]
    Variables to adjust:
        query_cache_size (> 32M)
        table_cache (> 5000)
        innodb_buffer_pool_size (>= 23M)
        innodb_log_buffer_size (>= 1M)
    Can anyone guide me through to optimize my plan?
    Thanks in advance,
    Francisco
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. nunoxico

    nunoxico Registered

    Joined:
    Apr 14, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi Michael,
    Thanks for your reply.
    Can you please guide trough the steps.
    Witch file should I edit?
    I also try mysqlcheck –optimize -A but doesn't work.
    Again, thanks for any help,
    Francisco
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    copy here full my.cnf and full mysqltuner.pl result

    for now you can adjust in my.cnf

    innodb_buffer_pool_size = 50M
    innodb_log_buffer_size = 16M

    and restart
    service mysql restart
     
  5. nunoxico

    nunoxico Registered

    Joined:
    Apr 14, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi Thinkbot,

    Because I have done some modifications, I prefer show you my actual settings before doing anything else.
    Please tell me what should I do to improve my settings and bring back memory usage to 2GB = 100%

    Thanks in advance,
    Francisco

    mysqltuner.pl

    Code:
    >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.1.73-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 2M (Tables: 120)
    [--] Data in InnoDB tables: 29M (Tables: 463)
    [--] Data in MEMORY tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 20h 3m 13s (1M q [10.003 qps], 19K conn, TX: 2B, RX: 372M)
    [--] Reads / Writes: 62% / 38%
    [--] Total buffers: 218.0M global + 8.5M per thread (250 max threads)
    [!!] Maximum possible memory usage: 2.3G (117% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 8% (20/250)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/1.5M
    [OK] Key buffer hit rate: 99.4% (227K cached / 1K reads)
    [OK] Query cache efficiency: 96.4% (1M cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
    [OK] Temporary tables created on disk: 17% (11K on disk / 61K total)
    [OK] Thread cache hit rate: 99% (20 created / 19K connections)
    [!!] Table cache hit rate: 0% (666 open / 330K opened)
    [OK] Open file limit used: 1% (295/20K)
    [OK] Table locks acquired immediately: 99% (142K immediate / 142K locks)
    [!!] InnoDB  buffer pool / data size: 8.0M/29.8M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog[/url]
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        table_cache (> 10000)
        innodb_buffer_pool_size (>= 29M)

    my.cnf

    Code:
    [mysqld]
    innodb_file_per_table=1
    bind-address=127.0.0.1
    open_files_limit=3474
    [mysqld]
        local-infile = 0
        max_connections = 250
        key_buffer = 64M
        myisam_sort_buffer_size = 64M
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 4M
    max_heap_table_size = 16M
    table_cache = 10000
    thread_cache_size = 286
    interactive_timeout = 50
    wait_timeout = 7000
    connect_timeout = 15
        max_allowed_packet = 16M
        max_connect_errors = 10
        query_cache_limit = 2M
        query_cache_size = 128M
        query_cache_type = 1
        tmp_table_size = 16M
    
    
    [mysqld_safe]
    
    
    [mysqldump]
        quick
        max_allowed_packet = 16M
    [myisamchk]
        key_buffer = 64M
        sort_buffer = 64M
        read_buffer = 16M
        write_buffer = 16M
    [mysqlhotcopy]
        interactive-timeout
    Any suggestions will be very welcome.
     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    adjust:

    max_connections = 100
    key_buffer = 25M
    join_buffer_size = 1M
    read_buffer_size = 128K
    sort_buffer_size = 256K
    table_cache = 6000
    thread_cache_size = 100
    connect_timeout = 5
    query_cache_size = 30M

    innodb_buffer_pool_size = 50M
    innodb_log_buffer_size = 16M

    and restart, memory savings would be huge
     
  7. nunoxico

    nunoxico Registered

    Joined:
    Apr 14, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi thinkbot,
    I follow your instructions but the results make me a little bit confuse.

    Code:
    RAM
    Before
    [!!] Maximum possible memory usage: 2.3G (117% of installed RAM)
    now
    [OK] Maximum possible memory usage: 313.0M (15% of installed RAM)
    
    Key buffer
    before
    [OK] Key buffer hit rate: 99.4% (227K cached / 1K reads)
    and now
    !!] Key buffer hit rate: 92.1% (355 cached / 28 reads)
    
    Query cache efficiency
    before
    [OK] Query cache efficiency: 96.4% (1M cached / 1M selects)
    now
    [OK] Query cache efficiency: 35.3% (65 cached / 184 selects)
    
    Thread cache
    before
    [OK] Thread cache hit rate: 99% (20 created / 19K connections)
    now
    [OK] Thread cache hit rate: 90% (1 created / 11 connections)
    
    Table cache
    before
    [!!] Table cache hit rate: 0% (666 open / 330K opened)
    now
    [OK] Table cache hit rate: 90% (69 open / 76 opened)
    
    Here the report
    >> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >> Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.1.73-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 2M (Tables: 120)
    [--] Data in InnoDB tables: 29M (Tables: 463)
    [--] Data in MEMORY tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 14
    
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1m 10s (210 q [3.000 qps], 11 conn, TX: 507K, RX: 42K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 138.0M global + 1.8M per thread (100 max threads)
    [OK] Maximum possible memory usage: 313.0M (15% of installed RAM)
    [OK] Slow queries: 0% (0/210)
    [OK] Highest usage of available connections: 1% (1/100)
    [OK] Key buffer size / total MyISAM indexes: 25.0M/1.5M
    [!!] Key buffer hit rate: 92.1% (355 cached / 28 reads)
    [OK] Query cache efficiency: 35.3% (65 cached / 184 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 38 sorts)
    [OK] Temporary tables created on disk: 25% (5 on disk / 20 total)
    [OK] Thread cache hit rate: 90% (1 created / 11 connections)
    [OK] Table cache hit rate: 90% (69 open / 76 opened)
    [OK] Open file limit used: 0% (38/12K)
    [OK] Table locks acquired immediately: 100% (223 immediate / 223 locks)
    [OK] InnoDB buffer pool / data size: 50.0M/29.9M
    [OK] InnoDB log waits: 0
    -------- 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
    and here the Cron Daemon alert
    /usr/bin/mysqladmin: connect to server at 'localhost' failed
    error: 'Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space'

    Please, kindly support me on that.
    Thanks,
    Francisco
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You need to run mysql for a little bit more time than 1min, as recommendation says
    "MySQL started within last 24 hours - recommendations may be inaccurate"

    [--] Up for: 1m 10s (210 q [3.000 qps], 11 conn, TX: 507K, RX: 42K)

    Previous result
    [!!] Maximum possible memory usage: 2.3G (117% of installed RAM)

    New one
    [OK] Maximum possible memory usage: 313.0M (15% of installed RAM)


    So the maximum mysql usage dropped to acceptable level for your server, at this point, your mysql data doesnt require more

    In the future adjust only those
    [OK] Key buffer size / total MyISAM indexes: 25.0M/1.5M
    [OK] InnoDB buffer pool / data size: 50.0M/29.9M

    so that MyISAM indexes fits Key buffer size and InnoDB data size fits InnoDB buffer pool, as it does now
     
Loading...

Share This Page