log

日記です

5.9きたメモ

★公開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)