partitioning mysql table with 3b records per year

Question

What is good approach to handle 3b rec table where concurrent read/write is very frequent within few days?

Linux server, running MySQL v8.0.15.

I have this table that will log device data history. The table need to retain its data for one year, possibly two years. The growth rate is very high: 8,175,000 rec/day (1mo=245m rec, 1y=2.98b rec). In the case of device number growing, the table is expected to be able to handle it. The table read is frequent within last few days, more than a week then this frequency drop significantly.

There are multi concurrent connection to read and write on this table, and the target to r/w is quite close to each other, therefore deadlock / table lock happens but has been taken care of (retry, small transaction size).

I am using daily partitioning now, since reading is hardly spanning >1 partition. However there will be too many partition to retain 1 year data. Create or drop partition is on schedule with cron.

CREATE TABLE `table1` (
  `group_id` tinyint(4) NOT NULL,
  `DeviceId` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `DataTime` datetime NOT NULL,
  `first_log` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `first_res` tinyint(1) NOT NULL DEFAULT '0',
  `last_log` datetime DEFAULT NULL,
  `last_res` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`group_id`,`DeviceId`,`DataTime`),
  KEY `group_id` (`group_id`,`DataTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (to_days(`DataTime`))
(
 PARTITION p_20191124 VALUES LESS THAN (737753) ENGINE = InnoDB,
 PARTITION p_20191125 VALUES LESS THAN (737754) ENGINE = InnoDB,
 PARTITION p_20191126 VALUES LESS THAN (737755) ENGINE = InnoDB,
 PARTITION p_20191127 VALUES LESS THAN (737756) ENGINE = InnoDB,
 PARTITION p_future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Insert are performed in size ~1500/batch:

INSERT INTO table1(group_id, DeviceId, DataTime, first_result) 
VALUES(%s, %s, FROM_UNIXTIME(%s), %s) 
ON DUPLICATE KEY UPDATE last_log=NOW(), last_res=values(first_result);

Select are mostly to get count by DataTime or DeviceId, targeting specific partition.

SELECT DataTime, COUNT(*) ct FROM table1 partition(p_20191126) 
WHERE group_id=1 GROUP BY DataTime HAVING ct<50;

SELECT DeviceId, COUNT(*) ct FROM table1 partition(p_20191126) 
WHERE group_id=1 GROUP BY DeviceId HAVING ct<50;

So the question:

1. Accord to RickJames blog, it is not a good idea to have >50 partitions in a table, but if partition is put monthly, there are 245m rec in one partition. What is the best partition range in use here? Does RJ's blog still taken place with current mysql version?
2. Is it a good idea to leave the table not partitioned? (the index is running well atm)

note: I have read this stack question, having multiple table is a pain, therefore if it is not necessary i wish not to break the table. Also, sharding is currently not possible.

Answer 1

First of all, INSERTing 100 records/second is a potential bottleneck. I hope you are using SSDs. Let me see SHOW CREATE TABLE. Explain how the data is arriving (in bulk, one at a time, from multiple sources, etc) because we need to discuss batching the input rows, even if you have SSDs.

Retention for 1 or 2 years? Yes, PARTITIONing will help, but only with the deleting via DROP PARTITION. Use monthly partitions and use PARTITION BY RANGE(TO_DAYS(DataTime)). (See my blog which you have already found.)

What is the average length of DeviceID? Normally I would not even mention normalizing a VARCHAR(10), but with billions of rows, it is probably worth it.

The PRIMARY KEY you have implies that a device will not provide two values in less than one second?

What do "first" and "last" mean in the column names?

In older versions of MySQL, the number of partitions had impact on performance, hence the recommendation of 50. 8.0's Data Dictionary may have a favorable impact on that, but I have not experimented yet to see if the 50 should be raised.

The size of a partition has very little impact on anything.

In order to judge the indexes, let's see the queries.

Sharding is not possible? Do too many queries need to fetch multiple devices at the same time?

Do you have Summary tables? That is a major way for Data Warehousing to avoid performance problems. (See my blogs on that.) And, if you do some sort of "staging" of the input, the summary tables can be augmented before touching the Fact table. At that point, the Fact table is only an archive; no regular SELECTs need to touch it? (Again, let's see the main queries.)

One table per day (or whatever unit) is a big no-no.

Ingestion via IODKU

For the batch insert via IODKU, consider this:

1. collect the 1500 rows in a temp table, preferably with a single, 1500-row, INSERT.
2. massage that data if needed

3. do one IODKU..SELECT:

   INSERT INTO table1(group_id, DeviceId, DataTime, first_result) 
       ON DUPLICATE KEY UPDATE
           last_log=NOW(), last_res=values(first_result)
       SELECT group_id, DeviceId, DataTime, first_result
           FROM tmp_table;
   

If necessary, the SELECT can do some de-dupping, etc.

This approach is likely to be significantly faster than 1500 separate IODKUs.

DeviceID

If the DeviceID is alway 10 characters and limited to English letters and digits, then make it

CHAR(10) CHARACTER SET ascii

Then pick between COLLATION ascii_general_ci and COLLATION ascii_bin, depending on whether you allow case folding or not.

Answer 2

Just for your reference: I have a large table right now over 30B rows, grows 11M rows daily. The table is innodb table and is not partitioned. Data over 7 years is archived to file and purged from the table.

So if your performance is acceptable, partition is not necessary.

From management perspective, it is easier to manage the table with partitions, you might partition the data by week. It will 52 - 104 partitions if you keep last or 2 years data online