Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード Twitter

2018-06-12

MySQL on CentOS 6.9 に sysbench でベンチマークをかけてみる

ファイルシステム初期化

# mkdir /nvme0n1 /e32000
# fdisk -l
# mkfs.ext4 /dev/nvme0n1
# mkfs.ext4 /dev/xvdb
# mount /dev/nvme0n1 /nvme01
# mount /dev/xvdb /e32000

インストール

# yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
# yum -y install mysql-community-server
# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
# sudo yum -y install sysbench
# yum -y install strace 
# mysql -u root
mysql> create database mydb;
  • テーブルを作成してデータをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-user=root \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
  • datadir 配下のファイルをコピーしておく
# service mysqld stop
# cp -pr /var/lib/mysql /nvme01/
# cp -pr /var/lib/mysql /dev/shm/
# service mysqld start
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-db=mydb \
 --mysql-user=root \
 --time=30 \
 --db-ps-mode=disable \
 --threads=1 \
 run

結果

  • E300
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            101892
        write:                           29112
        other:                           14556
        total:                           145560
    transactions:                        7278   (242.57 per sec.)
    queries:                             145560 (★4851.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0013s
    total number of events:              7278

Latency (ms):
         min:                                    3.78
         avg:                                    4.12
         max:                                   22.54
         95th percentile:                        4.33
         sum:                                29979.87

Threads fairness:
    events (avg/stddev):           7278.0000/0.00
    execution time (avg/stddev):   29.9799/0.00
  • E32000
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            99582
        write:                           28452
        other:                           14226
        total:                           142260
    transactions:                        7113   (237.08 per sec.)
    queries:                             142260 (4741.55 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0007s
    total number of events:              7113

Latency (ms):
         min:                                    3.88
         avg:                                    4.21
         max:                                    7.83
         95th percentile:                        4.41
         sum:                                29979.78

Threads fairness:
    events (avg/stddev):           7113.0000/0.00
    execution time (avg/stddev):   29.9798/0.00
  • NVMe
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            152558
        write:                           43588
        other:                           21794
        total:                           217940
    transactions:                        10897  (363.17 per sec.)
    queries:                             217940 (★7263.49 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0027s
    total number of events:              10897

Latency (ms):
         min:                                    2.49
         avg:                                    2.75
         max:                                    4.85
         95th percentile:                        2.81
         sum:                                29972.98

Threads fairness:
    events (avg/stddev):           10897.0000/0.00
    execution time (avg/stddev):   29.9730/0.00
  • tmpfs
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            209902
        write:                           59972
        other:                           29986
        total:                           299860
    transactions:                        14993  (499.73 per sec.)
    queries:                             299860 (★9994.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0003s
    total number of events:              14993

Latency (ms):
         min:                                    1.89
         avg:                                    2.00
         max:                                    4.16
         95th percentile:                        2.07
         sum:                                29982.17

Threads fairness:
    events (avg/stddev):           14993.0000/0.00
    execution time (avg/stddev):   29.9822/0.00

システムコール(fsyncの所要時間)

  • コマンド
# strace -yy -fe fsync -Tttp <PID>
  • E300
[pid 28833] 15:16:59.322025 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001616>
[pid 28833] 15:16:59.340313 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001362>
[pid 28833] 15:16:59.358445 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001512>
  • NVMe
[pid 28510] 15:14:48.225248 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000204>
[pid 28510] 15:14:48.241499 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000194>
[pid 28510] 15:14:48.257621 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000224>
  • tmpfs
[pid  8264] 15:12:02.394681 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000029>
[pid  8264] 15:12:02.410769 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000029>
[pid  8264] 15:12:02.426758 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000030>
[pid  8264] 15:12:02.442635 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000030>

システムコール(集計)

  • コマンド
# strace -fcp <PID>
  • E300
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.57   51.989330       53487       972           io_getevents
 32.08   30.015424        3314      9057      1740 futex
 11.11   10.398417      142444        73           select
  1.01    0.945853      157642         6         5 restart_syscall
  0.15    0.142054          84      1686           fsync
  0.03    0.027272           0    199846           gettimeofday
  0.02    0.014865           0    103448           clock_gettime
  0.01    0.008399           0     65686         2 recvfrom
  0.01    0.007503           0     32842           sendto
  0.00    0.002924           0      8466           madvise
  0.00    0.001104           0      6570           sched_yield
  0.00    0.000734           0      1681           pwrite
  0.00    0.000233           1       339           pread
  0.00    0.000042           0       401           io_submit
  0.00    0.000020           3         6           read
  0.00    0.000000           0         6           open
  0.00    0.000000           0         5           close
  0.00    0.000000           0         1           stat
  0.00    0.000000           0         6           lstat
  0.00    0.000000           0         2           poll
  0.00    0.000000           0         9           lseek
  0.00    0.000000           0         3           mmap
  0.00    0.000000           0        22           mprotect
  0.00    0.000000           0         1           munmap
  0.00    0.000000           0         2         1 access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         5           fcntl
  0.00    0.000000           0         6           getcwd
------ ----------- ----------- --------- --------- ----------------
100.00   93.554174                431151      1749 total
  • NVMe
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 56.05   54.565652       37710      1447           io_getevents
 32.70   31.833832        3075     10353      1931 futex
 11.14   10.849351      139094        78           select
  0.03    0.027523           0    223938           gettimeofday
  0.02    0.022996        3833         6         5 restart_syscall
  0.02    0.016611           9      1892           fsync
  0.01    0.014254           0    115670           clock_gettime
  0.01    0.010720           0     73445         1 recvfrom
  0.01    0.007561           0     36722           sendto
  0.00    0.003592           0      9214           madvise
  0.00    0.002005           0      7359           sched_yield
  0.00    0.000726           0      1883           pwrite
  0.00    0.000205           0       447           pread
  0.00    0.000163           0       888           io_submit
  0.00    0.000000           0         6           read
  0.00    0.000000           0         6           open
  0.00    0.000000           0         5           close
  0.00    0.000000           0         1           stat
  0.00    0.000000           0         6           lstat
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         9           lseek
  0.00    0.000000           0         3           mmap
  0.00    0.000000           0        22           mprotect
  0.00    0.000000           0         1           munmap
  0.00    0.000000           0         2         1 access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         5           fcntl
  0.00    0.000000           0         6           getcwd
------ ----------- ----------- --------- --------- ----------------
100.00   97.355191                483419      1939 total
  • tmpfs
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.82   41.885262       11926      3512           io_getevents
 33.08   24.819955        1427     17391      2800 futex
 11.02    8.271746      114885        72           select
  0.03    0.023314           0    230171           gettimeofday
  0.02    0.011618           0    117623           clock_gettime
  0.01    0.007673           0     74685         1 recvfrom
  0.01    0.006094           0     37342           sendto
  0.00    0.002555           0      8772           madvise
  0.00    0.001471           0      7508           sched_yield
  0.00    0.001067           0      3799           io_submit
  0.00    0.001000         167         6         5 restart_syscall
  0.00    0.000930           0      2012           pwrite
  0.00    0.000315           0      2100           fsync
  0.00    0.000000           0         1           close
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         3           pread
  0.00    0.000000           0         1           access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         3           fcntl
------ ----------- ----------- --------- --------- ----------------
100.00   75.033000                505006      2807 total

起動と停止

  • 停止
# service mysqld stop
  • datadir の変更
# vi /etc/my.conf
#datadir=/var/lib/mysql
#datadir=/e32000/mysql
#datadir=/nvme01/mysql
datadir=/dev/shm/mysql

  • 起動
# service mysqld start

環境


参考

2018-06-11

MySQL の InnoDB テーブルの断片化の影響を調べてみた

準備

  • テーブルを作成して1千万件データをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=10000000 \
 --mysql-host=aurora01.******.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=****** \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
  • テーブルの件数と空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   3145728 |    9867937 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)
  • 断片化させるプロシージャを作成する
mysql> delimiter //
mysql> create procedure fragment_sbtest1(in x int, in y int)
begin
 while x < y do
  delete from mydb.sbtest1 where id = x;
  set x = x + 2;
 end while;
end
//
mysql> delimiter ;

クエリ実行時間を測定する

1千万件
  • 実行時間は1分15秒程度
mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|   10000000 |
+------------+
1 row in set (1 min 15.50 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|   10000000 |
+------------+
1 row in set (1 min 17.92 sec)
5百万件(断片化状態)
  • データを歯抜けに削除する
mysql> call fragment_sbtest1(1, 10000000);
  • テーブルの件数と空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   7340032 |    4922775 |
+--------------+------------+-----------+------------+
1 row in set (0.02 sec)
  • 実行時間は1分10秒程度でほぼ変化なし。
mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (1 min 8.51 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (1 min 12.69 sec)

5百万件(断片化後)
  • 断片化を解消する
mysql> alter table sbtest1 engine innodb;
Query OK, 0 rows affected (34.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • テーブルの件数と空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   4194304 |    5154227 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)
  • 実行時間は35秒程度と半分程度に短縮。
mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (35.31 sec)

mysql> select count(pad) from sbtest1;
+------------+
| count(pad) |
+------------+
|    5000000 |
+------------+
1 row in set (34.81 sec)

環境

"select count(pad) from sbtest1" で実行時間を測定したインスタンスは以下の通り。性能差を測定するためミニマムな環境にした。

  • db.t2.small
  • query_cache_type: 0
  • innodb_buffer_pool_size: 67108864

2018-06-10

MySQL の InnoDB テーブルの統計情報を確認する

MySQLInnoDB テーブルにどんな統計情報があるのか調べてみた。MySQL 5.6 にはテーブル統計インデックス統計だけで、MySQL 8.0 からヒストグラム*1が入るらしい。


  • テーブル定義
mysql> desc mydb.sbtest1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int(11)   | NO   | PRI | NULL    | auto_increment |
| k     | int(11)   | NO   | MUL | 0       |                |
| c     | char(120) | NO   |     |         |                |
| pad   | char(60)  | NO   |     |         |                |
+-------+-----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from mysql.innodb_table_stats where table_name = 'sbtest1';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| mydb          | sbtest1    | 2018-06-10 20:39:25 |      1 |                    1 |                        1 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.innodb_index_stats where table_name = 'sbtest1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| mydb          | sbtest1    | PRIMARY    | 2018-06-10 20:39:25 | n_diff_pfx01 |          1 |           1 | id                                |
| mydb          | sbtest1    | PRIMARY    | 2018-06-10 20:39:25 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mydb          | sbtest1    | PRIMARY    | 2018-06-10 20:39:25 | size         |          1 |        NULL | Number of pages in the index      |
| mydb          | sbtest1    | k_1        | 2018-06-10 20:39:25 | n_diff_pfx01 |          1 |           1 | k                                 |
| mydb          | sbtest1    | k_1        | 2018-06-10 20:39:25 | n_diff_pfx02 |          1 |           1 | k,id                              |
| mydb          | sbtest1    | k_1        | 2018-06-10 20:39:25 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mydb          | sbtest1    | k_1        | 2018-06-10 20:39:25 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)

参考

一体いつ変わったのかリリースノートにも全然書いてないんだけど、 mysql.column_statistics に名前が変わってシステムテーブルとして直接参照が不可能に、中身を覗くには information_schema.column_statistics に名前が変わっていた。

日々の覚書: MySQL 8.0.0でついにヒストグラムがサポートされるらしい(が、自動で統計してくれるわけではない様子)

MySQL の InnoDB テーブルの断片化状況を確認する

MySQLInnoDB テーブルの空き領域は INFORMATION_SCHEMA.TABLES の DATA_FREE カラムで確認でき、ALTER TABLE <テーブル名> ENGINE INNODB で断片化(フラグメンテーション)を解消することができる。なお、テーブルの断片化でI/O量が増えて性能劣化するのはフルテーブルスキャンで、インデックススキャンは影響を受けない。


前提


確認方法

select table_schema, table_name, data_free, table_rows
	from information_schema.tables
	where table_name='テーブル名';

  • テーブルを作成してデータをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=10000 \
 --mysql-host=aurora01.******.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=******** \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
  • 1件を残して削除する
mysql> delete from sbtest1 where id < 10000;
Query OK, 9999 rows affected (0.16 sec)
  • テーブルの空き領域を確認する
mysql> select table_schema, table_name, data_free, table_rows
    -> from information_schema.tables
    -> where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |   6291456 |          1 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)
mysql> alter table sbtest1 engine innodb;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select table_schema, table_name, data_free, table_rows
    -> from information_schema.tables
    -> where table_name='sbtest1';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| mydb         | sbtest1    |         0 |          1 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

参考

DATA_FREE カラムには、InnoDB テーブルの空き領域がバイト単位で表示されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.22 INFORMATION_SCHEMA TABLES テーブル

少し話がそれてしまったが、OPTIMIZEが必要になるのはどういう時だろうか?簡単にいうと、「行をDELETEのして無駄な領域がたくさん生じたとき」だけである。これによって、利用されていない領域が回収されることになる。そして、フラグメンテーションが解消するわけである。

実は、InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。OPTIMIZE TABLE t1は次のコマンドを実行するのと同じなのである。

mysql> ALTER TABLE t1 ENGINE INNODB;

このコマンドを実行すると、テーブルのデータが詰め直される。データの挿入は主キーの順番で行われるため、OPTIMIZE TABLEすると

という効果が期待できることになる。残念ながら、セカンダリインデックスセカンダリインデックスの順番で詰め直す方法はない。そのため、セカンダリインデックスの空間効率はあまりよくない場合があるので注意が必要である。

OPTIMIZE TABLEが内部的にALTER TABLEと同じということは、別に悪いことばかりではない。モノホンのOPTIMIZE TABLEだと実行中は参照も更新もブロックしてしまうことになるが、ALTER TABLEであればメンテナンス中も参照は可能なのである。

漢(オトコ)のコンピュータ道: 大人のためのInnoDBテーブルとの正しい付き合い方。

13.7.2.4 OPTIMIZE TABLE 構文

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

ストレージ領域を削減し、テーブルアクセス時の I/O 効率を向上させるために、テーブルデータとそれに関連付けられたインデックスデータの物理ストレージを再編成します。各テーブルに加えられる正確な変更は、そのテーブルによって使用されているストレージエンジンによって異なります。

OPTIMIZE TABLE は、テーブルのタイプに応じて次の場合に使用します。

  • innodb_file_per_table オプションが有効な状態で作成されたために独自の .ibd ファイル を含む InnoDB テーブルに対して大量の挿入、更新、または削除操作を行なったあと。テーブルとインデックスが再編成されるため、ディスク領域をオペレーティングシステムによる使用のために再利用できます。
  • InnoDB テーブル内の FULLTEXT インデックスの一部であるカラムに対して大量の挿入、更新、または削除操作を行なったあと。最初に、構成オプション innodb_optimize_fulltext_only=1 を設定します。インデックス保守期間を妥当な時間に維持するために、検索インデックスで更新するワード数を指定する innodb_ft_num_word_optimize オプションを設定し、検索インデックスが完全に更新されるまで OPTIMIZE TABLE ステートメントシーケンスを実行します。
  • MyISAM または ARCHIVE テーブルの大きな部分を削除するか、あるいは可変長行を含む MyISAM または ARCHIVE テーブル (VARCHAR、VARBINARY、BLOB、または TEXT カラムを含むテーブル) に多くの変更を行なったあと。削除された行はリンクリスト内に保持され、以降の INSERT 操作は古い行の位置を再利用します。OPTIMIZE TABLE を使用すると、未使用領域を再利用したり、データファイルをデフラグしたりできます。テーブルを大幅に変更したあとは、このステートメントにより、そのテーブルを使用するステートメントのパフォーマンスを (場合によっては大幅に) 向上させることができます。

(中略)

InnoDB の詳細

InnoDB テーブルの場合、OPTIMIZE TABLE は ALTER TABLE ... FORCE にマップされます。これは、インデックス統計を更新し、クラスタ化されたインデックス内の未使用領域を解放するためにテーブルを再構築します。これは、次に示すように、InnoDB テーブルに対して実行したときに OPTIMIZE TABLE の出力に表示されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文

MySQL 5.0以降は、INFORMATION_SCHEMAという便利なシロモノがある。SHOW TABLE STATUSコマンドを利用する代わりに、INFOMATION_SCHEMA.TABLESテーブルを参照することで同様の情報を表示することが可能であり、innodb_file_per_tableオプション利用時は次のように利用することで、全てのテーブルに関する情報を一括表示することが可能だ。

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,DATA_FREE
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ENGINE='innodb';
mysql> SELECT TABLE_SCHEMA,SUM(DATA_FREE)
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ENGINE='innodb'
    -> GROUP BY TABLE_SCHEMA WITH ROLLUP;

SHOWコマンドの出力は加工することは出来ないが、INFORMATION_SCHEMAは好きなようにWHERE句で絞り込みを行ったり集計したりすることができるので便利なのだ。

ところで、Data_freeの数値はInnoDBにおいてどの程度信頼できるのだろうか。

InnoDBのテーブルスペース内では、データは16KBのページ単位で管理されており、さらに64個の連続するページから成るエクステントにグループ化されている。InnoDBテーブルからデータを削除すると、対応するBツリーインデックスが縮小される。これによって、他のテーブルに対して空き領域が再利用できるかどうかは、削除のパターンがテーブルスペースの個々のページやエクステントを解放するかどうかによる。エクステントが解放された場合には、そのエクステントは他のテーブルによって再利用されるが、個々のページやページの一部だけが開放されただけの場合には他のテーブルによって再利用されることはない。つまり、Data_freeは使用されていないエクステントのサイズであり、ページ単位で見ると空き領域がある場合があるということだ。しかしながら、領域を再利用できるかどうかはエクステント全体が空いているかどうかで決まるため、Data_freeはほぼ正確な空き領域を示していると言える。

最後にちょっとだけ内部構造的な話。SHOW TABLE STATUSやINFORMATION_SCHEMAの情報は、最終的にはinnobase/fsp/fsp0fsp.c内のfsp_get_available_space_in_free_extents()関数によって値が取得される。関数引数はテーブルスペースのIDである。下記は関数の末尾からの抜粋である。

return((ullint)(n_free - reserve)
* FSP_EXTENT_SIZE
* (UNIV_PAGE_SIZE / 1024));

空き領域(n_free)から予約された領域(reserve)を引いたものに、一つのエクステントに含まれるページ数(FSP_EXTENT_SIZE=64)を掛けて、さらにページサイズ(UNIV_PAGE_SIZE/1024=16kb)を掛けたものを返しているというワケである。興味のある人はソースコードを覗いてみよう。

漢(オトコ)のコンピュータ道: InnoDBテーブルスペースの空き領域

*1Oracle Database で言う列統計ヒストグラム

2018-06-07

PPS を計測する

インストール

$ yum -y install sysstat htop

計測

サーバ
  • iperf で listen する
for i in {5201..5204}
do
	iperf3 -s -p $i &
done

クライアント
  • 負荷をかける
iperf3 -c 172.31.**.*** -P 10 -t 60 -p 5201
iperf3 -c 172.31.**.*** -P 10 -t 60 -p 5201

性能測定する
$ sar -n DEV 5
Linux 4.14.33-51.37.amzn1.x86_64 (ip-172-31-**-***) 	06/07/2018 	_x86_64_	(64 CPU)

02:11:55 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
02:12:00 PM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
02:12:00 PM      eth0 345469.08  96451.41 3041362.50   6413.93      0.00      0.00      0.00

02:12:00 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
02:12:05 PM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
02:12:05 PM      eth0 344750.50  96154.31 3035024.22   6392.70      0.00      0.00      0.00

02:12:05 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
02:12:10 PM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
02:12:10 PM      eth0 345448.39  95719.28 3041171.64   6373.29      0.00      0.00      0.00

02:12:10 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
02:12:15 PM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
02:12:15 PM      eth0 345441.16  95467.47 3041113.27   6349.58      0.00      0.00      0.00

02:12:15 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
02:12:20 PM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
02:12:20 PM      eth0 344753.51  95941.88 3035055.92   6384.97      0.00      0.00      0.00

02:12:20 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
02:12:25 PM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
02:12:25 PM      eth0 345439.76  95952.81 3041095.64   6388.09      0.00      0.00      0.00
$ htop

2018-06-06

Aurora MySQL で DDL を実行してみる

準備

  • データをロードする
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=500000 \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
mysql> select count(id) from sbtest1;
+-----------+
| count(id) |
+-----------+
|   5000000 |
+-----------+
1 row in set (0.82 sec)

DDL実行

  • カラムを追加する
mysql> alter table sbtest1 add added_col1 char(255);
Query OK, 0 rows affected (53.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table sbtest1 add added_col2 char(255) default 'abcdefghijklmnopqrstuvwxyz';
Query OK, 0 rows affected (1 min 9.43 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • テーブル定義を確認する
mysql> desc sbtest1;
+------------+-----------+------+-----+----------------------------+----------------+
| Field      | Type      | Null | Key | Default                    | Extra          |
+------------+-----------+------+-----+----------------------------+----------------+
| id         | int(11)   | NO   | PRI | NULL                       | auto_increment |
| k          | int(11)   | NO   | MUL | 0                          |                |
| c          | char(120) | NO   |     |                            |                |
| pad        | char(60)  | NO   |     |                            |                |
| added_col1 | char(255) | YES  |     | NULL                       |                |
| added_col2 | char(255) | YES  |     | abcdefghijklmnopqrstuvwxyz |                |
+------------+-----------+------+-----+----------------------------+----------------+
6 rows in set (0.00 sec)

DML実行

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest1 set added_col1 = '123' where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

参考