Inserting into large MySQL table without auto-increment primary key is very slow

Question

I've recently noticed a significant increase in the difference in time required to complete a simple INSERT statement. While these statements take about 11 milliseconds on average, sometimes they can take 10-30 seconds, and I've even noticed them taking more than 5 minutes to execute.

MySQL version is 8.0.24, running on Windows Server 2016. As far as I know, the server's resources have never been overloaded. The server has ample CPU overhead available and 32GB of RAM allocated to it.

This is the table I'm using:

CREATE TABLE `saved_segment` (
  `recording_id` bigint unsigned NOT NULL,
  `index` bigint unsigned NOT NULL,
  `start_filetime` bigint unsigned NOT NULL,
  `end_filetime` bigint unsigned NOT NULL,
  `offset_and_size` bigint unsigned NOT NULL DEFAULT '18446744073709551615',
  `storage_id` tinyint unsigned NOT NULL,
  PRIMARY KEY (`recording_id`,`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

This table has no other indexes or foreign keys, and is not used as a reference to foreign keys in any other table. The entire table size is about 20GB and the number of rows is about 281M, which I feel is not too big.

The table is used almost exclusively in read-only mode, with up to 1000 reads per second. All of these reads occur in simple SELECT queries rather than complex transactions, and they effectively utilize the primary key index. There are very few, if any, concurrent writes to this table. This was done on purpose to try and figure out if it would help with slow insertion, but it didn't. Until then, there are always up to 10 concurrent inserts in progress. UPDATE or DELETE statements will never be executed on this table.

The queries I have problems with are all constructed like this. They never appear in the transaction.While inserts based on the clustered primary key are definitely not append-only, the query will almost always insert between 1 and 20 adjacent rows into the table:

INSERT IGNORE INTO saved_segment
(recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) VALUES
(19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10),
(19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10),
(19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10),
(19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10),
(19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10),
(19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10),
(19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10),
(19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10),
(19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10),
(19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)

This is the output of the EXPLAIN statement for the above query:

id	Select type	Table	Partition	Type	Possible keys	key	key_len	Reference	OK	Filtered	Extra
1	Insert	Saved segments	Empty	All	Empty	Empty	Empty	Empty	Empty	Empty	Empty

These issues are relatively new and were not noticeable when the table was about twice as small.

I tried reducing the number of concurrent inserts in the table from about 10 to 1. I also removed the foreign keys (recording_id) on some columns to speed up the inserts even more. Analyzing tables and schema analysis did not yield any actionable information.

One solution I thought of was to remove the clustered primary key and add an auto-increment primary key and a regular index on the (recording_id, index) column. In my opinion this would help make the insert "append-only".I'm open to any and all suggestions, thanks in advance!

Edit: I'll address some of the points and questions raised in the comments and answers:

• autocommit is set to ON
The value of
• innodb_buffer_pool_size is 21474836480, and the value of innodb_buffer_pool_chunk_size is 134217728< /code>
• One comment raised concerns about contention between the read lock used for reads and the exclusive lock used for writes. The table is used somewhat like a cache, I don't need the reads to always reflect the latest state of the table if it means an increase in performance. However, the table should remain durable even in the event of server crashes and hardware failures. Could this be achieved with a more relaxed transaction isolation level?
• The architecture can definitely be optimized; recording_id can be a 4-byte integer, end_filetime can be changed to a passed value, and start_filetime ; may also be smaller. I'm afraid these changes will just postpone the problem for a while until the size of the table increases to compensate for the space saved.
• Inserts into the table are always continuous A SELECT performed on the table looks like this:

SELECT TRUE
FROM saved_segment
WHERE recording_id = ? AND `index` = ?

SELECT index, start_filetime, end_filetime, offset_and_size, storage_id
FROM saved_segment
WHERE recording_id = ? AND
        start_filetime >= ? AND
        start_filetime <= ?
ORDER BY `index` ASC

The second type of query could certainly be improved with an index, but I'm concerned that this would further degrade INSERT performance.

Another thing I forgot to mention is that a table very similar to this exists. It queries and inserts exactly the same, but may cause further IO starvation.

Edit 2: SHOW TABLE STATUS table saved_segment, and a very similar table saved_screenshot (which is in bigint unsigned not There is an additional index on the null column).

Name	Engine	Version	Row format	OK	Average line length	Data length	Maximum data length	Index_length	No data	Auto-increment	Creation time	Updated	Check time	Organization	Checksum	Creation Options	Comments
Saved screenshot	InnoDB	10	News	483430208	61	29780606976	0	21380464640	6291456	Empty	“2021-10-21 01:03:21”	“2022-11-07 16:51:45”	Empty	utf8mb4_0900_ai_ci	Empty
Saved segments	InnoDB	10	News	281861164	73	20802699264	0	0	4194304	Empty	“2022-11-02 09:03:05”	“2022-11-07 16:51:22”	Empty	utf8mb4_0900_ai_ci	Empty

Answer 1

I'll go out on a limb with this answer.

Assumption

• innodb_buffer_pool_size has a value slightly less than 20MB, and
• 1K selections per second arrive at random parts of the table, then

Systems have become I/O bound lately, as the "next" block required for the next Select is more and more often not cached in the buffer_pool.

The simple solution is to get more RAM and increase the setting of this tunable. But the table will only grow to the next limit you purchase.

Instead, here are some partial solutions.

• If the numbers are not too large, the first two columns may be INT UNSIGNED (4 bytes instead of 8), or even MEDIUMINT UNSIGNED (3 bytes) . Note that ALTER TABLE will lock the table for a long time.
• These start and end times look like timestamps with fractional seconds and are always ".000". DATETIME and TIMESTAMP take 5 bytes (instead of 8 bytes).
• Your example shows an elapsed time of 0. If (end-start) is typically very small, storing the elapsed time instead of the end time will further shrink the data. (But using an end time can make things confusing).
• The sample data you provided looks "continuous". This is about as efficient as auto-increment. Is this the norm? If not, the INSERT may be part of the I/O thrashing.
• You suggest adding artificial intelligence as well as secondary indexes, which doubles the work of inserting; so I don't recommend it.

More

Yes, that is the case.

Putting this as INDEX, or better yet, as the beginning of PRIMARY KEY will give you the best help with both of your queries:

(recording_id, index)

reply:

SELECT  TRUE
FROM    saved_segment
WHERE   recording_id = ? AND `index` = ?

If it is used to control some other SQL, consider adding it to the other SQL:

... EXISTS ( SELECT 1
        FROM    saved_segment
        WHERE   recording_id = ? AND `index` = ? ) ...

This query (in either form) requires content you already have

PRIMARY KEY(recording_id, index)

Your other inquiry needs

INDEX(recording_id, start_filetime)

So, add index, or ...

Better...This combination is better for bothSELECT:

PRIMARY KEY(recording_id, start_filetime, index).
INDEX(recording_id, index)

With this combination,

• Single row existence check will be performed "using index" because it is "covered".
• Another query will find all related rows clustered together on the PK.
• (PK has these 3 columns because it needs to be unique. Having them in this order helps your second query. And it's a PK, not just an INDEX, so it doesn't need to be in the index's Bounce between BTree and data of BTree.)
• "Cluster" can improve performance by reducing the number of disk blocks required for such queries. This reduces "thrashing" in the buffer_pool, thereby reducing the need to increase RAM.
• My index suggestions are mostly orthogonal to my data type suggestions.