axel Marko informs me that a bug fix is trivial once he has some reasonable suggestion for a limit. Would you have a recommendation from your experience? Thank you!

From the discussion with marko in Slack it seems as if this background thread does nothing more than marking pages as dirty so they would be picked up by the checkpointing mechanism and (re)encrypted. I fail to see why one would need multiple such threads at all. The encryption itself and the IO would be the bottleneck of that background operation.

I will however do some testing to find out if multiple such threads do any good.

This testcase;

# mysqld options required for replay:  --innodb-encryption-threads=10000

Leads to another double assert:

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 0x0 thread_stack 0x49000

2020-04-25 00:45:25 0x7fccc113f700  InnoDB: Assertion failure in file /test/10.5_dbg/storage/innobase/include/sync0arr.ic line 82

InnoDB: Failing assertion: *cell != NULL

InnoDB: We intentionally generate a memory trap.

[...]

2020-04-25 00:45:25 0x7fccc6f03700  InnoDB: Assertion failure in file /test/10.5_dbg/storage/innobase/include/sync0arr.ic line 82

InnoDB: Failing assertion: *cell != NULL

InnoDB: We intentionally generate a memory trap.

[...]

This testcase:

SET @@GLOBAL.innodb_encryption_threads=255;

SET @@GLOBAL.innodb_encryption_threads=1000;

SET @@GLOBAL.innodb_encryption_threads=10000;

Produces the issue quicker without CLI/Server hangs.

Discussing with Axel. Test done (and same result for innodb_sync_array_size=1000);

10.5.3>SELECT @@GLOBAL.innodb_sync_array_size;

+---------------------------------+

| @@GLOBAL.innodb_sync_array_size |

+---------------------------------+

|                              10 |

+---------------------------------+

1 row in set (0.000 sec)

10.5.3>SET @@GLOBAL.innodb_encryption_threads=10000;

ERROR 2013 (HY000): Lost connection to MySQL server during query

That backtrace from Roel hints that the number of innodb_encryption_threads counts against the number of sync array slots (srv_max_n_threads). Having too many innodb_encryption_threads would just fill the sync array and eventuelly overflow it and then crash the server. The limit for innodb_encryption_threads should hence depend on srv_max_n_threads. It should probably be no larger than 1/10th of it or the encryption threads would disrupt normal operations.

Now I see in MDEV-14462 and friends that srv_max_n_threads is about to be removed. So I wonder how much sense such a solution would make.

Proposal to set it to an arbitrary but more sensible limit? 255? 100? 10?

I would prefer an exact proposal, so that I do not have to investigate too much myself. Now that innodb_sync_array_size was mentioned, I see that its minimum value is 1. Does that minimum value cause crashes if encryption is not involved?

I performed a quick benchmark myself. I believe that this test keeps the server idle while the files are being encrypted in the background.

diff --git a/mysql-test/suite/encryption/t/encrypt_and_grep.opt b/mysql-test/suite/encryption/t/encrypt_and_grep.opt

index c50ec719307..ed6cbd72b49 100644

--- a/mysql-test/suite/encryption/t/encrypt_and_grep.opt

+++ b/mysql-test/suite/encryption/t/encrypt_and_grep.opt

@@ -1,7 +1,6 @@

 --innodb-encrypt-tables=ON

 --innodb-encrypt-log=ON

 --innodb-encryption-rotate-key-age=15

---innodb-encryption-threads=4

 --innodb-tablespaces-encryption

for i in 1 4 16 64; do ./mtr --mysqld=--innodb-encryption-threads=$i encryption.encrypt_and_grep; done

This was on a system with 2×10 CPU cores, or 40 threads in total with hyperthreading.

I also tried innodb_encryption_threads=255 and innodb_sync_array_size=1 without any problem. The test execution time was about the same as with innodb_encryption_threads=16.

Based on this experiment, I think that 255 would be a reasonable upper limit.

marko the innodb_sync_array_size variable is misnamed. This variable does not set the size of the sync array, but in how many partitions it is split (to reduce mutex contention). The crash with very big innodb_encryption_threads happens because each such thread allocates one slot in the sync array. Then the array overflows eventually.

The sync array is statically sized using srv_max_n_threads. I could not find where this in turn is set, but it must be somewhere when InnoDB is initialized. Probably related to max_connections? In any case there is an implied relationship between the size of the sync array and the number of threads in InnoDB. It was just never foreseen that there could be a huge number of internal threads using the sync array. IMHO the whole logic for determining the size of the sync array should be checked and fixed.

axel, sorry, I missed your comment until now. In MDEV-21452 (MariaDB 10.6), we reduced the sync arrays to a single 64-bit std::atomic integer that roughly keeps track of the longest wait on dict_sys.mutex and allows innodb_fatal_semaphore_wait_threshold to be enforced on that single mutex (which usually is participating in all "long semaphore waits" that I can remember). RW-locks will be either native ones (MDEV-24167) or futex-based, and the shared/update/exclusive locks for index trees or buffer pool blocks (MDEV-24142) are based on futex, or a mutex and condition variables.

After MDEV-21452 in 10.6, the encryption threads are constantly sleeping and waking up. The reason for this is the I/O throttling: fil_crypt_find_space_to_rotate() would first potentially wait in fil_crypt_alloc_iops(), and at the end, invoke fil_crypt_return_iops() to interrupt the sleep of any other threads that are waiting in fil_crypt_alloc_iops(). The assumption seems to be that there are plenty of tablespaces that need key rotation. It would appear that when there is no work to do, the threads would be waking up unnecessarily.

I repeated my previous benchmark, on 10.5 and 10.6, both compiled with the following:

cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo -DPLUGIN_PERFSCHEMA=NO

This was with 10.5 e8217d070fc3e60870131615a48515836c773b07 and 10.6 af1335c2d6c3f3d656e4227bc0c925c5f7051d7e.

So, we still seem to have some benefit from multiple threads. But, what if we use innodb_encryption_threads=1 (there is a single buf_flush_page_cleaner thread that does the actual work, after all) and a larger value of innodb_encryption_rotation_iops?

There is some random variation in the numbers, but we still seem to benefit from multiple threads that are dirtying the pages concurrently.

Having a large number of encryption threads may lead to extreme thrashing, as noted in MDEV-24426.