Help us understand the problem. What is going on with this article?

MySQL5.6で1億レコードのテーブルを軽くベンチマーク

More than 3 years have passed since last update.

なんとなく思い立って1億レコードのテーブルってどれくらい重いのか調べてみました。

環境

マシン: AWS EC2 c4.2xlarge(8vCPU, 32GBメモリ)
DB: MySQL5.6
ディスク: EBS 1000GB SSD 3000IOPS

DBは一切設定の変更なし。

テーブル(ログデータ的なものをイメージ)

 CREATE TABLE `logs` (
  `time` int(11) NOT NULL,
  `arg1` int(11) NOT NULL,
  `arg2` int(11) NOT NULL,
  `arg3` varchar(255) NOT NULL,
  `arg4` varchar(255) NOT NULL,
  `arg5` varchar(255) NOT NULL,
  KEY `idx_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

実行結果

COUNT(*)

SQL

mysql> select count(*) from logs;
+-----------+
| count(*)  |
+-----------+
| 100000000 |
+-----------+
1 row in set (32.96 sec)

実行中のsar(一部)

02:59:XX PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
02:59:08 PM     all     10.26      0.00      3.25      0.00      0.00     86.48
02:59:09 PM     all     10.89      0.00      2.50      0.00      0.00     86.61
02:59:10 PM     all     10.50      0.00      3.00      0.00      0.00     86.50
02:59:11 PM     all     10.39      0.00      3.00      0.00      0.00     86.61

COUNT(DISTINCT)

SQL

mysql> select count(distinct arg1) from logs;
+----------------------+
| count(distinct arg1) |
+----------------------+
|                  200 |
+----------------------+
1 row in set (1 min 21.48 sec)

実行中のsar(一部)

03:03:XX PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
03:03:05 PM     all      6.80      0.00      1.39      6.05      0.13     85.64
03:03:06 PM     all      6.57      0.00      1.39      5.94      0.00     86.09
03:03:07 PM     all      7.18      0.00      1.13      6.05      0.00     85.64
03:03:08 PM     all      7.59      0.00      0.76      5.94      0.00     85.71
03:03:09 PM     all      6.49      0.00      0.89      6.11      0.00     86.51
~ (途中からiowaitが落ち着いてCPU使用率が挙がった)
03:04:03 PM     all     14.77      0.00      2.25      0.00      0.00     82.98
03:04:04 PM     all     14.39      0.00      2.75      0.00      0.00     82.85
03:04:05 PM     all     14.79      0.00      2.38      0.00      0.00     82.83
03:04:06 PM     all     14.12      0.00      3.00      0.00      0.00     82.88
03:04:07 PM     all     14.39      0.00      2.75      0.00      0.00     82.85
03:04:08 PM     all     14.30      0.00      2.63      0.00      0.00     83.06

GROUP BY

SQL

mysql> select arg1, count(*) from logs group by arg1;
(結果略)
200 rows in set (54.71 sec)

実行中のsar(一部)

03:07:43 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
03:07:44 PM     all     14.91      0.00      1.63      0.00      0.00     83.46
03:07:45 PM     all     14.62      0.00      2.00      0.00      0.00     83.38
03:07:46 PM     all     14.02      0.00      2.50      0.00      0.00     83.48
03:07:47 PM     all     14.27      0.00      2.25      0.00      0.00     83.48

インデックス

SQL

select COUNT(*) from logs where time between 1443622684 and 1443623684;
+----------+
| COUNT(*) |
+----------+
|   100100 |
+----------+
1 row in set (0.03 sec)

さすがに1億レコードでもインデックス貼ってると早いですね。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした