カテゴリー「mariadb」

MariaDBに特化した、データベースの差分検出/反映ツールを作ってみました。

https://github.com/shigenobu/magentadesk

こちらのツールは、同一筐体内に存在する2つのデータベース(スキーマ)の差分を検出し、検出した差分結果を使って、反映対象先データベースにデータをコピーするものとなります。

以下のMariaDB特有機能をふんだんに使ってしまっているため、MySQLでは動きません。

  • Dynamic Column
  • Except syntax
  • CTE
  • Sequence

使いどころとしては、webサービスなどで、staging環境で確認が取れたデータを、production環境に反映するといったシチュエーションとなるかと。

staging環境とproduction環境の筐体を同一にしておき、productionはそこからレプリケーションでproduction専用のDBサーバに配るような形がよいかなと思います。

日本語のマニュアルは以下となります。

https://github.com/shigenobu/magentadesk/blob/master/README.ja.md

JAVAの単体実行バイナリをreleaseページからDLしてもられば使えます。

依存を極小化したので、わずか1.5MBのバイナリとなり、取り回しも楽かと。

そのほか、過去に作成したものですが、JAVAのTCPサーバ/クライアント、UDPサーバ/クライアントのMAVENライブラリも紹介させてください。

(redchest)

https://github.com/shigenobu/redchest

websocketライクなインタフェースで設計しており、NIO2のTCP実装を使っています。Linuxで動かせば、epollのシステムコールで処理します。特徴としては、断線などにも対応すべく、最終受信からN秒で接続を破棄するように作り込んでいます。注意点として、広域(日本⇔ヨーロッパなど)で使う場合、epollのシステムコールの制約なのか、ごく稀にMTUより小さい受信バッファサイズであっても、さらに分割される場合があります。(C/C++でも同様の現象確認済)その際は、次回受信で不足分が取得できますので、前回メッセージを一時的にセッションに蓄積するなどの工夫が必要です。

(blueshelf)

https://github.com/shigenobu/blueshelf

こちらもwebsocketライクなインターフェースで設計した、NIOのUDP実装を使っています。Linuxならselectのシステムコールですね。特徴としては、複数ポートでlistenすることで、多重のIOを実現しています。そもそも、UDPには多重IOの技術はあまり不要かと思いますが、複数ポートを使うことで、複数のIOをうまく使い、メニーcore環境におけるパフォーマンス向上を目指しました。こちらも、最終受信からN秒で、「切断」のような扱いにしています。

以上

投稿日時:2019年06月25日 22:53   カテゴリー:java, mariadb   [コメントがあればどうぞ]

MariaDB10.4.6がGAとしてリリースされました。(2019年6月18日)

10.3からやや遅れたそうですが、ほぼ1年、おめでとうございます。

今回は、10.3と比べると、MariaDB Server関連の機能的な追加はすくないように見えます。

そのかわり、Galeraがバージョン4になっており、こちらが今回の目玉のように見えます。

Percona XtraDB Cluster 5.7のGalera3より一歩先行した感じでしょうか。

変更点の概要は公式ページを参照ください。

https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-104/

これから諸々検証していければと思います。

以上

投稿日時:2019年06月25日 22:22   カテゴリー:mariadb   [コメントがあればどうぞ]

githubが開発しているOrchestoratorについて、

以前の記事で、MariaDBに対応していない?と記載してしまったが、

実際に試してみたところ、MariaDBのGTIDレプリケーションにも対応していたので、

その備忘録を記載します。


なお、検証したバージョンは以下となります。

  • CentOS7.5
  • MariaDB10.3.11
  • Orchestorator3.0.13

1.MariaDBのセットアップ

master、slave×2の準同期レプリケーションを構築します。

構成は以下の通り。

----------------------------------------
master
IP:192.168.35.11
----------------------------------------
     |                       |
     |(semi sync)            |(semi sync)
     |                       |
    --------------------    --------------------
    slave                   slave
    IP:192.168.35.12        IP:192.168.35.13
    --------------------    --------------------

設定は以下の通り。

# マルチソースをやる際に必要かもしれないので、レプリケーションクラスターで共通の数字
gtid_domain_id=11

# レプリケーションクラスター間で一意の数字(IPアドレスの下2桁など)
server_id={{ server_id }}

# 名前解決ができない環境向けに、自身のノードのIPアドレスを設定しておく
report_host={{ ip_addr }}

# とりあえず0だが、動的に変更する
read_only=0

