ツナワタリマイライフ

日常ネタから技術ネタ、音楽ネタまで何でも書きます。

MariaDBの5.5と10.2でチューニング＆ベンチマーク比較(1) - mysqltuner編

Infrustructure

はじめに

多くは語りませんが仕事でMariaDBを触っており、その一環です。

環境はCloud Garageさん！ありがとうございます！！！

take-she12.hatenablog.com

準備

CloudGarageでCentOS6.8でVM作成。

repoファイルを置く。centos7用に使っていたものを適当に修正。

[root@mariadb5-5-45 ~]# cat /etc/yum.repos.d/MariaDB.repo 
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5.45/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

10系の場合はバージョンをs/5.5.45/10.2.8/g してください。

そしてyum install MariaDBする。

[root@mariadb5-5-45 ~]# rpm -qa | grep Maria
MariaDB-compat-5.5.45-1.el6.x86_64
MariaDB-common-5.5.45-1.el6.x86_64
MariaDB-client-5.5.45-1.el6.x86_64
MariaDB-server-5.5.45-1.el6.x86_64
[root@mariadb10-2-8 ~]# rpm -qa | grep Maria
MariaDB-compat-10.2.8-1.el6.x86_64
MariaDB-common-10.2.8-1.el6.x86_64
MariaDB-client-10.2.8-1.el6.x86_64
MariaDB-server-10.2.8-1.el6.x86_64

sysbenchもyumでinstall。もともとepelが入ってた。ないひとはyum install epel-releaseしてからyum install sysbenchしてください。

[root@mariadb5-5-45 ~]# sysbench --version
sysbench 1.0.9

基本性能

まぁ同じスペックでVM作ってるから同じになるのは当たり前っちゃ当たり前なんですが、念のため両ノードでスペック差がないか、CPU/メモリ/IO観点で調査した。結果は割愛するが、ほぼ同じになった。

方法は以下を参照しました。ありがとうございました。

sysbenchでCPU/メモリ/ディスクのパフォーマンスを計測する。

デフォルト設定でのmysqlベンチマーク

さて次はmysqlのベンチマークです。

まず、sysbenchでテストするためのテストユーザとテストテーブルを作成します。

[root@mariadb5-5-45 ~]# mysqladmin -u root create sbtest
[root@mariadb5-5-45 ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.45-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> GRANT ALL ON sbtest.* TO 'sbtest'@'localhost' IDENTIFIED BY 'sbtest';
Query OK, 0 rows affected (0.01 sec)

テーブルを準備します。

[root@mariadb5-5-45 ~]# sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --db-driver=mysql --oltp-table-size=1000000 --mysql-host=localhost --mysql-password=sbtest prepare
sysbench 1.0.9 (using system LuaJIT 2.0.4)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...

さてテスト実行です。

[root@mariadb5-5-45 ~]# sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=1000000 --db-driver=mysql --mysql-host=localhost --mysql-db=sbtest --mysql-user=sbtest --mysql-password=sbtest --time=60 --events=0 --threads=1 --oltp_read_only=off run >> /tmp/sysbench_oltp_read_write_1.log

read/writeのテストをしました。thread数1での結果は。。。

[root@mariadb5-5-45 ~]# grep transactions /tmp/sysbench_oltp_read_write_1.log 
    transactions:                        19970  (332.80 per sec.)
[root@mariadb10-2-8 ~]# grep transactions /tmp/sysbench_oltp_read_write_1.log 
    transactions:                        15385  (256.39 per sec.)

transactionsをみると10.2.8のほうが性能は落ちるようです。

さて、thread数を増やして実行してみます。

結果は以下のようになりました。試験時間は60秒で、数値はtransactions。

threads 5.5.45 10.2.8
1 21367 15911
2 23426 18765
4 23251 20641
8 24237 21442
16 24242 21712
32 24360 21208
64 26085 21627
128 24532 21869

clientの問題か、これ以上のスレッド数ではエラーを吐いてしまった。

[root@mariadb5-5-45 ~]# ./sysbench.sh 
Error in my_thread_global_end(): 83 threads didn't exit
Error in my_thread_global_end(): 57 threads didn't exit
Error in my_thread_global_end(): 103 threads didn't exit

さて、今回の結果は、スレッド数を増やすと差は縮まったものの、どのスレッド数においてもMariaDB5.5のほうが若干性能が良いということがわかった。

参考

blog.apar.jp

qiita.com

mysqltuner

うーん。でもconfigをどう変えてチューニングすればパフォーマンスがあがるかわからない。。。

そこでmysqltunerですよ！

github.com

perlスクリプトで、現在のmysqlサーバに対して「いい感じ」な設定をおすすめしてくれる大変いいやつである。

[root@mariadb5-5-45 ~]# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
--2017-10-27 22:55:06--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
raw.githubusercontent.com をDNSに問いあわせています... 
raw.githubusercontent.com に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 214792 (210K) [text/plain]
`mysqltuner.pl' に保存中

100%[================================================>] 214,792     --.-K/s 時間 0.01s   

2017-10-27 22:55:06 (17.1 MB/s) - `mysqltuner.pl' へ保存完了 [214792/214792]

[root@mariadb5-5-45 ~]# chmod u+x mysqltuner.pl

githubからraw scriptをwgetして、実行権を与えてやればいい。さぁそれぞれで診断してやろう。

5.5.45の結果

[root@mariadb5-5-45 ~]# ./mysqltuner.pl 
 >>  MySQLTuner 1.7.4 - 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
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.5.45-MariaDB
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/mariadb5-5-45.err(1K)
[OK] Log file /var/lib/mysql/mariadb5-5-45.err exists
[OK] Log file /var/lib/mysql/mariadb5-5-45.err is readable.
[OK] Log file /var/lib/mysql/mariadb5-5-45.err is not empty
[OK] Log file /var/lib/mysql/mariadb5-5-45.err is smaller than 32 Mb
[!!] /var/lib/mysql/mariadb5-5-45.err contains 1 warning(s).
[OK] /var/lib/mysql/mariadb5-5-45.err doesn't contain any error.
[--] 1 start(s) detected in /var/lib/mysql/mariadb5-5-45.err
[--] 1) 171027 22:01:18 [Note] /usr/sbin/mysqld: ready for connections.
[--] 0 shutdown(s) detected in /var/lib/mysql/mariadb5-5-45.err
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 241M (Tables: 1)
[OK] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[!!] User '@localhost' is an anonymous account.
[!!] User '@mariadb5-5-45' is an anonymous account.
[!!] User '@localhost' has no password set.
[!!] User '@mariadb5-5-45' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User 'root@localhost' has no password set.
[!!] User 'root@mariadb5-5-45' has no password set.
[!!] User '@localhost' has user name as password.
[!!] User '@mariadb5-5-45' has user name as password.
[!!] User 'sbtest@localhost' has user name as password.
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 55m 11s (8M q [2K qps], 5K conn, TX: 15G, RX: 664M)
[--] Reads / Writes: 77% / 23%
[--] Binary logging is disabled
[--] Physical Memory     : 996.2M
[--] Max MySQL memory    : 836.0M
[--] Other process memory: 27.0M
[--] Total buffers: 416.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: 838.8M (84.20% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (83.92% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/8M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Aborted connections: 0.35%  (18/5145)
[!!] 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.
[!!] Query cache efficiency: 0.0% (0 cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 823K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 411K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 116% (177 open / 152 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (7M immediate / 7M 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: 1 thread(s).
[--] Using default value is good enough for your version (5.5.45-MariaDB)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/99.0K
[!!] Read Key buffer hit rate: 42.9% (7 cached / 4 reads)
[OK] Write Key buffer hit rate: 100.0% (4 cached / 4 writes)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[!!] InnoDB buffer pool / data size: 128.0M/241.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.79% (81983156 hits/ 82152336 total)
[OK] InnoDB Write log efficiency: 94.41% (3481763 hits/ 3687842 total)
[OK] InnoDB log waits: 0.00% (0 waits / 206079 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
 
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
 
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/mariadb5-5-45.err file
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Set thread_cache_size to 4 as a starting value
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    thread_cache_size (start at 4)
    innodb_file_per_table=ON
    innodb_buffer_pool_size (>= 241M) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

結構長いが、!!のところが対応必要な項目。そして最後に対応方針を示してくれている。今回関心があるのは性能なのでPerformance Metricsをみよう。

なるほど、max connectionの151を超えてしまったから先ほど256以上は失敗したんですね。max_connectionは十分大きな値にしましょう。

thread_cacheが0のようなので、thread_cache_sizeを4にしてみましょう。

デフォルトストレージエンジンであるinnodb_bugger_pool_sizeはメモリの25%にあたる256Mぐらいに設定してみましょう。

ちなみに10.2.8での結果もほとんど差がなかったので省略します。

config変更

レビュー指摘反映。(笑うところです)

[root@mariadb5-5-45 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]
max_connections = 3000
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M
thread_cache_size = 3000
innodb_buffer_pool_size = 256M

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

さて、thread_cache_sizeの指摘はmariadb10.2.8ではでてなかった。それはデフォルト値が10.2からautoとなったからである。max_connectionsが256の小さいほうになるらしい。5系では0である。今回はいずれもmax_connectionsと同じ3000にしておきましょう。

Server System Variables - MariaDB Knowledge Base

service mysql restartを忘れずに。

再計測

ここで余談ですけどscript化しておくとらくですね。

こんな絶望的なファイル出力していたとしても

[root@mariadb5-5-45 ~]# ls /tmp/ | sort -n
sysbench_oltp_read_write_1.log
sysbench_oltp_read_write_1024.log
sysbench_oltp_read_write_128.log
sysbench_oltp_read_write_16.log
sysbench_oltp_read_write_2.log
sysbench_oltp_read_write_256.log
sysbench_oltp_read_write_32.log
sysbench_oltp_read_write_4.log
sysbench_oltp_read_write_512.log
sysbench_oltp_read_write_64.log
sysbench_oltp_read_write_8.log

sortコマンドのセパレータを指定してやればいいですね。

[root@mariadb5-5-45 ~]# ls /tmp/ | sort -t_ -nk5
sysbench_oltp_read_write_1.log
sysbench_oltp_read_write_2.log
sysbench_oltp_read_write_4.log
sysbench_oltp_read_write_8.log
sysbench_oltp_read_write_16.log
sysbench_oltp_read_write_32.log
sysbench_oltp_read_write_64.log
sysbench_oltp_read_write_128.log
sysbench_oltp_read_write_256.log
sysbench_oltp_read_write_512.log
sysbench_oltp_read_write_1024.log

余談でした。で、結果です。

[root@mariadb5-5-45 tmp]# ls /tmp/ | sort -t_ -nk5 | xargs -I{} grep transaction {}
    transactions:                        20963  (349.36 per sec.)
    transactions:                        24208  (403.42 per sec.)
    transactions:                        25565  (426.04 per sec.)
    transactions:                        24855  (414.20 per sec.)
    transactions:                        25634  (427.06 per sec.)
    transactions:                        25672  (427.23 per sec.)
    transactions:                        25843  (430.24 per sec.)
    transactions:                        26327  (437.70 per sec.)
    transactions:                        24750  (410.60 per sec.)
    transactions:                        22730  (371.98 per sec.)
[root@mariadb10-2-8 tmp]# ls /tmp/ | sort -t_ -nk5 | xargs -I{} grep transaction {}
    transactions:                        16510  (275.14 per sec.)
    transactions:                        19560  (325.97 per sec.)
    transactions:                        21380  (356.29 per sec.)
    transactions:                        22405  (373.33 per sec.)
    transactions:                        22544  (375.61 per sec.)
    transactions:                        22200  (369.74 per sec.)
    transactions:                        23139  (385.28 per sec.)
    transactions:                        22164  (368.60 per sec.)
    transactions:                        23066  (383.06 per sec.)
    transactions:                        21922  (362.38 per sec.)

若干改善したものの、やはりまだ5.5のほうが性能いいですね。

(これグラフにしたいな)

threads 5.5.45 5.5.45(2) 10.2.8 10.2.8(2)
1 21367 20963 15911 16510
2 23426 24208 18765 19560
4 23251 25565 20641 21380
8 24237 24855 21442 22405
16 24242 25634 21712 22544
32 24360 25672 21208 22200
64 26085 25843 21627 23139
128 24532 26327 21869 22164
256 24750 23066
512 22730 21922

さてmax_connectionsを3000にしたのにも関わらず1024スレッドだとしんじゃったのは謎ですが、再びtunerさんに診断してもらいましょう。

[root@mariadb10-2-8 ~]# Error in my_thread_global_end(): 3 threads didn't exit

[OK] Thread cache hit rate: 75% (514 created / 2K connections)

thread cacheが効いているようですね。

さてvariableについては、5.5.45に対して

    table_open_cache (> 400)
    innodb_file_per_table=ON
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

10.2.8に対して

    performance_schema = ON enable PFS
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

それぞれで指摘が違います。

各パラメータの意味を調べてみましょう。

table_open_cache

tableのキャッシュ。今回1テーブルでやってるので効果は見られない気がする。 * https://mariadb.com/kb/en/library/optimizing-table_open_cache/ * 10.1.7からdefaultが2000になったそう。(それまでは400) それで指摘が違ったんですね。

開いたテーブルの数を確認すると、

5.5.45

MariaDB [(none)]> show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 94083 |
+---------------+-------+
1 row in set (0.00 sec)

10.2.8

MariaDB [(none)]> show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 600   |
+---------------+-------+
1 row in set (0.00 sec)

と大差がついている。ここは10.2.8と同じく2000を設定しよう。(ますます5系の性能があがる気がするが。。。)

innodb_file_per_table

innodbのデータファイルをテーブルごとに作成するみたいです。

https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table

マニュアルによると5.5からデフォルトONみたいなんだけど、なんで5.5には指摘でたんだろう？実際に値を確認してみる。

MariaDB [(none)]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

OFFやんけ。というわけで、ONの設定を追加する。

innodb_log_file_size

これはinnodb_buffer_pool_sizeの25%ぐらいにしろと言っている。256Mなので64Mだね。

再々実施

5.5.45のほうではすべてスレッドがしんでしまった。10系では逆に問題なかった。何がいけなかったのだろうか？

ログをみるとibdataのlog file sizeが契機か。

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
InnoDB: Possible causes for this error:
 (a) Incorrect log file is used or log file size is changed
 (b) In case default size is used this log file is from 10.0
 (c) Log file is corrupted or there was not enough disk space
 In case (b) you need to set innodb_log_file_size = 48M
171028  0:32:03 [ERROR] Plugin 'InnoDB' init function returned error.
171028  0:32:03 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
171028  0:32:03 [Note] Plugin 'FEEDBACK' is disabled.
171028  0:32:03 [ERROR] Unknown/unsupported storage engine: InnoDB
171028  0:32:03 [ERROR] Aborting

壊れたので/var/lib/mysql以下全削除、yum remove & installで再インストールして、dbとuser再作成しました。

さて、10系ではtuner先生にあとひところだけ言われます。

performance_schema = ON enable PFS

PERFORMANCE_SCHEMAの概要 - MariaDB Knowledge Base

5.5から採用されたperformanceを計測するためのテーブル、performance schemaはmariadb10.0.12でデフォルトオフになったようです。

メモリを余計に食ってしまい性能が下がってしまわないか気になりますが、onにして再試験してみましょう。

やはり有効にすると両方とも若干落ちました。

おわりに

今回mysqltunerを指標にしてチューニングをし、性能への改善は見られましたが、10.2.8が5.5.45に勝つことはありませんでした。

今回第1回として、様々なアプローチで10.2.8が全力を出し5.5.45を超えるための方法を探していきたいと思います。