MariaDB : very high trx_rseg_history_len

Question

I have a production server running MariaDB 10.11.6 and some simple queries are taking forever (literraly, I have to kill them even after 5 minutes). My last discovery is a very high trx_rseg_history_len (13395128) but I don't know how to lower it. I arrived to this discovery from this blog post : https://www.percona.com/blog/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/

The original problem

Yesterday I had only one kind of "simple and slow query" : SELECT * FROM commande_reference WHERE crf_rfr_id IN (SELECT rfl_from_rfr_id FROM reference_front_link WHERE rfl_to_rfr_id = '116') I achieved to speed up the query with a simple and obvious FORCE INDEX(crf_rfr_id). As this request was working fine on the other servers, I made an EXPLAIN and the servers showed different results. So, I decided to update the server to Debian Bookworm and installed last MariaDB version. Now, the EXPLAIN statements are identical, but now the server is absolutely strugling and even easiest queries are taking forever...

The first reproducer

This query takes forever to execute (from 50 seconds to... forever) : select * from un_shipping_shipment where nss_cst_id = '1' and nss_reference = '1_1'

The explain shows :

With that, I discovered a missing index on nss_cst_id. After adding the index (which took 68 seconds), the query takes 0.09s. The thing is that this table has "only" 354724 records... And I don't see why a full scan would take more than 60 seconds !

Create table statement :

CREATE TABLE un_shipping_shipment ( 
nss_id mediumint(1) unsigned NOT NULL AUTO_INCREMENT,
nss_cst_id mediumint(1) unsigned NOT NULL, 
nss_nssm_id mediumint(8) unsigned NOT NULL, 
nss_reference varchar(100) NOT NULL, 
nss_weight mediumint(1) unsigned NOT NULL, 
nss_shipping_method tinyint(1) unsigned NOT NULL, 
nss_tracking varchar(50) NOT NULL, 
nss_created_at datetime DEFAULT NULL, 
nss_ready tinyint(1) unsigned NOT NULL, 
nss_nssl_id mediumint(1) unsigned NOT NULL, 
nss_company varchar(100) NOT NULL, 
nss_last_name varchar(100) NOT NULL, 
nss_first_name varchar(100) NOT NULL, 
nss_address1 varchar(100) NOT NULL, 
nss_address2 varchar(100) NOT NULL, 
nss_zip_code varchar(10) NOT NULL, 
nss_city varchar(100) NOT NULL, 
nss_country char(2) NOT NULL, 
nss_relay_id varchar(20) NOT NULL, 
nss_email varchar(100) NOT NULL, 
nss_phone varchar(100) NOT NULL, 
nss_properties longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, 
nss_nssd_id mediumint(1) unsigned NOT NULL, 
PRIMARY KEY (nss_id), 
KEY nss_nssd_id_fk (nss_nssd_id), 
KEY nss_nssl_id (nss_nssl_id), 
KEY nss_cst_id (nss_cst_id), 
KEY nss_reference (nss_reference), 
CONSTRAINT nss_nssd_id_fk FOREIGN KEY (nss_nssd_id) REFERENCES un_shipping_sender_address (nssd_id) 
) ENGINE=InnoDB AUTO_INCREMENT=392019 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT

So, I think the problem comes from this high trx_rseg_history_len, but I have no idea how to solve it and Google couldn't help me so far...

Please remember that the nss_cst_id and nss_reference indexes are new. If I delete them, the "reproducer" query on this table takes forever...

Slight improvment

I executed those commands and trx_rseg_history_len is slowly decresing (12310004 -> 11445785) :

SET GLOBAL innodb_max_purge_lag=1;
SET GLOBAL innodb_max_purge_lag_delay=1;

Any idea on how to speed this up ?

Slight improvment 2

SET GLOBAL innodb_purge_threads=32;
SET GLOBAL innodb_purge_rseg_truncate_frequency=1;

I'm actually at trx_rseg_history_len = 5163027. I really hope it will work better once purged. But I'm not even sure of that...

The end of THIS problem

After two hours, trx_rseg_history_len = 0. It freed 100Go of space on the disk...

The server has cooled down and most of the requests are fine. But unfortunately I still have the same issue on a few simple requests.

New reproducer

I noticed that phpMyAdmin was slow. After digging, I discovered that SELECT COUNT(*) FROM marketplace_feed takes 13 seconds ! The table has 23811 entries and a MEDIUMINT index that should be used...

CREATE TABLE `marketplace_feed` (
  `mfd_id` mediumint(1) unsigned NOT NULL AUTO_INCREMENT,
  `mfd_external_id` bigint(1) unsigned NOT NULL,
  `mfd_type` tinyint(1) unsigned NOT NULL,
  `mfd_characteristics` text NOT NULL,
  `mfd_submitted_at` datetime NOT NULL,
  `mfd_processed_at` datetime DEFAULT NULL,
  `mfd_successes` text NOT NULL,
  `mfd_warnings` text NOT NULL,
  `mfd_errors` text NOT NULL,
  PRIMARY KEY (`mfd_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25205 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Answer 1

I think I finally found the solution : increase the innodb_buffer_pool_size to 16Go (our server has 32 and I initially had a value of 128Mo). The slow queries are fast again, trx_rseg_history_len is close to zero, the server is brething again.

The first reproducer (without the index) is now executing in 2s the first time, and in 0.4s the following times (I guess the InnoDB files are loaded in the buffer pool).

It's quite disapointing though, that MariaDB/InnoDB respecting MVVC has such a high performance impact for a quite standard/default installation...

And thanks @ErgestBasha for helping, I felt less alone :-)

Answer 2

History length may increase when the indexes are not optimal.

Item 1

SELECT  cr.*
    FROM  reference_front_link AS rfl
    JOIN  commande_reference AS cr
         ON cr.crf_rfr_id = rfl.reference_front_link
    WHERE  rfl.rfl_to_rfr_id = '116' 

With indexes

cr:  INDEX(crf_rfr_id)
rfl:  INDEX(rfl_to_rfr_id, reference_front_link)  -- unless rfl_to_rfr_id is the PK

Item 2

Replace

KEY nss_reference (nss_reference), 

with a composite index:

INDEX(nss_reference, nss_cst_id)

Item 3

"why a full scan would take more than 60 seconds" -- possibly a fall out of the high history len.

Item 4

Are you using autocommit = 1? Or using long transactions?