MySQLのInstant ADD COLUMNをちゃんと調べてみる

MySQL

この記事は裏freee developers Advent Calendar 2018の21日目の記事です。

freee株式会社でプロダクト基盤本部本部長をしています浅羽と申します。プロダクト基盤は文字通りプロダクトの基盤を作っており、SRE、分析基盤、アカウントアグリゲーション基盤を作っているチームになっています。コードを書く時間を減らして組織づくりにフォーカスしていますが、とはいえサービスを良くするためには技術も素振りする必要があると思っています。ということで、組織的な話ではなく技術的な話を書こうと思います。

サービスを運営しているとデータベースがだんだん大きくなってきて、RDBMSの性能がスローダウンするような場面があると思います。その引き金としてクエリが遅くなったり、大きめのテーブルに対してADD COLUMNをしてしまうなどがありえそうでしょうか。freeeではRDMBSはMySQLをメインに使っています。ADD COLUMNに関してはMySQLの場合はONLINE DDLも存在していますが、それでも結構気を使います。

2017年にAmazon AuroraでFast DDLなる機能がリリースされ、それとは全然実装が違うと思いますがMySQLでもInstant ADD COLUMNという機能が2018年にリリースされました。

これは文字通りに解釈すれば最高な機能なのですが、長くソフトウェアエンジニアをやっているとこういうgood newsを疑ってしまってなにか罠があるんじゃないか？と思ったので、ちゃんと調べてみようと思いました。ただ、実際にちゃんと調べてみると結構大変でまとまっているのかは怪しいですが、誰かが更に深掘りしてくれると信じて書いたものを出してみます。

Instant ADD COLUMNとは？

MySQLのALTER TABLEは5.7ではCOPY方式とINPLACE方式の2つありました。詳細は以下のドキュメントを読んでいただきたいのですが、ざっくり書くとこんな感じです。

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations

  • ALGORITHM=INPLACE
    • secondary indexに関してはテーブルデータを作り直さないのと、exclusive lockも取らない
    • ADD COLUMNについてはexclusive lockを取らないので並列して参照や更新ができるが、裏でテーブルを作り直すので結構重い。
  • ALGORITHM=COPY
    • exclusive lockを取ってデータを作り直すので、その間は対象のテーブルへの読み書きができなくなる

MySQL5.7でALGORITHMを指定せずにADD COLUMNをした場合は大抵の場合はInplaceの挙動になります（詳しくはドキュメントをご覧ください）。ただ、以下の処理が裏で走るため

  • テーブルを作り直す
  • secondary indexも作り直す
  • テーブル作り直し中の更新操作をログに記録
  • テーブル作り直し中の更新操作を作り直したテーブルへapplyする *COMMITの並列処理が抑えられる可能性があるため、ここの時間帯がおそらく一番重い

ちなみに、↑はMySQL5.6のONLINE DDLの処理を社内qiitaにまとめたときにコードを読んだ仕組みなので、もしかしたら5.7では挙動が変わっているかもしれません。観測している感じだと5.6と5.7でそんなに挙動は変わっていない気もします。

さて、やっと本題になりますが、MySQL 8.0.12からInstant ADD COLUMNという機能が追加されました。ALTER TABLEではALGORITHM=INSTANTです。

MySQL :: MySQL 8.0 Reference Manual :: 13.1.9 ALTER TABLE Syntax

INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

データディクショナリに格納されているメタデータのみを書き換えて、テーブルデータを全く変更しないモードです。普通に考えて非常に速そうですね。

Instant ADD COLUMNに関する資料

MySQLのデバッグビルド

マニュアル以上の挙動を調べるためにはソースコードを読むしかないと思います。ということで読む環境を整えます。Linuxのほうがデバッグする環境を作るのが楽なので、Linux(Ubuntu 18.04)を前提に書いていきます。

ソースコードのダウンロード

MySQL :: Download MySQL Community Server からソースコードをダウンロードできます。この記事では8.0.13を想定しています。

  • Select Operating System: Source Code
  • Select OS Version: Generic Linux (Architecture Independent)
    • Boost ありを選ぶ

デバッグビルド

ビルドに必要なツールをインストールしてから、デバッグビルドのオプションを指定した上でビルドします。当たり前な話ですが、本番環境等のデバッグ目的でない場合はデバッグビルドしないでください。

% sudo apt-get update
% sudo apt-get install build-essential cmake wget curl libncurses5-dev libssl-dev gdb gdbserver -y 
% tar zxvf mysql-boost-8.0.13.tar.gz
% cd mysql-8.0.13
% CFLAGS=-O0 cmake -DWITH_DEBUG=1 -DWITH_INNODB_EXTRA_DEBUG=1 -DCMAKE_INSTALL_PREFIX=$HOME/mysql-debug -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./boost
% make -j$(getconf _NPROCESSORS_ONLN) install

$HOME/mysql-debug ディレクトリ以下にバイナリをインストールしたので、データの初期化を行います。

% cd $HOME/mysql-debug
% ./bin/mysqld  --no-defaults --datadir=./data --basedir=./ --initialize-insecure

metadataを確認する

metadataはMySQL 8.0からData Dictionaryと呼ばれる場所に保存されるようになりました。

https://mysqlserverteam.com/the-unified-data-dictionary-lab-release/

調べてみた感じだと、このdata dictionaryの中身をdumpするには2通りありそうです。

ibd2sdi

ibdファイルからSDI(serialized dictionary information)を抽出してくれる便利コマンドです。出力はJSON形式です。

MySQL :: MySQL 8.0 Reference Manual :: 4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility

デバッグビルドしているのであればmysql.tables等から参照

ドキュメントに書いてあるやり方で参照することが可能です。

mysql> SET SESSION debug='+d,skip_dd_table_access_check';
mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
mysql> show create table mysql.columns \G;
*************************** 1. row ***************************
       Table: columns
