Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11738

Mariadb uses 100% of several of my 8 cpus doing nothing

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.20
    • 10.1.23
    • None
    • 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

    • 10.1.22

    Description

      I use mariadb purely for a local sql database (via LibreOffice Base). There are no other users.

      Periodically the mysql process (still called that) take 37% (sometimes more) of the total available cpu, even though I am not doing anything at all, and the local database is not being used. If I restart the service the usage drops to 0%. I have not found any specific trigger for this behaviour.

      mysql.txt attached is the result of 'show engine innodb status;'
      my.cnf is the configuration file. Note that I use encryption for some tables - the key is itself encrypted and held in a part of my user space which is itself encrypted (I had to allow the service to access this in the mysql.service file). I then only start the mariadb/mysql service when I decrypt that part of my user space, so that it can get the key it needs. All this works without trouble.
      The conf.d file mariadb.cnf only contain character set info.

      Attachments

        1. my.cnf
          5 kB
        2. mysql.txt
          6 kB

        Issue Links

          Activity

            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.

            pastim Tim Passingham added a comment - 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

            jplindst Jan Lindström (Inactive) added a comment - 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.

            marko Marko Mäkelä added a comment - 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

            pastim Tim Passingham added a comment - 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.

            jplindst Jan Lindström (Inactive) added a comment - 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.

            People

              jplindst Jan Lindström (Inactive)
              pastim Tim Passingham
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.