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.

Optimization 10.1.17-MariaDB with MySQLTuner 1.6.18

Discussion in 'Workarounds and Optimization' started by 3awh, Sep 12, 2016.

Tags:
  1. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    Please help not sure how to move on from here seems like it says the same thing each time.
    I have adjusted a lot and let it run for a long while on this result so it can get a better report, and here is the results of MySQLTuner 1.6.18


    Code:
     >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.1.17-MariaDB
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in MyISAM tables: 191M (Tables: 581)
    [--] Data in InnoDB tables: 1G (Tables: 3048)
    [OK] Total fragmented tables: 0
    -------- Security Recommendations ------------------------------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [--] There are 612 basic passwords in the list.
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 12d 6h 30m 38s (116M q [109.754 qps], 802K conn, TX: 527G, RX: 32G)
    [--] Reads / Writes: 75% / 25%
    [--] Binary logging is disabled
    [--] Physical Memory     : 31.3G
    [--] Max MySQL memory    : 27.8G
    [--] Other process memory: 1.5G
    [--] Total buffers: 26.4G global + 2.9M per thread (500 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [!!] Maximum reached memory usage: 26.7G (85.25% of installed RAM)
    [!!] Maximum possible memory usage: 27.8G (88.94% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 2% (2M/116M)
    [OK] Highest usage of available connections: 18% (93/500)
    [OK] Aborted connections: 0.06%  (513/802359)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 43.4% (62M cached / 144M selects)
    [!!] Query cache prunes per day: 497764
    [OK] Sorts requiring temporary tables: 0% (54 temp sorts / 3M sorts)
    [!!] Joins performed without indexes: 11737
    [!!] Temporary tables created on disk: 63% (4M on disk / 6M total)
    [OK] Thread cache hit rate: 99% (133 created / 802K connections)
    [!!] Table cache hit rate: 0% (300 open / 743K opened)
    [OK] Open file limit used: 1% (141/10K)
    [OK] Table locks acquired immediately: 99% (56M immediate / 56M locks)
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 8 thread(s).
    [--] Using default value is good enough for your version (10.1.17-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 20.0% (6M used / 33M cache)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/32.5M
    [OK] Read Key buffer hit rate: 98.8% (133M cached / 1M reads)
    [!!] Write Key buffer hit rate: 56.7% (21M cached / 9M writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 97.0% (142M cached / 4M reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 26.0G/1.3G
    [!!] InnoDB buffer pool instances: 8
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 100.00% (3825353586 hits/ 3825389008 total)
    [!!] InnoDB Write Log efficiency: 45.77% (9506050 hits/ 20770097 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 11264047 writes)
    -------- TokuDB Metrics ----------------------------------------------------------------------------
    [--] TokuDB is disabled.
    -------- Galera Metrics ----------------------------------------------------------------------------
    [--] Galera is disabled.
    -------- Replication Metrics -----------------------------------------------------------------------
    [--] Galera Synchronous replication: NO
    [--] No replication slave(s) for this server.
    [--] This is a standalone server.
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        Reduce your overall MySQL memory footprint for system stability
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        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 which have no LIMIT clause
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Beware that open_files_limit (10000) variable
        should be greater than table_open_cache ( 300)
    Variables to adjust:
        query_cache_size (> 100M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 150M)
        max_heap_table_size (> 150M)
        table_open_cache (> 300)
        innodb_buffer_pool_instances(=26)
    
    here is the my.cnf

    Code:
    # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
    # Configuration name 3Aliens_New_SSD_Server generated for - Removed - at 2016-08-19 22:59:00
    
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid-file                       = /var/lib/mysql/kc29.example.com.pid
    
    # MyISAM #
    key-buffer-size                = 32M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    sql-mode                       = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    sysdate-is-now                 = 1
    innodb                         = FORCE
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    #log-bin                        = /var/lib/mysql/mysql-bin
    #expire-logs-days               = 14
    #sync-binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp-table-size                 = 150M
    max-heap-table-size            = 150M
    query-cache-type               = 1
    query-cache-limit              = 256K
    query-cache-min-res-unit       = 2k
    query-cache-size               = 100M
    max-connections                = 500
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 300
    
    # INNODB #
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 512M
    innodb-flush-log-at-trx-commit = 1
    innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 26G
    
    # LOGGING #
    log-error                      = /var/log/mysql/mysql_error.log
    log-queries-not-using-indexes  = 1
    slow-query-log                 = 1
    slow-query-log-file            = /var/log/mysql/mysql_slow.log
    max_allowed_packet=268435456
    open_files_limit=10000
    
    thanks
    Mitch
     
    #1 3awh, Sep 12, 2016
    Last edited by a moderator: Sep 12, 2016
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,451
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  3. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    Seriously!
    I looked and really need some help on this as the other threads did not help
    I bumped because my thread is being ignored
    I guess I will have to hire someone thanks cPanel Sr. Product Evangelist
    Great support here people

    You know and another thing I looked at all the other threads and everyone else got fast support from cpanel support people and your first person replying to this thread and a support person at that denied helping

    Seriously!
    Have you read my thread where it says
    # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
    I looked and really need some help on this as the other threads did not help
    I bumped because my thread is being ignored
    Thanks for nothing
    Unbelievable
    and your a cPanel Sr. Product Evangelist
    I'm done with Cpanel
     
    #3 3awh, Sep 15, 2016
    Last edited by a moderator: Sep 15, 2016
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,451
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    You never mentioned what issues you were having that you really need help with. What purpose would suggesting changes for an unknown issue solve?

    The Recommendations section of your test result are more helpful than any suggestions from me.
     
  5. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    I have explained it in my first post.
    I have done adjustments to
    query_cache_size (> 100M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 150M)
    max_heap_table_size (> 150M)
    table_open_cache (> 300)
    innodb_buffer_pool_instances(=26)
    and they keep saying the same thing
    If you don't want to help me then stop replying to this thread
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page