>> MySQLTuner 2.3.2
* Jean-Marie Renouard <jmrenouard@gmail.com>
* Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[!!] The --forceswap option is required for remote connections
[!!] Assuming Swap size is 1Gb for simplify remote connection usage
[OK] Logged in using credentials passed on the command line
[--] Assuming 32000 MB of physical memory
[--] Assuming 1024 MB of swap space
[--] Skipping architecture check on remote host
[--] Using default 64 bits as target architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 60.9G (Tables: 235)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 8.0.34
[--] Skipping system checks on remote host
[--] Skipping error log files checks on remote host
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17h 24m 21s (41M q [655.661 qps], 41K conn, TX: 75G, RX: 5G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: OFF_PERMISSIVE)
[--] Physical Memory : 31.2G
[--] Max MySQL memory : 27.6G
[--] Other process memory: 0B
[--] Total buffers: 23.0G global + 1.8M per thread (2628 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 23.5G (75.22% of installed RAM)
[!!] Maximum possible memory usage: 27.6G (88.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (5K/41M)
[OK] Highest usage of available connections: 10% (269/2628)
[OK] Aborted connections: 0.00% (0/41203)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (12K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 273375
[!!] Temporary tables created on disk: 29% (806K on disk / 2M total)
[OK] Thread cache hit rate: 96% (1K created / 41K connections)
[OK] Table cache hit rate: 99% (48M hits / 48M requests)
[OK] table_definition_cache (2000) is greater than number of tables (582)
[OK] Open file limit used: 0% (17/1M)
[OK] Table locks acquired immediately: 100% (9K immediate / 9K locks)
[OK] Binlog cache memory access: 99.96% (164274 Memory / 164347 Total)
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--] +-- Total MyISAM Tables : 0
[--] +-- Total MyISAM indexes : 2.0K
[--] +-- KB Size :16.0M
[--] +-- KB Used Size :2.9M
[--] +-- KB used :18.2%
[--] +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--] +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 23.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 23.0G / 60.9G
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (1.08695652173913%): 100.0M / 23.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk: 184 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (43736578372 hits / 43737439972 total)
[!!] InnoDB Write Log efficiency: 70.89% (1561418 hits / 2202445 total)
[OK] InnoDB log waits: 0.00% (0 waits / 641027 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
MySQL was started within the 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=ON
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
skip-name-resolve=ON
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
performance_schema=ON
innodb_buffer_pool_size (>= 60.9G) if possible.
innodb_redo_log_capacity should be (=5G) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.
innodb_buffer_pool_instances(=23)