-- 2025-04-03: test savepoint and release savepoint

DELIMITER $$

CREATE OR REPLACE PROCEDURE InsertIntoLknmT2_bind_savept()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE id CHAR(9);

    PREPARE stmt FROM 'INSERT INTO lknm.t2 VALUES (?, ''AAAA'', NOW())';
	start transaction;
	savepoint savept1;
    WHILE i <= 9999999 DO
        SET id = LPAD(i, 8, '0');
        SET id = CONCAT('A', id);
        EXECUTE stmt USING id;
        SET i = i + 1;
    END WHILE;
	release savepoint savept1;
	commit;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

truncate table lknm.t2;
flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 09:40:41 |
+---------------------+
CALL InsertIntoLknmT2_bind_savept();
Query OK, 10000000 rows affected (5 min 1.471 sec)

MariaDB [lknm]> select min(id), max(id), min(ts), max(ts), count(*) from t2;
+-----------+-----------+---------------------+---------------------+----------+
| min(id)   | max(id)   | min(ts)             | max(ts)             | count(*) |
+-----------+-----------+---------------------+---------------------+----------+
| A00000000 | A09999999 | 2025-04-03 09:47:51 | 2025-04-03 09:52:06 | 10000000 |
+-----------+-----------+---------------------+---------------------+----------+

-- MariaDB memory at node 2 increased from 7.3% to 43.1% at the end, and kept on increasing within ~1 min., peaked at ~70%+.
-- MariaDB memory at node 2 decreased for ~1 min., to stabilize at 35.7%


    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  99289 gcis_gc+  20   0 9380068   3.2g   1.2g S 106.2  43.1 205:25.63 mariadbd
3993444 root      20   0 2309952 534780  50532 S   0.0   6.8   0:12.58 bdsecd


Tried once more:
truncate table lknm.t2;
flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 10:18:29 |
+---------------------+
CALL InsertIntoLknmT2_bind_savept();



MariaDB [lknm]> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` varchar(50) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `ts` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



DELIMITER $$

CREATE OR REPLACE PROCEDURE Update_LknmT2_bind_savept()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE id CHAR(9);

    PREPARE stmt FROM 'UPDATE lknm.t2 SET `ts`=NOW() WHERE `id`=?';
	start transaction;
	savepoint savept1;
    WHILE i <= 9999999 DO
        SET id = LPAD(i, 8, '0');
        SET id = CONCAT('A', id);
        EXECUTE stmt USING id;
        SET i = i + 1;
    END WHILE;
	release savepoint savept1;
	commit;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 11:10:12 |
+---------------------+

CALL Update_LknmT2_bind_savept();
Query OK, 10000000 rows affected (7 min 11.310 sec)

MariaDB [lknm]> select min(id), max(id), min(ts), max(ts), count(*) from t2;
+-----------+-----------+---------------------+---------------------+----------+
| min(id)   | max(id)   | min(ts)             | max(ts)             | count(*) |
+-----------+-----------+---------------------+---------------------+----------+
| A00000000 | A09999999 | 2025-04-03 11:11:07 | 2025-04-03 11:17:33 | 10000000 |
+-----------+-----------+---------------------+---------------------+----------+
1 row in set (3.454 sec)


DELIMITER $$

CREATE OR REPLACE PROCEDURE Update_LknmT2_bind()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE id CHAR(9);

    PREPARE stmt FROM 'UPDATE lknm.t2 SET `ts`=NOW() WHERE `id`=?';
	start transaction;
	-- savepoint savept1;
    WHILE i <= 9999999 DO
        SET id = LPAD(i, 8, '0');
        SET id = CONCAT('A', id);
        EXECUTE stmt USING id;
        SET i = i + 1;
    END WHILE;
	-- release savepoint savept1;
	commit;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 12:01:16 |
+---------------------+

CALL Update_LknmT2_bind();
Query OK, 10000000 rows affected (7 min 10.968 sec)

select min(id), max(id), min(ts), max(ts), count(*) from t2;
+-----------+-----------+---------------------+---------------------+----------+
| min(id)   | max(id)   | min(ts)             | max(ts)             | count(*) |
+-----------+-----------+---------------------+---------------------+----------+
| A00000000 | A09999999 | 2025-04-03 12:01:41 | 2025-04-03 12:08:08 | 10000000 |
+-----------+-----------+---------------------+---------------------+----------+

At node 2, run:
watch "top -b -o +%MEM | head -n 10"

MariaDB memory changed from 7.8% to 42.8%

flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 12:16:43 |
+---------------------+

MariaDB [lknm]> CALL Update_LknmT2_bind();
Query OK, 10000000 rows affected (7 min 6.370 sec)

select min(id), max(id), min(ts), max(ts), count(*) from t2;
+-----------+-----------+---------------------+---------------------+----------+
| min(id)   | max(id)   | min(ts)             | max(ts)             | count(*) |
+-----------+-----------+---------------------+---------------------+----------+
| A00000000 | A09999999 | 2025-04-03 12:17:21 | 2025-04-03 12:23:44 | 10000000 |
+-----------+-----------+---------------------+---------------------+----------+

MariaDB memory changed from 42.8% to 62.2%

- Restarted MariaDB, memory returned to 7.2%
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
 327949 gcis_gc+  20   0 8027892 561676  30080 S   0.0   7.2   0:01.81 mariadbd


flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 12:46:19 |
+---------------------+

CALL Update_LknmT2_bind_savept();
Query OK, 10000000 rows affected (7 min 8.669 sec)

select min(id), max(id), min(ts), max(ts), count(*) from t2;
+-----------+-----------+---------------------+---------------------+----------+
| min(id)   | max(id)   | min(ts)             | max(ts)             | count(*) |
+-----------+-----------+---------------------+---------------------+----------+
| A00000000 | A09999999 | 2025-04-03 12:46:45 | 2025-04-03 12:53:09 | 10000000 |
+-----------+-----------+---------------------+---------------------+----------+

MariaDB memory changed from 7.2% to 47.1%

flush query cache;
flush tables;
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-04-03 13:10:00 |
+---------------------+

CALL Update_LknmT2_bind_savept();
Query OK, 10000000 rows affected (7 min 7.405 sec)

select min(id), max(id), min(ts), max(ts), count(*) from t2;
+-----------+-----------+---------------------+---------------------+----------+
| min(id)   | max(id)   | min(ts)             | max(ts)             | count(*) |
+-----------+-----------+---------------------+---------------------+----------+
| A00000000 | A09999999 | 2025-04-03 13:10:08 | 2025-04-03 13:16:32 | 10000000 |
+-----------+-----------+---------------------+---------------------+----------+

MariaDB memory changed from 47.1% to 67.7% (started to increase at~ 13:16:38).