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 optimization

Discussion in 'Workarounds and Optimization' started by mehnihma, Feb 15, 2014.

  1. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hi
    Can you help me optimize my database:

    now I have :

    [mysqld]
    innodb_file_per_table=1
    open_files_limit=5294


    mysqltuner:
    PHP:
    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.5.35-cll
    [OKOperating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables4M (Tables50)
    [--] 
    Data in InnoDB tables64M (Tables1364)
    [--] 
    Data in PERFORMANCE_SCHEMA tables0B (Tables17)
    [!!] 
    Total fragmented tables24

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 10d 14h 31m 35s (7M q [8.648 qps], 149K connTX20BRX1B)
    [--] 
    Reads Writes95% / 5%
    [--] 
    Total buffers168.0M global + 2.8M per thread (151 max threads)
    [
    OKMaximum possible memory usage583.2M (3of installed RAM)
    [
    OKSlow queries0% (32/7M)
    [
    OKHighest usage of available connections11% (18/151)
    [
    OKKey buffer size total MyISAM indexes8.0M/1.2M
    [OKKey buffer hit rate98.2% (460K cached 8K reads)
    [!!] 
    Query cache is disabled
    [OKSorts requiring temporary tables0% (223 temp sorts 952K sorts)
    [!!] 
    Joins performed without indexes2998
    [!!] Temporary tables created on disk45% (721K on disk 1M total)
    [!!] 
    Thread cache is disabled
    [!!] Table cache hit rate0% (400 open 126K opened)
    [
    OKOpen file limit used1% (74/5K)
    [
    OKTable locks acquired immediately99% (8M immediate 8M locks)
    [
    OKInnoDB data size buffer pool64.6M/128.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
        When making adjustments
    make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 
    as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust
    :
        
    query_cache_size (>= 8M)
        
    join_buffer_size (> 128.0K, or always use indexes with joins)
        
    tmp_table_size (> 16M)
        
    max_heap_table_size (> 16M)
        
    thread_cache_size (start at 4)
        
    table_cache (> 400)
    thanks

    - - - Updated - - -

    Would this be ok?

    [mysqld]
    innodb_file_per_table=1
    open_files_limit=5294
    log-slow-queries=/var/lib/mysql/slow.log
    interactive_timeout=300
    log-slow-queries=/var/lib/mysql/slow.log
    query_cache_size=50M
    skip-bdb
    query_cache_type=1
    tmp_table_size=64M
    max_heap_table_size=64M
    thread_cache_size=4
    table_cache=600
    wait_timeout=300
     
    #1 mehnihma, Feb 15, 2014
    Last edited: Feb 15, 2014
  2. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    When I put this in my.conf


    [mysqld]
    innodb_file_per_table=1
    open_files_limit=5294
    log-slow-queries=/var/lib/mysql/slow.log
    interactive_timeout=300
    query_cache_size=50M
    skip-bdb
    query_cache_type=1
    tmp_table_size=64M
    max_heap_table_size=64M
    thread_cache_size=4
    table_cache=600
    wait_timeout=300

    I get:
    Waiting for mysql to restart....................................................................finished.


    mysql has failed, please contact the sysadmin (result was "mysql is not running").

    Can you help me?
     
  3. mehnihma

    mehnihma Well-Known Member

    Joined:
    Dec 15, 2012
    Messages:
    57
    Likes Received:
    1
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    This is making problems:
    skip-bdb


    Now I have this:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=6020
    log-slow-queries=/var/lib/mysql/slow.log
    interactive_timeout=300
    query_cache_size=50M
    query_cache_type=1
    query_cache_type=1
    tmp_table_size=64M
    max_heap_table_size=64M
    thread_cache_size=4
    table_cache=600
    wait_timeout=300
    query_cache_limit=64M


    And the result:
    >> MySQLTuner 1.2.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.5.35-cll
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 4M (Tables: 50)
    [--] Data in InnoDB tables: 64M (Tables: 1364)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 24

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5s (188 q [37.600 qps], 10 conn, TX: 2M, RX: 86K)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 502.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 917.2M (5% of installed RAM)
    [OK] Slow queries: 0% (0/188)
    [OK] Highest usage of available connections: 2% (4/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.2M
    [OK] Query cache efficiency: 24.4% (38 cached / 156 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 7% (1 temp sorts / 13 sorts)
    [!!] Temporary tables created on disk: 31% (6 on disk / 19 total)
    [OK] Thread cache hit rate: 60% (4 created / 10 connections)
    [OK] Table cache hit rate: 84% (39 open / 46 opened)
    [OK] Open file limit used: 0% (21/6K)
    [OK] Table locks acquired immediately: 100% (167 immediate / 167 locks)
    [OK] InnoDB data size / buffer pool: 64.6M/128.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    it's good configuration, don't worry
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    I just wanted to note that this option is not available in your version of MySQL (5.5) in-case others come across this thread with a similar error message.

    Thank you.
     
Loading...

Share This Page