[MDEV-30071] Huge dentry cache after upgrading to 10.6 - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6.10
Fix Version/s: None
Component/s: Server
Labels:
- performance
- regression
Environment:
Ubuntu 18.04 and 22.04, both ext4 and xfs. Mariadb packages from mariadb's repo.

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?

Attachments

Issue Links

relates to

MDEV-20492 Information schema query producing many disk temp table

Closed

Huge dentry cache after upgrading to 10.6

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration