How to use MySQLTuner in Mac

All MAMP discussions around troubleshooting and anything related to MAMP. Be as detailed as possible here when posting an issue.
Post Reply
mampsupportmod
Site Admin
Posts: 156
Joined: Wed Jan 20, 2021 3:06 am

How to use MySQLTuner in Mac

Post by mampsupportmod »

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

Compatibility:

MySQL 8.0 (partial support, password checks don't work)
Percona Server 8.0 (partial support, password checks don't work)
MySQL 5.7 (full support)
Percona Server 5.7 (full support)
MariaDB 10.6 (full support)
MariaDB 10.5 (full support)
MariaDB 10.4 (full support)
MariaDB 10.3 (full support)
Galera replication (full support)
Percona XtraDB cluster (full support)



Note: Ensure MySQL is running (via MAMP) before running MySQLTuner. It is recommended to have MySQL running for many hours or days to capture the best tuning metrics.


1. Download the latest version of MySQLTuner here.


2. Unzip MySQLTuner-perl-master.zip.


3. Launch Terminal and cd to MySQLTuner-perl-master (wherever you downloaded it)


4. If you try to run perl mysqltuner.pl you get the following error:

Code: Select all

MySQLTuner-perl-master % perl mysqltuner.pl
 >>  MySQLTuner 1.9.8
	 * 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
[!!] Couldn't find mysqladmin/mariadb-admin in your $PATH. Is MySQL installed?

5. Add the mysqladmin MAMP Pro path to your Terminal session:

Code: Select all

PATH=$PATH:/Applications/MAMP/Library/bin/

6. Run MySQLTuner:

Code: Select all

perl mysqltuner.pl


Output may look something like this. Keep in mind at the bottom of the output Variables to adjust: will be your recommendations for my.cnf.

Code: Select all

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login:
Please enter your MySQL administrative password: Argument "" isn't numeric in numeric ge (>=) at mysqltuner.pl line 327, <STDIN>
	line 2 (#1)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.
    
    Note that for the Inf and NaN (infinity and not-a-number) the
    definition of "numeric" is somewhat unusual: the strings themselves
    (like "Inf") are considered numeric, and anything following them is
    considered non-numeric.
    
[OK] Currently running supported MySQL version 5.7.34
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /Applications/MAMP/logs/mysql_error.log exists
[--] Log file: /Applications/MAMP/logs/mysql_error.log(984K)
[OK] Log file /Applications/MAMP/logs/mysql_error.log is not empty
[OK] Log file /Applications/MAMP/logs/mysql_error.log is smaller than 32 Mb
[OK] Log file /Applications/MAMP/logs/mysql_error.log is readable.
[!!] /Applications/MAMP/logs/mysql_error.log contains 5923 warning(s).
[OK] /Applications/MAMP/logs/mysql_error.log doesn't contain any error.
[--] 7 start(s) detected in /Applications/MAMP/logs/mysql_error.log
[--] 1) 2022-06-05T21:39:14.589299-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 2) 2022-06-05T09:56:37.014400-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 3) 2022-06-05T09:40:54.856694-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 4) 2022-06-05T09:18:56.186725-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 5) 2022-05-18T10:49:10.175706-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 6) 2022-05-18T10:46:36.729738-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 7) 2022-05-18T10:28:32.714573-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
[--] 6 shutdown(s) detected in /Applications/MAMP/logs/mysql_error.log
[--] 1) 2022-06-05T10:23:45.902313-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
[--] 2) 2022-06-05T09:51:49.786976-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
[--] 3) 2022-06-05T09:40:53.477642-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
[--] 4) 2022-05-18T10:50:53.836547-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
[--] 5) 2022-05-18T10:48:32.527117-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
[--] 6) 2022-05-18T10:46:35.893737-06:00 0 [Note] /Applications/MAMP/Library/bin/mysqld: Shutdown complete
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MEMORY tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 17.2M (Tables: 150)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'root'@'127.0.0.1' has user name as password.
[!!] User 'root'@'::1' has user name as password.
[!!] User 'root'@'localhost' has user name as password.
[--] There are 620 basic passwords in the list.
[!!] User 'root@localhost' is using weak password: root in a lower, upper or capitalize derivative version.
[!!] User 'mamp@localhost' is using weak password: root in a lower, upper or capitalize derivative version.
[!!] User 'root@127.0.0.1' is using weak password: root in a lower, upper or capitalize derivative version.
[!!] User 'root@::1' is using weak password: root in a lower, upper or capitalize derivative version.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17s (113 q [6.647 qps], 55 conn, TX: 67K, RX: 12K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 16.0G
[--] Max MySQL memory    : 146.9G
[--] Other process memory: 0B
[--] Total buffers: 109.0M global + 501.1M per thread (300 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 610.1M (3.72% of installed RAM)
[!!] Maximum possible memory usage: 146.9G (918.25% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/113)
[OK] Highest usage of available connections: 0% (1/300)
[OK] Aborted connections: 1.82%  (1/55)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 14 total)
[OK] Thread cache hit rate: 98% (1 created / 55 connections)
[!!] Table cache hit rate: 1% (3 hits / 151 requests)
[OK] table_definition_cache(650) is upper than number of tables(431)
[OK] Open file limit used: 0% (27/19K)
[OK] Table locks acquired immediately: 100% (117 immediate / 117 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (11.7M used / 64.0M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/43.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 12.0M/17.2M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (800 %): 48.0M * 2/12.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 82.85% (2136 hits/ 2578 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Check warning line(s) in /Applications/MAMP/logs/mysql_error.log file
    Set up a Secure Password for 'root'@'127.0.0.1' user: SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('secure_password');
    Set up a Secure Password for 'root'@'::1' user: SET PASSWORD FOR 'root'@'::1' = PASSWORD('secure_password');
    Set up a Secure Password for 'root'@'localhost' user: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secure_password');
    Set up a Secure Password for root@localhost user: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secure_password');
    Set up a Secure Password for mamp@localhost user: SET PASSWORD FOR 'mamp'@'localhost' = PASSWORD('secure_password');
    Set up a Secure Password for root@127.0.0.1 user: SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('secure_password');
    Set up a Secure Password for root@::1 user: SET PASSWORD FOR 'root'@'::1' = PASSWORD('secure_password');
    4 user(s) used basic or weak password from basic dictionary.
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (19216) variable 
    should be greater than table_open_cache (500)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    table_open_cache (> 500)
    key_buffer_size (~ 12M)
    innodb_buffer_pool_size (>= 17.2M) if possible.
    innodb_log_file_size should be (=1M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
MAMP Support Forums is an unofficial support forum covering MAMP & MAMP Pro solution stacks.
Post Reply