-------- Performance Metrics -------------------------------------------------
[--] Up for: 63d 14h 49m 31s (1B q [193.199 qps], 801K conn, TX: 13696B, RX: 1038B)
[--] Reads / Writes: 49% / 51%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Total buffers: 293.6G global + 32.9M per thread (10000 max threads)
[OK] Maximum reached memory usage: 351.1G (69.57% of installed RAM)
[!!] Maximum possible memory usage: 614.6G (121.79% of installed RAM)
[OK] Slow queries: 0% (729/1B)
[OK] Highest usage of available connections: 17% (1792/10000)
[OK] Aborted connections: 2.26% (18129/801691)
[!!] Query cache efficiency: 13.9% (53M cached / 387M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3M sorts)
[!!] Temporary tables created on disk: 37% (66K on disk / 179K total)
[OK] Thread cache hit rate: 99% (1K created / 801K connections)
[!!] Table cache hit rate: 1% (101 open / 6K opened)
[OK] Open file limit used: 0% (51/65K)
[OK] Table locks acquired immediately: 100% (2B immediate / 2B locks)
[OK] Binlog cache memory access: 100.00% ( 342684253 Memory / 342684253 Total)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/97.0K
[!!] Read Key buffer hit rate: 74.0% (5K cached / 1K reads)
[!!] Write Key buffer hit rate: 37.5% (8 cached / 5 writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 293.0G/143.0G
[!!] InnoDB buffer pool instances: 8
[OK] InnoDB Used buffer: 99.96% (19191800 used/ 19199992 total)
[OK] InnoDB Read buffer efficiency: 99.94% (147479420791 hits/ 147570407764 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1720111156 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[OK] This replication slave is running with the read_only option enabled.
[OK] This replication slave is up to date with master.
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
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 (65535) variable
should be greater than table_open_cache ( 5000)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 1G, or use smaller result sets)
tmp_table_size (> 56M)
max_heap_table_size (> 56M)
table_open_cache (> 5000)
innodb_buffer_pool_instances(=64)
### INNODB settings
expire_logs_days=6
innodb_buffer_pool_size=300000M
innodb_file_per_table=1
innodb_log_file_size=20M
innodb_log_buffer_size=256M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=8
default_storage_engine=InnoDB
innodb_log_files_in_group=2
innodb_use_native_aio=0
innodb_flush_method = O_DIRECT
#### MyISAM
key_buffer_size=32M
myisam_recover=FORCE,BACKUP
#### Connections
connect_timeout=60
max_connections=10000
max_connect_errors=5000
wait_timeout=3600
interactive_timeout=3600
net_read_timeout=600
net_write_timeout=600
#### Caches and buffers
table_open_cache=5000
thread_cache_size=5000
query_cache_type=1
query_cache_limit=1512M
query_cache_size=256M
join_buffer_size=32M
tmp_table_size=56M
max_heap_table_size=56M
#### Limits
open_files_limit=65535
#### Misc
# enforce usage of new password hashing method
old_passwords=0
symbolic-links=0
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
tmpdir=/var/tmp
Temporary tables created on disk: 37% (68K on disk / 184K total)=> is having
tmpdir=/var/tmp/instead of
/tmpautomatically causing the temp tables to be created in on disk ?
ibdata1is huge up to
31Gon the slaves comparing to the master(
13G)==> why this difference??