# 安定のrow
binlog_format=row

# 毎コミットごとにバイナリログに書き出す
sync_binlog=1

# フェイルオーバー時にslaveが昇格しても大丈夫なように
log_slave_updates=1

# レプリケーション安全設定
relay_log_recovery=1

# 大きなリレーログへの対応
slave_max_allowed_packet=1G

# タイムアウト長めに
slave_net_timeout=3600

# slaveのSQLスレッドの並列化
slave_parallel_threads={{ cpu_num }}

# 厳格モード
gtid_strict_mode=1

# 以下、準同期設定(MariaDB10.3では、plugin_load_addは不要)
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1500
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_wait_point=AFTER_SYNC

そして、レプリケーションを開始しておく。



2.Orchestoratorの設定

IPアドレス192.168.35.10のOrchestorator専用ノードを立てる。

orchestorator.conf.jsonの内容は以下の通り。

{
  "Debug": false,
  "EnableSyslog": false,
  "ListenAddress": ":3000",
  "MySQLTopologyUser": "adminuser",
  "MySQLTopologyPassword": "adminpassword",
  "BackendDB": "sqlite",
  "SQLite3DataFile": "/usr/local/orchestrator/orchestrator.sqlite3",
  "DiscoverByShowSlaveHosts": true,
  "InstancePollSeconds": 5,
  "UnseenInstanceForgetHours": 240,
  "SnapshotTopologiesIntervalHours": 0,
  "InstanceBulkOperationsWaitTimeoutSeconds": 10,
  "HostnameResolveMethod": "default",
  "MySQLHostnameResolveMethod": "@@report_host",
  "SkipBinlogServerUnresolveCheck": true,
  "ExpiryHostnameResolvesMinutes": 60,
  "RejectHostnameResolvePattern": "",
  "ReasonableReplicationLagSeconds": 10,
  "ProblemIgnoreHostnameFilters": [],
  "VerifyReplicationFilters": false,
  "ReasonableMaintenanceReplicationLagSeconds": 20,
  "CandidateInstanceExpireMinutes": 60,
  "DetectClusterAliasQuery": "SELECT @@report_host",
  "PromotionIgnoreHostnameFilters": [],
  "DetectSemiSyncEnforcedQuery": "",
  "URLPrefix": "",
  "StatusEndpoint": "/api/status",
  "StatusSimpleHealth": true,
  "StatusOUVerify": false,
  "AgentPollMinutes": 60,
  "UnseenAgentForgetHours": 6,
  "StaleSeedFailMinutes": 60,
  "SeedAcceptableBytesDiff": 8192,
  "PseudoGTIDPattern": "",
  "PseudoGTIDPatternIsFixedSubstring": false,
  "PseudoGTIDMonotonicHint": "asc:",
  "DetectPseudoGTIDQuery": "",
  "BinlogEventsChunkSize": 10000,
  "SkipBinlogEventsContaining": [],
  "ReduceReplicationAnalysisCount": true,
  "FailureDetectionPeriodBlockMinutes": 60,
  "RecoveryPeriodBlockSeconds": 3600,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "*"
  ],
  "OnFailureDetectionProcesses": [],
  "PreFailoverProcesses": [],
  "PostFailoverProcesses": [
    "echo -e 'Failed server is {failedHost}:{failedPort}.\nSuccessed server is {successorHost}:{successorPort}.' | mail -s 'Complete Orchestrator Failover' yourname@sample.com"
  ],
  "PostUnsuccessfulFailoverProcesses": [
    "echo -e 'Failed server is {failedHost}:{failedPort}.' | mail -s 'Failed Orchestrator Failover' yourname@sample.com"
  ],
  "PostMasterFailoverProcesses": [
    "/usr/local/bin/detect_orchestrator_failover.py -u adminuser -p adminpassword -H {successorHost} -P {successorPort}"
  ],
  "PostIntermediateMasterFailoverProcesses": [],
  "CoMasterRecoveryMustPromoteOtherCoMaster": true,
  "DetachLostSlavesAfterMasterFailover": true,
  "ApplyMySQLPromotionAfterMasterFailover": true,
  "MasterFailoverDetachSlaveMasterHost": false,
  "MasterFailoverLostInstancesDowntimeMinutes": 0,
  "PostponeSlaveRecoveryOnLagMinutes": 0
}

※adminuserは、レプリケーションクラスターに対してアクセス可能なsuperユーザとする。


ここでのポイントは、

