takachan's blog

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

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

AWS 技術

はじめに

インフラ監視について今一度再学習している過程で、MySQLに関しての内容において面白そうな一文がありました。

“接続数は、MySQLサーバに接続している接続数を監視します。接続数は、ご自身がmy.cnfに定義した値を上限としてください。また、あらかじめチューニングを施し、接続数が増えることによるメモリ不足が起こらないように備えてください。チューニングにあたっては、MySQLに関する書籍を参照するか、mysqltuner.plを活用するとよいでしょう。”

mysqltuner.plってなんだ？

参考リンク

github.com

MySQLTunerでMySQLのチューニングを診断する方法│株式会社イー･エージェンシー

構成

ケチケチして最小の構成で実施します。すべて東京リージョンで構築します。

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

RDSのパラメータグループはdefault.mysql5.6を使用します。

EC2事前準備

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

RDS接続確認

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
owners.

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

mysql>

MySQLTuner実行結果

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 -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[!!] 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以下に表示されている内容を確認し、 必要な部分は対応するとよいとのことです。

上記結果を参考に、以下のRDSのパラメータグループを変更しました。

  • table_open_cache
  • query_cache_limit
  • query_cache_type

まとめ

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

RDSはよく出来ているサービスなので皆さん是非使用しない手は無いですよ！！

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