Amazon RDS - Slow queries at every minute change

We recently noticed that we have a lot more records in our slow query log at every minute change (at around second 0 of every minute). This didn't seem to be related to any of our cronjobs so I decided to do a quick test on a new RDS t3.micro instance with MySQL 8.0.23.

Here are the steps to replicate:

1. Create a table:

    CREATE TABLE `_test` (
      `date` datetime NOT NULL,
      `val` int NOT NULL DEFAULT '1'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    COMMIT;
   

2. Make 100'000 inserts (important: use a single query for each row, do not insert multiple rows with one query), like:

    INSERT INTO `_test` (`date`, `val`) VALUES (NOW(), '1');
    INSERT INTO `_test` (`date`, `val`) VALUES (NOW(), '1');
    INSERT INTO `_test` (`date`, `val`) VALUES (NOW(), '1');
    ...
   

3. Run the following query:

    SELECT  SUBSTRING(`date`, -2, 2) AS `second`, AVG(`count`) AS `avg`
      FROM  
        ( SELECT  `date`, COUNT(1) AS `count`
            FROM  `_test`
            GROUP BY  `date`
        ) AS `temp`
      GROUP BY  `second`
      ORDER BY  `avg` ASC
   

My results were:

The inserts usually took about 0.02 seconds (50-56 inserts per second on average), except for second 0, where the inserts took 0.05 seconds (only 20 inserts per second on average).

And the lag seems to get bigger with bigger and more complex tables and/or queries with joins. Any idea what may be causing this lag at second 0?

EDIT:

Additional information for @Rick James.

    CREATE TABLE `rds_history` (
     `action_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     `called_by_user` varchar(50) NOT NULL,
     `action` varchar(20) NOT NULL,
     `mysql_version` varchar(50) NOT NULL,
     `master_host` varchar(255) DEFAULT NULL,
     `master_port` int DEFAULT NULL,
     `master_user` varchar(16) DEFAULT NULL,
     `master_log_file` varchar(50) DEFAULT NULL,
     `master_log_pos` mediumtext,
     `master_ssl` tinyint(1) DEFAULT NULL,
     `master_delay` int DEFAULT NULL,
     `auto_position` tinyint(1) DEFAULT NULL,
     `master_gtid` text
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    CREATE TABLE `rds_replication_status` (
     `action_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     `called_by_user` varchar(50) NOT NULL,
     `action` varchar(20) NOT NULL,
     `mysql_version` varchar(50) NOT NULL,
     `master_host` varchar(255) DEFAULT NULL,
     `master_port` int DEFAULT NULL,
     `replication_log_file` text,
     `replication_stop_point` bigint DEFAULT NULL,
     `replication_gtid` text
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

UPDATE:

Just did the same test on a brand new RDS t3.micro instance running MySQL 5.7.34 (default:mysql-5-7 option and parameter group). Similar results:

Since I wasn't able to replicate the results on my local machine and on a dedicated server (both running MySQL 5.7), this must be an Amazon RDS related problem rather than a MySQL version problem.

I asked Amazon to look into this and do the same test, no response. Their support hasn't been very helpful in general so far. Is anyone else here with an AWS account able to replicate and confirm the results above? I can not believe we are the only ones affected by this.

Since RDS is using block storage (EBS), changes to an RDS database are limited by the IOPS of the storage you choose. General purpose SSD storage comes with 3 IOPS per GB, so you have a limit of 60 IOPS with 20 GB, for example. Now if you use more then the provisioned 60 IOPS, your experiencing a delay at the end of every minute (or the beginning of the new minute).

Solution: Either increase storage (attention, general purpose SSD [gp2] is limited to a throughput of max 250 MiB/s and, thus, capable of max 1'000 IOPS, even if you increase storage to more than 333 GB) or choose IOPS-SSD storage, which supports higher numbers of IOPS.