The page flushing was last revised in MDEV-26055. Also MDEV-32977 has been filed for similar symptoms.

MariaDB Server 10.5.9 was released 3 years ago. Can you try to upgrade to a later version?

Ramachandrula Sriman narayana murthy <ramachandrula.srima@hcl-software.com>
8:02 PM (1 minute ago)
to Marko

Hi Marko Makela

Thank you for quick response. I find those two MDEV-26055. Also MDEV-32977 are not related to the issue we have here. The tickets mentioned are not part of encryption and there is no error relating to semaphore that is noticed in my error log file.

2024-03-12 11:36:13 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139894688810752 has waited at srv0srv.cc line 1554 for 275.00 seconds the semaphore:

The present application support that we hold is Mariadb10.5.9. so we can not move out of these version until we get confirmation from application support. Kindly guide me, to resolve in these regards.

The messages "Cannot close file" are due to [https://mariadb.com/kb/en/innodb-system-variables/#innodb_open_files|innodb_open_files] being reached. Could it be the autosizing isn't sufficient for the workload?

Is the usm_opt_lock table of sufficient size that taking over an hour to encrypt would seem unlikely (at 1000iops).

Was the ALTER TABLE being performed on the usm_opt_lock table?

What exact encryption variables where updated? And was the same behaviour observed with an ALTER TABLE under the same condition before the variables where updated?

As this is a bug tracker more than support ticket system, look to recreate your issue with a self contained test case, and then test on the latest 10.5 instance.

Also look at the status measurement and recommendations taken in MDEV-26055 and MDEV-32977 before dismissing them so quickly. Encryption maybe holding the table open, but the cause of the problem may be elsewhere.

Yes , Thank you , As advised, i did a test to recreate the issue .

 The type of encryption is Data At Rest Encryption using "file_key_management" with algorithm "aes_cbc"

plugin_load_add=file_key_management.so
file_key_management_encryption_algorithm=aes_cbc

After updating my.cnf file with the Encryption Variables. started MariaDB service, and checked the status with below query . after 5 min there is no change for the rows in below query.

MariaDB [(none)]> select name, KEY_ROTATION_PAGE_NUMBER, KEY_ROTATION_MAX_PAGE_NUMBER from information_schema.innodb_tablespaces_encryption where min_key_version = 0 or ROTATING_OR_FLUSHING = 1;
--------------------------------------------------------------------------

--------------------------------------------------------------------------

There are no issues noticed in ErrorLog file . Started manual encryption for other schema tables in parallel using "alter table TABLENAME encrypted=yes" .

The execution was slow, so altered the global variable as below. Later after changing innodb_encryption_rotation_iops there are errors noticed in ErrorLog file.

MariaDB [(none)]> select @@innodb_encryption_threads;
-----------------------------

-----------------------------

-----------------------------
1 row in set (0.000 sec)

MariaDB [(none)]> select @@innodb_encryption_rotation_iops;
-----------------------------------

-----------------------------------

-----------------------------------
1 row in set (0.000 sec)

MariaDB [(none)]> set global innodb_encryption_rotation_iops=10000;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> select @@innodb_encryption_rotation_iops;
-----------------------------------

-----------------------------------

-----------------------------------
1 row in set (0.000 sec)

Error Logfile:

[root@duuscn1ahxunwcldbs1001 mariadb]# tail -f /data/HCL/mariadb/Error_logs/duuscn1ahxunwcldbs1001.err
2024-03-13 5:42:13 0 [Note] Server socket created on IP: '10.210.2.131'.
2024-03-13 5:42:13 0 [Note] Reading of all Master_info entries succeeded
2024-03-13 5:42:13 0 [Note] Added new Master_info '' to hash table
2024-03-13 5:42:13 0 [Note] /data/HCL/mariadb/bin/mariadbd: ready for connections.
Version: '10.5.9-MariaDB-log' socket: '/tmp/mysqld.sock' port: 3306 MariaDB Server
2024-03-13 5:42:16 0 [Note] InnoDB: Buffer pool(s) load completed at 240313 5:42:16
2024-03-13 6:10:40 4 [Note] InnoDB: Cannot close file ./plan/uap_event_queue.ibd because of 1 pending operations
2024-03-13 6:11:55 4 [Note] InnoDB: Cannot close file ./plan/uap_event_queue.ibd because of 1 pending operations
2024-03-13 6:16:52 4 [Note] InnoDB: Cannot close file ./plan/uap_event_queue.ibd because of 1 pending operations
2024-03-13 6:29:06 4 [Note] InnoDB: Cannot close file ./plan/uap_event_queue.ibd because of 1 pending operations