Bug #89444 8.0.4rc --initialize-insecure is prohibitively slow
Submitted: 26 Jan 2018 23:21 Modified: 2 Mar 2018 12:38
Reporter: Roel Van de Paar Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Installing Severity:S1 (Critical)
Version:8.0.4rc OS:Any
Assigned to: CPU Architecture:Any

[26 Jan 2018 23:21] Roel Van de Paar
Description:
-bash-4.2$ rm -Rf /sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/data
-bash-4.2$ time /sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/bin/mysqld --no-defaults --initialize-insecure --basedir=/sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug --datadir=/sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/data
2018-01-26T22:47:50.216822Z 0 [System] [MY-010116] /sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/bin/mysqld (mysqld 8.0.4-rc-debug) starting as process 9178 ...
2018-01-26T22:47:56.304135Z 4 [Warning] [MY-010453] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

real	1m7.257s
user	0m10.564s
sys	0m0.638s

How to repeat:
rm -Rf /sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/data
/sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/bin/mysqld --no-defaults --initialize-insecure --basedir=/sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug --datadir=/sda/MS270118-mysql-8.0.4-rc-linux-x86_64-debug/data
[27 Jan 2018 0:12] Roel Van de Paar
Btw, not sure if it matters, but mysql was build on Ubuntu 17.10 with -DWITH_SSL=bundled then the resulting tarball was used on Centos 7, both x64.
[27 Jan 2018 8:37] Valeriy Kravchuk
Same (unusual) experience with 8.0.4 built from source on Fedora 27. cmake options were as follows:

cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/openxs/boost -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/8.0

The following command then took well more than a minute:

bin/mysqld --no-defaults --port=3306 --socket=/tmp/mysql.sock --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --initialize-insecure
[27 Jan 2018 20:15] MySQL Verification Team
On windows 10 pro

Attachment: 8944.png (image/png, text), 136.82 KiB.

[27 Jan 2018 20:19] MySQL Verification Team
Thank you for the bug report. I tested on Windows 10 Pro on the below hardware taking 19 seconds to create the data stuff, do you consider that slow?. Thanks in advance.

15:37:53>mkdir data

15:37:54>bin\mysqld --version
C:\mysql\bin\mysqld.exe  Ver 8.0.4-rc-log for Win64 on x86_64 (MySQL Community Server (GPL))

15:37:54>bin\mysqld --no-defaults --initialize-insecure --basedir=c:\mysql --datadir=c:\mysql\data

15:38:13>systeminfo

Host Name:                 URAL
OS Name:                   Microsoft Windows 10 Pro
OS Version:                10.0.16299 N/A Build 16299
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Standalone Workstation
OS Build Type:             Multiprocessor Free
Registered Owner:          N/A
Registered Organization:   N/A
Product ID:                00330-80000-00000-AA273
Original Install Date:     11/16/2017, 5:43:11 PM
System Boot Time:          1/4/2018, 8:06:41 PM
System Manufacturer:       LG Electronics
System Model:              15Z970-E.BH91P1
System Type:               x64-based PC
Processor(s):              1 Processor(s) Installed.
                           [01]: Intel64 Family 6 Model 142 Stepping 9 GenuineIntel ~2703 Mhz
BIOS Version:              Phoenix Technologies Ltd. 15ZB0210 X64, 3/14/2017
Windows Directory:         C:\WINDOWS
System Directory:          C:\WINDOWS\system32
Boot Device:               \Device\HarddiskVolume2
System Locale:             en-us;English (United States)
Input Locale:              en-us;English (United States)
Time Zone:                 (UTC-03:00) Brasilia
Total Physical Memory:     8,051 MB
Available Physical Memory: 3,376 MB
Virtual Memory: Max Size:  14,195 MB
Virtual Memory: Available: 5,505 MB
Virtual Memory: In Use:    8,690 MB
Page File Location(s):     C:\pagefile.sys
Domain:                    WORKGROUP
Logon Server:              \\URAL
Hotfix(s):                 7 Hotfix(s) Installed.
                           [01]: KB4048951
                           [02]: KB4053577
                           [03]: KB4054022
                           [04]: KB4056887
                           [05]: KB4058043
                           [06]: KB4058702
                           [07]: KB4056892
Network Card(s):           3 NIC(s) Installed.
                           [01]: Intel(R) Dual Band Wireless-AC 8265
                                 Connection Name: Wi-Fi
                                 Status:          Media disconnected
                           [02]: Realtek USB FE Family Controller
                                 Connection Name: Ethernet
                                 DHCP Enabled:    Yes
                                 DHCP Server:     192.168.2.1
                                 IP address(es)
                                 [01]: 192.168.2.163
                                 [02]: fe80::f53c:83ef:51e5:8c44
                           [03]: Bluetooth Device (Personal Area Network)
                                 Connection Name: Bluetooth Network Connection
                                 Status:          Media disconnected
Hyper-V Requirements:      VM Monitor Mode Extensions: Yes
                           Virtualization Enabled In Firmware: Yes
                           Second Level Address Translation: Yes
                           Data Execution Prevention Available: Yes

15:38:16>bin\mysqld --standalone --console
2018-01-27T17:38:19.540340Z 0 [System] [MY-010116] C:\mysql\bin\mysqld.exe (mysqld 8.0.4-rc-log) starting as process 8316 ...
2018-01-27T17:38:21.177453Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-01-27T17:38:21.216952Z 0 [System] [MY-010931] C:\mysql\bin\mysqld.exe: ready for connections. Version: '8.0.4-rc-log'  socket: ''  port: 3306  MySQL Community Server (GPL).
[30 Jan 2018 7:11] MySQL Verification Team
Developer commented it's duplicate of private bug which was closed without to fix 8.0, so verifying this public one.
[30 Jan 2018 7:42] Roel Van de Paar
This was tested on a fairly high end machine, so as mentioned time is very slow.
[30 Jan 2018 9:18] Terje Røsten
Hi Roel,

a change to scripts/mysql_system_tables_data.sql caused mysqld --initialize
to perform lots of implicit commits which in turn gives performance issue
on system with slow storage. Issue is fixed upstream.

For the record, what file system are you using?
If using XFS, switching to ext4 might help (as a workaround to next release is available).
[30 Jan 2018 20:46] Roel Van de Paar
Thanks. This was on ext4 (rw,noatime,nodiratime,discard,data=ordered), SSD I think, high end system.
[30 Jan 2018 20:51] Roel Van de Paar
Confirmed enterprise level SSD.
[13 Feb 2018 15:42] MySQL Verification Team
just pass the parameter --innodb-flush-log-at-trx-commit=0 when 
you run the mysqld --initialize
[13 Feb 2018 15:43] MySQL Verification Team
and sync-binlog=0  or --skip-log-bin .....
[2 Mar 2018 3:24] Erlend Dahl
Duplicate of 

Bug#89122 Severe performance regression in server bootstrap
[2 Mar 2018 4:05] Roel Van de Paar
This bug was just made a duplicate of itself. Please re-verify.
[2 Mar 2018 5:52] Erlend Dahl
Fixed.
[2 Mar 2018 8:26] Roel Van de Paar
Erlend, please provide some more information.

First it was made duplicate in error to itself, now it is marked as "fixed" yet made "duplicate". Please provide detail as to the versions fixed, what it is duplicate to, etc.
[2 Mar 2018 12:38] MySQL Verification Team
Duplicate of bug: https://bugs.mysql.com/bug.php?id=89122.
[19 Mar 2018 17:50] Paul DuBois
Fixed in 8.0.11 by Bug#89122.