"MySQLHostnameResolveMethod": "@@report_host"
"DetectClusterAliasQuery": "SELECT @@report_host"

である。

これにより、Orchestoratorはレプリケーションクラスターの状態取得をIPアドレスで行うことができるようになる。

この後、ひとまずOrechstoratorにレプリケーションクラスターのmasterノードを登録する。masterを登録しておけば、slaveも自動的に発見してくれる。

なお、登録方法は、WebGUIやらAPIなどであるが、ここでは割愛。



3.フェイルオーバー時のMariaDBパラメータの動的設定処理の配備

上記の設定の

"PostMasterFailoverProcesses"

の部分の

/usr/local/bin/detect_orchestrator_failover.py

を以下の感じで実装する。

なお、以下の処理は、masterのフェイルオーバー完了時に呼び出される。

#!/usr/bin/python
# -*- coding: utf-8 -*-
import os
import sys
import traceback
import datetime
import re
import argparse
import MySQLdb

parser = argparse.ArgumentParser(description = "detect orchestrator failover.")
parser.add_argument("-u", type=str, help = "(required) mysql user", required=True)
parser.add_argument("-p", type=str, help = "(required) mysql pass", required=True)
parser.add_argument("-H", type=str, help = "(required) master host", required=True)
parser.add_argument("-P", type=str, help = "(required) master port", required=True)
args = parser.parse_args()

mysql_user = args.u
mysql_pass = args.p
master_host = args.H
master_port = args.P

fp = None
master_conn = None
master_c = None
try:
  # log
  log_path = '/path_to_your_dir/detect_orchestrator_failover.log'
  fp = open(log_path, 'a')

  # connect master
  master_conn = MySQLdb.connect(
    user=mysql_user,
    passwd=mysql_pass,
    host=master_host,
    port=int(master_port),
    db='mysql'
  )
  master_c = master_conn.cursor(MySQLdb.cursors.DictCursor)
  fp.write('[{}]new master is {}:{}.\n'.format(datetime.datetime.now(), master_host, master_port))

  # set master
  master_queries = []
  master_queries.append('set global read_only = 0')
  for sql in master_queries:
    master_c.execute(sql)
    fp.write('[{}]new master query is {}.\n'.format(datetime.datetime.now(), sql))

  # get slaves
  sql = "show slave hosts"
  master_c.execute(sql)
  rows = master_c.fetchall()
  if isinstance(rows, tuple):
    for row in rows:
      slave_conn = None
      slave_c = None
      try:
        slave_host = row['Host']
        slave_port = row['Port']

        # connect slave
        slave_conn = MySQLdb.connect(
          user=mysql_user,
          passwd=mysql_pass,
          host=slave_host,
          port=int(slave_port),
          db='mysql'
        )
        slave_c = slave_conn.cursor(MySQLdb.cursors.DictCursor)
        fp.write('[{}]new slave is {}:{}.\n'.format(datetime.datetime.now(), slave_host, slave_port))

        # set slave
        slave_queries = []
        slave_queries.append('set global read_only = 1')
        for sql in slave_queries:
          slave_c.execute(sql)
          fp.write('[{}]new slave query is {}.\n'.format(datetime.datetime.now(), sql))

      except:
        fp.write('[{}]{}\n'.format(datetime.datetime.now(), traceback.format_exc()))
      finally:
          if slave_c is not None:
            slave_c.close()
          if slave_conn is not None:
            slave_conn.close()

except:
  fp.write('[{}]{}\n'.format(datetime.datetime.now(), traceback.format_exc()))
finally:
  if master_c is not None:
    master_c.close()
  if master_conn is not None:
    master_conn.close()
  if fp is not None:
    fp.close()


ここまでやっておくことにより、

  • IPアドレスベースでOrchestorator上からレプリケーションクラスターの認識/操作が行われる
  • フェイルオーバー時にslaveのread_onlyを1に変更できる

ということができる。


ちなみに、Orchestoratorでは、

masterのダウンに対する振舞のみがhookとして設定できるようなので、

slaveを追加した場合に自動的にread_onlyを設定するといったことは出来無いように見えます。(たぶん)


マニュアルが細かいので、利用の際はがんばってマニュアルを読んでみてください。

https://github.com/github/orchestrator/tree/master/docs


(参考)

https://qiita.com/rerorero/items/1f06cc8db9c469191289

https://www.s-style.co.jp/blog/2018/11/2875/

以上

