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.

Please help me to optimize mysql (my.cnf)

Discussion in 'Workarounds and Optimization' started by avinash.pudota, Jul 14, 2013.

  1. avinash.pudota

    Joined:
    Jan 27, 2013
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello, i have a VPS with 2 GB memory and around 50 websites and all are very low traffic websites (less than 500 visits per day in total, i expect 1 website may get more than 1000 visits per day soon so lets assume my server gets 2000 visits per day in total). static pages and other files load very quickly but pages which need database interaction is taking lot of time which i have found recently. After a small research i've found that i need to optimize my.cnf for better speed. I dont have much knowledge related to mysql, so please suggest me a my.cnf configuration file from the below inputs.

    Technical details of my VPS:
    OS: Cent OS
    Architecture i686
    Memory: 2 GB
    Processor: Intel(R) Xeon(R) CPU X3430 @ 2.40GHz
    Apache version 2.2.24
    PHP version 5.3.26
    MySQL version 5.1.70-cll


    Current my.cnf file
    Code:
    [mysqld]
    max_connections=500
    set-variable = max_connections=500
    safe-show-database
    open_files_limit=48244
    skip-name-resolve
    interactive_timeout=600
    wait_timeout=600
    query_cache_size=10M
    thread_cache_size=4

    mysqltuner.pl output

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.70-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 97M (Tables: 3975)
    [--] Data in InnoDB tables: 271M (Tables: 3507)
    [--] Data in MEMORY tables: 253K (Tables: 93)
    [!!] Total fragmented tables: 3551
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 5h 8m 28s (706K q [6.733 qps], 17K conn, TX: 2B, RX: 351M)
    [--] Reads / Writes: 95% / 5%
    [--] Total buffers: 44.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (67% of installed RAM)
    [OK] Slow queries: 0% (653/706K)
    [OK] Highest usage of available connections: 4% (24/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/36.6M
    [OK] Key buffer hit rate: 99.5% (5M cached / 28K reads)
    [OK] Query cache efficiency: 43.4% (270K cached / 623K selects)
    [!!] Query cache prunes per day: 23067
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19K sorts)
    [!!] Joins performed without indexes: 6101
    [OK] Temporary tables created on disk: 20% (5K on disk / 29K total)
    [OK] Thread cache hit rate: 98% (206 created / 17K connections)
    [!!] Table cache hit rate: 0% (64 open / 120K opened)
    [OK] Open file limit used: 0% (68/48K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [!!] InnoDB data size / buffer pool: 271.7M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 10M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 271M)
    
    My VPS consumes around 550 to 600 MB out of 2 GB all the time but some times it goes upto 1.5 GB (for few minutes) without any reason and even i cant see any heavy memory consuming processes during that time
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    add in the end


    table_open_cache = 2000
    table_definition_cache = 1000
    key_buffer_size = 100M
    innodb_buffer_pool_size = 500M
    innodb_buffer_pool_instances = 4
    join_buffer_size=1M
    read_rnd_buffer_size=2M

    and change
    max_connections=500
    to
    max_connections=100


    then restart
     
  3. avinash.pudota

    Joined:
    Jan 27, 2013
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have added the above lines and restarted mysql, but i'm getting the following error

    i reverted the file back to original setting and it started sucessfully
     
  4. avinash.pudota

    Joined:
    Jan 27, 2013
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I have tested by removing one line at a time and trying to restart mysql. when i remove this line mysql service started sucessfully.

     
    #4 avinash.pudota, Jul 15, 2013
    Last edited: Jul 15, 2013
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,811
    Likes Received:
    671
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    The "innodb_buffer_pool_instances" variable was introduced in MySQL version 5.5. It's not supported in MySQL version 5.1.

    Thank you.
     
  6. luigidelgado

    luigidelgado Well-Known Member

    Joined:
    Nov 6, 2010
    Messages:
    109
    Likes Received:
    2
    Trophy Points:
    16
    Location:
    Mexico
    cPanel Access Level:
    Root Administrator
    Twitter:
    Sorry for being a party pooper for some but mySqlTuner is not recommended for MySQL 5.5 and later. Your are safe on 5.1 but all others beware:

    Compatibility:

    MySQL 3.23, 4.0, 4.1, 5.0, 5.1 (full support)
    MySQL 5.4 (not fully tested, partially supported)
    MySQL 6.0 (partial support)
    MariaDB 10.x (partial support)
    Perl 5.6 or later (with perl-doc package)
    Unix/Linux based operating system (tested on Linux, BSD variants, and Solaris variants)
    Windows is not supported at this time
    Unrestricted read access to the MySQL server (OS root access recommended for MySQL < 5.0)


    Thread: [Case # 90317] - Warning on using mysqltuner and MySQL 5.6!
    http://forums.cpanel.net/f354/case-90317-warning-using-mysqltuner-mysql-5-6-a-389971.html

    Cheers.
     
Loading...

Share This Page