MariaDB large number of sessions waiting on index_tree_rw_lock

Question

I have a MariaDB RDS instance that is mostly saving batched insert statements to 4 tables. These inserts have batches of an average of 200/batch and each statement saves to a single table. However, the db keeps getting overloaded handling this load.

From the performance schema, all of the sessions are waiting on synch/sxlock/innodb/index_tree_rw_lock and to a lesser extent synch/mutex/innodb/buf_pool_mutex. From the cpu statistics, the utilization doesn't get anywhere more than 10%, which makes me think the size of the instance is sufficient.

Is there any way to tune the db to help with this or what else can be done to further diagnose the issue?

The schema for the tables (the actual schema has around 30 columns, but I truncated the non-key ones):

CREATE TABLE `table_name` (
  `id` varchar(191) NOT NULL,
  `datacenter` varchar(191) NOT NULL,
  `date` date NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `version` smallint(6) DEFAULT NULL,
  `shared` tinyint(1) DEFAULT NULL,
  `region` varchar(15) DEFAULT NULL,
  `country` varchar(15) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `zip_code` varchar(15) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`,`datacenter`,`date`),
  KEY `idx_duid_daily_summary_date` (`date`),
  KEY `idx_duid_daily_summary_type` (`type`),
  KEY `idx_duid_daily_summary_version` (`version`),
  KEY `idx_duid_daily_summary_region` (`region`),
  KEY `idx_diod_daily_summary_country` (`country`),
  KEY `idx_duid_daily_summary_city` (`city`),
  KEY `idx_duid_daily_summary_zip_code` (`zip_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

This is the result of show engine innodb status with the transactions truncated

=====================================
2023-06-26 16:11:05 0x14fcd4e5b700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 1711200 srv_idle
srv_master_thread log flush and writes: 1711074
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 116627587
Purge done for trxs n:o < 116606518 undo n:o < 0 state: running
History list length 11957
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 116617516, ACTIVE 101769 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 194
MariaDB thread id 494093, OS thread handle 23077741442816, query id 15982269 172.30.78.200 user Update
INSERT INTO `table_name` (`id`,`datacenter`,`date`,...
---TRANSACTION 116617364, ACTIVE 103272 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 179
MariaDB thread id 491367, OS thread handle 23074382518016, query id 15972493 172.30.89.39 user Update
INSERT INTO `table_name` (`id`,`datacenter`,`date`,...
---TRANSACTION 116617134, ACTIVE 105514 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 175
MariaDB thread id 487260, OS thread handle 23077251856128, query id 15957979 172.30.90.82 user Update
INSERT INTO `table_name` (`id`,`datacenter`,`date`,...
---TRANSACTION (0x150a788da000), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x150a788cb900), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
1680816271 OS file reads, 1953978402 OS file writes, 225036905 OS fsyncs
31.97 reads/s, 16384 avg bytes/read, 0.00 writes/s, 3.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3408348979108
Log flushed up to   3408348978265
Pages flushed up to 3407875484432
Last checkpoint at  3407875469389
0 pending log flushes, 0 pending chkp writes
70436960 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 49408901120
Dictionary memory allocated 293300472
Buffer pool size   2985216
Free buffers       0
Database pages     2985216
Old database pages 1101959
Modified db pages  1299250
Percent of dirty pages(LRU & free pages): 43.523
Max dirty pages percent: 90.000
Pending reads 2
Pending writes: LRU 0, flush list 0
Pages made young 325714839, not young 2260303365
1000.00 youngs/s, 4000.00 non-youngs/s
Pages read 1680750865, created 21295209, written 1838665120
4000.00 reads/s, 0.00 creates/s, 1000.00 writes/s
Buffer pool hit rate 875 / 1000, young-making rate 31 / 1000 not 125 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2985216, unzip_LRU len: 0
I/O sum[5924]:cur[972], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 274032212, updated 0, deleted 30276654, read 2036375358
1.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 20, updated 5857, deleted 20, read 137282
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Additional Information

Stats for the biggest table:

• 407 Million rows
• Around 150 inserts per second, 12960000 per day

Example selects, but they are run at most once per week

SELECT
    id,
    type
FROM
    table_name
WHERE
    date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
    AND date <= DATE_SUB(CURDATE(), INTERVAL 0 WEEK)
GROUP BY
    id, type
LIMIT 1000;

SELECT COUNT(*)
FROM (
    SELECT DISTINCT id
    FROM table_name
    WHERE city = '<city>'
) AS subquery;

SELECT
    AVG(price)
FROM
    table_name
WHERE
    city = <city>
    AND date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
    AND date <= DATE_SUB(CURDATE(), INTERVAL 0 WEEK);

Follow up

Partition by date may help some. But switching to it would require some downtime

• This will be the next plan of attack if the current scheme does not work out. Would you suggest partitioning on date? That was my initial thought of partition key.

Select Can be simplified and sped up with non subquery version

• When testing out the select statement with the sub query and without, I found the subquery version would run faster on that particular day. Currently they both seem to be similar

Who generates the id - Format

• The id has the format of fixed_prefix + hash + number
• Eg as-d5d85705f05df2abb811455900cfc169-22 ("as" is always the same)

How much RAM? What is the value of innodb_buffer_pool_size?

• The instance is m5.4xlarge, so 64 GB of memory. The buffer pool is set to 48 GB, which is RDSs default of 75%

Do you ever see "Deadlock" or "rollback" in innodb status

• Deadlocks are not seen, and INSERTS don't use transactions. I haven't noticed rollback either.

Follow Up 2

Explains for each query

• Sub query varient:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	null	null	null	null	1801110
2	DERIVED	table_name	ref	idx_city	idx_city	103	const	1801110	Using where; Using index

• Non-sub query varient:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table_name	ref	idx_city	idx_city	103	const	1801110	Using where; Using index

As I was running these queries, multiple times the sub query has an average execution time of 550ms and an average fetching time of 15ms, while the non-sub query version has an average execution time of 800ms and average fetching time of 15ms.

In total, across 4 tables, the space used is 780GB with 331GB data and 447GB index.

Answer 1

Insert batch size of 10 vs 200 -- Thanks for that feedback. I was unsure whether it would matter.

Partition by date may help some. But switching to it would require some downtime. More on Partition

You say that the SELECTs are rarely run? And they seem to scan lots of rows? This implies that the secondary INDEXes may be more burden than benefit.

SELECT COUNT(*)
FROM (
    SELECT DISTINCT id
    FROM table_name
    WHERE city = '<city>'
) AS subquery;

Can be simplified and sped up some to

SELECT COUNT(DISTINCT id)
    FROM table_name 
    WHERE city = '<city>'

Who generates the id? If it is somewhat chronological, then the batch inserts will go into one spot in the table. If they are UUIDs, then they will be scattered all over. I am fishing for a way to either get rid of it or rearrange the PRIMARY KEY to smooth out the INSERTs.

SELECT  id, type
    FROM  table_name
    WHERE  date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
      AND  date <= DATE_SUB(CURDATE(), INTERVAL 0 WEEK)
    GROUP BY  id, type
    LIMIT  1000;

could probably benefit from

INDEX(id, type, date)

(But, since it is rarely run, this won't matter much.)

How much RAM? What is the value of innodb_buffer_pool_size? These could impact performance. And the suggested Partitioning may improve performance because all the rows (of some Selects) would be confined to one or two Partitions. Note that each Partition has its own set of Indexes.

AND date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
AND date <= DATE_SUB(CURDATE(), INTERVAL 0 WEEK)

is 8 days' worth (if date is a DATE type). I would say this (it works for any datatype)

AND date >= CURDATE - INTERVAL 7 DAY
AND date  < CURDATE()

Do you ever see "Deadlock" or "rollback" in innodb status?

(Follow-up)

Yes, partition key = date. See the use of TO_DAYS(date) in the link.

Subquery timing -- Run each timing twice to avoid caching issues. Let's see the EXPLAINs for each.

The id format -- If the dataset is bigger than 48GB, there could be performance problems due to the randomness of id.

75% is good. But... How big is the dataset? I don't know the rest of the queries, but I would look for some indexes that don't seem to be needed.

More

If id is the PRIMARY KEY, then it is unique, hence no DISTINCT is needed. Time these:

SELECT COUNT(*)
FROM (
    SELECT id
    FROM table_name
    WHERE city = '<city>'
) AS subquery;

Can be simplified and sped up some to

SELECT COUNT(*)
    FROM table_name 
    WHERE city = '<city>'

For this, replace INDEX(city) with INDEX(city, date, price):

SELECT  AVG(price)
    FROM table_name
    WHERE city = <city>
      AND date >= CURDATE() - INTERVAL 7 DAY)
      AND date  < CURDATE();