781 文字
4 分
久しぶりにmysqltuner
概要
- mysqltunerは、MySQLの統計情報を使ってパフォーマンスチューニングのアドバイスをしてくれる。
- 最近はCVE（セキュリティ情報）も教えてくれるみたい。
- 調べた背景は、64GBのRAMが載ったApacheやbatchプログラムが共存したEC2インスタンスに48GBも
innodb_buffer_poolを盲目的に割り当てたベンダーが居て、それってどうなのよ？と思った。ベンダーに丸投げって怖いな。- 案の定、「2周間後にメモリが100％になりそうになってOOM Killerが発動されてmysqlが落ちるよ」と3回ぐらい警告したら設定値を変更してくれた。
インストール
cd /tmpwget https://github.com/major/MySQLTuner-perl/zipball/masterunzip mastercd major-MySQLTuner-perl-b828a6e/./mysqltuner.pl実行
自分のサーバはどうなんだろうと思って実行してみたけど、まぁ、適当に設定している割には良い感じ。
[OK] Currently running supported MySQL version 5.7.18-log[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------[--] Log file: /usr/local/var/mysql/teraren.local.err(0B)[!!] Log file /usr/local/var/mysql/teraren.local.err doesn't exist[!!] Log file /usr/local/var/mysql/teraren.local.err isn't readable.
-------- Storage Engine Statistics -----------------------------------------------------------------[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA[--] Data in InnoDB tables: 15G (Tables: 490)[--] Data in MyISAM tables: 60M (Tables: 161)[!!] Total fragmented tables: 1
-------- Security Recommendations ------------------------------------------------------------------[OK] There are no anonymous accounts for any database users[OK] All database users have passwords assigned[!!] User 'tel@%' hasn't specific host restriction.[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------[--] Up for: 60d 2h 32m 18s (124M q [23.963 qps], 1M conn, TX: 409G, RX: 16G)[--] Reads / Writes: 91% / 9%[--] Binary logging is enabled (GTID MODE: OFF)[--] Physical Memory : 8.0G[--] Max MySQL memory : 496.0M[--] Other process memory: 6.3G[--] Total buffers: 424.0M global + 1.1M per thread (64 max threads)[--] P_S Max memory usage: 72B[--] Galera GCache Max memory usage: 0B[OK] Maximum reached memory usage: 430.8M (5.26% of installed RAM)[OK] Maximum possible memory usage: 496.0M (6.05% of installed RAM)[OK] Overall possible memory usage with other process is compatible with memory available[OK] Slow queries: 0% (66/124M)[OK] Highest usage of available connections: 9% (6/64)[OK] Aborted connections: 0.00% (4/1550671)[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance[!!] Query cache may be disabled by default due to mutex contention.[OK] Query cache efficiency: 74.9% (81M cached / 109M selects)[!!] Query cache prunes per day: 100750[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 3M sorts)[OK] No joins without indexes[!!] Temporary tables created on disk: 30% (2M on disk / 6M total)[OK] Thread cache hit rate: 99% (6 created / 1M connections)[!!] Table cache hit rate: 19% (1K open / 9K opened)[OK] Open file limit used: 0% (323/65K)[OK] Table locks acquired immediately: 99% (985K immediate / 986K locks)[OK] Binlog cache memory access: 99.93% (2455619 Memory / 2457387 Total)
-------- Performance schema ------------------------------------------------------------------------[--] Memory used by P_S: 72B[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------[!!] Key buffer used: 19.9% (1M used / 8M cache)[OK] Key buffer size / total MyISAM indexes: 8.0M/4.2M[OK] Read Key buffer hit rate: 100.0% (8M cached / 134 reads)[OK] Write Key buffer hit rate: 99.3% (462K cached / 458K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------[--] InnoDB is enabled.[--] InnoDB Thread Concurrency: 0[OK] InnoDB File per table is activated[!!] InnoDB buffer pool / data size: 256.0M/15.5G[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M * 2/256.0M should be equal 25%[OK] InnoDB buffer pool instances: 1[--] Number of InnoDB Buffer Pool Chunk : 2 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.91% (53923725522 hits/ 53974926093 total)[!!] InnoDB Write Log efficiency: 66.99% (7965306 hits/ 11890918 total)[OK] InnoDB log waits: 0.00% (0 waits / 3925612 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------[--] TokuDB is disabled.
久しぶりにmysqltuner
https://blog.teraren.com/posts/mysqltuner/