### Environment details # System Summary Report ###################### Date | 2018-01-04 04:13:42 UTC (local TZ: CET +0100) Hostname | hod03 Uptime | 66 days, 13:57, 3 users, load average: 0.03, 0.06, 0.05 Platform | Linux Release | Red Hat Enterprise Linux Server release 7.1 (Maipo) Kernel | 3.8.13-98.2.2.el7uek.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.17 Compiler | GNU CC version 4.8.3 20140911 (Red Hat 4.8.3-9). SELinux | Disabled Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 2, cores = 16, virtual = 32, hyperthreading = yes Speeds | 32x2600.000 Models | 32xIntel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz Caches | 32x20480 KB # Memory ##################################################### Total | 251.9G Free | 649.8M Used | physical = 6.5G, swap allocated = 7.8G, swap used = 0.0, virtual = 6.5G Buffers | 244.7G Caches | 228.3G Dirty | 12 kB UsedRSS | 4.1G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 ## Followed steps [28 Dec 2017 22:37] Mark Callaghan ## Mark Callaghan - Just a reminder that reproduction requires database >> buffer pool. In my test I used 1gb buffer pool with 80M rows and database ended up >> 10gb ## The example used 80M rows total. With 1 table there is 80M rows in 1 table. With 16 tables there are 5M rows/table. In both cases there were 16 concurrent connections doing the insert. MySQL-python (lab team installed on required boxes) - MySQL-python-1.2.5-1.el7.x86_64 https://github.com/mdcallag/mytools/blob/master/bench/ibench/iibench.py https://gist.github.com/mdcallag/0843d07f83938108e180f0f0bcbda1fe https://github.com/mdcallag/mytools/blob/master/bench/conf/wrk/orig5635/etc/my.cnf - Conf/related scripts are at /usr/local/support/bugs/community/88827 on hod03 - 1G innodb buffer pool - to be able to get size > buffer pool, conf - commented *wait/interactive*timeout*(iibench had issues with low timeout) -- 5.6.35/5.6.38 - confirmed as reported, will try with 5.7.20 and would update once it is done (taking 10+hrs for each run), iibench, ibug.sh and conf file( [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35: cat docs/INFO_SRC commit: 1920b22f480ad50b125d77d42971a878e7ca4ea5 date: 2016-11-28 13:05:22 +0100 build-date: 2016-11-28 14:36:48 +0100 short: 1920b22 branch: mysql-5.6.35-release MySQL source 5.6.35 rm -rf data/ scripts/mysql_install_db --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/data bin/mysqld --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/data --core-file --log-error=$PWD/logs/log.err 2>&1 & For 1 table: ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35: bash ibug.sh 16 yes 5000000 root pw ib 16 users and 1 table wait for user 1 with pid 23797 wait for user 2 with pid 23806 wait for user 3 with pid 23807 wait for user 4 with pid 23808 wait for user 5 with pid 23809 wait for user 6 with pid 23810 wait for user 7 with pid 23811 wait for user 8 with pid 23812 wait for user 9 with pid 23813 wait for user 10 with pid 23814 wait for user 11 with pid 23815 wait for user 12 with pid 23816 wait for user 13 with pid 23817 wait for user 14 with pid 23818 wait for user 15 with pid 23819 wait for user 16 with pid 23820 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35: mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 1 dl_mb: 9433.9844 il_mb: 9525.8906 table_rows: 90472765 1 row in set (0.00 sec) For 16 tables: ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35: bash ibug.sh 16 no 5000000 root pw ib 16 users and 16 tables wait for user 1 with pid 2728 wait for user 2 with pid 2729 wait for user 3 with pid 2730 wait for user 4 with pid 2731 wait for user 5 with pid 2732 wait for user 6 with pid 2733 wait for user 7 with pid 2734 wait for user 8 with pid 2735 wait for user 9 with pid 2736 wait for user 10 with pid 2737 wait for user 11 with pid 2738 wait for user 12 with pid 2739 wait for user 13 with pid 2740 wait for user 14 with pid 2741 wait for user 15 with pid 2742 wait for user 16 with pid 2743 mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 16 dl_mb: 5046.2031 il_mb: 8808.3125 table_rows: 4863843 1 row in set (0.00 sec) mysql> mysql> show table status; +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Compact | 4863843 | 65 | 318636032 | 0 | 545013760 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi10 | InnoDB | 10 | Compact | 4854057 | 71 | 348028928 | 0 | 619413504 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi11 | InnoDB | 10 | Compact | 4864105 | 68 | 331235328 | 0 | 579633152 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi12 | InnoDB | 10 | Compact | 4856881 | 71 | 345931776 | 0 | 619479040 | 7340032 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi13 | InnoDB | 10 | Compact | 4866716 | 69 | 337526784 | 0 | 591167488 | 4194304 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi14 | InnoDB | 10 | Compact | 4872952 | 65 | 319684608 | 0 | 549208064 | 7340032 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi15 | InnoDB | 10 | Compact | 4863872 | 66 | 323878912 | 0 | 559693824 | 7340032 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi16 | InnoDB | 10 | Compact | 4863025 | 67 | 329138176 | 0 | 574390272 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi2 | InnoDB | 10 | Compact | 4858141 | 71 | 345931776 | 0 | 613171200 | 4194304 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi3 | InnoDB | 10 | Compact | 4865168 | 65 | 316538880 | 0 | 543981568 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi4 | InnoDB | 10 | Compact | 4865853 | 66 | 322830336 | 0 | 555483136 | 7340032 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi5 | InnoDB | 10 | Compact | 4861104 | 69 | 338575360 | 0 | 594296832 | 4194304 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi6 | InnoDB | 10 | Compact | 4857628 | 69 | 339640320 | 0 | 601653248 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi7 | InnoDB | 10 | Compact | 4866754 | 64 | 315490304 | 0 | 538722304 | 4194304 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi8 | InnoDB | 10 | Compact | 4858141 | 69 | 338575360 | 0 | 597458944 | 7340032 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi9 | InnoDB | 10 | Compact | 4864838 | 65 | 319684608 | 0 | 553418752 | 6291456 | 5000001 | 2018-01-01 17:00:15 | NULL | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 16 rows in set (0.00 sec) [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.35: du -h data/ib 15G data/ib ** Summary * database directory size is 15gb with 16 tables and 21.6gb with 1 table * data_length/index_length are about 4.9gb/8.6gb for 16 tables and then 9.21gb/9.3gb for 1 table --------------------------------- 5.6.38 rm -rf data/ scripts/mysql_install_db --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/data bin/mysqld --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/data --core-file --log-error=$PWD/logs/log.err 2>&1 & [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: cat docs/INFO_SRC commit: 733577633b7eec78a51b70636568d735c187e96c date: 2017-09-13 17:47:09 +0200 build-date: 2017-09-13 17:49:26 +0200 short: 7335776 branch: mysql-5.6.38-release MySQL source 5.6.38 mysql> grant all on *.* to 'root'@'%' IDENTIFIED BY 'pw' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to 'root'@'127.0.0.1' IDENTIFIED BY 'pw' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) --------- 1 table -- ibug session ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: time bash ibug.sh 16 yes 5000000 root pw ib 16 users and 1 table wait for user 1 with pid 20643 wait for user 2 with pid 20652 wait for user 3 with pid 20653 wait for user 4 with pid 20654 wait for user 5 with pid 20655 wait for user 6 with pid 20656 wait for user 7 with pid 20657 wait for user 8 with pid 20658 wait for user 9 with pid 20659 wait for user 10 with pid 20660 wait for user 11 with pid 20661 wait for user 12 with pid 20662 wait for user 13 with pid 20663 wait for user 14 with pid 20664 wait for user 15 with pid 20665 wait for user 16 with pid 20666 real 528m2.056s user 31m9.970s sys 7m35.507s -- CLI session mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 1 dl_mb: 9331.0000 il_mb: 9489.8750 table_rows: 85462751 1 row in set (0.00 sec) mysql> show table status; +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Compact | 85462751 | 114 | 9784262656 | 0 | 9950855168 | 6291456 | 80000001 | 2018-01-02 11:38:59 | NULL | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) - Dir [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: ls -lh data/ib total 21G -rw-rw---- 1 umshastr common 65 Jan 2 11:38 db.opt -rw-rw---- 1 umshastr common 13K Jan 2 11:38 pi1.frm -rw-rw---- 1 umshastr common 21G Jan 2 20:30 pi1.ibd --------- 16 tables -- ibug session ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: time bash ibug.sh 16 no 5000000 root pw ib 16 users and 16 tables wait for user 1 with pid 29393 wait for user 2 with pid 29394 wait for user 3 with pid 29395 wait for user 4 with pid 29396 wait for user 5 with pid 29397 wait for user 6 with pid 29398 wait for user 7 with pid 29399 wait for user 8 with pid 29400 wait for user 9 with pid 29401 wait for user 10 with pid 29402 wait for user 11 with pid 29403 wait for user 12 with pid 29404 wait for user 13 with pid 29405 wait for user 14 with pid 29406 wait for user 15 with pid 29407 wait for user 16 with pid 29408 real 378m45.235s user 30m2.624s sys 7m41.829s -- CLI session mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 16 dl_mb: 5075.2500 il_mb: 8877.2969 table_rows: 4856133 1 row in set (0.00 sec) mysql> show table status; +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Compact | 4856133 | 71 | 345931776 | 0 | 611074048 | 5242880 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi10 | InnoDB | 10 | Compact | 4858784 | 69 | 338575360 | 0 | 598491136 | 5242880 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi11 | InnoDB | 10 | Compact | 4860166 | 67 | 328089600 | 0 | 572276736 | 7340032 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi12 | InnoDB | 10 | Compact | 4865186 | 64 | 314441728 | 0 | 542916608 | 6291456 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi13 | InnoDB | 10 | Compact | 4866836 | 65 | 317587456 | 0 | 543965184 | 4194304 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi14 | InnoDB | 10 | Compact | 4867091 | 65 | 319684608 | 0 | 554450944 | 5242880 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi15 | InnoDB | 10 | Compact | 4863115 | 69 | 336478208 | 0 | 588005376 | 6291456 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi16 | InnoDB | 10 | Compact | 4846974 | 71 | 346980352 | 0 | 618414080 | 5242880 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi2 | InnoDB | 10 | Compact | 4860313 | 71 | 348028928 | 0 | 622624768 | 5242880 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi3 | InnoDB | 10 | Compact | 4864781 | 66 | 322830336 | 0 | 554467328 | 4194304 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi4 | InnoDB | 10 | Compact | 4864447 | 66 | 322830336 | 0 | 557613056 | 5242880 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi5 | InnoDB | 10 | Compact | 4854946 | 70 | 343834624 | 0 | 611074048 | 4194304 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi6 | InnoDB | 10 | Compact | 4855205 | 71 | 345931776 | 0 | 602685440 | 7340032 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi7 | InnoDB | 10 | Compact | 4858485 | 68 | 332283904 | 0 | 584859648 | 7340032 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi8 | InnoDB | 10 | Compact | 4865632 | 64 | 315490304 | 0 | 538722304 | 7340032 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | | pi9 | InnoDB | 10 | Compact | 4851948 | 70 | 342786048 | 0 | 606879744 | 7340032 | 5000001 | 2018-01-02 20:54:41 | NULL | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 16 rows in set (0.01 sec) - Dir [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.38: ls -lh data/ib total 15G -rw-rw---- 1 umshastr common 65 Jan 2 20:53 db.opt -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi10.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi10.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi11.frm -rw-rw---- 1 umshastr common 952M Jan 3 03:14 pi11.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi12.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi12.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi13.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi13.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi14.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi14.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi15.frm -rw-rw---- 1 umshastr common 944M Jan 3 03:14 pi15.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi16.frm -rw-rw---- 1 umshastr common 944M Jan 3 03:14 pi16.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi1.frm -rw-rw---- 1 umshastr common 944M Jan 3 03:14 pi1.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi2.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi2.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi3.frm -rw-rw---- 1 umshastr common 940M Jan 3 03:14 pi3.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi4.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi4.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi5.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi5.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi6.frm -rw-rw---- 1 umshastr common 936M Jan 3 03:14 pi6.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi7.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi7.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi8.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi8.ibd -rw-rw---- 1 umshastr common 13K Jan 2 20:54 pi9.frm -rw-rw---- 1 umshastr common 948M Jan 3 03:14 pi9.ibd ** Summary * database directory size is 15gb with 16 tables and 21gb with 1 table * data_length/index_length are about 4.9gb/8.6gb for 16 tables and then 9.11gb/9.26gb for 1 table ################################################################################### https://github.com/mdcallag/mytools/blob/master/bench/conf/wrk/orig5717/etc/my.cnf -- 5.7.20 rm -rf logs/ mkdir logs rm -rf data/ bin/mysqld --defaults-file=./my.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/data bin/mysqld --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/data --core-file --log-error=$PWD/logs/log.err 2>&1 & mysql> create database ib; Query OK, 1 row affected (0.00 sec) mysql> create user 'root'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> create user 'root'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to 'root'@'%' IDENTIFIED BY 'pw' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> grant all on *.* to 'root'@'127.0.0.1' IDENTIFIED BY 'pw' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> system cat docs/INFO_SRC commit: 0441472e19e9a2e41df5d61098eb97c3e211547c date: 2017-09-13 17:44:44 +0200 build-date: 2017-09-13 17:48:35 +0200 short: 0441472 branch: mysql-5.7.20-release MySQL source 5.7.20 --------- 1 table ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: time bash ibug.sh 16 yes 5000000 root pw ib 16 users and 1 table wait for user 1 with pid 7892 wait for user 2 with pid 7901 wait for user 3 with pid 7902 wait for user 4 with pid 7903 wait for user 5 with pid 7904 wait for user 6 with pid 7905 wait for user 7 with pid 7906 wait for user 8 with pid 7907 wait for user 9 with pid 7908 wait for user 10 with pid 7909 wait for user 11 with pid 7910 wait for user 12 with pid 7911 wait for user 13 with pid 7912 wait for user 14 with pid 7913 wait for user 15 with pid 7914 wait for user 16 with pid 7915 real 380m8.767s user 30m32.854s sys 7m40.822s mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 1 dl_mb: 10223.0000 il_mb: 9779.8906 table_rows: 97587300 1 row in set (0.00 sec) mysql> show table status; +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Dynamic | 97587300 | 109 | 10719592448 | 0 | 10254958592 | 5242880 | 80000001 | 2018-01-03 06:39:42 | 2018-01-03 12:59:51 | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: du -h data/ib 21G data/ib [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: ls -lh data/ib total 21G -rw-r----- 1 umshastr common 65 Jan 3 06:38 db.opt -rw-r----- 1 umshastr common 13K Jan 3 06:39 pi1.frm -rw-r----- 1 umshastr common 21G Jan 3 13:02 pi1.ibd --------- 16 tables ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: time bash ibug.sh 16 no 5000000 root pw ib 16 users and 16 tables wait for user 1 with pid 12828 wait for user 2 with pid 12829 wait for user 3 with pid 12830 wait for user 4 with pid 12831 wait for user 5 with pid 12832 wait for user 6 with pid 12833 wait for user 7 with pid 12834 wait for user 8 with pid 12835 wait for user 9 with pid 12836 wait for user 10 with pid 12837 wait for user 11 with pid 12838 wait for user 12 with pid 12839 wait for user 13 with pid 12840 wait for user 14 with pid 12841 wait for user 15 with pid 12842 wait for user 16 with pid 12843 real 416m45.269s user 29m43.067s sys 7m49.308s mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 16 dl_mb: 5097.3125 il_mb: 8928.2813 table_rows: 4864475 1 row in set (0.00 sec) mysql> mysql> show table status; +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Dynamic | 4864475 | 68 | 333332480 | 0 | 585908224 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:01:33 | NULL | latin1_swedish_ci | NULL | | | | pi10 | InnoDB | 10 | Dynamic | 4849832 | 71 | 345931776 | 0 | 617365504 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:56:27 | NULL | latin1_swedish_ci | NULL | | | | pi11 | InnoDB | 10 | Dynamic | 4852285 | 70 | 340688896 | 0 | 598458368 | 6291456 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:56:11 | NULL | latin1_swedish_ci | NULL | | | | pi12 | InnoDB | 10 | Dynamic | 4867973 | 65 | 316538880 | 0 | 541868032 | 4194304 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:55:22 | NULL | latin1_swedish_ci | NULL | | | | pi13 | InnoDB | 10 | Dynamic | 4865245 | 65 | 319684608 | 0 | 549208064 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:51:25 | NULL | latin1_swedish_ci | NULL | | | | pi14 | InnoDB | 10 | Dynamic | 4861537 | 68 | 335429632 | 0 | 584892416 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:01:28 | NULL | latin1_swedish_ci | NULL | | | | pi15 | InnoDB | 10 | Dynamic | 4854830 | 70 | 341737472 | 0 | 602685440 | 6291456 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:47:27 | NULL | latin1_swedish_ci | NULL | | | | pi16 | InnoDB | 10 | Dynamic | 4856195 | 71 | 345931776 | 0 | 607928320 | 6291456 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:56:18 | NULL | latin1_swedish_ci | NULL | | | | pi2 | InnoDB | 10 | Dynamic | 4857046 | 70 | 342786048 | 0 | 609009664 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:58:35 | NULL | latin1_swedish_ci | NULL | | | | pi3 | InnoDB | 10 | Dynamic | 4865522 | 67 | 328089600 | 0 | 567017472 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:52:19 | NULL | latin1_swedish_ci | NULL | | | | pi4 | InnoDB | 10 | Dynamic | 4850904 | 71 | 344883200 | 0 | 614219776 | 7340032 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:02:24 | NULL | latin1_swedish_ci | NULL | | | | pi5 | InnoDB | 10 | Dynamic | 4866428 | 65 | 316538880 | 0 | 538722304 | 7340032 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:44:52 | NULL | latin1_swedish_ci | NULL | | | | pi6 | InnoDB | 10 | Dynamic | 4854129 | 71 | 348028928 | 0 | 620527616 | 7340032 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:45:36 | NULL | latin1_swedish_ci | NULL | | | | pi7 | InnoDB | 10 | Dynamic | 4850229 | 70 | 339640320 | 0 | 601620480 | 6291456 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 21:58:54 | NULL | latin1_swedish_ci | NULL | | | | pi8 | InnoDB | 10 | Dynamic | 4865676 | 65 | 320733184 | 0 | 557596672 | 6291456 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:55:14 | NULL | latin1_swedish_ci | NULL | | | | pi9 | InnoDB | 10 | Dynamic | 4862647 | 66 | 324943872 | 0 | 564953088 | 5242880 | 5000001 | 2018-01-03 15:02:09 | 2018-01-03 20:49:34 | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 16 rows in set (0.00 sec) mysql> [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: du -h data/ib 15G data/ib [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: ls -lh data/ib total 15G -rw-r----- 1 umshastr common 65 Jan 3 15:01 db.opt -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi10.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi10.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi11.frm -rw-r----- 1 umshastr common 944M Jan 3 21:59 pi11.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi12.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi12.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi13.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi13.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi14.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi14.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi15.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi15.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi16.frm -rw-r----- 1 umshastr common 940M Jan 3 21:59 pi16.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi1.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi1.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi2.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi2.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi3.frm -rw-r----- 1 umshastr common 940M Jan 3 21:59 pi3.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi4.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi4.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi5.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi5.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi6.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi6.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi7.frm -rw-r----- 1 umshastr common 948M Jan 3 21:59 pi7.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi8.frm -rw-r----- 1 umshastr common 952M Jan 3 21:59 pi8.ibd -rw-r----- 1 umshastr common 13K Jan 3 15:02 pi9.frm -rw-r----- 1 umshastr common 944M Jan 3 21:59 pi9.ibd ** Summary * database directory size is 15gb with 16 tables and 21gb with 1 table * data_length/index_length are about 4.97gb/8.71gb for 16 tables and then 9.98gb/9.55gb for 1 table ### 8.0.4 https://github.com/mdcallag/mytools/blob/master/bench/conf/wrk/orig801/etc/my.cnf [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.4-rc-linux-x86_64: cat docs/INFO_SRC commit: 77632c043f93f64239a9286b3ec81640ddf45d27 date: 2017-11-01 15:38:24 +0100 build-date: 2017-11-01 17:30:38 +0100 short: 77632c0 branch: mysql-8.0 MySQL source 8.0.4 rm -rf logs/ mkdir logs rm -rf data/ bin/mysqld --defaults-file=./my.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/data bin/mysqld --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/data --core-file --log-error=$PWD/logs/log.err 2>&1 & -- 1 table ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.4-rc-linux-x86_64: time bash ibug.sh 16 yes 5000000 root pw ib 16 users and 1 table wait for user 1 with pid 12657 wait for user 2 with pid 12666 wait for user 3 with pid 12667 wait for user 4 with pid 12668 wait for user 5 with pid 12669 wait for user 6 with pid 12670 wait for user 7 with pid 12671 wait for user 8 with pid 12672 wait for user 9 with pid 12673 wait for user 10 with pid 12674 wait for user 11 with pid 12675 wait for user 12 with pid 12676 wait for user 13 with pid 12677 wait for user 14 with pid 12678 wait for user 15 with pid 12679 wait for user 16 with pid 12680 real 356m5.438s user 30m43.905s sys 7m35.018s mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 1 dl_mb: 10741.0000 il_mb: 10286.8906 TABLE_ROWS: 71057576 1 row in set (0.00 sec) mysql> show table status; +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Dynamic | 71057576 | 158 | 11262754816 | 0 | 10786586624 | 4194304 | 80000001 | 2018-01-04 05:34:16 | 2018-01-04 11:30:22 | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec) [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.4-rc-linux-x86_64: ls -lh data/ib total 21G -rw-r----- 1 umshastr common 21G Jan 4 11:33 pi1.ibd -- 16 tables ## Before running ibug.sh, ensure that "ib" schema exists, and root/pw(%,127.0.0.1,localhost etc) exists , and leave *timeout* settings to default [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.4-rc-linux-x86_64: time bash ibug.sh 16 no 5000000 root pw ib 16 users and 16 tables wait for user 1 with pid 12122 wait for user 2 with pid 12123 wait for user 3 with pid 12124 wait for user 4 with pid 12125 wait for user 5 with pid 12126 wait for user 6 with pid 12127 wait for user 7 with pid 12128 wait for user 8 with pid 12129 wait for user 9 with pid 12130 wait for user 10 with pid 12131 wait for user 11 with pid 12132 wait for user 12 with pid 12133 wait for user 13 with pid 12134 wait for user 14 with pid 12135 wait for user 15 with pid 12136 wait for user 16 with pid 12137 real 429m35.853s user 29m58.573s sys 7m49.243s mysql> select count(*), sum(data_length)/(1024*1024) as dl_mb, sum(index_length)/(1024*1024) as il_mb, table_rows from tables where table_schema = 'ib'\G *************************** 1. row *************************** count(*): 16 dl_mb: 5029.2188 il_mb: 8774.2500 TABLE_ROWS: 4865673 1 row in set (0.08 sec) mysql> use ib Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show table status; +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | pi1 | InnoDB | 10 | Dynamic | 4865673 | 65 | 316538880 | 0 | 551305216 | 4194304 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:09:37 | NULL | latin1_swedish_ci | NULL | | | | pi10 | InnoDB | 10 | Dynamic | 4867993 | 64 | 315490304 | 0 | 537673728 | 4194304 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:50:26 | NULL | latin1_swedish_ci | NULL | | | | pi11 | InnoDB | 10 | Dynamic | 4874020 | 64 | 316538880 | 0 | 541868032 | 6291456 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:08:13 | NULL | latin1_swedish_ci | NULL | | | | pi12 | InnoDB | 10 | Dynamic | 4864381 | 69 | 338575360 | 0 | 594296832 | 7340032 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:50:54 | NULL | latin1_swedish_ci | NULL | | | | pi13 | InnoDB | 10 | Dynamic | 4859622 | 71 | 345931776 | 0 | 615268352 | 7340032 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:23:49 | NULL | latin1_swedish_ci | NULL | | | | pi14 | InnoDB | 10 | Dynamic | 4858132 | 70 | 341737472 | 0 | 605831168 | 4194304 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:49:14 | NULL | latin1_swedish_ci | NULL | | | | pi15 | InnoDB | 10 | Dynamic | 4865950 | 65 | 318636032 | 0 | 548159488 | 6291456 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:46:29 | NULL | latin1_swedish_ci | NULL | | | | pi16 | InnoDB | 10 | Dynamic | 4861957 | 70 | 344883200 | 0 | 613154816 | 5242880 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:05:59 | NULL | latin1_swedish_ci | NULL | | | | pi2 | InnoDB | 10 | Dynamic | 4863930 | 67 | 327041024 | 0 | 563888128 | 5242880 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:26:19 | NULL | latin1_swedish_ci | NULL | | | | pi3 | InnoDB | 10 | Dynamic | 4871097 | 64 | 316538880 | 0 | 546062336 | 7340032 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:36:35 | NULL | latin1_swedish_ci | NULL | | | | pi4 | InnoDB | 10 | Dynamic | 4861590 | 65 | 317587456 | 0 | 545013760 | 6291456 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:49:49 | NULL | latin1_swedish_ci | NULL | | | | pi5 | InnoDB | 10 | Dynamic | 4855919 | 70 | 340688896 | 0 | 601636864 | 6291456 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:30:01 | NULL | latin1_swedish_ci | NULL | | | | pi6 | InnoDB | 10 | Dynamic | 4855826 | 71 | 346980352 | 0 | 620511232 | 5242880 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:07:31 | NULL | latin1_swedish_ci | NULL | | | | pi7 | InnoDB | 10 | Dynamic | 4871968 | 65 | 320733184 | 0 | 554450944 | 4194304 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:33:08 | NULL | latin1_swedish_ci | NULL | | | | pi8 | InnoDB | 10 | Dynamic | 4852099 | 70 | 343834624 | 0 | 612122624 | 5242880 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:55:29 | NULL | latin1_swedish_ci | NULL | | | | pi9 | InnoDB | 10 | Dynamic | 4865239 | 66 | 321781760 | 0 | 549224448 | 4194304 | 5000001 | 2018-01-04 12:45:53 | 2018-01-04 19:28:51 | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 16 rows in set (0.00 sec) [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.4-rc-linux-x86_64: ls -lh data/ib total 15G -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi10.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi11.ibd -rw-r----- 1 umshastr common 948M Jan 4 19:56 pi12.ibd -rw-r----- 1 umshastr common 948M Jan 4 19:56 pi13.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi14.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi15.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi16.ibd -rw-r----- 1 umshastr common 952M Jan 4 19:56 pi1.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi2.ibd -rw-r----- 1 umshastr common 948M Jan 4 19:56 pi3.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi4.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi5.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi6.ibd -rw-r----- 1 umshastr common 944M Jan 4 19:56 pi7.ibd -rw-r----- 1 umshastr common 948M Jan 4 19:56 pi8.ibd -rw-r----- 1 umshastr common 936M Jan 4 19:56 pi9.ibd [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.4-rc-linux-x86_64: du -h data/ib 15G data/ib [umshastr@hod03]/export/umesh/server/binaries/Trunk/my ** Summary * database directory size is 15gb with 16 tables and 21gb with 1 table * data_length/index_length are about 4.91gb/8.56gb for 16 tables and then 10.48gb/10.04gb for 1 table