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 <[email protected]> >> 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
