メモリに載らなくなったMyISAMをSpiderを使ってスケールさせてみる。
まず、c1.xlargeのMySQLに2000万件ほどデータをつっこむ。
CREATE TABLE `t1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `intcol3` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL, `charcol4` varchar(128) DEFAULT NULL, `charcol5` varchar(128) DEFAULT NULL, `charcol6` varchar(128) DEFAULT NULL, `charcol7` varchar(128) DEFAULT NULL, `charcol8` varchar(128) DEFAULT NULL, `xxx` varchar(255) DEFAULT NULL, `yyy` varchar(255) DEFAULT NULL, `zzz` varchar(255) DEFAULT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=20002817 DEFAULT CHARSET=utf8;
mysql> select count(1) from t1; +----------+ | count(1) | +----------+ | 20000000 | +----------+ 1 row in set (0.00 sec)
ファイルサイズは20GBぐらい。当然、メモリには載らない。
[root@ip-XXX-XXX-XXX-XXX mysqlslap]# ll t1.* -rw-rw---- 1 mysql mysql 20960000000 Jul 25 11:27 t1.MYD -rw-rw---- 1 mysql mysql 336250880 Jul 25 11:24 t1.MYI -rw-rw---- 1 mysql mysql 13148 Jul 25 06:14 t1.frm
別途、用意したc1.xlargeのクライアントからmysqlslapを叩く。
そのままだとテスト前にDBを消してしまうので、ちょっと修正(ついでにmixedの動作をSELECT+UPDATEに修正)
--- mysqlslap.c.orig 2010-06-04 00:50:21.000000000 +0900 +++ mysqlslap.c 2010-07-25 10:56:20.000000000 +0900 @@ -795,7 +795,7 @@ init_dynamic_string(&table_string, "", 1024, 1024); - dynstr_append(&table_string, "CREATE TABLE `t1` ("); + dynstr_append(&table_string, "CREATE TABLE `t0` ("); if (auto_generate_sql_autoincrement) { @@ -1323,7 +1323,7 @@ { int coin= 0; - query_statements= build_insert_string(); + query_statements= build_update_string(); /* This logic should be extended to do a more mixed load, at the moment it results in "every other". @@ -1334,7 +1334,7 @@ { if (coin) { - ptr_statement->next= build_insert_string(); + ptr_statement->next= build_update_string(); coin= 0; } else @@ -1574,7 +1574,7 @@ ulonglong count; DBUG_ENTER("create_schema"); - len= snprintf(query, HUGE_STRING_LENGTH, "CREATE SCHEMA `%s`", db); + len= snprintf(query, HUGE_STRING_LENGTH, "CREATE SCHEMA `%s_`", db); if (verbose >= 2) printf("Loading Pre-data\n"); @@ -1664,7 +1664,7 @@ char query[HUGE_STRING_LENGTH]; int len; DBUG_ENTER("drop_schema"); - len= snprintf(query, HUGE_STRING_LENGTH, "DROP SCHEMA IF EXISTS `%s`", db); + len= snprintf(query, HUGE_STRING_LENGTH, "DROP SCHEMA IF EXISTS `%s_`", db); if (run_query(mysql, query, len)) {
こんな感じのテストスクリプトを実行。
#!/bin/sh mysql -h ip-XXX-XXX-XXX-XXX -u scott -ptiger mysqlslap -e "drop table if exists t0" ./mysqlslap \ --auto-generate-sql \ --auto-generate-sql-add-autoincrement \ --engine=MyISAM \ --number-int-cols=3 \ --number-char-cols=8 \ --concurrency=8 \ --auto-generate-sql-execute-number=10000 \ --auto-generate-sql-load-type=update \ -h ip-XXX-XXX-XXX-XXX \ -u scott \ -ptiger \ --auto-generate-sql-write-number=1 | awk ' /Average number of seconds/{t=substr($0,47,5)} /Number of clients/{c=substr($0,37)} /Average number of queries per client/{n=substr($0,40)} END{print(n * c / t)}'
末尾のawkスクリプトはqps算出用。
結果。
load-type | 1回目 | 2回目 |
---|---|---|
update | 152.091 | 160.321 |
mixed | 175.439 | 175.439 |
予想はしてたけど、これはひどい。
で、SpiderでShardingしてみる。
まず、バックエンドにc1.xlargeを4つほど用意。7GB×4でメモリに収まるはず。
バックエンドにテーブルを用意。
CREATE TABLE `t1` ( `id` bigint(20), `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `intcol3` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL, `charcol4` varchar(128) DEFAULT NULL, `charcol5` varchar(128) DEFAULT NULL, `charcol6` varchar(128) DEFAULT NULL, `charcol7` varchar(128) DEFAULT NULL, `charcol8` varchar(128) DEFAULT NULL, `xxx` varchar(255) DEFAULT NULL, `yyy` varchar(255) DEFAULT NULL, `zzz` varchar(255) DEFAULT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=20002817 DEFAULT CHARSET=utf8;
フロント側にSpiderのテーブルを作成。
CREATE SERVER n1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'scott', PASSWORD 'tiger', HOST 'ip-A...', PORT 3306); CREATE SERVER n2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'scott', PASSWORD 'tiger', HOST 'ip-B...', PORT 3306); CREATE SERVER n3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'scott', PASSWORD 'tiger', HOST 'ip-C...', PORT 3306); CREATE SERVER n4 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'scott', PASSWORD 'tiger', HOST 'ip-D...', PORT 3306); CREATE TABLE `t1` ( `id` bigint(20) unsigned NOT NULL, `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `intcol3` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL, `charcol4` varchar(128) DEFAULT NULL, `charcol5` varchar(128) DEFAULT NULL, `charcol6` varchar(128) DEFAULT NULL, `charcol7` varchar(128) DEFAULT NULL, `charcol8` varchar(128) DEFAULT NULL, `xxx` varchar(255) DEFAULT NULL, `yyy` varchar(255) DEFAULT NULL, `zzz` varchar(255) DEFAULT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=SPIDER DEFAULT CHARSET=utf8 PARTITION BY HASH(id) ( PARTITION p1 COMMENT 'server "n1", table "t1"', PARTITION p2 COMMENT 'server "n2", table "t1"', PARTITION p3 COMMENT 'server "n3", table "t1"', PARTITION p4 COMMENT 'server "n4", table "t1"' );
2000万件データを投入。インサートに時間がかかるなぁ。。。MyISAMでも2〜3時間かかったけど。
mysql> select count(1) from t1; +----------+ | count(1) | +----------+ | 20000000 | +----------+ 1 row in set (40.25 sec)
全件舐めるのに40sかー。
mysqlslapのオプションに「--create-schema=mysqlslap_ndb」を追加して実行。
結果。
load-type | 1回目 | 2回目 |
---|---|---|
update | 372.093 | 396.04 |
4分割してスループットが2倍。。。納得いかん。
理由を考える。
- 4分割してもメモリに載るサイズじゃなかった
- いやいやいや
- 8分割ぐらいしないとダメとか
- 明示的にメモリに載せないとダメ
- ネットワークがボトルネック
- PKでSELECT/UPDATEだけど…
- フロントも複数立てないとダメ
- InnoDBじゃないとスケールしない
- クライアントの力不足
- concurrency=8で1000qps以下ってあるのかなー
とりあえず、バックエンド側でファイルをメモリに載せてみる
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# vmstat -a 3
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free inact active si so bi bo in cs us sy id wa st
2 0 0 1564256 3746104 1701872 0 0 1 182 87 46 0 0 98 2 0
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# cat t1.* > /dev/null
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# cat t1.* > /dev/null
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# cat t1.* > /dev/null
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# cat t1.* > /dev/null
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# cat t1.* > /dev/null
[root@ip-XXX-XXX-XXX-XXX9 mysqlslap_ndb]# vmstat -a 3
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free inact active si so bi bo in cs us sy id wa st
0 0 0 1563228 145660 5302576 0 0 1 178 90 50 0 0 98 2 0
activeが増えてるのでメモリには載ったはず。これを各バックエンドで繰り返す。
で、結果。
load-type | 1回目 | 2回目 |
---|---|---|
update | 388.35 | 390.244 |
mixed | 402.01 | 400 |
むむむむ…。
そういえば、AUTO_INCREMENTの扱いが謎。フロントのSpiderテーブルにAUTO_INCREMENTを定義すれば良きに計らってくれるのか、バックエンド側で制御してくれるのか、あるいはTriggerを使うのか。フロントで制御できるといいなー。auto_increment_incrementとauto_increment_offsetという変数があるのでバックエンドでも制御できなくはないけど、あんまやりたくない、Reshardの時に死にそうだ。