mysqltuner adalah skrip penyetelan MySQL berkinerja tinggi yang ditulis dalam perl yang akan memberi Anda gambaran tentang kesehatan server MySQL. Berdasarkan statistik yang dikumpulkan, rekomendasi spesifik akan diberikan yang akan meningkatkan efisiensi dan kinerja server MySQL.
1. Instal MySQLTuner
apt-get instal mysqltuner
2. Jalankan MySQLTuner
mysqltuner
Masukkan login dan kata sandi administratif MySQL/MariaDB Anda
Please enter your MySQL administrative login: Please enter your MySQL administrative password:
Berikut contoh hasilnya:
>> MySQLTuner 1.9.9
* 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 from Debian maintenance account.
[!!] Your MySQL version 10.11.6-MariaDB-0+deb12u1 is EOL software! Upgrade soon!
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in MEMORY tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 2.8G (Tables: 82)
[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
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 22m 40s (146K q [107.512 qps], 2K conn, TX: 3G, RX: 23M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 7.8G
[--] Max MySQL memory : 3.6G
[--] Other process memory: 0B
[--] Total buffers: 800.0M global + 18.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (16.49% of installed RAM)
[OK] Maximum possible memory usage: 3.6G (45.98% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (19/146K)
[OK] Highest usage of available connections: 17% (27/151)
[OK] Aborted connections: 0.00% (0/2022)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 29K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 23% (3K on disk / 16K total)
[OK] Thread cache hit rate: 98% (27 created / 2K connections)
[OK] Table cache hit rate: 99% (165K hits / 167K requests)
[OK] table_definition_cache (400) is greater than number of tables (376)
[OK] Open file limit used: 0% (59/32K)
[OK] Table locks acquired immediately: 100% (809 immediate / 809 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 ----------------------------------------------------------------------------
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 512.0M/2.8G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 1/512.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 64 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: 0% (-1251381 hits/ 0 total)
[!!] InnoDB Write Log efficiency: 88.25% (10857 hits/ 12303 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1446 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 87.4% (29K cached / 3K 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:
You are using n unsupported version for production environments
Upgrade as soon as possible to a supported version !
MySQL was started within the last 24 hours - recommendations may be inaccurate
Variables to adjust:
innodb_buffer_pool_size (>= 2.8G) if possible.
3. Sesuaikan file konfigurasi MySQL/MariaDB Anda
Untuk MySQL(/etc/mysql/my.cnf), untuk Mariadb(/etc/mysql/mariadb.conf.d/50-server.cnf) sesuai rekomendasi. Jangan menambah atau mengurangi nilainya terlalu banyak karena dapat berdampak negatif pada server. Jika ini adalah server produksi, cukup lakukan perubahan kecil setiap kali dan uji lagi beberapa jam/hari kemudian dan sesuaikan nilainya lagi jika diperlukan. Mungkin diperlukan waktu beberapa hari untuk mengetahui nilai terbaik untuk server Anda.
4. Restart MySQL/MariaDB
/etc/init.d/mysql restart
atau
/etc/init.d/mariadb restart
Selamat Mencoba…
Reporter: Adi Prabowo
