cpimportを使ってInfiniDBにデータをロードするシンタックスだけメモ

速度比較とかしてないし、明らかにこっちの方が速かったのでたぶん調べない。シンタックスだけメモ。テーブルは予めmysqlコマンドラインクライアントから作っておく。

# /usr/local/Calpont/bin/cpimport d1 t1 -s "\t" -e 1000000000 < ./fifo
Locale is : C
Column delimiter : \t

Using table OID 3013 as the default JOB ID
Input file(s) will be read from : STDIN
Job description file : /usr/local/Calpont/data/bulk/tmpjob/3013_D20140917_T173443_Job_3013.xml
Log file for this job: /usr/local/Calpont/data/bulk/log/Job_3013.log
2014-09-17 17:34:43 (24078) INFO : successfully loaded job file /usr/local/Calpont/data/bulk/tmpjob/3013_D20140917_T173443_Job_3013.xml
2014-09-17 17:34:43 (24078) INFO : Job file loaded, run time for this step : 1.10977 seconds
2014-09-17 17:34:43 (24078) INFO : PreProcessing check starts
2014-09-17 17:34:47 (24078) INFO : PreProcessing check completed
2014-09-17 17:34:47 (24078) INFO : preProcess completed, run time for this step : 3.65073 seconds
2014-09-17 17:34:47 (24078) INFO : No of Read Threads Spawned = 1
2014-09-17 17:34:47 (24078) INFO : No of Parse Threads Spawned = 3
2014-09-17 17:34:47 (24078) INFO : Reading input from STDIN to import into table d1.t1...
2014-09-17 17:36:41 (24078) INFO : Number of rows with errors = 18.  Row numbers with error reasons are listed in file /tmp/hoge/data/d1.tbl.Job_3013_24078.err
2014-09-17 17:36:41 (24078) INFO : Number of rows with errors = 18.  Exact error rows are listed in file /tmp/hoge/data/d1.tbl.Job_3013_24078.bad
2014-09-17 17:36:42 (24078) INFO : For table d1.t1: 19680191 rows processed and 19680173 rows inserted.
2014-09-17 17:36:42 (24078) INFO : Bulk load completed, total run time : 120.018 seconds

-sでフィールドセパレーターを指定。省略時は"|"らしい。
-eで無視するパースエラーの数を指定する。暗黙のデフォルトは10。この状態だと11回目のパースエラーが発生した状態でcpimportがまるっとアボートする。
-rで読み込みスレッド, -wでパーサースレッドの数を指定。12コアのマシン(HTで24スレッド)で8, 8くらいが良さそうだった。200MB/sくらい出てたからとりあえず十分。

MySQL 5.7で追加されたALTER TABLE .. RENAME INDEX

5.7.5が来る前に、5.7.4までのおさらい。

mysql57> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `org_index` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql57> ALTER TABLE t1 RENAME INDEX org_index TO renamed_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `renamed_index` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

これで、index_hogehoge_01なんて情報量ゼロのインデックスを見つけてもすぐに直せますね！

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

LOAD DATA INFILEで複数ファイルを食わせるのにちょっとだけ有効なTIPS

LOAD DATA INFILEで複数のファイルを順番(じゃなくてもいいけど)食わせる場合を考える。

↓こんなかんじ

$ ll *.tsv
-rw-rw-r-- 1 mysql mysql 541810051 Sep 16 10:51 20140912.tsv
-rw-rw-r-- 1 mysql mysql 523910384 Sep 16 10:52 20140913.tsv
-rw-rw-r-- 1 mysql mysql 513351897 Sep 16 10:53 20140914.tsv
-rw-rw-r-- 1 mysql mysql 513221906 Sep 16 10:54 20140915.tsv

無圧縮なファイルなら、シェルでぐるんぐるん回すのが便利。

$ for f in *.tsv ; do
> mysql -sse "LOAD DATA INFILE $f INTO TABLE .." && gzip $f
> done

