アーカイブ「2019年10月」

MariaDB10.4でhistgramの収集がデフォルトでONになりました。

(もちろんANALYZE TABLEを実行したときですけど)

10.3では、

select @@use_stat_tables;
+-------------------+
| @@use_stat_tables |
+-------------------+
| NEVER             |
+-------------------+

でしたが、

10.4では、

select @@use_stat_tables;
+------------------------+
| @@use_stat_tables      |
+------------------------+
| PREFERABLY_FOR_QUERIES |
+------------------------+

となり、ANALYZE TABLE 時に、特定のクエリーを加えると、histgramの収集が行われます。

ANALYZE TABLE 時にhistgramを収集するための書き方としては、

analyze table ${tableName} persistent for all;

のようになります。詳しくは公式を参考ください。

https://mariadb.com/kb/en/library/engine-independent-table-statistics/#examples-of-statistics-collection

で、実際にやってみました。


まずは、以下のようなテーブルに大量にデータを突っ込んでおきます。

MariaDB [test]> create table user (
   id         integer not null auto_increment,
   name       varchar(64),
   money_free integer,
   money_paid integer,
   primary key (id)
 );
MariaDB [test]>  select count() from user;
+----------+
| count() |
+----------+
| 16777214 |
+----------+

最小値と最大値をとっておきます。

MariaDB [test]> select min(money_free), max(money_free)  from user;
+-----------------+-----------------+
| min(money_free) | max(money_free) |
+-----------------+-----------------+
|               1 |             100 |
+-----------------+-----------------+

histgramを収集していない状態で、money_freeを条件として、フィルターされる行数を見てみます。

MariaDB [test]> explain extended select * from user where money_free between 30 and 31;
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 16305730 |   100.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

わかりにくいですが、filteredの値が100となっており、全くフィルターされない状態となっています。

次に、ANALYZE TABLE を実行してみます。

MariaDB [test]> analyze table user persistent for all;
+-----------+---------+----------+-----------------------------------------+
| Table     | Op      | Msg_type | Msg_text                                |
+-----------+---------+----------+-----------------------------------------+
| test.user | analyze | status   | Engine-independent statistics collected |
| test.user | analyze | status   | OK                                      |
+-----------+---------+----------+-----------------------------------------+
2 rows in set (36.498 sec)

結構時間かかりましたね。フルスキャンなので、公式にも記載があるように、slaveのみで実施するなどにして、サービスに影響が無いよう配慮する必要があります。

なお、バイナリログに記載されるようなので、masterで実施すると、slaveにも伝播してしまいますので、お気をつけ下さい。(flush localで回避できる模様)

そして、再度同じクエリーで、explainを実行してみます。

MariaDB [test]> explain extended select * from user where money_free between 30 and 31;
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 16777214 |     1.56 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

おおー、filteredが激減しましたね。

なお、ヒストグラムの情報は以下のテーブルに書かれるようです。

  • mysql.table_stats
  • mysql.column_stats
  • mysql.index_stats

MySQL8のように、ヒストグラムを削除するコマンドはないようなので、直接DELETEしちまってもいいような。。

削除したら、flush tablesを打っておくとよいのかな。。。(削除に限らず作成/更新したら?)

このようにヒストグラムを更新することで、INDEXがないカラムに対しても、JOIN時に評価対象行が減るので、非常にパフォーマンスがアップすることが期待されます。

もちろん、join_bufferを増やしたり、optimizer_switchを調整したりといったことも合わせて必要ですが、OLAP向けにもInnoDBでもこれまで以上に頑張れそうですね。

以上

投稿日時:2019年10月29日 18:49   カテゴリー:mariadb   [コメントがあればどうぞ]

久し振りの投稿。

MariaDBを10.3から10.4にupgradeして、色々検証中。

まずは、INSTANTの拡張について記載します。

検証したバージョンは、10.4.8-communityです。


10.3で導入されたINSTANT機能ですが、10.4になって以下のように拡張されています。

  • ALTER TABLE … ADD COLUMN(末尾への追加、10.3で導入)
  • ALTER TABLE … DROP COLUMN(カラム削除、ただしINDEXなし)
  • ALTER TABLE … MODIFY COLUMN(カラム変更)
    • Reordering Columns(順序変更、つまりafterが使える)
    • Changing the Data Type of a Column(varcharは最大まで、varbinaryは255まで、text型はダメ)
    • Changing a Column to NULL(NULL許可へ変更)
    • Adding a New ENUM Option(ENUM値の追加)
    • Adding a New SET Option(SET値の追加)
    • Removing System Versioning from a Column(system versionedの外し)
  • ALTER TABLE … ALTER COLUMN(カラム修正)
    • Setting a Column’s Default Value(デフォルト値の設定)
    • Removing a Column’s Default Value(デフォルト値の削除)
    • ALTER TABLE … CHANGE COLUMN(カラム名変更)
  • ALTER TABLE … DROP INDEX and DROP INDEX(Secodary INDEX削除)
  • ALTER TABLE … DROP FOREIGN KEY(外部キー削除)
  • ALTER TABLE … AUTO_INCREMENT=…(AUTOインクリメント値再設定)
  • ALTER TABLE … PAGE_COMPRESSED=1 and ALTER TABLE … PAGE_COMPRESSION_LEVEL=…(1のみ)
  • ALTER TABLE … DROP CONSTRAINT(制約の削除)
  • ALTER TABLE … RENAME TO and RENAME TABLE …(テーブル名変更)

(参考)

https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-the-instant-alter-algorithm/

※varcharの拡張は、utf8mb4環境で、最大まで一瞬でした。

※varbinaryについては公式に記載ありませんが、255までなら一瞬でした。256からはCOPYとなるようです。

いやー数が多いですね。

ここには記載がありませんが、virtualの生成列も一瞬で生成可能なので、これもINSTANTとしていいのかなと。

で、INSTANTを発動させるためには、DDLを発行する前に、

SET SESSION alter_algorithm='INSTANT';

をやると、INSTANT以外のものはエラーとなるのですが、

MariaDB [test]> select @@alter_algorithm;
+-------------------+
| @@alter_algorithm |
+-------------------+
| DEFAULT           |
+-------------------+

の状態であれば、INSTANT可能なDDLであれば、INSTANTが選択されるようです。

ただ、これ明示的な記載がないのが気になるところですが。。

(参考)

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


いくつか実験してみました。

以下のようなテーブルに大量にデータ入れてみます。

MariaDB [test]> create table user (
id integer not null auto_increment,
name varchar(64),
money_free integer,
money_paid integer,
primary key (id)
);
MariaDB [test]>  select count() from user;
+----------+
| count() |
+----------+
| 16777214 |
+----------+

これまでは、INSTANTが機能されると、

SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column';

の結果がインクリメントされるのですが、

MariaDB [test]> alter table user modify column name varchar(8192);
Query OK, 0 rows affected (0.017 sec)

などのvarchar拡張やら、enum追加などは、innodb_instant_alter_columnの値が増えませんでした。

これは、どうやら内部的には10.2でサポートされた拡張については、innodb_instant_alter_columnをインクリメントしないという意図的な動きのようです。

https://jira.mariadb.org/browse/MDEV-20801

ここまで広い範囲をカバーしてもらえると、DDL変更の運用負荷がとても下がりますね。

残っている箇所が、INDEX関係なので、相当難しいと思いますが、今後も期待ですね。

以上

投稿日時:2019年10月28日 17:14   カテゴリー:mariadb   [コメントがあればどうぞ]