mysqldumpがどれくらい時間かかるのか試してみる
VirtualBoxにCentOSをとりあえずインストール
の続き
MacBookProのVirtualBoxの環境で、
1000万件のデータをINSERTした状態で、
mysqldumpがどれくらい時間かかるのか試してみる。
MySQLのインストール
とりあえずMySQLをインストールする。
$ sudo yum -y install mysql-server $ sudo service mysqld start $ sudo chkconfig mysqld on
現時点のibdata1のサイズは10MB。
$ sudo du -sh /var/lib/mysql/* 5.0M /var/lib/mysql/ib_logfile0 5.0M /var/lib/mysql/ib_logfile1 10M /var/lib/mysql/ibdata1 988K /var/lib/mysql/mysql 0 /var/lib/mysql/mysql.sock 4.0K /var/lib/mysql/test
1000万件のデータを追加
今回は、testデータベースを使う。
$ mysql -u root mysql> use test
大量データを入れるためのテーブルを用意する。
mysql> create table tmp (id int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; mysql> insert into tmp values (), (), (), (), (), (), (), (), (), ();
下記のテーブルに1000万件のデータを入れる。
mysql> create table sample ( id int NOT NULL AUTO_INCREMENT, c1 int NOT NULL, c2 int NOT NULL, c3 smallint NOT NULL, c4 smallint NOT NULL, c5 tinyint(1) NOT NULL, c6 tinyint(1) NOT NULL, c7 varchar(64) NOT NULL, c8 varchar(64) NOT NULL, c9 text NOT NULL, c10 text NOT NULL, c11 datetime NOT NULL, c12 datetime NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;
mysql> insert into sample (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12) select FLOOR(RAND()*10000 + 1), FLOOR(RAND()*10000 + 1), FLOOR(RAND()*100 + 1), FLOOR(RAND()*100 + 1), FLOOR(RAND()*2), FLOOR(RAND()*2), ELT(t1.id, 'sato', 'suzuki', 'takahashi', 'tanaka', 'ito', 'yamamoto', 'watanabe', 'nakamura', 'kobayashi', 'kato'), ELT(t1.id, 'sato', 'suzuki', 'takahashi', 'tanaka', 'ito', 'yamamoto', 'watanabe', 'nakamura', 'kobayashi', 'kato'), 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj', 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj', now(), now() from tmp t1, tmp t2, tmp t3, tmp t4, tmp t5, tmp t6, tmp t7;
約3分半かかった。
Query OK, 10000000 rows affected (3 min 34.86 sec)
下記のような感じのデータが入る。
mysql> select * from sample limit 10; +----+------+------+----+----+----+----+-----------+-----------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11 | c12 | +----+------+------+----+----+----+----+-----------+-----------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------------------+---------------------+ | 1 | 5419 | 7316 | 4 | 97 | 1 | 1 | sato | sato | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 2 | 6935 | 1265 | 56 | 39 | 0 | 0 | suzuki | suzuki | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 3 | 7251 | 3458 | 56 | 73 | 1 | 1 | takahashi | takahashi | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 4 | 8200 | 8266 | 68 | 89 | 0 | 0 | tanaka | tanaka | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 5 | 6765 | 2437 | 19 | 22 | 1 | 1 | ito | ito | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 6 | 8141 | 4804 | 96 | 36 | 1 | 0 | yamamoto | yamamoto | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 7 | 6342 | 7538 | 87 | 8 | 1 | 1 | watanabe | watanabe | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 8 | 6311 | 4094 | 16 | 55 | 0 | 1 | nakamura | nakamura | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 9 | 1914 | 2084 | 47 | 72 | 0 | 1 | kobayashi | kobayashi | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | | 10 | 9962 | 8793 | 41 | 41 | 1 | 1 | kato | kato | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj | 2014-06-26 01:02:56 | 2014-06-26 01:02:56 | +----+------+------+----+----+----+----+-----------+-----------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------------------+---------------------+
ibdata1のサイズは約3GBになった。
$ sudo du -sh /var/lib/mysql/* 5.0M /var/lib/mysql/ib_logfile0 5.0M /var/lib/mysql/ib_logfile1 3.0G /var/lib/mysql/ibdata1 988K /var/lib/mysql/mysql 0 /var/lib/mysql/mysql.sock 28K /var/lib/mysql/test
mysqldumpを試す。
ダンプを取得してみる。
$ mysqldump -u root --all-databases > dump.sql
約2分かかった。
ダンプのサイズは約2.8GB。1000万件なので、1件300Bくらい。
$ ls -lh -rw-rw-r-- 1 hoge hoge 2.8G 6月 26 01:11 2014 dump.sql
圧縮してみる。
$ tar czvf dump.tar.gz dump.sql
約1分かかった。
約140MBになった。
$ ls -lh -rw-rw-r-- 1 hoge hoge 138M 6月 26 01:14 2014 dump.tar.gz