Create Table: CREATE TABLE `columns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_id` bigint(20) unsigned NOT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
  `ordinal_position` int(10) unsigned NOT NULL,
  `type` enum('MYSQL_TYPE_DECIMAL','MYSQL_TYPE_TINY','MYSQL_TYPE_SHORT','MYSQL_TYPE_LONG','MYSQL_TYPE_FLOAT','MYSQL_TYPE_DOUBLE','MYSQL_TYPE_NULL','MYSQL_TYPE_TIMESTAM
P','MYSQL_TYPE_LONGLONG','MYSQL_TYPE_INT24','MYSQL_TYPE_DATE','MYSQL_TYPE_TIME','MYSQL_TYPE_DATETIME','MYSQL_TYPE_YEAR','MYSQL_TYPE_NEWDATE','MYSQL_TYPE_VARCHAR','MYSQ
L_TYPE_BIT','MYSQL_TYPE_TIMESTAMP2','MYSQL_TYPE_DATETIME2','MYSQL_TYPE_TIME2','MYSQL_TYPE_NEWDECIMAL','MYSQL_TYPE_ENUM','MYSQL_TYPE_SET','MYSQL_TYPE_TINY_BLOB','MYSQL_
TYPE_MEDIUM_BLOB','MYSQL_TYPE_LONG_BLOB','MYSQL_TYPE_BLOB','MYSQL_TYPE_VAR_STRING','MYSQL_TYPE_STRING','MYSQL_TYPE_GEOMETRY','MYSQL_TYPE_JSON') COLLATE utf8_bin NOT NU
LL,
  `is_nullable` tinyint(1) NOT NULL,
  `is_zerofill` tinyint(1) DEFAULT NULL,
  `is_unsigned` tinyint(1) DEFAULT NULL,
  `char_length` int(10) unsigned DEFAULT NULL,
  `numeric_precision` int(10) unsigned DEFAULT NULL,
  `numeric_scale` int(10) unsigned DEFAULT NULL,
  `datetime_precision` int(10) unsigned DEFAULT NULL,
  `collation_id` bigint(20) unsigned DEFAULT NULL,
  `has_no_default` tinyint(1) DEFAULT NULL,
  `default_value` blob,
  `default_value_utf8` text COLLATE utf8_bin,
  `default_option` blob,
  `update_option` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `is_auto_increment` tinyint(1) DEFAULT NULL,
  `is_virtual` tinyint(1) DEFAULT NULL,
  `generation_expression` longblob,
  `generation_expression_utf8` longtext COLLATE utf8_bin,
  `comment` varchar(2048) COLLATE utf8_bin NOT NULL,
  `hidden` enum('Visible','SE','SQL') COLLATE utf8_bin NOT NULL,
  `options` mediumtext COLLATE utf8_bin,
  `se_private_data` mediumtext COLLATE utf8_bin,
  `column_key` enum('','PRI','UNI','MUL') COLLATE utf8_bin NOT NULL,
  `column_type_utf8` mediumtext COLLATE utf8_bin NOT NULL,
  `srs_id` int(10) unsigned DEFAULT NULL,
  `is_explicit_collation` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `table_id` (`table_id`,`name`),
  UNIQUE KEY `table_id_2` (`table_id`,`ordinal_position`),
  KEY `collation_id` (`collation_id`),
  KEY `srs_id` (`srs_id`),
  CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
  CONSTRAINT `columns_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`),
  CONSTRAINT `columns_ibfk_3` FOREIGN KEY (`srs_id`) REFERENCES `st_spatial_reference_systems` (`id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=4062 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

gdbで追いかける

事前準備

準備としてテーブルを作ってデータを投入します。

mysql> create database test;
mysql> create table hoge4(a int auto_increment primary key);
mysql> insert into hoge4(a) values (1), (2);

この時点でibd2sdiでメタデータを確認します。どこかにメモっておきましょう。

% ./bin/ibd2sdi data/test/hoge4.ibd

ALTER TABLE ADD COLUMN

以下の場所にbreakpointを貼ります。

(gdb) b ha_innobase::inplace_alter_table_impl<dd::Table>(TABLE*, Alter_inplace_info*, dd::Table const*, dd::Table*)

(gdb) b dd_commit_inplace_instant<dd::Table>(Alter_inplace_info*, THD*, trx_t*, dict_table_t*, TABLE const*, TABLE const*, dd::Table const*, dd::Table*, uint64_t*)

mysqlコマンドでデバッグビルドしたmysqldに接続し、alter tableを適当に流します。

mysql> alter table hoge4 add column c varchar(10) default 'aaa';

そうすると以下のようにbreakpointに引っかかってmysqldが一時停止します。

(gdb) bt
#0  ha_innobase::inplace_alter_table_impl<dd::Table> (this=0x7fd400addb98, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980, old_dd_tab=0x7fd4007d2f28,
    new_dd_tab=0x7fd4000be328) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/handler0alter.cc:5723
#1  0x000000000475f1a7 in ha_innobase::inplace_alter_table (this=0x7fd400addb98, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980,
    old_dd_tab=0x7fd4007d2f28, new_dd_tab=0x7fd4000be328) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/handler0alter.cc:1168
#2  0x00000000030c658c in handler::ha_inplace_alter_table (this=0x7fd400addb98, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980,
    old_table_def=0x7fd4007d2f28, new_table_def=0x7fd4000be328) at /home/y-asaba/src/mysql-8.0.13/sql/handler.h:5041
#3  0x00000000030b2108 in mysql_inplace_alter_table (thd=0x7fd4006d6da0, schema=..., new_schema=..., table_def=0x7fd4007d2f28, altered_table_def=0x7fd4000be328,
    table_list=0x7fd400d4fce8, table=0x7fd400f29600, altered_table=0x7fd400b150e0, ha_alter_info=0x7fd438f92980, inplace_supported=HA_ALTER_INPLACE_INSTANT,
    alter_ctx=0x7fd438f93570, columns=std::set with 0 elements, fk_key_info=0x7fd400f5da40, fk_key_count=0, fk_invalidator=0x7fd438f928c0)
    at /home/y-asaba/src/mysql-8.0.13/sql/sql_table.cc:11621
#4  0x00000000030bcc72 in mysql_alter_table (thd=0x7fd4006d6da0, new_db=0x7fd400d50280 "test", new_name=0x0, create_info=0x7fd438f948a0, table_list=0x7fd400d4fce8,
    alter_info=0x7fd438f949a0) at /home/y-asaba/src/mysql-8.0.13/sql/sql_table.cc:14963
#5  0x00000000035a93ca in Sql_cmd_alter_table::execute (this=0x7fd400d50380, thd=0x7fd4006d6da0) at /home/y-asaba/src/mysql-8.0.13/sql/sql_alter.cc:345
#6  0x0000000002ff9f78 in mysql_execute_command (thd=0x7fd4006d6da0, first_level=true) at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:4307
#7  0x0000000002ffc644 in mysql_parse (thd=0x7fd4006d6da0, parser_state=0x7fd438f96380, force_primary_storage_engine=false)
    at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:5041
#8  0x0000000002ff1f8c in dispatch_command (thd=0x7fd4006d6da0, com_data=0x7fd438f96cf0, command=COM_QUERY) at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:1687
#9  0x0000000002ff05ee in do_command (thd=0x7fd4006d6da0) at /home/y-asaba/src/mysql-8.0.13/sql/sql_parse.cc:1260
#10 0x000000000319dfd9 in handle_connection (arg=0x7d5cea0) at /home/y-asaba/src/mysql-8.0.13/sql/conn_handler/connection_handler_per_thread.cc:308
#11 0x0000000004625004 in pfs_spawn_thread (arg=0x7d430e0) at /home/y-asaba/src/mysql-8.0.13/storage/perfschema/pfs.cc:2836
#12 0x00007fd44d9186ba in start_thread (arg=0x7fd438f97700) at pthread_create.c:333
#13 0x00007fd44c2f441d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

実際にdebuggerでstep実行していくと、すぐに以下のようにinstant ddlかどうかを判断し、instantの場合はこの関数を抜けるようになっています。

template <typename Table>
bool ha_innobase::inplace_alter_table_impl(TABLE *altered_table,
                                           Alter_inplace_info *ha_alter_info,
                                           const Table *old_dd_tab,
                                           Table *new_dd_tab) {
  // ...
  if (!(ha_alter_info->handler_flags & INNOBASE_ALTER_DATA) ||
      is_instant(ha_alter_info)) {
  ok_exit:
    DEBUG_SYNC(m_user_thd, "innodb_after_inplace_alter_table");
    DBUG_RETURN(false);
  }

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/handler/handler0alter.cc#L5041-L5047

そしてCOMMIT処理の中でmetadataのみを書き換える処理をやっています（おそらく）。

template <typename Table>
static void dd_commit_inplace_instant(Alter_inplace_info *ha_alter_info,
                                      THD *thd, trx_t *trx, dict_table_t *table,
                                      const TABLE *old_table,
                                      const TABLE *altered_table,
                                      const Table *old_dd_tab,
                                      Table *new_dd_tab, uint64_t *autoinc) {
//...
  switch (type) {
//...
    case Instant_Type::INSTANT_ADD_COLUMN:
      dd_copy_private(*new_dd_tab, *old_dd_tab);

      if (!dd_table_is_partitioned(new_dd_tab->table()) ||
          dd_part_is_first(reinterpret_cast<dd::Partition *>(new_dd_tab))) {
        dd_commit_instant_table(table, old_table, altered_table,
                                &old_dd_tab->table(), &new_dd_tab->table());
      }

      if (dd_table_is_partitioned(new_dd_tab->table())) {
        dd_commit_instant_part(table,
                               reinterpret_cast<dd::Partition *>(new_dd_tab));
      }

      row_mysql_lock_data_dictionary(trx);
      innobase_discard_table(thd, table);
      row_mysql_unlock_data_dictionary(trx);
      break;

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/handler/handler0alter.cc#L3923-L3940

実際にibd2sdiでダンプすると以下のようにメタデータが追加されているのを確認できると思います。

            {
                "name": "c",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 3,
                "char_length": 40,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": false,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "A2FhYQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=",
                "default_value_utf8_null": false,
                "default_value_utf8": "aaa",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "default=616161;table_id=1066;",
                "column_key": 1,
                "column_type_utf8": "varchar(10)",
                "elements": [],
                "collation_id": 255,
                "is_explicit_collation": false
            },

SELECTの挙動

では、metadataだけを書き換えたのはなんとなくわかりましたが、実際にSELECTした際にどういう挙動になるのかを簡単に見てみました。（コードが巨大なので追いきれていない）

以下の場所にbreakpointを貼ります。

(gdb) b ha_innobase::rnd_next
(gdb) b rec_get_nth_field_instant

mysql cliで "select * from hoge4" という感じでSQLを投げます。

(gdb) bt
#0  rec_get_nth_field_instant (rec=0x7fd425950080 "\200", offsets=0x7fd438f93f50, n=0, index=0x7fd40093c868, len=0x7fd438f93328) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/include/rem0rec.ic:1238
#1  0x00000000049198de in row_sel_store_mysql_field_func (mysql_rec=0x7fd4009cfab8 "\377", prebuilt=0x7fd400e5e188, rec=0x7fd425950080 "\200", index=0x7fd40093c868, offsets=0x7fd438f93f50, field_no=0,
    templ=0x7fd400cbad38, sec_field_no=18446744073709551615, lob_undo=0x0) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/row/row0sel.cc:2867
#2  0x000000000491a2fb in row_sel_store_mysql_rec (mysql_rec=0x7fd4009cfab8 "\377", prebuilt=0x7fd400e5e188, rec=0x7fd425950080 "\200", vrow=0x0, rec_clust=0, index=0x7fd40093c868, offsets=0x7fd438f93f50,
    clust_templ_for_sec=false, lob_undo=0x0) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/row/row0sel.cc:3064
#3  0x0000000004920b14 in row_search_mvcc (buf=0x7fd4009cfab8 "\377", mode=PAGE_CUR_G, prebuilt=0x7fd400e5e188, match_mode=0, direction=0) at /home/y-asaba/src/mysql-8.0.13/storage/innobase/row/row0sel.cc:5462
#4  0x0000000004703511 in ha_innobase::index_read (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/ha_innodb.cc:8755
#5  0x000000000470465d in ha_innobase::index_first (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377") at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/ha_innodb.cc:9095
#6  0x00000000047048f0 in ha_innobase::rnd_next (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377") at /home/y-asaba/src/mysql-8.0.13/storage/innobase/handler/ha_innodb.cc:9169
#7  0x00000000033400a8 in handler::ha_rnd_next (this=0x7fd400845128, buf=0x7fd4009cfab8 "\377") at /home/y-asaba/src/mysql-8.0.13/sql/handler.cc:2822
...

最終的にここの処理で、デフォルトの値をつけるべきであればmetadataからデータを取り出してくっつける処理を行っています。

/** Gets the value of the specified field in the record.
This is only used when there is possibility that the record comes from the
clustered index, which has some instantly add columns
@param[in]  rec record
@param[in]  offsets array returned by rec_get_offsets()
@param[in]  n   index of the field
@param[in]  index   clustered index where the record resides
@param[in,out]  len length of the field, UNIV_SQL_NULL if SQL null
@return value of the field, could be either pointer to rec or default value */
UNIV_INLINE
const byte *rec_get_nth_field_instant(const rec_t *rec, const ulint *offsets,
                                      ulint n, const dict_index_t *index,
                                      ulint *len) {
  ulint off = rec_get_nth_field_offs(offsets, n, len);

  if (*len != UNIV_SQL_ADD_COL_DEFAULT) {
    return (rec + off);
  }

  ut_a(index != nullptr);
  ut_ad(index->has_instant_cols());

  return (index->get_nth_default(n, len));
}

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/rem0rec.ic#L1225-L1248

  /** Get the default value of nth field and its length if exists.
  If not exists, both the return value is nullptr and length is 0.
  @param[in]    nth nth field to get
  @param[in,out]    length  length of the default value
  @return   the default value data of nth field */
  const byte *get_nth_default(ulint nth, ulint *length) const {
    ut_ad(nth < n_fields);
    ut_ad(get_instant_fields() <= nth);
    const dict_col_t *col = get_col(nth);
    if (col->instant_default == nullptr) {
      *length = 0;
      return (nullptr);
    }

    *length = col->instant_default->len;
    ut_ad(*length == 0 || *length == UNIV_SQL_NULL ||
          col->instant_default->value != nullptr);
    return (col->instant_default->value);
  }

https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/dict0mem.h#L1170-L1182

innodbの物理フォーマットをちゃんと把握できていないので間違っていたらすみませんが、普通はblockに行のイメージを詰められるだけ詰めており、ブロック単位でメモリにロードしてから該当の行を取り出します。その行は通常はoffsetを計算しながらこの場所はこの列の値であると計算していますが、それの範囲外の場合にデフォルトの値をくっつけるという挙動になっているようです。

ということなので、以下のような制約がINSTANT ADD COLUMNについているのかと思います。

A column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported.

実験

簡単なパフォーマンス比較

まあ当然速いよねという結果です。

  • ALGORITHM=INSTANT
alter table hoge add column b varchar(10) default 'aaa', ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • ALGORITHM=INPLACE
mysql> alter table hoge add column b varchar(10) default 'aaa', ALGORITHM = INPLACE;
Query OK, 0 rows affected (21.74 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • ALGORITHM=COPY
mysql> alter table hoge add column b varchar(10) default 'aaa', ALGORITHM = COPY;
Query OK, 1000001 rows affected (2 min 45.31 sec)
Records: 1000001  Duplicates: 0  Warnings: 0

optimize tableをした場合どうなる？

information_schema.innodb_tablesをoptimize tableの前後でINSTANT_COLSの値が変わっているのがわかるかと思います。あと、get_nth_default にbreakpointを貼ってみましたがそこを通過しなかったので、テーブルを作り直すタイミングでデフォルトの値を行に入れて作り直しているようです。

mysql> select * from information_schema.innodb_tables where name = 'test/hoge' \G;
*************************** 1. row ***************************
     TABLE_ID: 1075
         NAME: test/hoge
         FLAG: 33
       N_COLS: 5
        SPACE: 18
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 1
1 row in set (0.01 sec)

mysql> optimize table hoge;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.hoge | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.hoge | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (21.89 sec)

mysql> select * from information_schema.innodb_tables where name = 'test/hoge' \G;
*************************** 1. row ***************************
     TABLE_ID: 1076
         NAME: test/hoge
         FLAG: 33
       N_COLS: 5
        SPACE: 19
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 0
1 row in set (0.00 sec)

ALTER TABLE後にINSERTした行はどうなる？

optimize tableと同じように、INSERTした行のデフォルトの値はちゃんと入っているようです。同じところにbreakpointを貼ってみてください。

updateするとテーブルサイズはどうなる？

すみません、実験しきれませんでした。なんとなく不要領域が増えそうな気がしていてテーブルが肥大化するのかなという推測をしているのですが、実際にやっていないのでどなたか調べてほしいです。

まとめ？

身も蓋もないまとめですが、INSTANT ADD COLUMNはよくできているなあ。という感想でした。あと、MySQLのソースコードに興味ある人にこういう感じで読むこともできるよ、とお伝えできていると幸いです。

次回はKarate Masterの@krtによる分析基盤のいい話が聞けると思います！

宣伝

freeeのプロダクト基盤というチームでは以下のポジションで積極採用しております。興味ある方はぜひ弊社オフィスまで遊びに来てください！

PostgreSQLのtable partitioning

PostgreSQL

例えば日付でテーブルを分けたいという時に、制約を使った方法があります。 http://www.postgresql.jp/document/9.3/html/ddl-partitioning.html

ただ、例えば年月でパーティションされたテーブルを作り忘れたり、triggerを更新しなかったりすると悲劇なので、そこら辺を忘れても大丈夫なようにしましょうという話です。

partitioningの例

説明のために以下のようなテーブルを作ります。salesテーブルを分割することを想定します。

CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT, price numeric CHECK (price > 0));
INSERT INTO items(name, price) VALUES ('hoge', 100);
INSERT INTO items(name, price) VALUES ('abc', 200);

CREATE TABLE sales (id SERIAL PRIMARY KEY, item_id INT REFERENCES items(id), count INT, ctime TIMESTAMP NOT NULL DEFAULT current_timestamp);
CREATE INDEX ctime_idx ON sales (ctime);

子テーブル(例として2013/12, 2014/1)は以下の様なSQLで作成するものとします。LIKE使った場合にインデックスや制約は引き継いでくれますが、外部キー制約は引き継いでくれないので、ちゃんと指定する必要があります。CHECKでこのテーブルに入るデータの制限をかけます。

CREATE TABLE sales_201312 (
    LIKE sales INCLUDING ALL,
    FOREIGN KEY (item_id) REFERENCES items(id),
    CHECK (ctime >= '2013-12-01 00:00:00' and ctime < '2014-01-01 00:00:00')
) INHERITS (sales);


CREATE TABLE sales_201401 (
    LIKE sales INCLUDING ALL,
    FOREIGN KEY (item_id) REFERENCES items(id),
    CHECK (ctime >= '2014-01-01 00:00:00' and ctime < '2014-02-01 00:00:00')
) INHERITS (sales);

実際に2014/1のデータを入れてEXPLAINすると、ctimeがWHERE句に入っていればうまく枝刈りしてくれます。下の例だと201312のテーブルが枝刈りされています。

hoge=# insert into sales_201401(item_id, count, ctime)  values (1, 2, '2014-01-10 10:00:00');
INSERT 0 1
hoge=# analyze;
ANALYZE
hoge=# explain select * from sales where ctime >= '2014-01-10 00:00:00' and ctime < '2014-01-11 00:00:00';
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..1.01 rows=2 width=20)
   ->  Seq Scan on sales  (cost=0.00..0.00 rows=1 width=20)
         Filter: ((ctime >= '2014-01-10 00:00:00'::timestamp without time zone) AND (ctime < '2014-01-11 00:00:00'::timestamp without time zone))
   ->  Seq Scan on sales_201401  (cost=0.00..1.01 rows=1 width=20)
         Filter: ((ctime >= '2014-01-10 00:00:00'::timestamp without time zone) AND (ctime < '2014-01-11 00:00:00'::timestamp without time zone))
(5 rows)
TRIGGER

やっと本題です。どのテーブルにINSERTするかは、アプリでもちろん決めることができますが、めんどくさい場合やCURRENT_TIMESTAMP使っている場合は、親テーブルにトリガー仕込んでおきます。親テーブルにINSERTするとトリガーが勝手に子テーブルのどこかに入れるようにします。

トリガー使う場合に注意したいのは以下の点です。

  • 振り分けルールがない
    • たとえば、201401ならsales_201401に入れるみたいなルールをそのまま列挙していると、いつかルールを書き忘れて想定しない挙動になってしまうかもしれません
  • 振り分けルールはあるが、実際に振り分け先のテーブルがない
    • 悲劇ですね

ということで上の2つの問題を解決するために、トリガーの中で動的にテーブル格納先を決めつつ、テーブルなければテーブル作るようなトリガーを作ってみます。

CREATE OR REPLACE FUNCTION sales_insert_trigger_func() RETURNS TRIGGER AS
  $$
    DECLARE
      partition text;
      createtable_sql text;
      startdate text;
      enddate text;
      parent_table text;
    BEGIN
      parent_table := 'sales';
      partition := parent_table || '_' || to_char(new.ctime, 'YYYYMM');
      EXECUTE 'INSERT INTO ' || partition || ' VALUES(($1).*)' USING new;
      RETURN NULL;
    EXCEPTION
        WHEN undefined_table THEN
            startdate := to_char(NEW.ctime, 'YYYY-MM-01');
            enddate := to_char(NEW.ctime + interval '1 month', 'YYYY-MM-01');
            createtable_sql := 'CREATE TABLE IF NOT EXISTS ' || partition || ' (LIKE '|| parent_table || ' INCLUDING ALL,
                                FOREIGN KEY (item_id) REFERENCES items(id),
                                CHECK (ctime >= ' || quote_literal(startdate) || ' AND ctime < ' || quote_literal(enddate) || ')) INHERITS ( ' || parent_table || ')';
            EXECUTE createtable_sql;
            EXECUTE 'INSERT INTO ' || partition || ' VALUES(($1).*)' USING new;
            RETURN NULL;
    END;
  $$
  LANGUAGE plpgsql;

CREATE TRIGGER insert_sales_trigger BEFORE INSERT ON sales FOR EACH ROW EXECUTE PROCEDURE sales_insert_trigger_func();

new.ctime(INSERTしようとしている行のctime)からテーブル名を決定し、EXECUTEでSQLを実行します。もしテーブルがない場合は、 undefined_tableというエラーが返ってくるので、テーブル作って再実行するということをやります。

実際にテーブルない状態で2014/2のデータをINSERTしてみた結果です。

hoge=# insert into sales(item_id, count, ctime)  values (1, 2, '2014-02-10 10:00:00');
NOTICE:  merging column "id" with inherited definition
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
                                  FOREIGN KEY (item_id) REFERENCES items(id),
                                  CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
NOTICE:  merging column "item_id" with inherited definition
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
                                  FOREIGN KEY (item_id) REFERENCES items(id),
                                  CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
NOTICE:  merging column "count" with inherited definition
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
                                  FOREIGN KEY (item_id) REFERENCES items(id),
                                  CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
NOTICE:  merging column "ctime" with inherited definition
CONTEXT:  SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
                                  FOREIGN KEY (item_id) REFERENCES items(id),
                                  CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
INSERT 0 0

hoge=# select * from sales_201402;
 id | item_id | count |        ctime
----+---------+-------+---------------------
  4 |       1 |     2 | 2014-02-10 10:00:00
(1 row)

なお注意点としては、

  • トリガー使った場合はINSERTの戻り値が常に0件になるのと、RETURNINGも動かないので、or mapperが勝手にreturningしている場合はうまく回避する必要があります
  • あまりまじめに検証していないので、同時にテーブル作成しようとした場合にどうなるかはちょっと怪しいので、基本的にはcrontabか何かにtable作るような定期ジョブを設定しておいたほうが良いです。といってもテーブル忘れた場合にエラーが出続けるよりはいいとは思います。

といったところでしょうか。

PostgreSQLをwarmupするツール(pg_warmup)作りました

PostgreSQL

pg_warmupとは

PostgreSQL用のwarmupツールです。slave追加する時とかに、キャッシュ載っていない状態でサービスインさせるとパフォーマンスでないで障害起きるみたいなことがよくある話かと思いますが、それを回避するために必要なテーブルやインデックスをキャッシュ(disk cache もしくは shared_buffer)に乗っけておくというツールです。

y-asaba/pg_warmup · GitHub

なお、PostgreSQLについてはDirect IO使って自分でIOをscheduleするような仕組みはなくて（正確に言うとWALの書き出しでDirect IO使う場合もある、はず）、page cache使うのでshared_bufferの値は物理メモリの25%くらいにしておけというbest practiceがあります。

詳しく知りたい方は、最近議論あったMLのスレッドにサマリがありますので、そちらを御覧ください。

仕組み

基本的には、cacheに乗っける=データを読み込む、ということをするだけです。直接ファイルを読み込んでpage cacheに乗っけるか、PostgreSQLのshared bufferを経由してデータを読み込みます。

どうやって物理ファイルのパスを特定するか？

pg_classのrelfilenodeに格納されるので、それを使います。例えばpgbench -i -s 200 hogeを実行しておくと、pgbench_accountsなどのテーブルができます。このpgbench_accountsのファイルの場所を調べるSQLがこれです。

SELECT relname, current_setting('data_directory') || '/' ||  pg_relation_filepath(oid) AS filepath,
       pg_relation_size(oid) AS filesize
   FROM pg_class WHERE relname = 'pgbench_accounts'

psqlで実行するとfilepathカラムに絶対パスが入っています。

     relname      |                  filepath                  |  filesize
------------------+--------------------------------------------+------------
 pgbench_accounts | /Users/y-asaba/pg/92/data/base/86530/86556 | 2685902848
(1 row)

ただ、このまま使うのは若干まずいところがあって、すでに説明したようにファイルは1GBで分割されるので、上のファイルパス + . + 数字というファイル名も読み込みの対象とする必要があります。

% find . | grep 86556                                                                                                                                                                 [22:12:37]
./base/86530/86556 ← これ
./base/86530/86556.1 ←これ
./base/86530/86556.2 ← これ
./base/86530/86556_fsm
./base/86530/86556_vm

インデックスのファイルパスもちょっとだけ複雑ですが、同じようにpg_classから取り出します。

SELECT relname, current_setting('data_directory') || '/' ||  pg_relation_filepath(oid) AS filepath,
       pg_relation_size(oid) AS filesize
   FROM pg_class WHERE oid IN (SELECT indexrelid FROM pg_index
                                  WHERE indrelid = (SELECT oid FROM pg_class WHERE relname = 'pgbench_accounts'))

pgbench_accountsの場合はprimary keyがあるだけなので、1件だけしか返ってきませんが、複数のインデックスがあれば複数行返ってきます。

       relname        |                  filepath                  | filesize
-----------------------+--------------------------------------------+-----------
 pgbench_accounts_pkey | /Users/y-asaba/pg/92/data/base/86530/86561 | 449249280

データの読み込み

cat して /dev/nullに捨てるだけです。ionice使える環境であればionice -c 3 cat fileをしたりもします。

shared bufferに乗っける

これも単純にseq scanするようなクエリを投げます。具体的にはカウントするだけです。

SELECT COUNT(*) FROM pgbench_accounts

ただ、この場合だとインデックス使っていないので、インデックスはshared bufferには乗っからないので注意してください。

使い方

pypiに登録したので、pip install pg_warmupでインストールすることができます。python 2.7と3.3では動作確認をしました。コマンドはgithubにあるREADMEにある通りで、

initdbしたディレクトリはinitdbをしたユーザしか読めないので、pg_warmupはinitdbを実行したユーザで実行してください。あと、warmupしようとしているPostgreSQLのport番号が違う場合や、パスワードが設定されている場合は、PG*環境変数をあらかじめセットしておいてください。

Usage: pg_warmup [options]

Options:
  -h, --help            show this help message and exit
  -t TABLE, --table=TABLE
                        warmup the named table
  -d DATABASE, --database=DATABASE
                        dbname
  -i                    use ionice command
  -s                    cache on shared buffer, not page cache
  -x                    execute warmup

MonetDBを調べてみる

未分類

awsのredshiftみたいな話もあったので、カラム指向DBとか列指向DBとか呼ばれる技術をMonetDBで調べてみました。

カラム指向DB

wikipediaに書いてある内容を見ていただくのが手っ取り早いと思いますが、普通のRDBMSだと1ブロックにたいして、行を詰められるだけ詰めるという仕組みだと思いますが、カラム指向DBの場合は行ではなく、ある列だけをひとまとめにするような仕組みっぽいです。

ちなみにpostgresの場合は、こんな感じでデータを詰め込んでいます。

ビルド

当然デバッグビルドします。シンボルつけて、最適化なしでビルドします。なお、ビルドの際にはlibxml-dev(ubuntuの場合)が必要です。

[sourcecode]

% CFLAGS="-g -O0" ./configure --prefix=$HOME

% make

% make install

[/sourcecode]

動かし方

mserver5というのがインストールされているので、とりあえずこれを起動すればokです。

[sourcecode]

% mserver5

# MonetDB 5 server v11.15.1 "Feb2013"

# Serving database 'demo', using 2 threads

# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked

# Found 999.285 MiB available main-memory.

# Copyright (c) 1993-July 2008 CWI.

# Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved

# Visit http://www.monetdb.org/ for further information

# Listening for connection requests on mapi:monetdb://127.0.0.1:50000/

# MonetDB/JAQL module loaded

# MonetDB/SQL module loaded

>

[/sourcecode]

クライアントはmclientというものを使います。初期ユーザとパスワードは"monetdb"のようです。

[sourcecode]

% mclient

user(y-asaba):monetdb

password:

Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013)

Database: MonetDB v11.15.1 (Feb2013), 'demo'

Type \q to quit, \? for a list of available commands

auto commit mode: on

sql>

[/sourcecode]

あとはmclient上でSQLをテーブル作ったりを普通にできます。

アーキテクチャ

http://www.monetdb.org/Documentation/Manuals/MonetDB/Architecture

こちらを見ても正直よくわからないところですが、MALというVMを実装していて、SQLを実行する際には最終的にはMALのinstructionを実行することになります。

例えば、create table aiueo (a int, b int)というテーブルを作った場合に、insert into aiueo values (1, 2)というのを実行すると、

  1. aiueoというテーブルのaというカラムに対して値をappendするfunction callする

  2. aiueoというテーブルのbというカラムに対して値をappendするfunction callする

  3. affected_rowsを返すfunction callする

  4. commitする

みたいなinstructionが生成され、実行されます。

/MonetDB-11.15.1/monetdb5/mal/mal_interpreter.c:

[sourcecode language="cpp" firstline="568"]

if (!RECYCLEentry(cntxt, mb, stk, pci)){

/* The interpreter loop

* The interpreter is geared towards execution a MAL procedure together

* with all its decendant invocations. As such, it provides the

* MAL abtract machine processor.

[/sourcecode]

[sourcecode language="cpp" firstline="636"]</pre>

case PATcall:

if (pci->fcn == NULL) {

ret = createScriptException(mb, stkpc, MAL, NULL,

"address of pattern %s.%s missing", pci->modname, pci->fcnname);

} else

ret = (str)(*pci->fcn)(cntxt, mb, stk, pci); <-- ここ

break;

[/sourcecode]

appendの場合は、mvc_appendが呼ばれます。

[sourcecode]</pre>

(gdb) bt

#0 BUNappend (b=0x279c540, t=0x296bd80, force=1 '\001') at gdk_bat.c:1319

#1 0x00007f8139c36d3f in delta_append_val (bat=0x14f2900, i=0x296bd80) at bat_storage.c:326

#2 0x00007f8139c36e2c in append_col (tr=0x22532d0, c=0x27682b0, i=0x296bd80, tpe=5) at bat_storage.c:343

#3 0x00007f8139b68597 in mvc_append_wrap (cntxt=0x107dc68, mb=0x2483290, stk=0x296bcc0, pci=0x28fbc40) at sql.mx:3261

#4 0x00007f813d2e9b98 in runMALsequence (cntxt=0x107dc68, mb=0x2483290, startpc=1, stoppc=0, stk=0x296bcc0, env=0x0, pcicaller=0x0) at mal_interpreter.c:641

#5 0x00007f813d2e9182 in callMAL (cntxt=0x107dc68, mb=0x2483290, env=0x7f81390f4cf0, argv=0x7f81390f4cb0, debug=0 '\000') at mal_interpreter.c:469

#6 0x00007f8139b32a04 in SQLexecutePrepared (c=0x107dc68, be=0x221ea00, q=0x10b4330) at sql_scenario.c:1773

#7 0x00007f8139b32da2 in SQLengineIntern (c=0x107dc68, be=0x221ea00) at sql_scenario.c:1840

#8 0x00007f8139b332c0 in SQLengine (c=0x107dc68) at sql_scenario.c:1941

#9 0x00007f813d313587 in runPhase (c=0x107dc68, phase=4) at mal_scenario.c:522

#10 0x00007f813d313751 in runScenarioBody (c=0x107dc68) at mal_scenario.c:567

#11 0x00007f813d313860 in runScenario (c=0x107dc68) at mal_scenario.c:586

#12 0x00007f813d314744 in MSserveClient (dummy=0x107dc68) at mal_session.c:431

#13 0x00007f813b02d9ca in start_thread (arg=<value optimized out>) at pthread_create.c:300

#14 0x00007f813ad8a21d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

#15 0x0000000000000000 in ?? ()

[/sourcecode]

物理ファイル

[sourcecode]

sql>select * from storage() where "table" = 'aiueo';

+--------+-------+--------+------+----------+-------+-----------+------------+----------+---------+--------+

| schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted |

+========+=======+========+======+==========+=======+===========+============+==========+=========+========+

| sys | aiueo | a | int | 15/1532 | 18 | 4 | 72 | 0 | 0 | false |

| sys | aiueo | b | int | 15/1531 | 18 | 4 | 72 | 0 | 0 | false |

+--------+-------+--------+------+----------+-------+-----------+------------+----------+---------+--------+

[/sourcecode]

locationというのが実際の物理ファイルで、ここに書き込まれていきます。普通のRDBMSであれば、1テーブル1ファイル(テーブルサイズがでかいと分割することもありますが)な構成に対して、1カラム1ファイルになるようです。で、どんな感じに圧縮されるのかを見たかったのですが、データサイズが小さいためか圧縮されなかったので、これはまた後日見るということで。

[sourcecode]

insert into aiueo values (9, 9)をしたあと。"¥t¥0¥0¥0"が9

% od -c 1531.tail

0000000 002 \0 \0 \0 003 \0 \0 \0 003 \0 \0 \0 003 \0 \0 \0

0000020 003 \0 \0 \0 003 \0 \0 \0 320 \a \0 \0 005 \0 \0 \0

0000040 005 \0 \0 \0 005 \0 \0 \0 005 \0 \0 \0 005 \0 \0 \0

*

0000100 005 \0 \0 \0 005 \0 \0 \0 \t \0 \0 \0

0000114

[/sourcecode]

まとめ

実用に耐えられるかどうかは全く検証していませんが、とりあえず簡単にMonetDBを眺めてみました。面白そうなのでもうちょっとソースコードを追いかけてみたいと思います。

XSLTを使ってXMLデータからオープン拡張辞書形式に変換する方法(3)

未分類

実際に変換してみよう

最後にXSLファイルとWikipediaのXMLを組み合わせて、拡張辞書を生成しましょう。変換処理はWindows7に付属しているPowerShellを使用します。PowerShellは「アクセサリ」の下に「Windows PowerShell」があるので起動してください。PowerShellスクリプトは次の通りです。なお、WikipediaのXMLファイルサイズが大きいため、変換処理には1GB以上のメモリが必要になります。

実行する前に、XSLファイルとXMLファイルが置いてあるフォルダに移動し、以下のプログラムをコピー＆ペーストしてください。フォルダの移動はcdコマンドを使います。

[sourcecode language="powershell"]

#

# XSLTの準備

#

[IO.Directory]::SetCurrentDirectory((pwd).Path)

$xslt = new-object System.Xml.Xsl.XslCompiledTransform

$setting = new-object System.Xml.Xsl.XsltSettings

$setting.EnableScript = $true

#

# XSLファイルの読み込みの準備

#

$reader = [system.xml.XmlReader]::Create("wiki2oed.xsl")

#

# オープン拡張辞書を書き出すための設定

#

$writer_setting = new-object System.Xml.XmlWriterSettings

$writer_setting.Indent = $true

$writer = [System.Xml.XmlWriter]::Create("WikipediaDictionary.dctx", $writer_setting)

$resolver = new-object System.Xml.XmlUrlResolver

#

# XSLファイルの読み込み

#

$xslt.Load($reader, $setting, $resolver)

#

# 変換

#

$xslt.Transform("jawiki-latest-abstract.xml", $writer)

$reader.Close()

$writer.Close()

[/sourcecode]

WikipediaDictionary.dctxというファイルが出来上がり、ダブルクリックすれば無事辞書がインストールされます。例えば「おたてのらん」を変換してみてください。以下のようにコメントが出るようになります。また、リンクをクリックすればWikipediaのページに飛ぶこともできます。

まとめ

本記事ではXSLTを使ってオープン拡張辞書を作成する方法を説明しました。例題として、abstractのみを収録したファイルから変換しました。文書全体をさらに解析することでもう少し語彙を増やせるかもしれません。また、今回は「あ」～「と」ではじまる単語のみを変換してみました。「な」以降のものについては皆様ぜひチャレンジしてみてください。

今回紹介したXSLTファイルとPowerShellスクリプトをこちらに置いておきました。 なお、スクリプトはご自由にお使いいただいて構いませんが、スクリプトや本記事が原因によるいかなる損害について無保証です。ご了承ください。

最後に、もしこの説明を読んで実際に作成した Wikipedia の辞書を再配布したりする場合は、Wikipedia の引用に関する規定を熟読してください。

XSLTを使ってXMLデータからオープン拡張辞書形式に変換する方法(2)

未分類

前回は、オープン拡張辞書の説明と、辞書情報に関する説明を行いました。ここらはXSLを使ってオープン拡張辞書を作成したいと思います。

XSLファイルを作成する

Wikipedia Dictionaryのデータを定義したので、WikipediaのXMLファイルからオープン拡張辞書フォーマットへXSLTを使って変換したいと思います。これからの説明は、Windows7上で作業を行うことを想定します。また、作成したXSLTはMSXMLの独自機能を一部使っています。

XSLTとは何かというと、XMLデータからXMLやCSVなどといった形式に変換する仕組みです。どのようなルールで変換するかをXSL(Extensible Stylesheet Language)で記述します。Wikipediaに詳しいことが書いてありますので興味ある方はご覧ください。ある程度XSLTについて知識があるという前提で説明を続けます。http://ja.wikipedia.org/wiki/XSLT

まずはXSLで変換ルールを作成する前に、どのような単語データを出力する必要があるかを先に見ていきたいと思います。単語データは以下の2つは必ず必要になります。

  1. 登録したい単語

    • 例：オープン拡張辞書

  2. その単語のよみ

    • 例：おーぷんかくちょうじしょ

登録したい単語とその読みについてはWikipediaのデータから取り出します。品詞とコストについて今回は決め打ちで、品詞は「名詞」、コストは「200」とします。上記の例をオープン拡張辞書フォーマットに変換すると次のようになります。

[sourcecode language="xml"]

<ns1:DictionaryEntry>

<ns1:InputString>おーぷんかくちょうじしょ</ns1:InputString> よみ（ひらがな）

<ns1:OutputString>オープン拡張辞書</ns1:OutputString> 単語

<ns1:PartOfSpeech>Noun</ns1:PartOfSpeech> “Noun”は名詞

<ns1:CommentData1>…</ns1:CommentData1> コメント

<ns1:URL>…</ns1:URL> リンク

<ns1:Priority>200</ns1:Priority> コスト

<ns1:ReverseConversion>true</ns1:ReverseConversion> 逆変換に使うか？

<ns1:CommonWord>false</ns1:CommonWord> 第一候補になるか？

</ns1:DictionaryEntry>

[/sourcecode]

オープン拡張辞書では4つのデータ以外に、単語に対してコメントをつけることも可能です。せっかくなので、Wikipediaのデータを活用してコメントもつけてみたいと思います。

変換するためには、変換前のXML形式を当然知っておく必要があるので、Jawiki-latest-abstract.xmlのスキーマを説明します。

実例を見たほうが早そうなので、サンプルの形式を作ってみました（実際にWikipediaにあるデータではなく、例として作りました）。

[sourcecode language="html"]

<doc>

…

</doc>

…

<doc>

<title>Wikipedia: オープン拡張辞書</title>

<url>http://ja.wikipedia.org/...</url>

<abstract>オープン拡張辞書（おーぷんかくちょうじしょ）は、・・・</abstract>

<links>

….

</links>

</doc>

</feed>

[/sourcecode]

<feed>タグが一番トップになり、その中に<doc>タグが複数入ります。この<doc>タグがWikipediaの1ページのデータに相当します。<doc>タグの下にさらに以下のタグを持ちます。

  • <title>

    • ページタイトル

  • <url>

    • ページのURL

  • <abstract>

    • 概要

  • <links>

    • リンク情報

さて、ざっとWikipediaのXMLファイルの構造を理解したので、このデータから辞書エントリを生成します。登録単語、単語のよみ、リンク以外は決め打ちにするとすでに決めましたので、残っている3つをXSLの中で取り出して、オープン拡張辞書形式に変換したいと思います。

登録単語

登録単語はページタイトル(titleタグ)から取り出します。必ず”Wikipedia: “という文字がページタイトルの前につくので、取り除く必要があります。

<title>Wikipedia: オープン拡張辞書</title>

単語のよみ

日本語のWikipediaの各ページは、概要の中に単語のよみを入れる習慣があるようです（すべてではないですが）。赤字の部分がよみになります。先頭に単語がきて、そのあとにかっこ内によみが入ります。

<abstract>オープン拡張辞書（おーぷんかくちょうじしょ）は、・・・</abstract>

リンク

リンクはurlタグのURLをそのまま使います。

以上で、登録単語、単語の読み、リンクの3つのデータを取り出す方法がわかりましたので、実際にXSLのコードに落とし込みます。ソースコード(wiki2oed.xsl)をこちらに置きましたので、お手元で開いてください。

XSLファイルは大きく分けると3つのブロックからなります。

1. よみを取り出したりするC#のコード(MSXML独自の機能)

かっこの中からよみを取り出します。かっこの中はカタカナや句読点が混じっていたりしているので、少し複雑な正規表現を書いていますが、よみをとる処理をしています。

2. 静的な辞書情報を埋め込んでいる処理(<xsl:templatematch="/feed">…</xsl:template> )

基本的には単純にタグを埋め込んでいるだけですが、最後の赤字の部分が、次に説明する単語生成処理を呼び出すために書いておく必要があります。

</ns1:DictionaryHeader>

<xsl:apply-templates select="doc"/>

</ns1:Dictionary>

3. 単語データを解析し、DictionaryEntryタグに変換している処理(<xsl:templatematch="doc">…</xsl:template>)

Wikipediaの各docタグを処理する部分です。1.で作成したC#のコードを呼び出して、登録単語、よみを抽出します。

              <xsl:variablename="title"select="user:RemoveParenthesis(substring-after(title, 'Wikipedia: '))" />

              <xsl:variablename="reading"select="translate(user:KatakanaToHiragana(user:GetReadingFromAbstract(abstract)), ' ', '')" />

コメントについてはそのまま取り出したデータを埋め込んで上げます。xsl:value-ofというのが、指定したタグからデータを取り出す操作になります。

                                            <ns1:CommentData1><xsl:value-ofselect="abstract" /></ns1:CommentData1>

                                            <ns1:URL><xsl:value-ofselect="url" /></ns1:URL>

これでXSLファイルの完成です。Wiki2oed.xslというファイル名でUTF-8で保存してください。

さて、次回で最後です。実際に辞書を生成したいと思います。

XSLTを使ってXMLデータからオープン拡張辞書形式に変換する方法(3)

XSLTを使ってXMLデータからオープン拡張辞書形式に変換する方法(1)

未分類

Microsoft Office IME2010からオープン拡張辞書と呼ばれる新しい辞書フォーマットがサポートされました。オープン拡張辞書はXML形式で記述することが可能になり、容易に編集することが可能となります。すでに何らかの単語リストをXML形式で持っていれば、XSLT(XML Stylesheet Language Transformations)を使って簡単にオープン拡張辞書を作成することできます。本記事ではXMLデータからオープン拡張辞書を作成する方法について解説したいと思います。

オープン拡張辞書フォーマット

オープン拡張辞書のXML形式のドキュメントはダウンロード可能です。以下のURLにアクセスし、上から2番目の” PackageForDLC\Open Extended Dictionary Format-Japanese.xps“をダウンロードしてください。

http://www.microsoft.com/downloads/details.aspx?FamilyID=f138dcd4-edb3-4319-bb69-82784e3ea52f&DisplayLang=ja

辞書を作成するには、大きく分けると2つの情報が必要になります。

  1. 辞書に関する情報(DictionaryHeaderタグ)
  2. 辞書に登録する単語情報(DictionaryEntryタグ)

XML形式は以下のようになります。

    <ns1:Dictionary xmlns:ns1="http://www.microsoft.com/ime/dctx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <ns1:DictionaryHeader>

              辞書に関する情報

      </ns1:DictionaryHeader>

      <ns1:DictionaryEntry>

              単語Aの定義

      </ns1:DictionaryEntry>

      <ns1:DictionaryEntry>

              単語Bの定義

      </ns1:DictionaryEntry>

              ….

    </ns1:Dictionary>            

 

辞書データの作成準備

では、実際に辞書を作ってみたいと思います。作り方を説明するための具体的な例として、WikipediaのXMLファイルから辞書(Wikipedia Dictionaryと命名)を作ってみたいと思います。WikipediaのXMLファイルは以下の場所からダウンロードすることができます。Jawiki-latest-abstract.xmlを使います。

http://download.wikimedia.org/jawiki/latest/

（注）WikipediaのデータはCreative Commons Attribution-ShareAlike licenseに従って配布されています。詳細はリンク先をご覧ください。

すでに説明したとおり、辞書を作成するには以下の2つのデータが必要です。

  1. 辞書に関する情報
    • 辞書の名前、バージョン、copyright、等
  2. 辞書に登録する単語情報
    • 単語の表記、よみ、品詞、等

1.については辞書を生成する前にあらかじめ用意しておく必要があります。2.についてはすでにXMLデータを持っているという前提で、XSLTを使って変換します。こちらについては後程解説します。

辞書情報

辞書情報は以下の6つのデータをあらかじめ用意しておく必要があります。それぞれの意味についてはデータフォーマットドキュメントの8ページ以降に書いてありますので、そちらをご覧ください。

  1. 辞書GUID(重複しない)
  2. プロパティウィンドウに出てくる辞書名
  3. 候補ウィンドウに出てくる辞書名
  4. 辞書の説明
  5. Copyright
  6. 辞書を置く場所(URL)

今回作成するWikipedia Dictionaryはこのようにしました。

  1. 辞書作成時にGUIDを自動生成します
    • GUIDはPowerShellを使って生成することができます。以下のコマンドをPowerShellコンソールで入力し、出力された文字列の前後にかっこ（｛｝）をつけてください。Windows7をお使いであれば、PowerShellは「アクセサリ」の下に「Windows PowerShell」があるので起動してください。

[guid]::NewGuid().Guid.ToUpper()

  1. Wikipedia辞書(あ～と)
    • 単語を登録できる上限が36万語なので、この上限を回避するために念のため「あ」から「と」ではじまる単語だけを収録することとします。
  2. Wikipedia辞書(あ～と)
  3. Wikipediaから生成した辞書です
  4. この辞書は Creative Commons Attribution/Share-Alike License（http://creativecommons.org/licenses/by-sa/3.0/） の下で利用できます。
  5. http://download.wikimedia.org/jawiki/
    1. 辞書の置き場ではなく、XMLファイルの置き場ですが、暫定的にこちらにしています。

さて、このデータをオープン拡張辞書フォーマットの辞書ヘッダ部分(DictionaryHeaderタグ)に割り当てると、次のようになります。なお、青字の部分は説明なので、実際のXMLファイルには含まれません。

      <ns1:DictionaryHeader>

        <ns1:DictionaryGUID>{B309E2A5-B617-4E0B-88FB-FB9D09598850}</ns1:DictionaryGUID> 生成したGUIDを”{“と”}”で囲います

        <ns1:DictionaryLanguage>ja-jp</ns1:DictionaryLanguage> 日本語の辞書なので”ja-jp”を指定してください

        <ns1:DictionaryVersion>1</ns1:DictionaryVersion> 辞書のバージョン番号です。とりあえず1にしています。

        <ns1:SourceURL>http://download.wikimedia.org/jawiki/</ns1:SourceURL> 辞書の置き場所を指定してください。

        <ns1:CommentInsertion>true</ns1:CommentInsertion> 作成する辞書ではコメントを表示できるようにするので、trueにしています

        <ns1:DictionaryInfo Language="ja-jp"> 日本語の辞書情報を書きます

          <ns1:ShortName>Wikipedia辞書(あ～と)</ns1:ShortName> 候補ウィンドウに出てくる辞書名を指定します

          <ns1:LongName>日本のWikipedia辞書(あ～と)</ns1:LongName> プロパティに出てくる辞書名を指定します

          <ns1:Description>Wikipediaから生成した辞書です</ns1:Description> 辞書の説明を指定します

          <ns1:Copyright>この辞書はウィキペディア(http://ja.wikipedia.org/)のテキストを利用しています。テキストはクリエイティブ・コモンズ表示-継承ライセンス(CC-BY-SA)の下で利用可能です。追加の条件が適用される場合があります。詳細はウィキペディアの利用規約を参照してください。ライセンスのURL： http://creativecommons.org/licenses/by-sa/3.0/deed.ja </ns1:Copyright> Copyrightを指定します。

          <ns1:CommentHeader1>概要</ns1:CommentHeader1>

        </ns1:DictionaryInfo>

        <ns1:DictionaryInfo Language="en-us">　英語の辞書情報を書きます。なくてもよいです。

          <ns1:ShortName>Wikipedia Dictionary(あ～と)</ns1:ShortName>

          <ns1:LongName>Japanese Wikipedia Dictionary(あ～と)</ns1:LongName>

          <ns1:Description>This dictionary is based on Japanese Wikipedia data.</ns1:Description>

          <ns1:Copyright>Wikipedia data is available under the Creative Commons Attribution/Share-Alike License (http://creativecommons.org/licenses/by-sa/3.0/legalcode) ; additional terms may apply. See Terms of Use (http://wikimediafoundation.org/wiki/Terms_of_Use) for details. </ns1:Copyright>

          <ns1:CommentHeader1>Abstract</ns1:CommentHeader1>

        </ns1:DictionaryInfo>

      </ns1:DictionaryHeader>

辞書情報に関する説明は以上になります。次回はXSLファイルを作る方法を説明したいと思います。

XSLTを使ってXMLデータからオープン拡張辞書形式に変換する方法(2)