## Environment

 lscpu | grep -E '^Thread|^Core|^Socket|^CPU\('
CPU(s):                16
Thread(s) per core:    2
Core(s) per socket:    8
Socket(s):             1

free -g -t
              total        used        free      shared  buff/cache   available
Mem:            117           2         104           2           9         111
Swap:             7           0           7
Total:          125           2         112

Note - Before each scenarios, confirmed that "free" is always >65+

 cat /etc/*release
Oracle Linux Server release 7.9
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9
Red Hat Enterprise Linux Server release 7.9 (Maipo)
Oracle Linux Server release 7.9

-- file system on which datadir residing

 df -Th /export/home/tmp/ushastry/binaries/mysql-5.7.40
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sdb1      xfs   2.0T  109G  1.9T   6% /export/home


-- Startup
rm -rf 108913/
bin/mysqld --defaults-file=./my.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/108913 --log-error-verbosity=3
bin/mysqld_safe --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/108913 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/108913/log.err --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &

-- build
cat docs/INFO_SRC
commit: 026e1ba907cb1628355a38c1b88a06a29a078ed4
date: 2022-08-29 15:16:10 +0200
build-date: 2022-08-30 03:50:33 +0000
short: 026e1ba907c
branch: mysql-5.7.40-release

MySQL source 5.7.40

-- conf
 cat my.cnf
[mysqld]
innodb_flush_method="O_DIRECT"
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=49392123904


-- - With ALGORITHM=DEFAULT, OCK=DEFAULT to avoid ERROR 1846


== First scenario:
++ Load data
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source Test.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.17 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.89 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

Query OK, 8 rows affected, 8 warnings (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 8

Query OK, 16 rows affected, 16 warnings (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 16

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 1/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32 rows affected, 32 warnings (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 32

Query OK, 64 rows affected, 64 warnings (0.02 sec)
Records: 64  Duplicates: 0  Warnings: 64

Query OK, 128 rows affected, 128 warnings (0.04 sec)
Records: 128  Duplicates: 0  Warnings: 128

Query OK, 256 rows affected, 256 warnings (0.04 sec)
Records: 256  Duplicates: 0  Warnings: 256

Query OK, 512 rows affected, 512 warnings (0.02 sec)
Records: 512  Duplicates: 0  Warnings: 512

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 2/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 1024 rows affected, 1024 warnings (0.05 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

Query OK, 2048 rows affected, 2048 warnings (0.31 sec)
Records: 2048  Duplicates: 0  Warnings: 2048

Query OK, 4096 rows affected, 4096 warnings (0.16 sec)
Records: 4096  Duplicates: 0  Warnings: 4096

Query OK, 8192 rows affected, 8192 warnings (0.28 sec)
Records: 8192  Duplicates: 0  Warnings: 8192

Query OK, 16384 rows affected, 16384 warnings (0.72 sec)
Records: 16384  Duplicates: 0  Warnings: 16384

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 3/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32768 rows affected, 32768 warnings (1.50 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

Query OK, 65536 rows affected, 65535 warnings (4.23 sec)
Records: 65536  Duplicates: 0  Warnings: 65536

Query OK, 131072 rows affected, 65535 warnings (6.90 sec)
Records: 131072  Duplicates: 0  Warnings: 131072

Query OK, 262144 rows affected, 65535 warnings (13.28 sec)
Records: 262144  Duplicates: 0  Warnings: 262144

Query OK, 524288 rows affected, 65535 warnings (26.91 sec)
Records: 524288  Duplicates: 0  Warnings: 524288

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 4/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (28.13 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.75 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.62 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.71 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.34 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 5/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (26.93 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.80 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.42 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.28 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (29.58 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.09 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 6/6 completed |
+-------------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|  7536640 |
+----------+
1 row in set (1.62 sec)


++ Drop partition

mysql> alter table big_table drop partition p20221112;
Query OK, 0 rows affected (6.19 sec)
Records: 0  Duplicates: 0  Warnings: 0



== Second Scenario:
++ Load data

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source Test.sql; select now();
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.15 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.99 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

Query OK, 4 rows affected, 4 warnings (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 4

Query OK, 8 rows affected, 8 warnings (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 8

Query OK, 16 rows affected, 16 warnings (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 16

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 1/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32 rows affected, 32 warnings (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 32

Query OK, 64 rows affected, 64 warnings (0.03 sec)
Records: 64  Duplicates: 0  Warnings: 64

Query OK, 128 rows affected, 128 warnings (0.04 sec)
Records: 128  Duplicates: 0  Warnings: 128

Query OK, 256 rows affected, 256 warnings (0.04 sec)
Records: 256  Duplicates: 0  Warnings: 256

Query OK, 512 rows affected, 512 warnings (0.02 sec)
Records: 512  Duplicates: 0  Warnings: 512

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 2/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 1024 rows affected, 1024 warnings (0.04 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

Query OK, 2048 rows affected, 2048 warnings (0.33 sec)
Records: 2048  Duplicates: 0  Warnings: 2048

Query OK, 4096 rows affected, 4096 warnings (0.15 sec)
Records: 4096  Duplicates: 0  Warnings: 4096

Query OK, 8192 rows affected, 8192 warnings (0.29 sec)
Records: 8192  Duplicates: 0  Warnings: 8192

Query OK, 16384 rows affected, 16384 warnings (0.67 sec)
Records: 16384  Duplicates: 0  Warnings: 16384

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 3/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32768 rows affected, 32768 warnings (1.68 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

Query OK, 65536 rows affected, 65535 warnings (3.42 sec)
Records: 65536  Duplicates: 0  Warnings: 65536

Query OK, 131072 rows affected, 65535 warnings (6.54 sec)
Records: 131072  Duplicates: 0  Warnings: 131072

Query OK, 262144 rows affected, 65535 warnings (14.54 sec)
Records: 262144  Duplicates: 0  Warnings: 262144

Query OK, 524288 rows affected, 65535 warnings (26.22 sec)
Records: 524288  Duplicates: 0  Warnings: 524288

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 4/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (27.96 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.89 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (29.55 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.34 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (29.03 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 5/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (28.15 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.05 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.46 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.96 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.57 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.83 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 6/6 completed |
+-------------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|  7536640 |
+----------+
1 row in set (1.62 sec)

+---------------------+
| now()               |
+---------------------+
| 2022-11-11 15:07:34 |
+---------------------+
1 row in set (0.00 sec)

++ Leave server idle for 30 minutes
++ Drop partition


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-11 15:39:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> alter table big_table drop partition p20221112;
Query OK, 0 rows affected (6.17 sec)
Records: 0  Duplicates: 0  Warnings: 0



Every 2.0s: ls -lh 108913/test/                                                                                                   

total 18G
-rw-r----- 1 umshastr common  13K Nov 11 15:01 big_table.frm
-rw-r----- 1 umshastr common  18G Nov 11 15:07 big_table#P#p20221112.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221113.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221114.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221115.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221116.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221117.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221118.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221119.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221120.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221121.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#pMax.ibd
-rw-r----- 1 umshastr common   65 Nov 11 15:01 db.opt


Every 2.0s: ls -lh 108913/test/                                                                                                   Fri Nov 11 15:41:24 2022

total 1.3M
-rw-r----- 1 umshastr common  13K Nov 11 15:39 big_table.frm
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221113.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221114.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221115.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221116.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221117.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221118.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221119.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221120.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#p20221121.ibd
-rw-r----- 1 umshastr common 128K Nov 11 15:01 big_table#P#pMax.ibd
-rw-r----- 1 umshastr common   65 Nov 11 15:01 db.opt


###########################################################################
Pls correct my understanding here, when you say "REBOOT Server" I assume mysqld and not the OS.

== Third Scenario:
++ Load data

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source Test.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.17 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.92 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2

Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

Query OK, 8 rows affected, 8 warnings (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 8

Query OK, 16 rows affected, 16 warnings (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 16

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 1/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32 rows affected, 32 warnings (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 32

Query OK, 64 rows affected, 64 warnings (0.02 sec)
Records: 64  Duplicates: 0  Warnings: 64

Query OK, 128 rows affected, 128 warnings (0.05 sec)
Records: 128  Duplicates: 0  Warnings: 128

Query OK, 256 rows affected, 256 warnings (0.05 sec)
Records: 256  Duplicates: 0  Warnings: 256

Query OK, 512 rows affected, 512 warnings (0.03 sec)
Records: 512  Duplicates: 0  Warnings: 512

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 2/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 1024 rows affected, 1024 warnings (0.04 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

Query OK, 2048 rows affected, 2048 warnings (0.32 sec)
Records: 2048  Duplicates: 0  Warnings: 2048

Query OK, 4096 rows affected, 4096 warnings (0.15 sec)
Records: 4096  Duplicates: 0  Warnings: 4096

Query OK, 8192 rows affected, 8192 warnings (0.31 sec)
Records: 8192  Duplicates: 0  Warnings: 8192

Query OK, 16384 rows affected, 16384 warnings (0.60 sec)
Records: 16384  Duplicates: 0  Warnings: 16384

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 3/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32768 rows affected, 32768 warnings (1.40 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

Query OK, 65536 rows affected, 65535 warnings (4.03 sec)
Records: 65536  Duplicates: 0  Warnings: 65536

Query OK, 131072 rows affected, 65535 warnings (6.47 sec)
Records: 131072  Duplicates: 0  Warnings: 131072

Query OK, 262144 rows affected, 65535 warnings (13.74 sec)
Records: 262144  Duplicates: 0  Warnings: 262144

Query OK, 524288 rows affected, 65535 warnings (27.19 sec)
Records: 524288  Duplicates: 0  Warnings: 524288

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 4/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (28.36 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.88 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.71 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.35 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.21 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 5/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (28.61 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.30 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.85 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (30.56 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (29.00 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (29.02 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 6/6 completed |
+-------------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|  7536640 |
+----------+
1 row in set (1.62 sec)



++ Reboot the server


mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

bin/mysqld_safe --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/108913 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/108913/log.err --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &
[1] 25969
[umshastr@support-cluster04:/export/home/tmp/ushastry/binaries/mysql-5.7.40]$ 2022-11-11T15:03:09.251546Z mysqld_safe Logging to '/export/home/tmp/ushastry/binaries/mysql-5.7.40/108913/log.err'.
2022-11-11T15:03:09.268448Z mysqld_safe Starting mysqld daemon with databases from /export/home/tmp/ushastry/binaries/mysql-5.7.40/108913

++ Drop partition

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
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> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table big_table drop partition p20221112;
Query OK, 0 rows affected (2 min 58.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>


== Fourth Scenario:
++ Load data

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source Test.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.15 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.95 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

Query OK, 8 rows affected, 8 warnings (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 8

Query OK, 16 rows affected, 16 warnings (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 16

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 1/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32 rows affected, 32 warnings (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 32

Query OK, 64 rows affected, 64 warnings (0.03 sec)
Records: 64  Duplicates: 0  Warnings: 64

Query OK, 128 rows affected, 128 warnings (0.04 sec)
Records: 128  Duplicates: 0  Warnings: 128

Query OK, 256 rows affected, 256 warnings (0.03 sec)
Records: 256  Duplicates: 0  Warnings: 256

Query OK, 512 rows affected, 512 warnings (0.03 sec)
Records: 512  Duplicates: 0  Warnings: 512

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 2/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 1024 rows affected, 1024 warnings (0.04 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

Query OK, 2048 rows affected, 2048 warnings (0.32 sec)
Records: 2048  Duplicates: 0  Warnings: 2048

Query OK, 4096 rows affected, 4096 warnings (0.16 sec)
Records: 4096  Duplicates: 0  Warnings: 4096

Query OK, 8192 rows affected, 8192 warnings (0.30 sec)
Records: 8192  Duplicates: 0  Warnings: 8192

Query OK, 16384 rows affected, 16384 warnings (0.71 sec)
Records: 16384  Duplicates: 0  Warnings: 16384

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 3/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 32768 rows affected, 32768 warnings (1.44 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

Query OK, 65536 rows affected, 65535 warnings (4.60 sec)
Records: 65536  Duplicates: 0  Warnings: 65536

Query OK, 131072 rows affected, 65535 warnings (7.09 sec)
Records: 131072  Duplicates: 0  Warnings: 131072

Query OK, 262144 rows affected, 65535 warnings (13.36 sec)
Records: 262144  Duplicates: 0  Warnings: 262144

Query OK, 524288 rows affected, 65535 warnings (27.83 sec)
Records: 524288  Duplicates: 0  Warnings: 524288

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 4/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (28.50 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.71 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.36 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.02 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.09 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 5/6 completed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 589824 rows affected, 65535 warnings (28.39 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.72 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (30.50 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.19 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (27.77 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

Query OK, 589824 rows affected, 65535 warnings (28.27 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

+-------------------------+
| STATUS                  |
+-------------------------+
| Data load 6/6 completed |
+-------------------------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|  7536640 |
+----------+
1 row in set (1.64 sec)

++ Reboot the server

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

2022-11-11T15:16:38.275127Z mysqld_safe mysqld from pid file 
/export/home/tmp/ushastry/binaries/mysql-5.7.40/108913/support-cluster04.pid ended


bin/mysqld_safe --defaults-file=./my.cnf --basedir=$PWD --datadir=$PWD/108913 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/108913/log.err --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &
[1] 30997
[umshastr@support-cluster04:/export/home/tmp/ushastry/binaries/mysql-5.7.40]$ 2022-11-11T15:16:42.660128Z mysqld_safe Logging to '/export/home/tmp/ushastry/binaries/mysql-5.7.40/108913/log.err'.
2022-11-11T15:16:42.677168Z mysqld_safe Starting mysqld daemon with databases from /export/home/tmp/ushastry/binaries/mysql-5.7.40/108913


 bin/mysql -uroot -S /tmp/mysql.sock                                         
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-11 16:16:49 |
+---------------------+
1 row in set (0.00 sec)

mysql>


++ Leave server idle for 30 minutes
++ Drop partition


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-11 16:47:04 |
+---------------------+
1 row in set (0.00 sec)

mysql> use test
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>  alter table big_table drop partition p20221112;
ERROR 1067 (42000): Invalid default value for 'created_day'
mysql>
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  alter table big_table drop partition p20221112;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0




Every 2.0s: ls -lh 108913/test/                                                                                                   

total 18G
-rw-r----- 1 umshastr common  13K Nov 11 16:08 big_table.frm
-rw-r----- 1 umshastr common  18G Nov 11 16:15 big_table#P#p20221112.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221113.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221114.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221115.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221116.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221117.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221118.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221119.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221120.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221121.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#pMax.ibd
-rw-r----- 1 umshastr common   65 Nov 11 16:08 db.opt


Every 2.0s: ls -lh 108913/test/                                                                                                   

total 1.3M
-rw-r----- 1 umshastr common  13K Nov 11 16:47 big_table.frm
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221113.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221114.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221115.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221116.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221117.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221118.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221119.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221120.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#p20221121.ibd
-rw-r----- 1 umshastr common 128K Nov 11 16:08 big_table#P#pMax.ibd
-rw-r----- 1 umshastr common   65 Nov 11 16:08 db.opt