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

slow server status - dict_sys_get_size()

Details

    Description

      Hello,

      On enviroment with 3M tables and MariaDB 10.1.37, "server status;" is painfully slow, it tooks almost 10 seconds to complete query.

      It's caused by dict_sys_get_size function that was introduced by fix of MDEV-13325. MariaDB spends almost whole time in this function.
      It tooks about 0.01 seconds to complete this query on MariaDB 10.1.21.

      --- aaa.c	2018-12-07 20:19:24.948128381 +0100
      		 
      +++ mariadb-10.1.37/storage/xtradb/dict/dict0dict.cc	2018-10-31 22:54:07.000000000 +0100
      		 
      @@ -25,9 +25,6 @@
      		 
       Created 1/8/1996 Heikki Tuuri
      		 
       ***********************************************************************/
      		 
       
      -#include <time.h>
      		 
      -#include <sys/time.h>
      		 
      -
      		 
       #include "dict0dict.h"
      		 
       #include "fts0fts.h"
      		 
       #include "fil0fil.h"
      		 
      @@ -7258,8 +7255,6 @@
      		 
       ulint
      		 
       dict_sys_get_size()
      		 
       {
      		 
      -	struct timeval t1, t2;
      		 
      -	gettimeofday(&t1, NULL);
      		 
       	ulint size = 0;
      		 
       
       	ut_ad(dict_sys);
      		 
      @@ -7285,8 +7280,5 @@
      		 
       
       	mutex_exit(&dict_sys->mutex);
      		 
       
      -	gettimeofday(&t2, NULL);
      		 
      -	ib_logf(IB_LOG_LEVEL_INFO, "call dict_sys_get_size: %f", (double) (t2.tv_sec - t1.tv_sec) + ((double) (t2.tv_usec - t1.tv_usec)) / 1000000);
      		 
      -
      		 
       	return (size);
      		 
       }


      Dec  4 17:00:49 -------- mysqld: 2018-12-04 17:00:49 139919386642176 [Note] InnoDB: call dict_sys_get_size: 7.715302
      		 
      Dec  4 21:25:24 -------- mysqld: 2018-12-04 21:25:24 139919441475328 [Note] InnoDB: call dict_sys_get_size: 4.888833

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13325 InnoDB assert dict_sys->size > 0 during ALTER TABLE

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            The fix of MDEV-13325 removed the aggregate field dict_sys->size and introduced a function dict_sys_get_size() that is traversing the data dictionary cache while holding dict_sys->mutex, to get some memory usage statistics.

            We’d better protect such traversal with rw_lock_s_lock(dict_operation_lock) in order to avoid unnecessary blocking of other threads. But that would not reduce the time for the traversal.

            Another observation is that not all memory allocations by the InnoDB data dictionary cache are being accounted for. One example is foreign key constraints, which were never tracked by dict_sys->size nor by the new function. Another example is virtual column metadata, starting with 10.2.

            Do we really need to provide inaccurate statistics of memory usage, or could we remove this output altogether? There are better tools for diagnosing memory usage, such as the heap profiler of tcmalloc.

            marko Marko Mäkelä added a comment - The fix of MDEV-13325 removed the aggregate field dict_sys->size and introduced a function dict_sys_get_size() that is traversing the data dictionary cache while holding dict_sys->mutex , to get some memory usage statistics. We’d better protect such traversal with rw_lock_s_lock(dict_operation_lock) in order to avoid unnecessary blocking of other threads. But that would not reduce the time for the traversal. Another observation is that not all memory allocations by the InnoDB data dictionary cache are being accounted for. One example is foreign key constraints, which were never tracked by dict_sys->size nor by the new function. Another example is virtual column metadata, starting with 10.2. Do we really need to provide inaccurate statistics of memory usage, or could we remove this output altogether? There are better tools for diagnosing memory usage, such as the heap profiler of tcmalloc .
            marko Marko Mäkelä added a comment -

            In XtraDB, the function dict_sys_get_size() is invoked in more places than in InnoDB. We cannot remove it easily.
            Some external programs (such as innotop) could look for the string Dictionary memory allocated in the SHOW ENGINE INNODB STATUS output. While that count is known to be inaccurate, I am not sure if we can remove that in a GA version.

            marko Marko Mäkelä added a comment - In XtraDB, the function dict_sys_get_size() is invoked in more places than in InnoDB. We cannot remove it easily. Some external programs (such as innotop ) could look for the string Dictionary memory allocated in the SHOW ENGINE INNODB STATUS output. While that count is known to be inaccurate, I am not sure if we can remove that in a GA version.
            marko Marko Mäkelä added a comment -

            In the end, I decided to replace dict_sys_get_size() with a crude estimate that can be computed without holding any mutex. This will affect various output from InnoDB and XtraDB:

            • SHOW ENGINE INNODB STATUS
            • the InnoDB monitor output
            • INFORMATION_SCHEMA.XTRADB_INTERNAL_HASH_TABLES
            marko Marko Mäkelä added a comment - In the end, I decided to replace dict_sys_get_size() with a crude estimate that can be computed without holding any mutex. This will affect various output from InnoDB and XtraDB: SHOW ENGINE INNODB STATUS the InnoDB monitor output INFORMATION_SCHEMA.XTRADB_INTERNAL_HASH_TABLES

            People

              marko Marko Mäkelä
              bambuch Adam Bambuch
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.