MariaDB チューニング

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/good-dba/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