0

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.

4
  • (1) Let's see the SELECTs. (2) Let's decrease the number of INDEXes. (3) see if batches of 100 work better than 200. How many rows in the table? How many are added per day? Why is id so big -- VARCHAR(191)?
    – Rick James
    Jun 26, 2023 at 18:58
  • @RickJames I have added additional information. Previously batches were 10 and the same problem occured. Following the advice from aws: repost.aws/knowledge-center/aurora-mysql-synch-wait-events. Batch size was increased to 200 in order to reduce concurrency and take more advantage of the insert statements. I can try 100.
    – ryanrasa
    Jun 26, 2023 at 20:20
  • It does not make sense to SELECT id, type but only GROUP BY id (cf: only_full_group_by`)
    – Rick James
    Jun 26, 2023 at 21:09
  • You are correct. I have fixed the GROUP BY
    – ryanrasa
    Jun 26, 2023 at 21:38

1 Answer 1

0

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();
8
  • Thank you for your response. I will answer the questions as they come up.
    – ryanrasa
    Jun 27, 2023 at 16:09
  • Added in "Follow Up"
    – ryanrasa
    Jun 27, 2023 at 18:37
  • Followed up to your follow-up.
    – Rick James
    Jun 27, 2023 at 23:06
  • Thanks I have added more details. From what I am seeing, the current plan will be to try and prune and redesign the indicies and think about partitioning by date
    – ryanrasa
    Jun 28, 2023 at 16:56
  • @ryanrasa - I followed up with More.
    – Rick James
    Jun 28, 2023 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.