Spiderで書き込みをスケールさせてみる

メモリに載らなくなった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	１回目	２回目
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	１回目	２回目
update	372.093	396.04

4分割してスループットが２倍。。。納得いかん。
理由を考える。

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	１回目	２回目
update	388.35	390.244
mixed	402.01	400

むむむむ…。

そういえば、AUTO_INCREMENTの扱いが謎。フロントのSpiderテーブルにAUTO_INCREMENTを定義すれば良きに計らってくれるのか、バックエンド側で制御してくれるのか、あるいはTriggerを使うのか。フロントで制御できるといいなー。auto_increment_incrementとauto_increment_offsetという変数があるのでバックエンドでも制御できなくはないけど、あんまやりたくない、Reshardの時に死にそうだ。