[MDEV-30071] Huge dentry cache after upgrading to 10.6 Created: 2022-11-22  Updated: 2024-02-07

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.6.10
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Arthur van Kleef Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: performance, regression
Environment:

Ubuntu 18.04 and 22.04, both ext4 and xfs. Mariadb packages from mariadb's repo.

Issue Links:
Relates
relates to MDEV-20492 Information schema query producing ma... Closed

 Description   

We recently upgraded from 10.4.26 to 10.6.10. While running 10.4 directory entry (dentry) cache was always stable at around ~1.5 GB. But after upgrading it slowly grows up to 120 GB.

I suspect these are caused by negative dentry cache entries, which are created because of our ORM (ActiveRecord) which constantly runs SHOW COLUMNS FROM <tablename>. As explained in MDEV-20492 this causes a tmp disk table to be created (and increments Created_tmp_disk_tables...)

$ sudo slabtop -o
 
 Active / Total Objects (% used)    : 1034571881 / 1053004080 (98,2%)
 
 Active / Total Slabs (% used)      : 20735738 / 20735738 (100,0%)
 
 Active / Total Caches (% used)     : 105 / 169 (62,1%)
 
 Active / Total Size (% used)       : 129896174,85K / 131196507,05K (99,0%)
 
 Minimum / Average / Maximum Object : 0,01K / 0,12K / 16,75K
 
 
 
  OBJS ACTIVE  USE OBJ SIZE  SLABS OBJ/SLAB CACHE SIZE NAME                   
530471168 513264640   0%    0,06K 8288612       64  33154448K kmalloc-rcl-64         
513672180 513584847   0%    0,19K 12230290       42  97842320K dentry                 
 
 
$ cat /proc/sys/fs/dentry-state
 
513679517	513646653	45	0	513632323	0       # 5th column is nr_negative

Now for the dentry cache, here's dcsnoop -a running 10.4 on Ubuntu 18.04 while executing SHOW COLUMNS FROM table:

43.904147   114578 mysqld           R tmp/#sql_1bf92_0.MAI
 
43.904221   114578 mysqld           R tmp/#sql_1bf92_0.MAD
 
43.904267   114578 mysqld           R tmp
 
43.904276   114578 mysqld           R tmp/#sql_1bf92_0.MAI
 
43.904283   114578 mysqld           R #sql_1bf92_0.MAI
 
43.904290   114578 mysqld           R tmp/#sql_1bf92_0.MAI
 
43.904296   114578 mysqld           R #sql_1bf92_0.MAI
 
43.904309   114578 mysqld           R tmp
 
43.904315   114578 mysqld           R #sql_1bf92_0.MAI
 
43.904321   114578 mysqld           R tmp/#sql_1bf92_0.MAD
 
43.904327   114578 mysqld           R #sql_1bf92_0.MAD
 
43.904333   114578 mysqld           R tmp/#sql_1bf92_0.MAD
 
43.904339   114578 mysqld           R #sql_1bf92_0.MAD
 
43.904806   114578 mysqld           R tmp/#sql_1bf92_0.MAI
 
43.904815   114578 mysqld           R #sql_1bf92_0.MAI
 
43.904823   114578 mysqld           R tmp/#sql_1bf92_0.MAI
 
43.904829   114578 mysqld           R tmp/#sql_1bf92_0.MAD
 
43.904835   114578 mysqld           R #sql_1bf92_0.MAD
 
43.904841   114578 mysqld           R tmp/#sql_1bf92_0.MAD

There are no M(isses) and dentry cache usage is not increasing.

10.6 on Ubuntu 18.04:

23.638942   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAI
 
23.638986   9903   mariadbd         M #sql-temptable-26af-93303-3206.MAI                  < !!!
 
23.639032   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAD
 
23.639039   9903   mariadbd         M #sql-temptable-26af-93303-3206.MAD                < !!!
 
23.639112   9903   mariadbd         R tmp
 
23.639123   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAI
 
23.639129   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAI
 
23.639135   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAI
 
23.639140   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAI
 
23.639146   9903   mariadbd         R tmp
 
23.639152   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAI
 
23.639157   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAD
 
23.639171   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAD
 
23.639177   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAD
 
23.639182   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAD
 
23.640095   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAI
 
