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.

MySQL my.cnf tuning, me too?

Discussion in 'Workarounds and Optimization' started by DAdams982, Oct 21, 2011.

  1. DAdams982

    DAdams982 Registered

    Joined:
    Sep 27, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    I am sure 900 people start new threads on this issue, so I will jsut use this one. i am attempting to tune mysq on my VPS now which has 1.5G of ram.. Here is the perl script output:

    Code:
    >>  MySQLTuner 1.2.0 - Major Hayden <[EMAIL="major@mhtx.net"]major@mhtx.net[/EMAIL]>
     >>  Bug reports, feature requests, and downloads at [URL]http://mysqltuner.com/[/URL]
     >>  Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 61M (Tables: 1263)
    [--] Data in InnoDB tables: 347M (Tables: 1024)
    [--] Data in MEMORY tables: 2M (Tables: 22)
    [!!] Total fragmented tables: 1024
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7d 23h 45m 40s (11M q [16.926 qps], 284K conn, TX: 33B, RX: 3B)
    [--] Reads / Writes: 45% / 55%
    [--] Total buffers: 106.0M global + 11.2M per thread (75 max threads)
    [OK] Maximum possible memory usage: 949.8M (70% of installed RAM)
    [OK] Slow queries: 0% (104/11M)
    [OK] Highest usage of available connections: 18% (14/75)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/13.1M
    [OK] Key buffer hit rate: 99.2% (4M cached / 34K reads)
    [OK] Query cache efficiency: 90.5% (8M cached / 9M selects)
    [!!] Query cache prunes per day: 40648
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 57K sorts)
    [!!] Joins performed without indexes: 8316
    [OK] Temporary tables created on disk: 9% (37K on disk / 410K total)
    [OK] Thread cache hit rate: 99% (14 created / 284K connections)
    [!!] Table cache hit rate: 0% (512 open / 184K opened)
    [OK] Open file limit used: 50% (560/1K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [!!] InnoDB data size / buffer pool: 347.6M/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 (> 32M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        table_cache (> 512)
        innodb_buffer_pool_size (>= 347M)
    
    And here is my.cnf

    Code:
    [mysqld]
    safe-show-database
    tmp_table_size = 64M
    max_heap_table_size = 32M
    query_cache_limit=1M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_connections=75
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    delayed_insert_timeout=40
    interactive_timeout=10
    wait_timeout=150
    connect_timeout=20
    thread_cache_size=8
    key_buffer=32M ## 32MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=512
    record_buffer=1M
    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=2M  ## 1MB for every 1GB of RAM
    thread_concurrency=2 ## Number of CPUs x 2
    myisam_sort_buffer_size=32M
    
    Any recommendations you all can share?
     
  2. gnutoolbox

    gnutoolbox Member

    Joined:
    Sep 25, 2011
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Below config works well with sever having 2 GB of ram. If your mysql server is still hungry of using more cpu/memory, you may need to try data caching options like memcached.

    /http://www.gnutoolbox.com/memcached-php-mysql/


    My.cnf for 2 GB :

    =================

    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    safe-show-database
    back_log = 150
    max_connections = 250
    key_buffer_size = 16M
    myisam_sort_buffer_size = 16M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    #table_cache = 1000
    thread_cache_size = 256
    wait_timeout = 10
    connect_timeout = 10
    tmp_table_size = 16M
    max_heap_table_size = 16M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    thread_concurrency = 4
    concurrent_insert = 2
    table_lock_wait_timeout = 15
    read_rnd_buffer_size = 2M
    bulk_insert_buffer_size = 8M
    query_cache_limit = 20M
    query_cache_size = 6M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 16
    net-read-timeout = 5
    [mysqld_safe]
    nice = -10
    open_files_limit = 8192
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    [mysqldump]
    quick
    max_allowed_packet = 16M

    ===================
     
Loading...

Share This Page