データロード中のパフォーマンス確認メモ
検証データ
データ量
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]>
データロード中
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]>