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
Here is MY.CNF
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
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 <[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: 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)
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-timeouttable_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
Last edited by a moderator:
