euglena1215/isucon11-qualify-devil

isucon11-qualify-devil's Issues

コードリーディング

気になったことをかいていく

ログ

ミドルウェア

ミドルウェア

MariaDBとNginx

DB

MariaDBが動いている(version 15.1)

$ mysql --version
mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

$ lsof -i:3306
COMMAND   PID   USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
isucondit 771 isucon   13u  IPv4 1229793      0t0  TCP localhost:39556->localhost:mysql (ESTABLISHED)
isucondit 771 isucon   31u  IPv4 1029093      0t0  TCP localhost:39522->localhost:mysql (ESTABLISHED)

web server

443でnginxが立っている

isucon@isucon11-q-albino-devi-1:~$ sudo lsof -i:443
COMMAND  PID     USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
nginx    518     root    6u  IPv4   22183      0t0  TCP *:https (LISTEN)
nginx    519 www-data    6u  IPv4   22183      0t0  TCP *:https (LISTEN)
nginx    520 www-data    6u  IPv4   22183      0t0  TCP *:https (LISTEN)
agent   4879 dd-agent   28u  IPv4 1242189      0t0  TCP ip-172-31-41-180.ap-northeast-1.compute.internal:41146->ec2-3-233-148-72.compute-1.amazonaws.com:https (ESTABLISHED)

DBまとめ

mysqlへのログイン

mysql -u isucon -pisucon isuconditionで出来る

MYSQL_PORT=3306
MYSQL_USER=isucon
MYSQL_DBNAME=isucondition
MYSQL_PASS=isucon

DBスキーマ

テーブルは次の4つ

• isu
• isu_association_config
• isu_condition
• user

MariaDB [isucondition]> show tables;
+------------------------+
| Tables_in_isucondition |
+------------------------+
| isu                    |
| isu_association_config |
| isu_condition          |
| user                   |
+------------------------+
4 rows in set (0.000 sec)

負荷走行前のデータ量

MariaDB [isucondition]> SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+------------------------+--------+------------+----------------+-------+------+------+
| table_name             | engine | table_rows | avg_row_length | allMB | dMB  | iMB  |
+------------------------+--------+------------+----------------+-------+------+------+
| isu_condition          | InnoDB |      67397 |            210 |    13 |   13 |    0 |
| isu                    | InnoDB |         70 |          52662 |     3 |    3 |    0 |
| isu_association_config | InnoDB |          1 |          16384 |     0 |    0 |    0 |
| user                   | InnoDB |         13 |           1260 |     0 |    0 |    0 |
+------------------------+--------+------------+----------------+-------+------+------+

負荷走行後のデータ量

MariaDB [isucondition]> SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+------------------------+--------+------------+----------------+-------+------+------+
| table_name             | engine | table_rows | avg_row_length | allMB | dMB  | iMB  |
+------------------------+--------+------------+----------------+-------+------+------+
| isu_condition          | InnoDB |      73897 |            191 |    13 |   13 |    0 |
| isu                    | InnoDB |         71 |          51921 |     3 |    3 |    0 |
| isu_association_config | InnoDB |          1 |          16384 |     0 |    0 |    0 |
| user                   | InnoDB |         13 |           1260 |     0 |    0 |    0 |
+------------------------+--------+------------+----------------+-------+------+------+

isuテーブル

imageがそのままDBに入っている用に見える

MariaDB [isucondition]> desc isu;
+--------------+--------------+------+-----+----------------------+--------------------------------+
| Field        | Type         | Null | Key | Default              | Extra                          |
+--------------+--------------+------+-----+----------------------+--------------------------------+
| id           | bigint(20)   | NO   | PRI | NULL                 | auto_increment                 |
| jia_isu_uuid | char(36)     | NO   | UNI | NULL                 |                                |
| name         | varchar(255) | NO   |     | NULL                 |                                |
| image        | longblob     | YES  |     | NULL                 |                                |
| character    | varchar(255) | YES  |     | NULL                 |                                |
| jia_user_id  | varchar(255) | NO   |     | NULL                 |                                |
| created_at   | datetime(6)  | YES  |     | current_timestamp(6) |                                |
| updated_at   | datetime(6)  | YES  |     | current_timestamp(6) | on update current_timestamp(6) |
+--------------+--------------+------+-----+----------------------+--------------------------------+
8 rows in set (0.000 sec)

MariaDB [isucondition]> show index from isu;
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| isu   |          0 | PRIMARY      |            1 | id           | A         |          35 |     NULL | NULL   |      | BTREE      |         |               |
| isu   |          0 | jia_isu_uuid |            1 | jia_isu_uuid | A         |          35 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

isu_association_config

MariaDB [isucondition]> desc isu_association_config;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   | PRI | NULL    |       |
| url   | varchar(255) | NO   | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

isu_condition

MariaDB [isucondition]> desc isu_condition;
+--------------+--------------+------+-----+----------------------+----------------+
| Field        | Type         | Null | Key | Default              | Extra          |
+--------------+--------------+------+-----+----------------------+----------------+
| id           | bigint(20)   | NO   | PRI | NULL                 | auto_increment |
| jia_isu_uuid | char(36)     | NO   |     | NULL                 |                |
| timestamp    | datetime     | NO   |     | NULL                 |                |
| is_sitting   | tinyint(1)   | NO   |     | NULL                 |                |
| condition    | varchar(255) | NO   |     | NULL                 |                |
| message      | varchar(255) | NO   |     | NULL                 |                |
| created_at   | datetime(6)  | YES  |     | current_timestamp(6) |                |
+--------------+--------------+------+-----+----------------------+----------------+
7 rows in set (0.001 sec)

MariaDB [isucondition]> show index from isu_condition;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| isu_condition |          0 | PRIMARY  |            1 | id          | A         |       67397 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

user

MariaDB [isucondition]> desc user;
+-------------+--------------+------+-----+----------------------+-------+
| Field       | Type         | Null | Key | Default              | Extra |
+-------------+--------------+------+-----+----------------------+-------+
| jia_user_id | varchar(255) | NO   | PRI | NULL                 |       |
| created_at  | datetime(6)  | YES  |     | current_timestamp(6) |       |
+-------------+--------------+------+-----+----------------------+-------+

サーバースペック

サーバースペック

1: 2CPU, 4GB
2: 2CPU, 4GB
3: 2CPU, 4GB

全部一緒みたい