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!

Optimizing MySQL for 512Mb : (80% of installed RAM)

Discussion in 'Database Discussion' started by LeGastronome, Oct 23, 2010.

  1. LeGastronome

    LeGastronome Active Member

    Joined:
    Oct 21, 2010
    Messages:
    35
    Likes Received:
    1
    Trophy Points:
    58
    Hello,

    After several readings about MySQL optimizations, I have decide to optimize my server :

    After the first run of MySQLTuner :
    I had : Maximum possible memory usage: 2G (400% of installed RAM)

    On a 512MB server, it seems to be a little too huge :)

    so after many changes in the .conf I have this, Could you tell me if is it ok for you ?

    Code:
    >>  MySQLTuner 1.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.0.91-community-log
    [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: 60M (Tables: 243)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 1
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 15h 25m 31s (327K q [5.899 qps], 4K conn, TX: 483M, RX: 52M)
    [--] Reads / Writes: 77% / 23%
    [--] Total buffers: 142.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 410.7M (80% of installed RAM)
    [OK] Slow queries: 0% (2/327K)
    [OK] Highest usage of available connections: 6% (6/100)
    [OK] Key buffer size / total MyISAM indexes: 100.0M/36.4M
    [!!] Key buffer hit rate: 92.7% (909K cached / 66K reads)
    [OK] Query cache efficiency: 94.5% (285K cached / 302K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
    [!!] Joins performed without indexes: 244
    [OK] Temporary tables created on disk: 18% (419 on disk / 2K total)
    [OK] Thread cache hit rate: 99% (6 created / 4K connections)
    [OK] Table cache hit rate: 42% (216 open / 511 opened)
    [OK] Open file limit used: 36% (373/1K)
    [OK] Table locks acquired immediately: 99% (47K immediate / 47K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
    And the .conf :

    Code:
    [client]
    #port            = 3306
    #socket          = /var/lib/mysql/mysql.sock
    
    # Disable Berkley DB functionality, saving memory
    #skip-bdb
    
    ###The MySQL server
    [mysqld]
    #port            = 3306
    #socket          = /var/lib/mysql/mysql.sock
    key_buffer = 100M
    max_connections = 100
    max_allowed_packet = 1M
    table_cache = 256
    #sort_buffer_size = 4M
    #read_buffer_size = 4M
    myisam_sort_buffer_size = 32M
    thread_cache_size = 8
    wait_timeout = 60
    connect_timeout = 30
    interactive-timeout = 50
    query-cache-type = 1
    query_cache_size = 16M
    #query_cache_limit = 1M
    # Try number of CPU's*2 for thread_concurrency
    #thread_concurrency = 8
    max_user_connections = 50
    log-error = /var/log/mysqld.log
    log-slow-queries = /var/log/mysqlslow.log
    old-passwords = 1
    
    [mysqldump]
    #quick
    max_allowed_packet = 2M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 4M
    write_buffer = 4M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 4M
    write_buffer = 4M
    
    [mysqlhotcopy]
    interactive-timeout
    
    Thanks
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Let's go ahead and remove the stuff commented out and fix things improperly listed (like key_buffer which is actually key_buffer_size):

    ###The MySQL server
    [mysqld]
    key_buffer_size = 100M
    max_connections = 100
    max_allowed_packet = 1M
    table_cache = 256
    myisam_sort_buffer_size = 32M
    thread_cache_size = 8
    wait_timeout = 60
    connect_timeout = 30
    interactive-timeout = 50
    query-cache-type = 1
    query_cache_size = 16M
    query_cache_limit = 1M
    max_user_connections = 50
    log-error = /var/log/mysqld.log
    log-slow-queries = /var/log/mysqlslow.log
    old-passwords = 1

    [mysqldump]
    max_allowed_packet = 2M

    [mysql]
    no-auto-rehash

    [isamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 4M
    write_buffer_size = 4M

    [myisamchk]
    key_buffer_size = 64M
    sort_buffer_size = 64M
    read_buffer_size = 4M
    write_buffer_size = 4M

    [mysqlhotcopy]
    interactive-timeout

    If you aren't using InnoDB as the report appears to indicate, then you can skip it, so you could add:

    skip-innodb

    I did uncomment query_cache_limit since, even though you have it set to the default, it's easier to have the three that are associated together in the my.cnf (query_cache_type, query_cache_size and query_cache_limit).

    Next, you can try optimizing all databases to defragment tables:

    Code:
    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    Finally, you've cut off the top of the report that tells your MySQL version. Which MySQL version are you using?

    Code:
    mysql --version
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

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.
    Dismiss Notice