MySQLTuner adalah skrip yang ditulis dalam Perl yang membantu kita menganalisis dan mengkonfigurasi server database MySQL/MariaDB untuk mendapatkan peningkatan kinerja, stabilitas, dan lingkungan server yang seimbang.
Namun pada kondisi tertentu kita tidak bisa mengeksekusi perintah mysqltuner. Error tersebut paling sering terjadi pada sistem Debian/Ubuntu, dimana kita benar-benar tidak dapat menjalankan perintah mysqltuner.
Pesan error yang paling umum saat kita menjalankan mysqltuner adalah sebagai berikut:
root@dev:~# mysqltuner >> MySQLTuner 1.7.17 - Major Hayden>> 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 [!!] Attempted to use login credentials from Debian maintenance account, but they failed.
Bagaimana memperbaikinya?
1. Nah, untuk memperbaiki kesalahan ini pertama-tama kita perlu mendownload mysqltuner.pl dengan perintah sederhana.
$ wget http://mysqltuner.pl/ -O mysqltuner.pl --no-check-certificate
2. Kemudian silahkan atur izin/permission pada file mysqltuner.pl yang baru saja kita download.
$ chmod +x mysqltuner.pl
3. Terakhir, untuk memperbaiki script mysqltuner yang error, Anda bisa menjalankan mysqltuner dengan perintah berikut.
$ perl mysqltuner.pl --user root --pass 'passwordroot'
NOTE: Ubah ‘passwordroot’ dengan kata sandi root MySQL/MariaDB Anda
Anda dapat menggunakan perintah di atas setiap kali Anda ingin menjalankan mysqltuner, daripada hanya menjalankan mysqltuner atau perintah ./mysqltuner.pl.
root@dev:~# perl mysqltuner.pl --user root --pass 'passwordroot' >> MySQLTuner 2.5.3 * Jean-Marie Renouard* Major Hayden >> 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 [OK] Logged in using credentials passed on the command line [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 27.0G (Tables: 282) [--] Data in MEMORY tables: 0B (Tables: 1) [OK] Total fragmented tables: 0 [OK] Currently running supported MySQL version 10.5.23-MariaDB-0+deb11u1 -------- Log file Recommendations ------------------------------------------------------------------ [!!] Log file doesn't exist -------- 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 [--] There are 620 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 3m 39s (1K q [8.388 qps], 30 conn, TX: 149M, RX: 3M) [--] Reads / Writes: 95% / 5% [--] Binary logging is disabled [--] Physical Memory : 259.6G [--] Max MySQL memory : 861.2M [--] Other process memory: 0B [--] Total buffers: 417.0M global + 2.9M per thread (151 max threads) [--] Performance_schema Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 422.9M (0.16% of installed RAM) [OK] Maximum possible memory usage: 861.2M (0.32% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/1K) [OK] Highest usage of available connections: 1% (2/151) [!!] Aborted connections: 10.00% (3/30) [!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 348 sorts) [OK] No joins without indexes [OK] Temporary tables created on disk: 21% (50 on disk / 237 total) [OK] Thread cache hit rate: 93% (2 created / 30 connections) [OK] Table cache hit rate: 98% (1K hits / 2K requests) [!!] table_definition_cache (400) is less than number of tables (476) [OK] Open file limit used: 0% (28/32K) [OK] Table locks acquired immediately: 100% (53 immediate / 53 locks) -------- Performance schema ------------------------------------------------------------------------ [!!] Performance_schema should be activated. [--] Sys schema is not installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [--] General MyIsam metrics: [--] +-- Total MyISAM Tables : 0 [--] +-- Total MyISAM indexes : 0B [--] +-- KB Size :128.0M [--] +-- KB Used Size :23.3M [--] +-- 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 ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G ) [!!] InnoDB buffer pool / data size: 128.0M / 27.0G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25% [--] 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 [OK] InnoDB Read buffer efficiency: 99.06% (19493727 hits / 19678771 total) [!!] InnoDB Write Log efficiency: 84.59% (439 hits / 519 total) [OK] InnoDB log waits: 0.00% (0 waits / 80 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K [!!] Aria pagecache hit rate: 84.6% (408 cached / 63 reads) -------- 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: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL was started within the last 24 hours: recommendations may be inaccurate Reduce or eliminate unclosed connections and network issues Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON Performance schema should be activated for better diagnostics Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time Variables to adjust: table_open_cache (> 2000) table_definition_cache (400) > 476 or -1 (autosizing if supported) innodb_log_buffer_size (> 16M)
Untuk Variable-variable setting yang bisa ditingkatkan, MySQLTuner akan memberikan Suggestion/adjustment untuk setting performance MySQL/MariaDB anda