23.640105   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAI
 
23.640111   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAI
 
23.640166   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAD
 
23.640174   9903   mariadbd         R #sql-temptable-26af-93303-3206.MAD
 
23.640181   9903   mariadbd         R tmp/#sql-temptable-26af-93303-3206.MAD

And 10.6 on Ubuntu 22.04:

3.841741    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841819    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841829    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841835    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841842    993595 mariadbd         M #sql-temptable-f293b-4017f-7cb.MAI              < !!!
 
3.841851    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.841856    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.841861    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.841870    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.841875    993595 mariadbd         M #sql-temptable-f293b-4017f-7cb.MAD             < !!!
 
3.841881    993595 mariadbd         R var
 
3.841886    993595 mariadbd         R var/lib
 
3.841892    993595 mariadbd         R lib
 
3.841897    993595 mariadbd         R var/lib/mysql
 
3.841902    993595 mariadbd         R lib/mysql
 
3.841908    993595 mariadbd         R mysql
 
3.841913    993595 mariadbd         R var/lib/mysql/tmp
 
3.841919    993595 mariadbd         R lib/mysql/tmp
 
3.841924    993595 mariadbd         R mysql/tmp
 
3.841929    993595 mariadbd         R tmp
 
3.841934    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841940    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841944    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841950    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841955    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAI
 
3.841960    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841965    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841971    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841975    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.841982    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAI
 
3.841987    993595 mariadbd         R var
 
3.841992    993595 mariadbd         R lib
 
3.841997    993595 mariadbd         R mysql
 
3.842002    993595 mariadbd         R tmp
 
3.842007    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAI
 
3.842012    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842019    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842024    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842029    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842034    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAD
 
3.842041    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842046    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842051    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842056    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842062    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAD
 
3.842067    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842072    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842077    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842082    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842087    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAI
 
3.842092    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842097    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842102    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842120    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAI
 
3.842128    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842133    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842138    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842143    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842148    993595 mariadbd         R #sql-temptable-f293b-4017f-7cb.MAD
 
3.842153    993595 mariadbd         R var/lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842158    993595 mariadbd         R lib/mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842163    993595 mariadbd         R mysql/tmp/#sql-temptable-f293b-4017f-7cb.MAD
 
3.842168    993595 mariadbd         R tmp/#sql-temptable-f293b-4017f-7cb.MAD

In 10.6 each time our ORM executes SHOW COLUMNS FROM <table> (1000/s) dentry cache usage increases, amounting to about ~120 GB after one month.

Obviously the temp_table name changed in 10.6, but it also seems that there's more path searching being done?

I'm not sure about the performance impact caused by this, the memory is reclaimable and we set vm.min_free_kbytes to a value high enough so that there's always enough memory available. But it is very different behavior from what it was in 10.4 and in environments where memory capacity is more constrained this could become problematic because of memory fragmentation?

 Comments   
Comment by Daniel Black [ 2024-02-02 ]

Probably using O_TMPFILE on Aria would avoid this problem.

Beginnings of a rather large patch:

diff --git a/storage/maria/ma_open.c b/storage/maria/ma_open.c
 
index 947db3a1762..7c47ae5f573 100644
 
--- a/storage/maria/ma_open.c
 
+++ b/storage/maria/ma_open.c
 
@@ -348,7 +350,12 @@ MARIA_HA *maria_open(const char *name, int mode, uint open_flags,
 
                         goto err;
 
                       });
 
       DEBUG_SYNC_C("mi_open_kfile");
 
-      if ((kfile=mysql_file_open(key_file_kfile, name_buff,
 
+      if (open_flags & HA_OPEN_TMP_TABLE)
 
+      {
 
+       if ((kfile=create_temp_file(name_buff, getenv("TMPDIR"), "aria_tmp", O_SHARE, MYF(common_flag | MY_TEMPORARY))) < 0)
 
+         goto err;
 
+      }
 
+      else if ((kfile=mysql_file_open(key_file_kfile, name_buff,
 
                                  (open_mode=O_RDWR) | O_SHARE | O_NOFOLLOW | O_CLOEXEC,
 
                                  MYF(common_flag | MY_NOSYMLINKS))) < 0)
 
       {

Generated at Thu Feb 08 10:13:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.