★公開AMIのID
ami-9b81049a
★レギュレーションPDFのURL
https://www.dropbox.com/s/hpsk5gexe3lbb9m/tuningathon.pdf
★インデックス消してInnoDBのメモリ増やした段階
[ec2-user@ip-10-132-147-165 ~]$ python tgbench_mysql.py 127.0.0.1
0.000240087509155
0.931501150131
1.94021010399
3.10157513618
6.42820906639
10.2923099995
10.2923800945
★インデックスを張って主キーを変更した段階
[ec2-user@ip-10-132-147-165 ~]$ python tgbench_mysql.py 127.0.0.1
0.000233888626099
0.430819034576
0.922338008881
0.923327922821
3.28254389763
5.91230487823
5.91236400604
★pageのインデックス
mysql> show index from page;
+-------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| page | 0 | PRIMARY | 1 | page_id | A | 1664509 | NULL | NULL | | BTREE | | |
| page | 1 | red_name | 1 | page_is_redirect | A | 17 | NULL | NULL | | BTREE | | |
| page | 1 | red_name | 2 | page_namespace | A | 17 | NULL | NULL | | BTREE | | |
| page | 1 | touched | 1 | page_touched | A | 197 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
・メモ
create index touched on page(page_touched);
ALTER TABLE page DROP INDEX touched;
★revisionのインデックス
mysql> show index from revision;
+----------+------------+-----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| revision | 0 | PRIMARY | 1 | rev_page | A | 1725629 | NULL | NULL | | BTREE | | |
| revision | 1 | id | 1 | rev_id | A | 1725629 | NULL | NULL | | BTREE | | |
| revision | 1 | user_time | 1 | rev_user | A | 19609 | NULL | NULL | | BTREE | | |
| revision | 1 | user_time | 2 | rev_timestamp | A | 1725629 | NULL | NULL | | BTREE | | |
+----------+------------+-----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
★No.3のクエリ
EXPLAIN SELECT page_id FROM page JOIN revision ON page_id = rev_page WHERE page_is_redirect = 0 AND page_namespace = 0 AND rev_user = 0 ORDER BY page_touched DESC LIMIT 10;
+----+-------------+----------+--------+-------------------+---------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+-------------------+---------+---------+------------------------+------+-------------+
| 1 | SIMPLE | page | index | PRIMARY,red_name | touched | 14 | NULL | 20 | Using where |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,user_time | PRIMARY | 4 | wikipedia.page.page_id | 1 | Using where |
+----+-------------+----------+--------+-------------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)
★No.4のクエリ
EXPLAIN SELECT rev_user,count(*) AS c FROM page JOIN revision ON page_id = rev_page WHERE page_is_redirect = 0 AND page_namespace = 0 GROUP BY rev_user ORDER BY c DESC;
+----+-------------+----------+--------+------------------+----------+---------+------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+------------------+----------+---------+------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | page | ref | PRIMARY,red_name | red_name | 5 | const,const | 844751 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY | PRIMARY | 4 | wikipedia.page.page_id | 1 | |
+----+-------------+----------+--------+------------------+----------+---------+------------------------+--------+----------------------------------------------+
2 rows in set (0.00 sec)
★No.5クエリのEXPLAIN
explain SELECT SUBSTRING(rev_timestamp,1,6),count(*) AS c FROM page JOIN revision ON page_id = rev_page WHERE page_is_redirect = 0 AND page_namespace = 0 GROUP BY SUBSTRING(rev_timestamp,1,6) ORDER BY c DESC;
+----+-------------+----------+--------+------------------+----------+---------+------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+------------------+----------+---------+------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | page | ref | PRIMARY,red_name | red_name | 5 | const,const | 832879 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY | PRIMARY | 4 | wikipedia.page.page_id | 1 | |
+----+-------------+----------+--------+------------------+----------+---------+------------------------+--------+----------------------------------------------+
2 rows in set (0.00 sec)