投稿日時:2019年03月03日 01:12   カテゴリー:mariadb   [コメントがあればどうぞ]

MariaDBにてudfを作成したのですが、

生成カラムで利用する際の制限が存在しているようなので記載します。

なお、MariaDBのバージョンは、10.3.13で、

以下のようなudfを作成したとします。

> create function myfunc returns string soname 'myfunc.so';

1.virtualタイプの生成カラム

virtualタイプの生成カラムでは、条件式として使えます。

以下のようなalterをすることができます。

> alter table t1 add column c2 varchar(64) as (myfunc(c1)) virtual;

しかし、c2に対するINDEXを作成することはできません。



2.persistent(stored)タイプの生成カラム

persistentタイプの生成カラムでは、udfを使えません。

当然INDEXも作成できません。



以下のマニュアルにもその旨が記載されていますが、udfを用いたvirtualタイプの生成列にINDEXを作成することができないとは記載されていないような。。

https://mariadb.com/kb/en/library/generated-columns/



実は、この制約は結構痛いなーと感じてます。

udfは、C/C++やらgolangで実装できるので、アプリケーションの処理を通さなければ判定できなかった状態を、データベース側で判定できるようになる、、にも拘わらずINDEXが使えない、つまり高速アクセスできない。。。残念。

また、persistentタイプにも使えないことで、文字列を全文検索用に整形するようなudfを作っても、すごく便利には使えないなどのことが発生してしまう。

(FTSに対応するのは、persistentタイプのみなので)



将来のバージョンアップに期待。



以上


投稿日時:2019年03月02日 23:07   カテゴリー:mariadb   [コメントがあればどうぞ]

MariaDBにMroongaはバンドルされていますが、

TokenizerとしてMecabを使う際は、少し注意が必要です。

公式には、以下のように書かれていますが、手順を残しておきます。

https://mariadb.com/kb/en/library/mroonga-overview/

「Tokenise using MeCab. Required Groonga to be buillt with MeCab support.」

なお、OSはCentOS7、MariaDBのバージョンは、10.3.8です。


まずは、バンドルされているMroongaのインストールを見ていきます。

公式(https://mariadb.com/kb/en/library/about-mroonga/)を参考にしています。

1.プラグインのインストール

MariaDB [(none)]> INSTALL SONAME 'ha_mroonga';

2.Mroongaのインストール確認

MariaDB [(none)]> show engines;
+--------------------+---------+-----------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                 | Transactions | XA   | Savepoints |
+--------------------+---------+-----------------------------------------+--------------+------+------------+
| CSV                | YES     | Stores tables as CSV files              | NO           | NO   | NO         |
:(省略)
| Mroonga            | YES     | CJK-ready fulltext search, column store | NO           | NO   | NO         |
:(省略)
+--------------------+---------+-----------------------------------------+--------------+------+------------+

3.UDFの作成

公式(https://mariadb.com/kb/en/library/creating-mroonga-user-defined-functions/)より引用。

4.プラグインの確認

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+---------------+---------+
| Name                          | Status   | Type               | Library       | License |
+-------------------------------+----------+--------------------+---------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
:(省略)
| Mroonga                       | ACTIVE   | STORAGE ENGINE     | ha_mroonga.so | GPL     |
| Mroonga_stats                 | ACTIVE   | INFORMATION SCHEMA | ha_mroonga.so | GPL     |
+-------------------------------+----------+--------------------+---------------+---------+

5.Tokenizerの確認

MariaDB [(none)]> select json_detailed(mroonga_command("tokenize TokenBigram '東京都'"));
+----------------------------------------------------------------+
| json_detailed(mroonga_command("tokenize TokenBigram '東京都'")) |
+----------------------------------------------------------------+
| [
    {
        "value": "東京",
        "position": 0,
        "force_prefix": false
    },
    {
        "value": "京都",
        "position": 1,
        "force_prefix": false
    },
    {
        "value": "都",
        "position": 2,
        "force_prefix": false
    }
]      |
+----------------------------------------------------------------+
MariaDB [(none)]> select json_detailed(mroonga_command("tokenize TokenMecab '東京都'"));
ERROR 1026 (HY000): [tokenize] nonexistent tokenizer 

TokenBigramは使えるが、TokenMecabは使えない。。。

このように、公式にバンドルされているMroongaではMecabが使えないので、

Groongaレポジトリから取得するMroongaを使うには、

一旦削除しておく必要があります。

MariaDB [(none)]> UNINSTALL SONAME 'ha_mroonga';

ここからGroongaレポジトリからもろもろ取得し、設定していきます。

主な設定方法は、以下の公式ページに記載されています。

http://mroonga.org/ja/docs/install/centos.html#centos-7-with-mariadb-10-3-package

1.レポジトリのインストール

# yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm

2.Mroonga関連インストール

# yum install -y --enablerepo=epel mecab mecab-devel groonga groonga-tokenizer-mecab mariadb-10.3-mroonga

※依存関係により、この時点でMariaDB-Serverのバージョンが上がる可能性あるため、mysql_upgradeを適宜実行する。

3.Mecabの確認

$ mecab -D
filename:    /usr/lib64/mecab/dic/ipadic/sys.dic
version:    102
charset:    utf8
type:    0
size:    392126
left size:    1316
right size:    131
$ echo '東京都' | mecab
東京    名詞,固有名詞,地域,一般,,,東京,トウキョウ,トーキョー
都    名詞,接尾,地域,,,*,都,ト,ト
EOS

4.Groongaの確認

$ groonga --version | head -1
Groonga 8.0.9 [linux-gnu,x86_64,utf8,match-escalation-threshold=0,nfkc,mecab,msgpack,mruby,onigmo,zlib,lz4,zstd,epoll]

ここまでで、Mecabが無事動作し、Groongaからも認識されていればOK。

5.Mroongaプラグインをインストール

MariaDB [(none)]> source /usr/share/mroonga/install.sql;

上記コマンドで、Groonga提供のMroongaライブラリがインストールされ、UDFも上書きされます。

6.プラグインの状態確認

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+------------------------+---------+
| Name                          | Status   | Type               | Library                | License |
+-------------------------------+----------+--------------------+------------------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
:
| Mroonga                       | ACTIVE   | STORAGE ENGINE     | ha_mroonga_official.so | GPL     |
+-------------------------------+----------+--------------------+------------------------+---------+

お、共有ライブラリの名称が「ha_mroonga.so」から「ha_mroonga_official.so」に変わっていますね。

7.Mecabの実行確認

MariaDB [(none)]> select json_detailed(mroonga_command("tokenize TokenMecab '東京都'"));
+---------------------------------------------------------------+
| json_detailed(mroonga_command("tokenize TokenMecab '東京都'")) |
+---------------------------------------------------------------+
| [
    {
        "value": "東京",
        "position": 0,
        "force_prefix": false,
        "force_prefix_search": false
    },
    {
        "value": "都",
        "position": 1,
        "force_prefix": false,
        "force_prefix_search": false
    }
]    |
+---------------------------------------------------------------+

おおー、無事にMariaDBからMecabを使えるようになりました。

長くなりましたが、以上です。


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

MariaDB10.3系で導入されたsequence(nextvalの方)において、

ほかテーブルのdefault値として設定できるようです。

確認したバージョンは、10.3.8です。


最初にsequenceを作ります。

MariaDB [test]> create sequence s_user increment by 0;

このとき、galeraなど使う際、問題ないように「increment by 0」をつけておきます。

ちなみに、このときのsequence関連のパラメータは以下の通りなので、

「increment by 0」をつけても、1ずつインクリメントされます。

MariaDB [test]> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------

次に以下のようなテーブルを作成します。

MariaDB [test]> create table t_user (
  user_id int not null,
  name varchar(32) not null,
  seq_no bigint not null unique default nextval(s_user),
  primary key (user_id)
);

上記テーブルにおいて、seq_noカラムのdefault値にsequenceの結果を設定したので、以下のようなinsertにて、nextvalの結果が格納されます。

MariaDB [test]> insert into t_user (user_id, name) values (1, 'name01');
MariaDB [test]> insert into t_user (user_id, name) values (2, 'name02');
MariaDB [test]> insert into t_user (user_id, name) values (3, 'name03');
MariaDB [test]> insert into t_user (user_id, name) values (11, 'name11');
MariaDB [test]> insert into t_user (user_id, name) values (12, 'name12');
MariaDB [test]> insert into t_user (user_id, name) values (13, 'name13');

MariaDB [test]> select * from t_user;
+---------+--------+--------+
| user_id | name   | seq_no |
+---------+--------+--------+
|       1 | name01 |      1 |
|       2 | name02 |      2 |
|       3 | name03 |      3 |
|      11 | name11 |      4 |
|      12 | name12 |      5 |
|      13 | name13 |      6 |
+---------+--------+--------+


sequenceが導入されてから、見落としていたのですが、なにげにこれは嬉しい。

公式にもこっそり書いてありました。

https://mariadb.com/kb/en/library/sequence-overview/

PERSISTENTタイプの生成カラムで設定できたら、変更不能な連番を作り出せたので、それがあっても良かったのかなと少し思います。(とはいえ変更不能も困るか。。)

なお、sequenceの上限は、

9223372036854775806

で、javaなどのlong型の限界値

9223372036854775807

より-1したものとなっています。

以上


投稿日時:2018年12月14日 14:42   カテゴリー:mariadb   [コメントがあればどうぞ]

MariaDB10.2で導入されたCHECK CONSTRAINTについて挙動を確認してみました。

使用したバージョンは、10.3.8です。


まず、create table時に一緒に制約を入れてみる。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(32) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `reg_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `check_status` CHECK (`status` between 1 and 10)
)

これで、statusカラムは1以上10以下しか入らないはずで、以下のSQLを流してみる。

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u1', 1, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u2', 10, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u3', 0, now());
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u4', 11, now()); 
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

見事に、0と11が弾かれている。
もちろん、0と11を登録可能なようにするためには、一度制約を解除する必要がある。

 

次に、ALTERのときの動きを見てみる。

前回作って800万件入っている以下のテーブルを利用する。

 
create table user ( 
  id integer not null auto_increment, 
  name varchar(64), 
  money_total_virtual integer as (money_free + money_paid) virtual
  money_free integer, 
  money_paid integer, 
  primary key (id) 
); 

・通常カラムへの制約

MariaDB [test]> alter table user add constraint check_money_free check (money_free <= 100);
Query OK, 8388608 rows affected (59.516 sec)           
Records: 8388608  Duplicates: 0  Warnings: 0

・virtualカラムへの制約

MariaDB [test]> alter table user add constraint check_money_total_virtual check (money_total_virtual <= 200);
Query OK, 8388608 rows affected (1 min 0.316 sec)      
Records: 8388608  Duplicates: 0  Warnings: 0

やはり、大量のレコードがある状態では、制約を掛けるのも時間がかかることがわかる。

 

最後にCHECK制約の仕様をまとめておきます。

  • すでに入っているデータが制約違反の場合、制約自体が掛からない
  • 制約の発動はINSERT/UPDATE時
  • primary keyに対しても制約は掛けられる
  • auto_incrementに対しては制約は掛けることができない

合わせて公式のマニュアルも参照ください。

https://mariadb.com/kb/en/library/constraint/

 

以上

投稿日時:2018年11月13日 17:44   カテゴリー:mariadb   [コメントがあればどうぞ]

MariaDBの生成カラムをALTERするときの速度検証してみました。

確認バージョンは、10.3.8です。

 

準備として、以下のようなテーブルを作成し、データを投入しておきます。

create table user (
  id         integer not null auto_increment,
  name       varchar(64),
  money_free integer,
  money_paid integer,
  primary key (id)
);
insert into user () values ();
insert into user (id) select 0 from user;
: 上を20回くらい実行

update user set name = concat('name-', lpad(id, 10, '0')), money_free = ceil(rand() * 100), money_paid = ceil(rand() * 100);

MariaDB [test]> select count(*) from user;
+----------+
| count(*) |
+----------+
| 8388608  |
+----------+
1 row in set (1.579 sec)

下準備として、約800万件のデータを投入。


この状態で、通常・PERSITENT・VIRTUALのカラムをALTERで追加してみます。

通常カラムは、最後尾に追加すると、INSTANTが発動してしまうので、すべてAFTERで途中に差し込んでみます。

 

・通常

# カラム追加
MariaDB [test]> alter table user add column money_total_nornaml integer after name;
Query OK, 0 rows affected (17.338 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx01 on user (money_total_nornaml);
Query OK, 0 rows affected (20.096 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_nornaml;
Query OK, 0 rows affected (16.986 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加:17秒

インデックス追加:20秒

カラム削除:16秒

という結果でした。

 

・PERSISTENT

# カラム追加
MariaDB [test]> alter table user add column money_total_persitent integer as (money_free + money_paid) persistent after name;
Query OK, 8388608 rows affected (36.700 sec)
Records: 8388608 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx02 on user (money_total_persitent);
Query OK, 0 rows affected (19.660 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_persitent;
Query OK, 0 rows affected (16.810 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加:36秒

インデックス追加:19秒

カラム削除:16秒

という結果でした。

 

・VIRTUAL

# カラム追加
MariaDB [test]> alter table user add column money_total_virtual integer as (money_free + money_paid) virtual after name;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx03 on user (money_total_virtual);
Query OK, 0 rows affected (20.224 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_virtual;
Query OK, 0 rows affected (0.011 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加:0.01秒

インデックス追加:20秒

カラム削除:0.01秒

という結果でした。

 

PERSISTENTは、実データの生成も一緒に行われるので、

通常に比べて、ADDも遅くなるという結果でした。

VIRTUALは、実データの生成が無いので、

通常に比べて、ADD/DROPも圧倒的に高速ですね。

インデックスの作成速度は変わらないですね。

VIRTUALはPERISITENTに比べて、演算コストがかかるため、

そのあたりは注意が必要であると思いますが、

基本的には、VIRTUALを使っていく方針でよいのかな?という考察です。

 

以上

投稿日時:2018年11月09日 11:47   カテゴリー:mariadb   [コメントがあればどうぞ]

まず自分自身がMariaDBの生成カラム周りおよびJSONについて、

以下2点勘違いをしていた。

  • MariaDBでもMySQLと同様に、VIRTUAL COLUMNにインデックスを張ることができる(すいません、できないと思ってました。。)
  • MariaDBでもCHECK制約を使うことで、「JSONの正しさ」を保証できる(すいません、できないと思ってました。。)

以前書いた記事にも訂正を追記しておきました。

 

というわけで、MariaDB10.2.17、10.3.10のバージョンで生成カラムについて見ていきます。


ではまず、以下のようなテーブルを作ってみます。

CREATE TABLE `mail` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`to` varchar(255) NOT NULL,
`cc` varchar(255),
`bcc` varchar(255),
`subject` varchar(255) NOT NULL,
`body` json NOT NULL,
`addrs` text AS (CONCAT_WS(',', `to`, `cc`, `bcc`)) PERSISTENT,
`sign` varchar(255) AS (JSON_VALUE(`body`, '$.sign')) VIRTUAL,
CHECK (JSON_VALID(`body`)),
PRIMARY KEY (`id`),
FULLTEXT KEY `mail_fidx01` (`addrs`),
KEY `mail_idx01` (`sign`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ちょっと長いのですが、ポイントは以下の通り。

  1. addrsカラムに対して、PERSISTENTタイプの生成カラムを利用して、全文検索INDEXを張る
  2. bodyカラムに対して、JSONの型チェックを行う
  3. signカラムに対して、VIRTUALタイプの生成カラムを利用して、JSONの部分抽出を行い、セカンダリーINDEXを張る

1はカラムを結合した状態を全文検索にしたい場合、

2はJSONの正しさを保証したい場合、

3はJSONの中の特定のキーを条件に高速にデータアクセスしたい場合、

に用いられるような想定です。

この状態のテーブルを見てみます。

MariaDB [test]> desc mail;
+---------+--------------+------+-----+---------+-------------------+
| Field   | Type         | Null | Key | Default | Extra             |
+---------+--------------+------+-----+---------+-------------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment    |
| to      | varchar(255) | NO   |     | NULL    |                   |
| cc      | varchar(255) | YES  |     | NULL    |                   |
| bcc     | varchar(255) | YES  |     | NULL    |                   |
| subject | varchar(255) | NO   |     | NULL    |                   |
| body    | longtext     | NO   |     | NULL    |                   |
| addrs   | text         | YES  | MUL | NULL    | STORED GENERATED  |
| sign    | varchar(255) | YES  | MUL | NULL    | VIRTUAL GENERATED |
+---------+--------------+------+-----+---------+-------------------+
8 rows in set (0.002 sec)

MariaDB [test]> show index from mail;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mail  | 0          | PRIMARY     | 1            | id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               |
| mail  | 1          | mail_idx01  | 1            | sign        | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               |
| mail  | 1          | mail_fidx01 | 1            | addrs       | NULL      | NULL        | NULL     | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.000 sec)

生成カラムの情報がdescのExtraにも表現されていますね。

ちなみに、生成カラムは「NOT NULL DEFAULT ・・・」は付けられないようで、生成カラムの評価ができない(失敗?)の場合は、NULLが入る仕様のようです。

 

次に以下のようなデータを流し込んで、結果を見てみます。

INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00001@example.com', 'cc-00001@example.com', 'bcc-00001@example.com', 'subject-00001', '{"sign": "sign-00001", "message": "message-00001"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00002@example.com', 'cc-00002@example.com', null , 'subject-00002', '{"sign": "sign-00002", "message": "message-00002"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00003@example.com', null , null , 'subject-00003', '{"sign": "sign-00003", "message": "message-00003"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00004@example.com', null , null , 'subject-00004', '{"message": "message-00004"}');
MariaDB [test]> select * from mail\G;
*************************** 1. row ***************************
     id: 1
     to: to-00001@example.com
     cc: cc-00001@example.com
    bcc: bcc-00001@example.com
subject: subject-00001
   body: {"sign": "sign-00001", "message": "message-00001"}
  addrs: to-00001@example.com,cc-00001@example.com,bcc-00001@example.com
   sign: sign-00001
*************************** 2. row ***************************
     id: 2
     to: to-00002@example.com
     cc: cc-00002@example.com
    bcc: NULL
subject: subject-00002
   body: {"sign": "sign-00002", "message": "message-00002"}
  addrs: to-00002@example.com,cc-00002@example.com
   sign: sign-00002
*************************** 3. row ***************************
     id: 3
     to: to-00003@example.com
     cc: NULL
    bcc: NULL
subject: subject-00003
   body: {"sign": "sign-00003", "message": "message-00003"}
  addrs: to-00003@example.com
   sign: sign-00003
*************************** 4. row ***************************
     id: 4
     to: to-00004@example.com
     cc: NULL
    bcc: NULL
subject: subject-00004
   body: {"message": "message-00004"}
  addrs: to-00004@example.com
   sign: NULL
4 rows in set (0.000 sec)

ちゃんとデータが入っていますので、
PERSISTENTタイプのaddrsへの全文検索と、
VIRTUALタイプのsignへの検索を行ってみます。

 

・PERSISTENTタイプのaddrsへの全文検索

MariaDB [test]> select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
     id: 2
     to: to-00002@example.com
     cc: cc-00002@example.com
    bcc: NULL
subject: subject-00002
   body: {"sign": "sign-00002", "message": "message-00002"}
  addrs: to-00002@example.com,cc-00002@example.com
   sign: NULL
1 row in set (0.001 sec)

MariaDB [test]> explain select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mail
         type: fulltext
possible_keys: mail_fidx01
          key: mail_fidx01
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.000 sec)

・VIRTUALタイプのsignへの検索

MariaDB [test]> select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
     id: 3
     to: to-00003@example.com
     cc: NULL
    bcc: NULL
subject: subject-00003
   body: {"sign": "sign-00003", "message": "message-00003"}
  addrs: to-00003@example.com
   sign: sign-00003
1 row in set (0.001 sec)

MariaDB [test]> explain select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mail
         type: ref
possible_keys: mail_idx01
          key: mail_idx01
      key_len: 1023
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.001 sec)

 

ちょっと件数少ないですが、両方共INDEXが効いていることがわかります。

 

生成カラムって実際どのくらい使われているのかわかりませんが、うまく使うことで、機能追加にも柔軟な対応ができそうな予感です。

生成カラムの進化はこれからも期待しています。

おっと、INSTANT ADDは、生成カラムには効かないので、ご注意ください。INSTANT対応も今後はあるのでは、と期待してます。

 

以上

投稿日時:2018年11月01日 16:38   カテゴリー:mariadb   [コメントがあればどうぞ]

pt-online-schema-changeをslaveに対して実行した場合に痛い目見たので、その備忘録。

 

そもそもの話、master側で不要なINDEXをslaveのみ張ってました。

OLTP側ではいらないINDEXだけど、集計とかであると嬉しいINDEXってやつですね。

そして、slaveに張っているINDEXをpt-oscでオンラインで張替えをしようとしたら、

リレーログからの取り込みが、旧テーブルに行ってしまい、

pt-oscで作成される新テーブルに入らなかったいうオチ。。。

その結果、レプリケーションの不整合が起きて、最初から構築するという目にあった。。

 

このような場合、素直にstop slaveを掛けてからDDLを実行すれば良かったです。。

とはいえ、リレーログからのデータ取り込みの際、pt-oscで実行しているとダメなことがわかったので、

もうちょっとこのあたりについて調べてみようかと思います。

 

あ、binlog_row_image=FULLだったし、MariaDB10.3だからflashback使えば、

もっと短時間で復旧できたかも、、って書いているときに思いました。。

 

以上

投稿日時:2018年10月22日 12:48   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]