Linux 4.8.0-32-generic #34-Ubuntu SMP Tue Dec 13 14:30:43 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Ubuntu 16.10
7.5 GB memory. Core™ i7-4770T CPU @ 2.50GHz × 8. 64 bit.
Intel® Haswell Desktop

It is probably another case of MDEV-11581 and MDEV-11565 – please check the comments there, there is an explanation.

That said, I'll still assign it to jplindst – given the number of complaints (which aren't altogether unfounded), maybe it makes sense to reconsider the design, at least for future releases?

Thanks for this. I'll set the number of innodb-encrption-threads to 0.

It still seems odd, given that I have no key rotation (as I understand it this plugin doesn't support it anyway), nor innodb-tablespaces-encryption on (so as I understand it only explicit tables and the log are encrypted). However, I am very much an amateur user, so I'm sure there was a purpose to all that activity.

Apologies for raising an issue that had been raised before. I did search for issues but didn't know enough to realise where the problem might lie so didn't find the other reports.

pastim, in the attached config you do actually have innodb-encrypt-tables enabled.

My understanding is that innodb-encrypt-tables has to be at least ON to allow me to encrypt some tables explicitly, which I want. I didn't want the overhead of using innodb-tablespaces-encryption, which I can imagine may require mariadb to do more work when not actually being used for queries. But as I said, I'm very much the amateur here

bb-10.1-MDEV-11738

I would prefer to avoid adding a configuration parameter, and I think we should attach the collection of encrypted tablespaces directly to fil_system.

https://github.com/MariaDB/server/commit/add67f3b5f37dedf53d770f6218d6c60cf409217

The patch is a step to the right direction, but I think that it needs some more refinement. I would remove the redundant flag fil_space_crypt_t::closing altogether and rely entirely on the fil_space_t fields stop_new_ops, n_pending_ops, and crypt_data with appropriate concurrency control.

Please re-request a full review after addressing my review comments.

https://github.com/mariadb/server/commit/bde1dad6a5021586a7ce899c611664ee82ec7697

I posted some review comments to GitHub.
Sorry, I think that some further revision is needed. But it definitely is on the right track.

Tried to address most of the review comments, there were few that did not actually work.

https://github.com/MariaDB/server/commit/352283b3ee08dc49880a729a8ed652d82acaa92c

Thanks, it is getting better. I think that in order to move forward faster, it is OK to file follow-up bugs for some remaining issues. Remember to link them to this ticket.

Do you need me, as one of the reporters and an 'ordinary' user, to test this? I assume that if I reset innodb-encryption-threads to, say, 4, I should be able to see that the problem doesn't re-occur. Let me know and I'll give a whirl.

I just did an ubuntu update and mariadb is now 10.1.22, but that's not the latest as I understand it, so I'll need to find the latest one if you want me to test it.

Please note that to disable key rotation you need to set innodb-encryption-rotate-key-age=0

pastim, this fix narrowly missed the 10.1.22 release, which was published today.

I'll have to wait then

I have set set innodb-encryption-rotate-key-age=0

commit 50eb40a2a8aa3af6cc271f6028f4d6d74301d030
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Mar 14 12:56:01 2017 +0200

MDEV-11738: Mariadb uses 100% of several of my 8 cpus doing nothing

MDEV-11581: Mariadb starts InnoDB encryption threads
when key has not changed or data scrubbing turned off

Background: Key rotation is based on background threads
(innodb-encryption-threads) periodically going through
all tablespaces on fil_system. For each tablespace
current used key version is compared to max key age
(innodb-encryption-rotate-key-age). This process
naturally takes CPU. Similarly, in same time need for
scrubbing is investigated. Currently, key rotation
is fully supported on Amazon AWS key management plugin
only but InnoDB does not have knowledge what key
management plugin is used.

This patch re-purposes innodb-encryption-rotate-key-age=0
to disable key rotation and background data scrubbing.
All new tables are added to special list for key rotation
and key rotation is based on sending a event to
background encryption threads instead of using periodic
checking (i.e. timeout).

fil0fil.cc: Added functions fil_space_acquire_low()
to acquire a tablespace when it could be dropped concurrently.
This function is used from fil_space_acquire() or
fil_space_acquire_silent() that will not print
any messages if we try to acquire space that does not exist.
fil_space_release() to release a acquired tablespace.
fil_space_next() to iterate tablespaces in fil_system
using fil_space_acquire() and fil_space_release().
Similarly, fil_space_keyrotation_next() to iterate new
list fil_system->rotation_list where new tables.
are added if key rotation is disabled.
Removed unnecessary functions fil_get_first_space_safe()
fil_get_next_space_safe()

fil_node_open_file(): After page 0 is read read also
crypt_info if it is not yet read.

btr_scrub_lock_dict_func()
buf_page_check_corrupt()
buf_page_encrypt_before_write()
buf_merge_or_delete_for_page()
lock_print_info_all_transactions()
row_fts_psort_info_init()
row_truncate_table_for_mysql()
row_drop_table_for_mysql()
Use fil_space_acquire()/release() to access fil_space_t.

buf_page_decrypt_after_read():
Use fil_space_get_crypt_data() because at this point
we might not yet have read page 0.

fil0crypt.cc/fil0fil.h: Lot of changes. Pass fil_space_t* directly
to functions needing it and store fil_space_t* to rotation state.
Use fil_space_acquire()/release() when iterating tablespaces
and removed unnecessary is_closing from fil_crypt_t. Use
fil_space_t::is_stopping() to detect when access to
tablespace should be stopped. Removed unnecessary
fil_space_get_crypt_data().

fil_space_create(): Inform key rotation that there could
be something to do if key rotation is disabled and new
table with encryption enabled is created.
Remove unnecessary functions fil_get_first_space_safe()
and fil_get_next_space_safe(). fil_space_acquire()
and fil_space_release() are used instead. Moved
fil_space_get_crypt_data() and fil_space_set_crypt_data()
to fil0crypt.cc.

fsp_header_init(): Acquire fil_space_t*, write crypt_data
and release space.

check_table_options()
Renamed FIL_SPACE_ENCRYPTION_* TO FIL_ENCRYPTION_*

i_s.cc: Added ROTATING_OR_FLUSHING field to
information_schema.innodb_tablespace_encryption
to show current status of key rotation.