sys.innodb_buffer_stats_by_tableの確認

データロード中のパフォーマンス確認メモ
検証データ

データ量

root@localhost [nyosm]>select
    ->  table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
    ->  floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
    ->  floor((data_length)/1024/1024) as 'DATA(MB)',
    ->  floor((index_length)/1024/1024) as 'INDEX(MB)'
    ->  from information_schema.tables
    ->  where table_schema=database()
    ->  order by (data_length+index_length) desc;
+-----------------+--------+----------+------+-----------+----------+-----------+
| table_name      | engine | tbl_rows | rlen | ToTal(MB) | DATA(MB) | INDEX(MB) |
+-----------------+--------+----------+------+-----------+----------+-----------+
| nodes           | InnoDB |  5145519 |  116 |       671 |      571 |       100 |
| waytags         | InnoDB |  4751170 |   57 |       630 |      262 |       367 |
| waynodes        | InnoDB |  6761106 |   49 |       454 |      319 |       134 |
| nodetags        | InnoDB |   651766 |   60 |        93 |       37 |        55 |
| ways            | InnoDB |   866342 |   89 |        91 |       73 |        17 |
| relationtags    | InnoDB |    28886 |   91 |         7 |        2 |         4 |
| relationmembers | InnoDB |    72882 |   64 |         6 |        4 |         1 |
| relations       | InnoDB |     4748 |   89 |         0 |        0 |         0 |
+-----------------+--------+----------+------+-----------+----------+-----------+
8 rows in set (0.19 sec)

root@localhost [nyosm]>

HTOPにてシステムリソース確認

データロード中

root@localhost [sys]>show full processlist;
+----+------+-----------+-------+---------+------+------------------+----------------------------------------------------------------+
| Id | User | Host      | db    | Command | Time | State            | Info                                                           |
+----+------+-----------+-------+---------+------+------------------+----------------------------------------------------------------+
|  7 | root | localhost | sys   | Query   |    0 | System lock      | show full processlist                                          |
|  9 | root | localhost | nyosm | Query   |   48 | Reading from net | LOAD DATA LOCAL INFILE '/tmp/waynodes.txt' INTO TABLE waynodes |
+----+------+-----------+-------+---------+------+------------------+----------------------------------------------------------------+
2 rows in set (0.13 sec)

root@localhost [sys]>select * from sys.innodb_buffer_stats_by_table order by data desc limit 0,10;
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name        | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| nyosm         | waynodes           | 91.73 MiB | 85.43 MiB |  5871 |         5871 |      5871 |     1382935 |
| mysql         | innodb_index_stats | 16.00 KiB | 11.64 KiB |     1 |            1 |         1 |         126 |
| mysql         | innodb_table_stats | 16.00 KiB | 1.95 KiB  |     1 |            1 |         1 |          32 |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
3 rows in set (0.90 sec)

root@localhost [sys]>

データLOAD終了
INFO: finished after 0 hours, 23 minutes and 2 seconds

root@localhost [sys]>show full processlist;
+----+------+-----------+------+---------+------+-------------+-----------------------+
| Id | User | Host      | db   | Command | Time | State       | Info                  |
+----+------+-----------+------+---------+------+-------------+-----------------------+
|  7 | root | localhost | sys  | Query   |    0 | System lock | show full processlist |
+----+------+-----------+------+---------+------+-------------+-----------------------+
1 row in set (0.03 sec)

root@localhost [sys]>select * from sys.innodb_buffer_stats_by_table order by data desc limit 0,10;
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | object_name        | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
| nyosm         | waynodes           | 79.41 MiB  | 73.97 MiB  |  5082 |         5082 |      5082 |     1204526 |
| InnoDB System | SYS_INDEXES        | 16.00 KiB  | 7.85 KiB   |     1 |            1 |         1 |         113 |
| InnoDB System | SYS_TABLES         | 32.00 KiB  | 7.81 KiB   |     2 |            2 |         2 |          66 |
| InnoDB System | SYS_FIELDS         | 16.00 KiB  | 5.59 KiB   |     1 |            1 |         1 |         131 |
| nyosm         | relationmembers    | 5.69 MiB   | 5.24 MiB   |   364 |          364 |       364 |       73607 |
| nyosm         | relations          | 544.00 KiB | 449.98 KiB |    34 |           34 |        34 |        4770 |
| nyosm         | relationtags       | 4.08 MiB   | 3.11 MiB   |   261 |          261 |       261 |       28638 |
| InnoDB System | SYS_DATAFILES      | 16.00 KiB  | 3.01 KiB   |     1 |            1 |         1 |          57 |
| InnoDB System | SYS_TABLESPACES    | 16.00 KiB  | 2.96 KiB   |     1 |            1 |         1 |          57 |
| mysql         | innodb_table_stats | 16.00 KiB  | 2.14 KiB   |     1 |            1 |         1 |          35 |
+---------------+--------------------+------------+------------+-------+--------------+-----------+-------------+
10 rows in set (0.09 sec)

root@localhost [sys]>