[MDEV-17933] slow server status - dict_sys_get_size() Created: 2018-12-07  Updated: 2019-01-25  Resolved: 2019-01-23

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.1.37
Fix Version/s: 10.4.2, 10.1.38, 10.2.22, 10.3.13

Type: Bug Priority: Major
Reporter: Adam Bambuch Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: performance, xtradb

Issue Links:
Relates
relates to MDEV-13325 InnoDB assert dict_sys->size > 0 duri... Closed

 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



 Comments   
Comment by Marko Mäkelä [ 2018-12-08 ]

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.

Comment by Marko Mäkelä [ 2019-01-22 ]

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.

Comment by Marko Mäkelä [ 2019-01-23 ]

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
Generated at Thu Feb 08 08:40:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.