MariaDB チューニング
Nid: 1182
1 . 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.7.14 - 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 [OK] Logged in using credentials from Debian maintenance account. [OK] Currently running supported MySQL version 10.1.34-MariaDB-0ubuntu0.18.04.1 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysql/error.log(0B) [OK] Log file /var/log/mysql/error.log exists [OK] Log file /var/log/mysql/error.log is readable. [!!] Log file /var/log/mysql/error.log is empty [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb [OK] /var/log/mysql/error.log doesn't contain any warning. [OK] /var/log/mysql/error.log doesn't contain any error. [--] 0 start(s) detected in /var/log/mysql/error.log [--] 0 shutdown(s) detected in /var/log/mysql/error.log -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 52.8G (Tables: 12322) [--] Data in MyISAM tables: 1.0K (Tables: 1) [!!] Total fragmented tables: 1 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 618 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 6d 1h 47m 43s (23M q [44.909 qps], 103K conn, TX: 64G, RX: 41G) [--] Reads / Writes: 86% / 14% [--] Binary logging is disabled [--] Physical Memory : 31.3G [--] Max MySQL memory : 752.7M [--] Other process memory: 0B [--] Total buffers: 328.0M global + 2.8M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 361.8M (1.13% of installed RAM) [OK] Maximum possible memory usage: 752.7M (2.35% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/23M) [OK] Highest usage of available connections: 7% (12/151) [!!] Aborted connections: 3.08% (3180/103223) [!!] 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: 41.8% (14M cached / 34M selects) [!!] Query cache prunes per day: 623063 [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 1M sorts) [!!] Joins performed without indexes: 4292 [!!] Temporary tables created on disk: 26% (67K on disk / 254K total) [OK] Thread cache hit rate: 99% (24 created / 103K connections) [!!] Table cache hit rate: 2% (2K open / 77K opened) [OK] Open file limit used: 0% (11/16K) [OK] Table locks acquired immediately: 99% (10M immediate / 10M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 16 thread(s). [--] Using default value is good enough for your version (10.1.34-MariaDB-0ubuntu0.18.04.1) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (3M used / 16M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/124.0K [!!] Read Key buffer hit rate: 90.2% (488 cached / 48 reads) [OK] Write Key buffer hit rate: 100.0% (224 cached / 224 writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 128.0M/52.8G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25% [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1). [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 99.88% (20240549402 hits/ 20264794714 total) [OK] InnoDB Write log efficiency: 98.60% (193524782 hits/ 196267459 total) [OK] InnoDB log waits: 0.00% (0 waits / 2742677 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 100.0% (626M cached / 66K 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: STATEMENT [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE `db_octaviadata`.`com`; -- can free 156 MB
Total freed space after theses OPTIMIZE TABLE : 156 Mb
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (16364) variable
should be greater than table_open_cache (2000)
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 16M)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 2000)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 52.8G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances (=1)
Recommendations に表示された設定値を確認し、
$ sudo mysql -u root -e "show variables;" | grep 'query_cache_size\|query_cache_type\|join_buffer_size\|tmp_table_size\|max_heap_table_size\|table_open_cache\|performance_schema\s\+\|innodb_buffer_pool_size\|innodb_log_file_size\|innodb_buffer_pool_instances' innodb_buffer_pool_instances 8 innodb_buffer_pool_size 134217728 innodb_log_file_size 50331648 join_buffer_size 262144 max_heap_table_size 16777216 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off performance_schema OFF query_cache_size 16777216 query_cache_type ON table_open_cache 2000 tmp_table_size 16777216
Recommendations に従い対応します。
2 . The MariaDB sys schema
$ git clone https://github.com/FromDual/mariadb-sys.git $ sudo mysql < ./mariadb-sys/mariadb_sys_install.sql
$ sudo systemctl stop mysql
3 . 設定変更
$ sudo vi /etc/mysql/my.cnf
[mysqld] # Performance Schema performance_schema=ON performance-schema-instrument='stage/%=ON' performance-schema-consumer-events-stages-current=ON performance-schema-consumer-events-stages-history=ON performance-schema-consumer-events-stages-history-long=ON # sqltuner innodb_buffer_pool_instances=16 innodb_buffer_pool_size=24G join_buffer_size = 256K performance_schema=ON query_cache_type=0 query_cache_size=0 innodb_log_file_size=2G
$ sudo systemctl start mysql
- DB 最適化
$ sudo mysqlcheck -u root --optimize --all-databases
4 . MySQLTuner 再実行
期間を置いて、再実行。チューニング。
$ sudo perl mysqltuner.pl