• パフォーマンスチューニング

MySQLTuner のダウンロードと実行。

$ 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
$ sudo perl mysqltuner.pl

実行結果

 >>  MySQLTuner 1.6.14 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.0.25-MariaDB-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 9G (Tables: 12406)
[OK] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 0h 0m 12s (6M q [79.633 qps], 73K conn, TX: 4G, RX: 1G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 11.5G
[--] Max MySQL memory    : 725.8M
[--] Other process memory: 1.2G
[--] Total buffers: 320.0M global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 336.1M (2.87% of installed RAM)
[OK] Maximum possible memory usage: 725.8M (6.19% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (12/6M)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.01%  (8/73662)
[!!] 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] Sorts requiring temporary tables: 0% (9 temp sorts / 75K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 22% (2K on disk / 12K total)
[!!] Table cache hit rate: 0% (400 open / 40K opened)
[OK] Open file limit used: 0% (4/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.0.25-MariaDB-0ubuntu0.16.04.1)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/121.0K
[!!] Read Key buffer hit rate: 62.2% (37 cached / 14 reads)
[!!] Write Key buffer hit rate: 0.0% (17 cached / 17 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 100.0% (10M cached / 1K reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/9.7G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.37% (399078673 hits/ 401590849 total)
[!!] InnoDB Write Log efficiency: 85.39% (755431 hits/ 884673 total)
[OK] InnoDB log waits: 0.00% (0 waits / 129242 writes)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
@mhtx.net>

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    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 (1024) variable 
    should be greater than table_open_cache ( 400)
Variables to adjust:
    query_cache_type (=0)
    table_open_cache (> 400)
    innodb_buffer_pool_size (>= 9G) if possible.
    innodb_buffer_pool_instances (=1)

Recommendations に表示された設定値を確認し、

$ sudo mysql -u root -e "show variables;" | grep 'query_cache_type\|table_open_cache\|innodb_buffer_pool_size\|innodb_buffer_pool_instances'
innodb_buffer_pool_instances    8
innodb_buffer_pool_size 134217728
query_cache_type        ON
table_open_cache        400

Recommendations に従い対応します。

$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
$ sudo systemctl restart mysql

• DB 最適化

$ sudo mysqlcheck -u root --optimize --all-databases

17G -> 12G