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.

Need Help Optimizing 10.1.16-MariaDB

Discussion in 'Workarounds and Optimization' started by 3awh, Jul 22, 2016.

Tags:
  1. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    I used MySQLTuner 1.6.14 - Major Hayden
    I have MySQL version 10.1.16-MariaDB
    Server restarted Exactly 24 hours 1 minute ago
    Server OS is CENTOS 6.8 x86_64 standard with Of Course Cpanel WHM 56.0 (build 28)


    Here is the MySQLTuner Report

    Code:
     >>  MySQLTuner 1.6.14 - 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.16-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: 580M (Tables: 1697)
    [--] Data in InnoDB tables: 1G (Tables: 4037)
    [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 605 basic passwords in the list.
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 1d 0h 1m 33s (4M q [51.770 qps], 46K conn, TX: 37G, RX: 1G)
    [--] Reads / Writes: 77% / 23%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 23.5G
    [--] Max MySQL memory    : 21.8G
    [--] Other process memory: 1.4G
    [--] Total buffers: 2.7G global + 129.5M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 6.6G (28.32% of installed RAM)
    [!!] Maximum possible memory usage: 21.8G (93.03% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/4M)
    [OK] Highest usage of available connections: 20% (31/151)
    [OK] Aborted connections: 0.03%  (13/46314)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 39.9% (2M cached / 5M selects)
    [!!] Query cache prunes per day: 225437
    [OK] Sorts requiring temporary tables: 0% (117 temp sorts / 127K sorts)
    [!!] Joins performed without indexes: 872
    [!!] Temporary tables created on disk: 68% (168K on disk / 246K total)
    [!!] Table cache hit rate: 0% (128 open / 214K opened)
    [OK] Open file limit used: 0% (51/13K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] Binlog cache memory access: 99.92% ( 621360 Memory / 621886 Total)
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 16 thread(s).
    [--] Using default value is good enough for your version (10.1.16-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (97M used / 536M cache)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/101.4M
    [OK] Read Key buffer hit rate: 96.2% (2M cached / 103K reads)
    [!!] Write Key buffer hit rate: 50.4% (278K cached / 138K writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 96.7% (5M cached / 167K reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 1.5G/1.4G
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 40.87% (40181 used/ 98303 total)
    [OK] InnoDB Read buffer efficiency: 99.98% (171893893 hits/ 171927388 total)
    [!!] InnoDB Write Log efficiency: 59.17% (896411 hits/ 1514925 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 618514 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
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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 (13000) variable
        should be greater than table_open_cache ( 128)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 100M)
        join_buffer_size (> 128.0M, or always use indexes with joins)
        table_open_cache (> 128)
    
    Here is MY.CNF

    Code:
    # The following options will be passed to all MariaDB clients
    [client]
    #password    = your_password
    port        = 3306
    socket        = /var/lib/mysql/mysql.sock
    
    # Here follows entries for some specific programs
    
    # The MariaDB server
    [mysqld]
    port        = 3306
    socket        = /var/lib/mysql/mysql.sock
    skip-external-locking
    key_buffer_size = 512M
    max_allowed_packet=268435456
    table_open_cache = 128
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    query_cache_type=1
    query_cache_limit = 256K
    query_cache_min_res_unit = 2k
    query_cache_size = 100M
    tmp_table_size = 500M
    max_heap_table_size = 500M
    join_buffer_size = 128M
    
    default-storage-engine=InnoDB
    
    # Point the following paths to different dedicated disks
    #tmpdir        = /tmp/
    
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking
    
    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin
    
    # binary logging format - mixed recommended
    binlog_format=mixed
    
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id    = 1
    
    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /var/lib/mysql
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 1536M
    innodb_additional_mem_pool_size = 2M
    # Set .._log_file_size to 25 % of buffer pool size
    innodb_log_file_size = 100M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    innodb_buffer_pool_instances=1
    
    open_files_limit=13000
    innodb_file_per_table=1
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [myisamchk]
    key_buffer_size = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    
    I Adjusted setting the yesterday and this is the results and not sure what to do from here
    table_open_cache (> 128) - I just adjusted that to 256 Not sure what to do with the rest.
    thank You for your help in advance
    Mitch
     
    #1 3awh, Jul 22, 2016
    Last edited by a moderator: Jul 22, 2016
  2. twhiting9275

    twhiting9275 Well-Known Member

    Joined:
    Sep 26, 2002
    Messages:
    538
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    Take a look at the Percona optimization wizard . This will generate configuration for MySQL, Maria, Percona, and works pretty well from what I've seen myself
     
  3. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    Thanks It does say
    Thanks for your suggestion though
    I'm so close to optimizing my.cnf above. if anyone can help me with that please
     
  4. twhiting9275

    twhiting9275 Well-Known Member

    Joined:
    Sep 26, 2002
    Messages:
    538
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    That's just a warning. I've used it to config an existing server before.
    Just make sure to copy your config file so you can revert to it if necessary.
     
  5. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    The my.cnf page it gave me has - in stead of _ does it matter

    tmp-table-size = 32M
    instead of
    tmp_table_size = 32M
     
  6. twhiting9275

    twhiting9275 Well-Known Member

    Joined:
    Sep 26, 2002
    Messages:
    538
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    that should be tmp_table_size. Not sure why it gave you tmp-table-size, really odd
     
  7. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    I didn't take a chance and converted it to the underscore. I will try to find a contact or feedback form and let the site know. I hope the config was correct.
    I did change two values thought they were too low this is what it gave me

    Code:
    # Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
    # Configuration name 3Aliens generated for EMAIL REMOVED at 2016-07-23 01:44:06
    
    [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/mysql.pid
    
    # MyISAM #
    key_buffer_size                = 32M
    myisam_recover                 = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet             = 16M
    max_connect_errors             = 1000000
    
    # 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                 = 32M
    max_heap_table_size            = 32M
    query_cache_type               = 1
    query_cache_size               = 8M
    max_connections                = 500
    thread_cache_size              = 50
    open_files_limit               = 65535
    table_definition_cache         = 4096
    table_open_cache               = 128
    
    # 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        = 3G
    
    # LOGGING #
    log_error                      = /var/lib/mysql/mysql-error.log
    log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql-slow.log
    
    
    Anything you see I don't have or should have from my original? Or I shouldn't have?
     
  8. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    Had to put back my old my.cnf on first post Server load was too high with the new one, could not do anything so back to my original question can someone please help me tune the first posts my.cnf file
    thanks
    Mitch
     
  9. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,456
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    How did you make out with the original recommendations you posted above?

    Code:
    -------- 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
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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 (13000) variable
        should be greater than table_open_cache ( 128)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 100M)
        join_buffer_size (> 128.0M, or always use indexes with joins)
        table_open_cache (> 128)
    
     
  10. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    It didn't run 24 hours I changed it to the tools.percona.com my.cnf and then before 24 hours was up the server had a heart attack and I reverted back to the one on the first post now its running with a low cpu. I did change the value of table_open_cache to 256
    i will get back to you in 20 hours.

    i just tested this is what it said
    Code:
     >>  MySQLTuner 1.6.14 - 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.16-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: 187M (Tables: 646)
    [--] Data in InnoDB tables: 1G (Tables: 3355)
    [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 605 basic passwords in the list.
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 5h 13m 49s (841K q [44.692 qps], 9K conn, TX: 7G, RX: 490M)
    [--] Reads / Writes: 85% / 15%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 23.5G
    [--] Max MySQL memory    : 21.4G
    [--] Other process memory: 858.6M
    [--] Total buffers: 2.3G global + 129.5M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 6.7G (28.51% of installed RAM)
    [!!] Maximum possible memory usage: 21.4G (91.07% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/841K)
    [OK] Highest usage of available connections: 23% (35/151)
    [OK] Aborted connections: 0.06%  (5/9027)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (11 temp sorts / 148K sorts)
    [!!] Joins performed without indexes: 1144
    [!!] Temporary tables created on disk: 78% (100K on disk / 128K total)
    [!!] Table cache hit rate: 0% (64 open / 50K opened)
    [OK] Open file limit used: 0% (12/13K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] Binlog cache memory access: 99.49% ( 67770 Memory / 68115 Total)
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 16 thread(s).
    [--] Using default value is good enough for your version (10.1.16-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.3% (98M used / 536M cache)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/27.1M
    [OK] Read Key buffer hit rate: 96.1% (887K cached / 34K reads)
    [!!] Write Key buffer hit rate: 62.1% (63K cached / 24K writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [!!] Aria pagecache hit rate: 94.5% (1M cached / 100K reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 1.5G/1.2G
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 50.01% (49164 used/ 98303 total)
    [OK] InnoDB Read buffer efficiency: 99.98% (108002814 hits/ 108025823 total)
    [OK] InnoDB Write log efficiency: 93.23% (1024955 hits/ 1099413 total)
    [!!] InnoDB log waits: 0.00% (1 waits / 74458 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
        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 (13000) variable
        should be greater than table_open_cache ( 64)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_type (=1)
        join_buffer_size (> 128.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
        table_open_cache (> 64)
        innodb_log_buffer_size (>= 8M)
    
     
  11. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    Forget my last post I just checked the my.cnf on the server because I remembered I changed
    query_cache_type (=1) the other day.
    I just put the correct one on the server.
    I guess I will be back in 24 hours and let you know the results of the new config
    Man I hate when I mess up
    Mitch
     
  12. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    1 Day 10 Mins and I'm back with the results

    Code:
     >>  MySQLTuner 1.6.14 - 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.16-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: 189M (Tables: 646)
    [--] Data in InnoDB tables: 1G (Tables: 3355)
    [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 605 basic passwords in the list.
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 1d 0h 10m 11s (4M q [50.769 qps], 47K conn, TX: 41G, RX: 1011M)
    [--] Reads / Writes: 78% / 22%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 23.5G
    [--] Max MySQL memory    : 21.8G
    [--] Other process memory: 1.5G
    [--] Total buffers: 2.7G global + 129.5M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 10.1G (42.88% of installed RAM)
    [!!] Maximum possible memory usage: 21.8G (93.03% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/4M)
    [OK] Highest usage of available connections: 38% (58/151)
    [OK] Aborted connections: 0.06%  (27/47315)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 38.6% (2M cached / 5M selects)
    [!!] Query cache prunes per day: 278028
    [OK] Sorts requiring temporary tables: 0% (494 temp sorts / 147K sorts)
    [!!] Joins performed without indexes: 799
    [!!] Temporary tables created on disk: 72% (185K on disk / 254K total)
    [!!] Table cache hit rate: 0% (256 open / 129K opened)
    [OK] Open file limit used: 2% (362/13K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] Binlog cache memory access: 99.81% ( 520382 Memory / 521349 Total)
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 16 thread(s).
    [--] Using default value is good enough for your version (10.1.16-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (97M used / 536M cache)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/27.4M
    [OK] Read Key buffer hit rate: 97.7% (3M cached / 71K reads)
    [!!] Write Key buffer hit rate: 53.9% (335K cached / 154K writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 96.1% (4M cached / 183K reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 1.5G/1.2G
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 52.01% (51124 used/ 98303 total)
    [OK] InnoDB Read buffer efficiency: 99.99% (342038386 hits/ 342069239 total)
    [!!] InnoDB Write Log efficiency: 64.98% (973560 hits/ 1498190 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 524630 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
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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 (13000) variable
        should be greater than table_open_cache ( 256)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 100M)
        join_buffer_size (> 128.0M, or always use indexes with joins)
        table_open_cache (> 256)
    
    I have Searched or I should say I have tried to search for each item in the suggestion list and I get other peoples results and not any explanation on what to change.

    EDITED: Did some changes

    Don't know what to do with these
    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


    Temporary table size is already large - reduce result set size

    I changed it to tmp_table_size = 300M


    Not sure what to do with this

    Increase table_open_cache gradually to avoid file descriptor limits

    Not sure what to do with the rest

    I have added logging Figured it was a good idea for optimizing
    When it says
    query_cache_size (> 100M)
    does it want you to increase or decrease it?
    I'm thinking > = Great then (increase it)?

    thanks
    Mitch
     
    #12 3awh, Jul 24, 2016
    Last edited: Jul 24, 2016
  13. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    I know its only 22 hours but close to the 24 hrs and its the same recommendations What should I do? I'm at a Loss

    Code:
    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        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
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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 (13000) variable
        should be greater than table_open_cache ( 300)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 100M)
        join_buffer_size (> 128.0M, or always use indexes with joins)
        table_open_cache (> 300)
    
     
  14. 3awh

    3awh Member

    Joined:
    Sep 1, 2008
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    WWW
    Its been up for 2d 7h 47m 29s
    Same results
    Code:
     >>  MySQLTuner 1.6.14 - 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.16-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: 208M (Tables: 646)
    [--] Data in InnoDB tables: 1G (Tables: 3014)
    [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 605 basic passwords in the list.
    -------- CVE Security Recommendations --------------------------------------------------------------
    [OK] NO SECURITY CVE FOUND FOR YOUR VERSION
    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 2d 7h 47m 29s (15M q [79.427 qps], 106K conn, TX: 92G, RX: 4G)
    [--] Reads / Writes: 48% / 52%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 23.5G
    [--] Max MySQL memory    : 21.6G
    [--] Other process memory: 929.8M
    [--] Total buffers: 2.5G global + 129.5M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 7.3G (31.26% of installed RAM)
    [!!] Maximum possible memory usage: 21.6G (92.20% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/15M)
    [OK] Highest usage of available connections: 25% (38/151)
    [OK] Aborted connections: 0.06%  (62/106809)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 39.1% (4M cached / 11M selects)
    [!!] Query cache prunes per day: 237438
    [OK] Sorts requiring temporary tables: 0% (224 temp sorts / 263K sorts)
    [!!] Joins performed without indexes: 1949
    [!!] Temporary tables created on disk: 71% (376K on disk / 526K total)
    [!!] Table cache hit rate: 0% (300 open / 161K opened)
    [OK] Open file limit used: 2% (304/13K)
    [OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
    [OK] Binlog cache memory access: 99.83% ( 1322804 Memory / 1325120 Total)
    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance schema is disabled.
    -------- ThreadPool Metrics ------------------------------------------------------------------------
    [--] ThreadPool stat is enabled.
    [--] Thread Pool Size: 16 thread(s).
    [--] Using default value is good enough for your version (10.1.16-MariaDB)
    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (97M used / 536M cache)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/32.4M
    [OK] Read Key buffer hit rate: 99.2% (55M cached / 446K reads)
    [!!] Write Key buffer hit rate: 57.6% (9M cached / 4M writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [OK] Aria pagecache hit rate: 95.0% (7M cached / 374K reads)
    -------- InnoDB Metrics ----------------------------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 1.5G/1.1G
    [OK] InnoDB buffer pool instances: 1
    [!!] InnoDB Used buffer: 44.82% (44062 used/ 98303 total)
    [OK] InnoDB Read buffer efficiency: 99.99% (503584056 hits/ 503614885 total)
    [!!] InnoDB Write Log efficiency: 62.78% (2229213 hits/ 3550944 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 1321731 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
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        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 (13000) variable
        should be greater than table_open_cache ( 300)
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 100M)
        join_buffer_size (> 128.0M, or always use indexes with joins)
        table_open_cache (> 300)
    
     
Loading...

Share This Page