Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

mariadb optimization - mysqltuner

Discussion in 'Workarounds and Optimization' started by Anthony Parsons, Sep 7, 2016.

Tags:
  1. Anthony Parsons

    Anthony Parsons Active Member

    Joined:
    Aug 4, 2016
    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    I just ran mysqltuner on my server, using mariadb 10.1, and it gave me the following, which I don't totally understand as to what I should adjust / add.

    Code:
    >>  MySQLTuner 1.6.18 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >>  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: 536M (Tables: 840)
    [--] Data in InnoDB tables: 3G (Tables: 418)
    [--] Data in MEMORY tables: 1M (Tables: 11)
    [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 is no basic password file list!
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 4h 53m 14s (1M q [108.469 qps], 95K conn, TX: 11G, RX: 946M)
    [--] Reads / Writes: 74% / 26%
    [--] Binary logging is disabled
    [--] Physical Memory     : 11.7G
    [--] Max MySQL memory    : 1.1G
    [--] Other process memory: 526.4M
    [--] Total buffers: 434.0M global + 2.9M per thread (250 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 521.2M (4.34% of installed RAM)
    [OK] Maximum possible memory usage: 1.1G (9.66% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (1/1M)
    [OK] Highest usage of available connections: 12% (30/250)
    [OK] Aborted connections: 0.00%  (0/95886)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 37.4% (579K cached / 1M selects)
    [!!] Query cache prunes per day: 314770
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 37K sorts)
    [OK] No joins without indexes
    [OK] Temporary tables created on disk: 22% (4K on disk / 20K total)
    [OK] Thread cache hit rate: 83% (15K created / 95K connections)
    [!!] Table cache hit rate: 3% (256 open / 6K opened)
    [OK] Open file limit used: 1% (159/10K)
    [OK] Table locks acquired immediately: 99% (983K immediate / 983K locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 6 thread(s).
    [--] Using default value is good enough for your version (10.1.17-MariaDB)
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 19.9% (26M used / 134M cache)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/71.9M
    [OK] Read Key buffer hit rate: 98.6% (1M cached / 19K reads)
    [!!] Write Key buffer hit rate: 48.0% (176K cached / 91K writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 99.2% (546K cached / 4K reads)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 128.0M/3.6G
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 99.06% (103447548 hits/ 104430221 total)
    [!!] InnoDB Write Log efficiency: 84.23% (1081725 hits/ 1284181 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 202456 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:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        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 ( 256)
    Variables to adjust:
        query_cache_size (> 10M)
        table_open_cache (> 256)
        innodb_buffer_pool_size (>= 3G) if possible.
        innodb_buffer_pool_instances (=1)
    [root@whm ~]#
    
    ----------------------------------------------------------------------------------------------------
    
    
    My my.cnf looks like:

    Code:
      GNU nano 2.3.1                                 File: /etc/my.cnf                                                                        
    
    [mysqld]
    bind-address=127.0.0.1
    
    performance-schema=0
    skip-networking
    slow-query-log
    local-infile = 0
    
    max_connections=250
    table_open_cache=256
    max_delayed_threads=20
    max_tmp_tables=32
    query_cache_type=1
    query_cache_size=10M
    thread_cache_size=4
    innodb_file_per_table=1
    innodb_use_native_aio=0
    default-storage-engine=MyISAM
    max_allowed_packet=419430400
    open_files_limit=10000
    innodb_buffer_pool_size=134217728
    
    
     
    #1 Anthony Parsons, Sep 7, 2016
    Last edited by a moderator: Sep 7, 2016
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,396
    Likes Received:
    1,857
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    I recommend letting MariaDB run for at least 24 hours before running the tuner to ensure the results are accurate before making changes to your /etc/my.cnf file.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    Anthony Parsons likes this.
  3. Anthony Parsons

    Anthony Parsons Active Member

    Joined:
    Aug 4, 2016
    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Thanks @cPanelMichael, yer I had to restart it as I increased the query_cache_size from 4M to 10M to fix a whole lot of slow queries that were happening. I basically had hundreds of queries happening with little in cache, which was causing users a whole bunch of problems in live chat and submitting posts in the forum.
     
  4. Anthony Parsons

    Anthony Parsons Active Member

    Joined:
    Aug 4, 2016
    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Ok, updated test... what does it all mean in relation to what I should be adjusting in my.cnf?

    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: 536M (Tables: 840)
    [--] Data in InnoDB tables: 3G (Tables: 418)
    [--] Data in MEMORY tables: 2M (Tables: 11)
    [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 is no basic password file list!
    
    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 1d 1h 50m 26s (10M q [115.518 qps], 525K conn, TX: 59G, RX: 5G)
    [--] Reads / Writes: 73% / 27%
    [--] Binary logging is disabled
    [--] Physical Memory     : 11.7G
    [--] Max MySQL memory    : 1.1G
    [--] Other process memory: 612.0M
    [--] Total buffers: 434.0M global + 2.9M per thread (250 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 623.0M (5.19% of installed RAM)
    [OK] Maximum possible memory usage: 1.1G (9.66% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (9/10M)
    [OK] Highest usage of available connections: 26% (65/250)
    [OK] Aborted connections: 0.00%  (0/525485)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 36.9% (3M cached / 8M selects)
    [!!] Query cache prunes per day: 356455
    [OK] Sorts requiring temporary tables: 0% (17 temp sorts / 205K sorts)
    [OK] No joins without indexes
    [OK] Temporary tables created on disk: 24% (29K on disk / 117K total)
    [OK] Thread cache hit rate: 82% (92K created / 525K connections)
    [!!] Table cache hit rate: 0% (256 open / 48K opened)
    [OK] Open file limit used: 1% (188/10K)
    [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
    
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 6 thread(s).
    [--] Using default value is good enough for your version (10.1.17-MariaDB)
    
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 20.4% (27M used / 134M cache)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/72.2M
    [OK] Read Key buffer hit rate: 98.4% (7M cached / 119K reads)
    [!!] Write Key buffer hit rate: 48.5% (953K cached / 491K writes)
    
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 96.5% (1M cached / 58K reads)
    
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [!!] InnoDB buffer pool / data size: 128.0M/3.6G
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [--] InnoDB Buffer Pool Chunk Size not used or defined in your version
    [OK] InnoDB Read buffer efficiency: 99.43% (810119305 hits/ 814776441 total)
    [!!] InnoDB Write Log efficiency: 84.87% (5969612 hits/ 7034158 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 1064546 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:
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        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 ( 256)
    Variables to adjust:
        query_cache_size (> 10M)
        table_open_cache (> 256)
        innodb_buffer_pool_size (>= 3G) if possible.
        innodb_buffer_pool_instances (=1)
    
    My current my.cnf:

    Code:
      GNU nano 2.3.1                                 File: /etc/my.cnf                                                                       
    
    [mysqld]
    bind-address=127.0.0.1
    
    performance-schema=0
    skip-networking
    slow-query-log
    local-infile = 0
    
    max_connections=250
    table_open_cache=256
    max_delayed_threads=20
    max_tmp_tables=32
    query_cache_type=1
    query_cache_size=10M
    thread_cache_size=4
    innodb_file_per_table=1
    innodb_use_native_aio=0
    default-storage-engine=MyISAM
    max_allowed_packet=419430400
    open_files_limit=10000
    innodb_buffer_pool_size=134217728
     
  5. Anthony Parsons

    Anthony Parsons Active Member

    Joined:
    Aug 4, 2016
    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    8
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    I have only made two existing changes to my.cnf, one was the max_connections, as I was getting connection errors logged in XF ACP, so I upped it from 200 to 250, however, what I believe the issue really was, was the next change I made, query cache size, from default 4M to 10M, which then radically dropped the connections and increased the caching of queries, thus subsequently fixed a whole bunch of issues users were getting with slow queries and taking up-to 30 seconds for something to happen.
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,396
    Likes Received:
    1,857
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    I'm happy to see you were able to make a change that addressed some issues. Thank you for updating us with the outcome.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    Anthony Parsons likes this.
  7. WebHostPro

    WebHostPro Well-Known Member
    PartnerNOC

    Joined:
    Jul 28, 2002
    Messages:
    1,651
    Likes Received:
    15
    Trophy Points:
    318
    Location:
    LA, Costa RIca
    cPanel Access Level:
    Root Administrator
    Twitter:
    We upgraded to MariaDB recently and was wondering if the tweaks you made helps MySQL run better on a high loaded shared server. If so, can you please post your end result for the my.cnf file.

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice