## 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


 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

-- build used
 cat docs/INFO_SRC
commit: da4abf34c335274d18a2a8b2d6c9a677a498b15b
date: 2022-09-13 18:06:48 +0200
build-date: 2022-09-13 16:16:26 +0000
short: da4abf34c33
branch: mysql-8.0.31-release

MySQL source 8.0.31

-- conf  (rest to default)
cat my.cnf
[mysqld]
innodb_flush_method="O_DIRECT"
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=49392123904


-- file system on which datadir residing

df -Th /export/home/tmp/ushastry/binaries/mysql-8.0.31
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sdb1      xfs   2.0T  106G  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=13306 --log-error=$PWD/108913/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &


## To avoid  ERROR 1067 (42000): Invalid default value for 'created_day', have set set sql_mode='';

== 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 8
Server version: 8.0.31 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 (0.00 sec)

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

Query OK, 1 row affected (0.01 sec)

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

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

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

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.94 sec)

Query OK, 1 row affected (0.01 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.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.02 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.05 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.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.05 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

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

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

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

Query OK, 16384 rows affected, 16384 warnings (0.82 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 (2.11 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

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

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

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

Query OK, 524288 rows affected, 65535 warnings (37.16 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 (42.29 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

Query OK, 589824 rows affected, 65535 warnings (41.88 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 (42.49 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

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

Query OK, 589824 rows affected, 65535 warnings (41.99 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 (0.42 sec)

++ Drop partition

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


-- file system snapshot before and after dropping

Every 2.0s: ls -lh 108913/test/                                                                                                   

total 18G
-rw-r----- 1 umshastr common  18G Nov 11 12:43 big_table#p#p20221112.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#pmax.ibd


Every 2.0s: ls -lh 108913/test/                                                                                                   

total 1.5M
-rw-r----- 1 umshastr common 144K Nov 11 12:43 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:34 big_table#p#pmax.ibd


###########################################################################


== 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 8
Server version: 8.0.31 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 (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, 1 warning (0.24 sec)

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

Query OK, 0 rows affected (0.01 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.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.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.03 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.05 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

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

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

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

Query OK, 16384 rows affected, 16384 warnings (0.79 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 (2.03 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

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

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

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

Query OK, 524288 rows affected, 65535 warnings (37.25 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 (41.76 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

Query OK, 589824 rows affected, 65535 warnings (42.42 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 (42.60 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

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

Query OK, 589824 rows affected, 65535 warnings (42.16 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 (0.44 sec)

+---------------------+
| now()               |
+---------------------+
| 2022-11-11 12:55:02 |
+---------------------+
1 row in set (0.00 sec)


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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-11 13:30:20 |
+---------------------+
1 row in set (0.00 sec)

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


-- file system snapshot before and after dropping

Every 2.0s: ls -lh 108913/test/                                                                                                   

total 18G
-rw-r----- 1 umshastr common  18G Nov 11 12:55 big_table#p#p20221112.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#pmax.ibd


Every 2.0s: ls -lh 108913/test/                                                                                                   

total 1.5M
-rw-r----- 1 umshastr common 144K Nov 11 13:30 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 12:46 big_table#p#pmax.ibd


###########################################################################
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 8
Server version: 8.0.31 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 (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, 1 warning (0.23 sec)

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

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.91 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.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.04 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.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.05 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

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

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

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

Query OK, 16384 rows affected, 16384 warnings (0.84 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 (2.06 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

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

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

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

Query OK, 524288 rows affected, 65535 warnings (37.13 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 (41.22 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

Query OK, 589824 rows affected, 65535 warnings (40.70 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 (42.68 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

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

Query OK, 589824 rows affected, 65535 warnings (41.93 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 (0.44 sec)



++ Reboot the server

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

mysql> Restarting mysqld...
2022-11-11T12:41:43.255642Z mysqld_safe Number of processes running now: 0
2022-11-11T12:41:43.258147Z mysqld_safe mysqld restarted


++ Drop partition

mysql> use test
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

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 (0.00 sec)

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


-- file system snapshot before and after dropping

Every 2.0s: ls -lh 108913/test/                                                                                                   

total 18G
-rw-r----- 1 umshastr common  18G Nov 11 13:41 big_table#p#p20221112.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:42 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#pmax.ibd

Every 2.0s: ls -lh 108913/test/                                                                                                   

total 1.5M
-rw-r----- 1 umshastr common 144K Nov 11 13:42 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:32 big_table#p#pmax.ibd

###########################################################################

== 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 8
Server version: 8.0.31 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 (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, 1 warning (0.23 sec)

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

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.97 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.03 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.04 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.05 sec)
Records: 1024  Duplicates: 0  Warnings: 1024

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

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

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

Query OK, 16384 rows affected, 16384 warnings (0.75 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.86 sec)
Records: 32768  Duplicates: 0  Warnings: 32768

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

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

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

Query OK, 524288 rows affected, 65535 warnings (37.65 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 (41.90 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

Query OK, 589824 rows affected, 65535 warnings (42.07 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 (41.35 sec)
Records: 589824  Duplicates: 0  Warnings: 589824

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

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

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

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

Query OK, 589824 rows affected, 65535 warnings (42.74 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 (0.45 sec)

++ Reboot the server

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

mysql> Restarting mysqld...
2022-11-11T12:59:40.628829Z mysqld_safe Number of processes running now: 0
2022-11-11T12:59:40.631304Z mysqld_safe mysqld restarted

mysql> select now();
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: test

+---------------------+
| now()               |
+---------------------+
| 2022-11-11 13:59:46 |
+---------------------+
1 row in set (0.02 sec)


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


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-11 14:30:58 |
+---------------------+
1 row in set (0.00 sec)

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 (0.00 sec)

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


-- file system snapshot before and after dropping

Every 2.0s: ls -lh 108913/test/                                                                                                   

total 18G
-rw-r----- 1 umshastr common  18G Nov 11 13:59 big_table#p#p20221112.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#pmax.ibd


Every 2.0s: ls -lh 108913/test/                                                                                                   

total 1.5M
-rw-r----- 1 umshastr common 144K Nov 11 14:31 big_table#p#p20221113.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221114.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221115.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221116.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221117.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221118.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221119.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221120.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#p20221121.ibd
-rw-r----- 1 umshastr common 144K Nov 11 13:50 big_table#p#pmax.ibd