成功したらgzip圧縮とかそのまんま書けるのでラク。
ただし、圧縮ファイルだと"展開" => "LOAD DATA INFILE" => "再圧縮"とか書くのはちょっと面倒だしなんかイヤだ。
あと、InfiniDBはある程度(100万行くらいだっけ？)まとめてLOAD DATA INFILEに食わせた方がデータの構築とかいっぺんに出来て速いとかそんな感じだった気がする(infinidb_use_import_for_batchinsert= 1の場合。0(MySQLのLOAD DATA INFILEをそのまま使う)場合はよく知らない）

という訳で、mkfifoを使ってます。

$ ll *.tsv.gz
-rw-rw-r-- 1 mysql mysql  2778925 Sep 12 18:04 20140722.tsv.gz
-rw-rw-r-- 1 mysql mysql  6775101 Sep 12 18:04 20140723.tsv.gz
-rw-rw-r-- 1 mysql mysql  7715720 Sep 12 18:04 20140724.tsv.gz
-rw-rw-r-- 1 mysql mysql  7811469 Sep 12 18:04 20140725.tsv.gz
-rw-rw-r-- 1 mysql mysql  8329598 Sep 12 18:05 20140726.tsv.gz
-rw-rw-r-- 1 mysql mysql  8256156 Sep 12 18:05 20140727.tsv.gz
-rw-rw-r-- 1 mysql mysql  8081465 Sep 12 18:05 20140728.tsv.gz
-rw-rw-r-- 1 mysql mysql  7881025 Sep 12 18:05 20140729.tsv.gz
-rw-rw-r-- 1 mysql mysql  7496167 Sep 12 18:06 20140730.tsv.gz
-rw-rw-r-- 1 mysql mysql  7934078 Sep 12 18:06 20140731.tsv.gz

$ mkfifo /tmp/fifo

$ zcat *.tsv.gz > /tmp/fifo

別のターミナルから

$ mysql -sse "LOAD DATA INFILE /tmp/fifo INTO TABLE .."

どうせLOAD DATA INFILEに失敗したらTRUNCATEして入れなおすからこれで十分。

InfiniDBのERROR 122 Bulkload Read Failed.

気持ちよくLOAD DATA INFILEしてたら急に現れたこんなやつ。

mysql> LOAD DATA INFILE '/data/tmp/fifo' INTO TABLE vegelog;
ERROR 122 (HY000): PM1 : Bulkload Read (thread 0) Failed for Table d1.t1.  Terminating this job.

ちなみにError 122はHandler層でのエラーらしい。

$ /usr/local/Calpont/mysql/bin/perror 122
OS error code 122:  Disk quota exceeded
MySQL error code 122: Internal (unspecified) error in handler

結論だけ書くと、テーブルのスキーマと食わせてたtsvファイルが完全に一致していなかった。
MySQLのLOAD DATA INFILEを使ってやると勝手にtruncationされて入る…んだけど、スキーマちゃんと調整しました。

mysql> SELECT @@infinidb_use_import_for_batchinsert; -- これがONだとLOAD DATA INFILEを/usr/local/Calpont/bin/cpimportにマップしてくれるので無効にする。
+---------------------------------------+
| @@infinidb_use_import_for_batchinsert |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION infinidb_use_import_for_batchinsert= 0; 
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/data/tmp/fifo' INTO TABLE vegelog;
Query OK, 17208325 rows affected, 2076 warnings (5 min 35.09 sec)
Records: 17208325  Deleted: 0  Skipped: 0  Warnings: 2050

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1262 | Row 5397 was truncated; it contained more data than there were input columns   |
| Warning | 1262 | Row 5406 was truncated; it contained more data than there were input columns   |
| Warning | 1262 | Row 59575 was truncated; it contained more data than there were input columns  |
..
| Warning | 1261 | Row 329176 doesn't contain data for all columns                                |
| Warning | 1261 | Row 329176 doesn't contain data for all columns                                |
| Warning | 1261 | Row 329176 doesn't contain data for all columns                                |
+---------+------+--------------------------------------------------------------------------------+
64 rows in set (0.00 sec)

はふん。

InfiniDBにロードしている時に ERROR 122 (HY000): CAL0006: IDB-2008: The version buffer overflowed. と言われた

fifoファイル使ってごりごり流してたら怒られた。

mysql> LOAD DATA INFILE '/data/tmp/fifo' INTO TABLE infinidb_table
ERROR 122 (HY000): CAL0006: IDB-2008: The version buffer overflowed. Increase VersionBufferFileSize or limit the rows to be processed.

VersionBufferFileSizeを上げるのはいいんだけど、どれくらいが適正なのか、何をするパラメーターなのか、そもそもどこで設定するのかとかいろんな情報が出てこなくて困った。

とりあえず上げ方。

$ /usr/local/Calpont/bin/calpontConsole

InfiniDB> getSystemConfig
getsystemconfig   Tue Sep 16 11:35:41 2014

System Configuration

SystemName = calpont-1
SoftwareVersion = 4.6.0
SoftwareRelease = 1
ParentOAMModuleName = pm1
StandbyOAMModuleName = unassigned
NMSIPAddr = 0.0.0.0
ModuleHeartbeatPeriod = 10
ModuleHeartbeatCount = 3
DBRootCount = 1
DBRoot1 = /usr/local/Calpont/data1
DBRMRoot = /usr/local/Calpont/data1/systemFiles/dbrm/BRM_saves
ExternalCriticalThreshold = 90
ExternalMajorThreshold = 80
ExternalMinorThreshold = 70
MaxConcurrentTransactions = 1000
SharedMemoryTmpFile = /usr/local/Calpont/data1/systemFiles/dbrm/CalpontShm
NumVersionBufferFiles = 0
VersionBufferFileSize = 2
OIDBitmapFile = /usr/local/Calpont/data1/systemFiles/dbrm/oidbitmap
FirstOID = 3000
TransactionArchivePeriod = 10

InfiniDB> setSystemConfig VersionBufferFileSize 4
setsystemconfig   Tue Sep 16 11:36:10 2014

   Successfully set VersionBufferFileSize = 4

InfiniDB> exit   Tue Sep 16 11:36:11 2014
Exiting the Calpont Command Console

ところでこれ、再設定したあと再起動とか要るのか要らないのかもよくわからん。

ちなみに、1でダメで2に変えてもまだダメだったので4に変えたところ。



【2014/09/16 12:34】
512まで上げても改善の兆しが見られなかったので/etc/init.d/infinidb restartで再起動したら上手くいった。

# /etc/init.d/infinidb restart
Shutting down InfiniDB Database Platform
Starting InfiniDB Database Platform

# cc

Calpont InfiniDB Command Console
   enter 'help' for list of commands
   enter 'exit' to exit the Calpont InfiniDB Command Console
   use up/down arrows to recall commands


Active Alarm Counts: Critical = 0, Major = 0, Minor = 0, Warning = 0, Info = 0

Critical Active Alarms:

InfiniDB>
InfiniDB> getSystemConfig VersionBufferFileSize
getsystemconfig   Tue Sep 16 12:27:45 2014

   VersionBufferFileSize = 512

再起動しても揮発せずに残ってるのね。ってかmysqld(だけでいいのかわからんけど)の再起動が必要って言ってよ。。


【2014/09/16 19:25】
ざっと見、InnoDBでいうところのInnoDB Buffer Pool Sizeに近いんだかどうなんだかよく判らないような感じで、1～4Gくらいまで上げても良さそうなこと書いてあるけど、だったら初期値の1は何なんだ。。

http://infinidb.co/community/how-to-change-location-of-temp-directory


【2014/09/17 16:11】

ファイル見つけた。

# pwd
/usr/local/Calpont/data1

# ll
total 4198416
drwxr-xr-x 3 root root       4096 Sep 12 14:36 000.dir
-rw-r--r-- 1 root root          0 Sep 16 18:07 OAMdbrootCheck
drwxr-xr-x 2 root root       4096 Sep 17 16:02 bulkRollback
drwxrwxrwt 4 root root       4096 Sep 12 14:32 systemFiles
-rw-r--r-- 1 root root 4294967296 Sep 17 16:10 versionbuffer.cdf

コンソールからMySQLでCOUNT .. GROUP BYするついでに視覚化してみる

想定しているのはこんなクエリー。

mysql56> SELECT DATE_FORMAT(timestamp, '%Y-%m') AS month, COUNT(*) AS count FROM tweets GROUP BY 1;
+---------+-------+
| month   | count |
+---------+-------+
| 2010-08 |     3 |
| 2010-09 |    90 |
| 2010-10 |    21 |
..
| 2014-07 |   608 |
| 2014-08 |   575 |
| 2014-09 |   276 |
+---------+-------+
50 rows in set (0.09 sec)

全文検索のテスト用にtweets.csvを食わせるSQL のエントリーで書いたそのまんまのDDLでtweets.csvを食わせたテーブル。
コンソールから作業していてこんなクエリーを叩いてもちょっと味気がなかったので、ひねってみた。

mysql56> SELECT DATE_FORMAT(timestamp, '%Y-%m') AS month, COUNT(*) AS count, CONCAT(REPEAT('+', COUNT(*) / 100), REPEAT('=', (COUNT(*) % 100) / 10), REPEAT('-', COUNT(*) % 10)) AS bar FROM tweets GROUP BY 1;
+---------+-------+----------------------------+
| month   | count | bar                        |
+---------+-------+----------------------------+
| 2010-08 |     3 | ---                        |
| 2010-09 |    90 | +=========                 |
| 2010-10 |    21 | ==-                        |
..
| 2014-07 |   608 | ++++++=--------            |
| 2014-08 |   575 | ++++++========-----        |
| 2014-09 |   276 | +++========------          |
+---------+-------+----------------------------+
50 rows in set (0.09 sec)

別に役に立ちはしないんだけど、人に説明するときにやって見せるとちょっと「おおお」と言われることが多い。

MyISAMなテーブルに一括でmyisamchkをかける方法

2014年も半分以上過ぎたというのにMyISAMとか言わない。

myisamchkはテーブル名または.MYIファイル名を引数に取れるので、

$ find ./ -name "*.MYI" -exec myisamchk --silent -rq {} \;

datadirにcdしてこれだけでMyISAMなテーブルを順番にmyisamchkできる。


あー、InnoDBつかいたーい。