1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need some help for my MySQL Server

Discussion in 'Server Configuration and Hosting' started by DRaver, Oct 22, 2013.

  1. DRaver

    DRaver Active Member

    I have mysql 5.6.14 running.

    Output from mysqltuner.pl :

    Code:
    [OK] Logged in using credentials from debian maintenance account.
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.14
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 972)
    [--] Data in InnoDB tables: 1G (Tables: 542)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MEMORY tables: 1008K (Tables: 19)
    [!!] Total fragmented tables: 82
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 12d 21h 43m 44s (15M q [13.756 qps], 273K conn, TX: 18B, RX: 5B)
    [--] Reads / Writes: 34% / 66%
    [--] Total buffers: 22.1G global + 2.9M per thread (300 max threads)
    [OK] Maximum possible memory usage: 22.9G (73% of installed RAM)
    [OK] Slow queries: 0% (1/15M)
    [OK] Highest usage of available connections: 17% (51/300)
    [OK] Key buffer size / total MyISAM indexes: 4.0G/1.3G
    [OK] Key buffer hit rate: 100.0% (22M cached / 1K reads)
    [OK] Query cache efficiency: 77.5% (7M cached / 10M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (5K temp sorts / 368K sorts)
    [OK] Temporary tables created on disk: 11% (122K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (51 created / 273K connections)
    [!!] Table cache hit rate: 0% (1K open / 5M opened)
    [OK] Open file limit used: 19% (1K/10K)
    [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
    [OK] InnoDB data size / buffer pool: 1.3G/16.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        table_cache (> 5000)
    
    My my.cnf :

    Code:
    [client]
    port                            = 3306
    socket                          = /var/run/mysqld/mysqld.sock
    
    [mysqld]
    
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    innodb_buffer_pool_size = 16G
    
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    # These are commonly set, remove the # and set as required.
    basedir        = /opt/mysql/server-5.6
    datadir = /opt/mysql/server-5.6/data
    port = 3306
    # server_id = .....
    socket = /var/run/mysqld/mysqld.sock
    pid-file = /var/run/mysqld/mysqld.pid
    bind-address  = 127.0.0.1
    default-storage-engine = InnoDB
    user        = mysql
    tmpdir        = /tmp
    tmp_table_size=32M
    max_heap_table_size=32M
    key_buffer = 2048M
    max_allowed_packet = 16M
    thread_stack = 192K
    thread_cache_size = 256
    myisam-recover = BACKUP
    read_rnd_buffer_size = 256K
    max_connections = 300
    table_open_cache = 5000
    sort_buffer_size = 256K
    read_buffer_size = 256K
    key_buffer_size = 4096M
    join_buffer_size = 2048K
    query_cache_limit    = 64M
    query_cache_size = 2048M
    query_cache_type=1
    #log_slow_queries    = /var/log/mysql/mysql-slow.log
    long_query_time = 2
    expire_logs_days = 10
    max_binlog_size = 100M
    innodb_file_per_table
    innodb_data_file_path = ibdata1:10G:autoextend
    innodb_log_buffer_size=16M
    innodb_flush_method=O_DIRECT
    innodb_flush_log_at_trx_commit=1
    innodb_log_file_size = 4GB
    innodb_buffer_pool_size = 16G
    innodb_additional_mem_pool_size = 8M
    innodb_support_xa = 0
    innodb_lock_wait_timeout = 100
    query_cache_min_res_unit = 2K
    The only problem seems to me that message:
    [!!] Table cache hit rate: 0% (1K open / 5M opened)

    I think table_open_cache = 5000 is high but 0% cache hit is not good.

    How can I fix my problem. Any performance tricks for MySQL 5.6 ?
     
  2. UrlJet

    UrlJet Member

    Floren and DRaver like this.
  3. Floren

    Floren Well-Known Member

    Mysqltuner is nothing fancy, unfortunately. The real way to tune your server is monitor closely the query logs and see what is eating your server up. Then, gradually make adjustments until you obtain proper results. This is an ongoing process, you have to repeat it every 6 months at least as your forums usage change constantly. It involves a lot of work effort but this is the only way you will get your MySQL server tuned properly.
     

Share This Page