Optimizing MariaDB need help with my.cnf config with mysqltuner results

Discussion in 'Server Operation' started by 3awh, Jul 26, 2016.

  1. 3awh

    3awh New Member

    I'm not very familiar with mysql so far I have tweaked the my.cnf config a few times and now I'm left with this the last two times I checked it. Don't know what to do to clear it up.
    I have a Dual Intel Xeon 5650 Dedicated Server, with 23 Megs of Ram. running MariaDB 10.1 and Cpanel
    here is my 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 = 300
    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 = 300M
    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
    
    # 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
    
    
    Here is the results of MySQLTuner
    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: 209M (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: 1d 19h 2m 26s (12M q [78.940 qps], 79K conn, TX: 66G, RX: 3G)
    [--] Reads / Writes: 44% / 56%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Physical Memory     : 23.5G
    [--] Max MySQL memory    : 21.6G
    [--] Other process memory: 1.2G
    [--] 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/12M)
    [OK] Highest usage of available connections: 25% (38/151)
    [OK] Aborted connections: 0.06%  (48/79023)
    [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
    [OK] Query cache efficiency: 38.9% (3M cached / 8M selects)
    [!!] Query cache prunes per day: 179646
    [OK] Sorts requiring temporary tables: 0% (164 temp sorts / 185K sorts)
    [!!] Joins performed without indexes: 1479
    [!!] Temporary tables created on disk: 71% (268K on disk / 378K total)
    [!!] Table cache hit rate: 0% (300 open / 110K opened)
    [OK] Open file limit used: 1% (140/13K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
    [OK] Binlog cache memory access: 99.83% ( 893556 Memory / 895057 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.6% (99M used / 536M cache)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/32.6M
    [OK] Read Key buffer hit rate: 99.2% (47M cached / 361K reads)
    [!!] Write Key buffer hit rate: 57.8% (8M cached / 3M writes)
    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/1B
    [!!] Aria pagecache hit rate: 94.8% (5M cached / 267K 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: 43.24% (42511 used/ 98303 total)
    [OK] InnoDB Read buffer efficiency: 99.99% (355149086 hits/ 355179588 total)
    [!!] InnoDB Write Log efficiency: 61.63% (1466584 hits/ 2379654 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 913070 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)
    
    The last adjustment, I have adjusted table_open_cache from 128 to 300 and got the same results
    Not sure how to fix the results.
    thanks
    Mitch
     

Share This Page