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.

Optimize MySQL Help

Discussion in 'Workarounds and Optimization' started by Filipe Barreto, Jan 14, 2015.

  1. Filipe Barreto

    Filipe Barreto Registered

    Joined:
    Jan 14, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Juiz de Fora, Brazil
    cPanel Access Level:
    Website Owner
    Hello ,

    i need help to oprimiz my MySQL

    this is my server info :

    Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz (8 cores)
    Hard Disk - SSD
    28GB RAM
    Ubuntu 14.04 - 64-Bit

    I use MARIADB 10 and my bank is full innodb.

    My.cnf

    Code:
    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    
    [mysqld_safe]
    nice = 0
    socket = /var/run/mysqld/mysqld.sock
    
    [mysqld]
    basedir = /usr
    bind-address = 0.0.0.0
    binlog_format = ROW
    character_set_server = utf8
    collation_server = utf8_general_ci
    datadir = /var/lib/mysql
    default-storage-engine = InnoDB
    expire_logs_days = 30
    innodb_autoinc_lock_mode = 2
    innodb_buffer_pool_size = 17G
    innodb_doublewrite = 1
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 0
    innodb_lock_wait_timeout = 600
    innodb_locks_unsafe_for_binlog = 1
    innodb_stats_on_metadata = 0
    innodb_log_buffer_size = 256M
    innodb_fast_shutdown=0
    
    max-connect-errors=1000000
    skip-name-resolve
    sync_binlog   = 1
    key_buffer_size = 2G
    lc-messages-dir = /usr/share/mysql
    lock_wait_timeout = 600
    max_allowed_packet = 128M
    max_binlog_size = 128M
    max_connections = 350
    myisam-recover = BACKUP
    myisam_sort_buffer_size = 64M
    net_buffer_length = 8K
    open-files-limit = 65535
    pid-file = /var/run/mysqld/mysqld.pid
    port = 3306
    query_cache_limit = 16M
    query_cache_size = 16M
    read_buffer_size = 16M
    read_rnd_buffer_size = 16M
    skip-external-locking
    socket = /var/run/mysqld/mysqld.sock
    sort_buffer_size = 16M
    table_cache = 2M
    table_definition_cache = 4096
    table_open_cache = 4096
    thread_cache_size = 8
    thread_concurrency = 8
    tmpdir = /tmp
    user = mysql
    
    [mysqldump]
    max_allowed_packet = 16M
    quick
    quote-names
    
    [mysql]
    
    [isamchk]
    !includedir /etc/mysql/conf.d/
    key_buffer = 256M
    read_buffer = 16M
    sort_buffer_size = 256M
    write_buffer = 16M
    
    ###########LOGS SERVER ##########################
    log-error = /var/log/mysql/error.log
    general_log_file = /var/log/mysql/mysql.log
    max_binlog_size         = 100M
    ###################################################
    
    ############SLOW_QUERY################################
    slow_query_log = 0
    slow_query_log = 0
    slow_query_log_file     = /var/log/mysql/slow.log
    long_query_time = 10
    log_slow_verbosity      = query_plan
    ########################################################

    Mytuner

    Code:
    [OK] Logged in using credentials from debian maintenance account.
    [OK] Currently running supported MySQL version 5.5.40-MariaDB-1~precise-wsrep
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM +SPHINX
    [--] Data in InnoDB tables: 3G (Tables: 186)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 52
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 8m 48s (5K q [1.302 qps], 221 conn, TX: 54M, RX: 495K)
    [--] Reads / Writes: 84% / 16%
    [--] Total buffers: 17.4G global + 48.4M per thread (350 max threads)
    [!!] Maximum possible memory usage: 33.9G (123% of installed RAM)
    [OK] Slow queries: 4% (247/5K)
    [OK] Highest usage of available connections: 2% (7/350)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/101.0K
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 138 sorts)
    [!!] Joins performed without indexes: 127
    [OK] Temporary tables created on disk: 1% (70 on disk / 4K total)
    [OK] Thread cache hit rate: 95% (9 created / 221 connections)
    [OK] Table cache hit rate: 51% (217 open / 418 opened)
    [OK] Open file limit used: 0% (50/65K)
    [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
    [OK] InnoDB buffer pool / data size: 17.0G/3.8G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)


    i have questions the configuration off the my.ini .... i search in internet and not searched the internet more not found a very enlightening stuff.... some indication for reading?
     
    #1 Filipe Barreto, Jan 14, 2015
    Last edited by a moderator: Jan 14, 2015
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. Filipe Barreto

    Filipe Barreto Registered

    Joined:
    Jan 14, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Juiz de Fora, Brazil
    cPanel Access Level:
    Website Owner
    Code:
     >>  MySQLTuner 1.1.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
    [!!] Your MySQL version 10.0.14-MariaDB-1~trusty-wsrep is EOL software!  Upgrade soon!
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in CSV tables: 0B (Tables: 2)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in InnoDB tables: 1G (Tables: 196)
    [--] Data in MyISAM tables: 1G (Tables: 125)
    [!!] Total fragmented tables: 36
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    Use of uninitialized value in addition (+) at /usr/bin/mysqltuner line 567 (#1)
        (W uninitialized) An undefined value was used as if it were already
        defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
        To suppress this warning assign a defined value to your variables.
    
        To help you figure out what was undefined, perl will try to tell you
        the name of the variable (if any) that was undefined.  In some cases
        it cannot do this, so it also tells you what operation you used the
        undefined value in.  Note, however, that perl optimizes your program
        anid the operation displayed in the warning may not necessarily appear
        literally in your program.  For example, "that $foo" is usually
        optimized into "that " . $foo, and the warning will refer to the
        concatenation (.) operator, even though there is no . in
        your program.
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7d 12h 8m 0s (18M q [28.455 qps], 985K conn, TX: 58B, RX: 13B)
    [--] Reads / Writes: 88% / 12%
    [--] Total buffers: 4.3G global + 416.0K per thread (350 max threads)
    [OK] Maximum possible memory usage: 4.4G (32% of installed RAM)
    [OK] Slow queries: 5% (1M/18M)
    [!!] Highest connection usage: 91%  (319/350)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/2.8G
    [OK] Key buffer hit rate: 100.0% (45B cached / 4M reads)
    [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 2M sorts)
    [!!] Joins performed without indexes: 1437892
    [OK] Temporary tables created on disk: 8% (400K on disk / 4M total)
    [OK] Thread cache hit rate: 91% (83K created / 985K connections)
    [!!] Table cache hit rate: 4% (893 open / 19K opened)
    Use of uninitialized value $myvar{"table_cache"} in concatenation (.) or string
            at /usr/bin/mysqltuner line 851 (#1)
    [OK] Open file limit used: 0% (460/65K)
    [OK] Table locks acquired immediately: 98% (58M immediate / 59M locks)
    [!!] Connections aborted: 31%
    [OK] InnoDB data size / buffer pool: 1.4G/2.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        Upgrade MySQL to version 4+ to utilize query caching
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        max_connections (> 350)
        wait_timeout (< 180)
        interactive_timeout (< 180)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> )
     
Loading...

Share This Page