takachan's blog

地方のエンジニアが、まずやってみる(Bias For Action)!!をモットーに気持ちを綴るブログです。

MySQLTunerを使ってAmazon RDS for MySQLのチューニング診断してみました。










  • EC2 t2.nano
  • RDS for MySQL db.t2.micro



sudo yum -y update
sudo yum -y mysql
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
chmod +x mysqltuner.pl


mysql -h xxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u rdstest -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.6.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



perl mysqltuner.pl --host xxxxxx.ap-northeast-1.rds.amazonaws.com --user rdstest --password xxxx --forcemem 256

[--] Performing tests on xxxxxx.ap-northeast-1.rds.amazonaws.com:3306
Please enter your MySQL administrative login: rdstest
Please enter your MySQL administrative password:  >>  MySQLTuner 1.6.2 - Major Hayden <major@mhtx.net>
 >>  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
[--] Assuming 256 MB of physical memory
[!!] Assuming 0 MB of swap space (use --forceswap to specify)
[OK] Currently running supported MySQL version 5.6.23-log

-------- Storage Engine Statistics -------------------------------------------
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'rdstest@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  -------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13m 59s (1K q [1.782 qps], 26 conn, TX: 93K, RX: 157K)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 646.0M global + 1.5M per thread (66 max threads)
[!!] Maximum reached memory usage: 649.0M (253.52% of installed RAM)
[!!] Maximum possible memory usage: 745.0M (291.02% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 3% (2/66)
[!!] Aborted connections: 3.85%  (1/26)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 3% (3 on disk / 100 total)
[OK] Thread cache hit rate: 92% (2 created / 26 connections)
[!!] Table cache hit rate: 14% (45 open / 303 opened)
[OK] Open file limit used: 0% (48/65K)
[OK] Table locks acquired immediately: 100% (424 immediate / 424 locks)
[OK] Binlog cache memory access: 100.00% ( 9 Memory / 9 Total)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.4M
[OK] Read Key buffer hit rate: 95.4% (438 cached / 20 reads)
[!!] Write Key buffer hit rate: 88.3% (384 cached / 45 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Reduce or eliminate unclosed connections and network issues
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (65535) variable
    should be greater than table_open_cache ( 2000)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=1)
    table_open_cache (> 2000)

[!!]と出ている行が警告で、チューニングの改善が必要な箇所が表示されます。 InnoDB関連はMyISAMを選択しない限り無視してOKです。

Recommendations以下に表示されている内容を確認し、 必要な部分は対応するとよいとのことです。


  • table_open_cache
  • query_cache_limit
  • query_cache_type


MySQLTunerを使って普段使用しているRDSのパラメータを診断してみました。 encodingやtimezoneは変更していましたが、診断結果によってチューニングできる箇所が分かると嬉しいですね。


試した方は最後に起動したEC2とRDSの削除をお忘れなくm( )m