MySQL degrading performance over many (~10-100K) short select queries

Question

I'm currently building an app on Node with MySQL database where on the initial run I have to calculate ~250M items. Each calculation takes 3 SELECT statements. I'm testing with a couple of thousands for now to understand what affects the performance. I'm seeing weird behaviour. Initially calculations run really quick, 1k gets processed in under a second. Then, after some (~20k) thousands are run it slows down to about 4 seconds per 1k.

Now, if I leave the database alone for a while (let's say 1 min), it regains speed for the first ~10k calculations.

I've implemented it such that there is a concurrency limit and no more than 20 are calculating at the same time. Increasing the limit seems to decrease performance. I am yet to fully test how this parameter impacts the calculation speed. Seems that there is some kind of buffer that fills up even though all the previous SELECTs are supposed to be finished.

Each of these calculate 2000 rows:

Just calculations: 2.557s
Just calculations: 1.566s
Just calculations: 1.617s
Just calculations: 1.543s
Just calculations: 1.475s
Just calculations: 1.635s
Just calculations: 1.613s
Just calculations: 3.324s
Just calculations: 4.103s
Just calculations: 8.325s
Just calculations: 7.583s
Just calculations: 7.482s
Just calculations: 7.216s
Just calculations: 7.145s
Just calculations: 7.419s
Just calculations: 7.244s
Just calculations: 7.066s
Just calculations: 7.635s
Just calculations: 7.169s
Just calculations: 7.194s

Performance degradation can be clearly seen. The degradation also affects INSERT queries, so I'm pretty sure that it's specifically the db layer.

Any tips or info on increasing performance would be appreciated.

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| item  | CREATE TABLE `item` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(255) NOT NULL,
  `base_unit_of_measure` varchar(255) DEFAULT NULL,
  `unit_price` double NOT NULL,
  `blocked` tinyint(1) NOT NULL,
  `item_disc__group` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_sku_unique` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=1375125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| contact | CREATE TABLE `contact` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` varchar(255) NOT NULL,
  `client_id` varchar(255) DEFAULT NULL,
  `customer_disc__group` int DEFAULT NULL,
  `customer_price_group` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contact_contact_id_unique` (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

   +-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales_price | CREATE TABLE `sales_price` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `sku` varchar(255) NOT NULL,
  `sales_type` int NOT NULL,
  `sales_code` varchar(255) DEFAULT NULL,
  `unit_of_measure_code` varchar(255) DEFAULT NULL,
  `minimum_quantity` int NOT NULL,
  `unit_price` double NOT NULL,
  `allow_line_disc` tinyint(1) NOT NULL,
  `starting_date` date DEFAULT NULL,
  `ending_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sales_price_unique` (`sku`,`sales_type`,`sales_code`,`starting_date`,`unit_of_measure_code`,`minimum_quantity`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales_line_discount | CREATE TABLE `sales_line_discount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` int NOT NULL,
  `code` varchar(255) NOT NULL,
  `sales_type` int NOT NULL,
  `sales_code` varchar(255) DEFAULT NULL,
  `unit_of_measure_code` varchar(50) DEFAULT NULL,
  `minimum_quantity` int NOT NULL,
  `line_discount` double NOT NULL,
  `starting_date` date DEFAULT NULL,
  `ending_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `line_discount_unique` (`type`,`code`,`sales_type`,`sales_code`,`starting_date`,`unit_of_measure_code`,`minimum_quantity`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The queries that are run against these tables are as follows:

bindings: [ 'TESTCONTACT1', 1 ],

SELECT `contact`.*
FROM   `contact`
WHERE  `contact`.`contact_id` = ?
LIMIT  ?  

bindings: [
        0,            'DUMMYSKU24999',
        1,            'DUMMYGROUP',
        '2021-08-30', '2021-08-30',
        0,            'TESTCONTACT1',
        1,            2,
        2,            3,
        'TESTCAMP1',  'TESTCAMP2'
]

SELECT `sales_line_discount`.*
FROM   `sales_line_discount`
WHERE  ( ( `type` = ?
           AND `code` = ?
            OR ( `type` = ?
                 AND `code` = ? ) )
         AND `starting_date` <= ?
         AND `ending_date` >= ? )
       AND ( `sales_type` = ?
             AND `sales_code` = ?
              OR ( `sales_type` = ?
                   AND `sales_code` = ? )
              OR ( `sales_type` = ? )
              OR ( `sales_type` = ?
                   AND `sales_code` IN ( ?, ? ) ) )  

 bindings: [
        'DUMMYSKU24999', '2021-08-30',
        '2021-08-30',    0,
        'TESTCONTACT1',  1,
        2,               2,
        3,               'TESTCAMP1',
        'TESTCAMP2'
      ],

SELECT `sales_price`.*
FROM   `sales_price`
WHERE  `sku` = ?
       AND `starting_date` <= ?
       AND `ending_date` >= ?
       AND ( `sales_type` = ?
             AND `sales_code` = ?
              OR ( `sales_type` = ?
                   AND `sales_code` = ? )
              OR ( `sales_type` = ? )
              OR ( `sales_type` = ?
                   AND `sales_code` IN ( ?, ? ) ) )  

Answer 1

In the end the reason was background processes that were periodically taking up portions of the available resources to the app.

As the computer wasn't lagging at all I had assumed that it just uses a single core and work on other cores doesn't impact it but htop proved differently. Killing all unnecessary processes solved the performance issue.