We have a time-series application that also writes all the records to kind of a 'head' table in MySQL. This is somewhat of a legacy, but very interwoven and hard to get rid of. The problem is, that it's forming a bottle-neck, mostly IO. I'm hoping there are (clever) tricks to make it faster. For instance, it can remain dirty in the buffer pool for a long time, that's not too important.

It's currently running on a 96 CPU Amazon RDS instance (db.m5.24xlarge, 384 GB RAM), Multi-AZ. It still has general purpose IO with 6000 IO/s for now, but that will be changed to provisioned IO. I'm not sure if it will have much effect though. We don't reach the 6000 yet.

The table is this:

CREATE TABLE `headData` (
  `idInstallation` int unsigned NOT NULL,
  `idDataAttribute` smallint unsigned NOT NULL,
  `instance` smallint unsigned NOT NULL,
  `secondsToNextLog` int DEFAULT NULL,
  `valueFloat` float DEFAULT NULL,
  `valueString` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `valueEnum` smallint DEFAULT NULL,
  `timestamp` int unsigned DEFAULT NULL,
  `alarmStart` int unsigned DEFAULT NULL,
  `alarmChangeTimer` int unsigned DEFAULT NULL,
  `alarmCleared` int unsigned DEFAULT NULL
  PRIMARY KEY (`idInstallation`,`idDataAttribute`,`instance`),
  KEY `idDataAttribute` (`idDataAttribute`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci

INSERT ON DUPLICATE KEY UPDATE statements are multi-value: with one idInstallation. It's several hundred per query. 40 of those statements are batched inside a transaction. One statement is:

  (idInstallation, idDataAttribute, instance, timestamp, 
  secondsToNextLog, valueFloat, valueString, valueEnum)
  (1, 2, 0, unix_timestamp(), 60, 4, null, null),
  (1, 3, 0, unix_timestamp(), 60, 66, null, null),
  (1, 4, 0, unix_timestamp(), 60, 3, null, null),
  (1, 5, 0, unix_timestamp(), 60, 1, null, null),
  (1, 100, 0, unix_timestamp(), 60, 4, null, null),
  timestamp = VALUES(timestamp), 
  secondsToNextLog = VALUES(secondsToNextLog), 
  valueFloat = VALUES(valueFloat), 
  valueString = VALUES(valueString), 
  valueEnum = VALUES(valueEnum)

In other words, it mostly finds existing rows and updates the values.

And currently about 2000/s of these are done.

It's currently updating about 500k rows per second, but it's normally lower. It's reporting 40k queries/s, but that number doesn't make sense to me (long story).

innodb_flush_log_at_trx_commit is 0. sync_binlog is 0.

The table is currently 'only' 3932.45MB big.

This is the load from an incident, where there is a lot of back-log to process:

Performance insight

You can see the light blue, which is IO.

You can also see the non-CPU load in this peak:

more metrics

Because new rows are infrequently added and only existing values overwritten, theoretically, it could make do with very little IO. If it were to sync every few minutes, I'd be fine with that.

I tried attaining that a little bit with innodb_log_file_size of 8 GB (I could increase this more). According to the log sequence numbers, there is currently about 2 GB per minute written. If I were to have an hours worth of data as recommended here, it would be 120 GB...

One specific question on a side note: can a high enough innodb_log_file_size make a whole table fit in memory without IO?

Any other ways to reduce the impact of IO?

Edit: also good to know there's a lot of parallelism. At the time of the incident, all 128 queue runners were active. They are set up so that they never update each other's rows. One idInstallation is ever only in one queue.

Edit: more graphs below, of the last 24 hours:

enter image description here

enter image description here

History length:

enter image description here


1 Answer 1


(I can't seem to solve your problem as it stands. Let me describe an alternative approach to the schema.)

You are overwriting data in some way -- like a circular table. Perhaps you are keeping 12 months of data and the primary key starts with the "month"? I think that INSERTs are faster than UPDATEs. So let me suggest you add to the end of the table via INSERTs and remove 13-month-old data via DROP PARTITION.

Inserts and Updates must keep the old state in case of a crash. (ACID) For Updates, that requires keeping the old copy of the modified rows. This seems more costly than for Inserts, wherein the "old copy" is empty.

Using PARTITION BY RANGE(...), the table is split into monthly (or weekly or daily or whatever) chunks. And DROP PARTITION is essentially instantaneous.

More on implementing a time series via Partitioning

  • I think I misled you some. The circular part I mentioned was about MySQL internals. This head table only every contains one value for each measurement, and all values need to be retained. By far the most frequent operation is updating an existing row. Given that MySQL acts like 'a an in-memory, durable cache' (quote elsewhere), should mean I can update pool pages all the time without (much) IO, I'd think. (I did use partitioning when we still did store historical data in this DB BTW).
    – Halfgaar
    May 16, 2023 at 22:38
  • I'm still confused by "circular...internals" and "head table". Maybe this is a rephrasing? You have a Current table and a History table. The Question is only about rapidly updating the Current table with lots of sensor data. And 'all' of the current data will be updated every N minutes.
    – Rick James
    May 17, 2023 at 1:09
  • And a value is either a float or string or number. Each datapoint is stored separately.
    – Rick James
    May 17, 2023 at 1:10
  • Thre may be a much faster way to do the update, but first I need to see what queries are used against Current ("headData") and how often they are run.
    – Rick James
    May 17, 2023 at 1:11
  • I added an example query. Forget about the circular and internals. This has nothing to do with SQL. I was just reasoning about how MySQL's storage engine works.
    – Halfgaar
    May 17, 2023 at 1:57

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.