Is there a difference between Range Type Partitioning & Temporal Data Tables (System-Versioned-Tables) in Maria DB 10.11.5?

Question

I have a migration project (from Oracle) to Maria DB 10.11.5 (my choice).

The needs of the database server are not ordinary. The database is to write to disk the results of software bots which are testing connectivity in voip, web, 3G mobile, etc.

So the data being entered is Ip addresses, success and error codes, timestamps, names of the bots, descriptions and comments (never exceeding 250 characters), etc. Data types are to be VARCHAR, DATETIME with nanosecond precision, DOUBLE, lots of INT & BIGINT, the occasional DECIMAL, etc.

The data needs to be kept only for a period of three months, and then dropped or deleted. There is no other archival demand.

The data is voluminous - for a principal table, there are over 1 billion rows in a three-month time period. There are only 9 tables which have a 3-month time-period number of rows exceeding 100,000 and they all have at least 80 million rows, most 300 million or so.

So, this is a heavily write-intensive operation. The clients needs are to agglomerate the data over various time periods ranging from 3 hour to 3 months and calculate the percentage of connectivity failures, by type, by type of software bot, and email the results if the percentage of connectivity failures is above a certain threshold.

Partitioning is an obvious solution to satisfy these needs, so I would like to know if there is any difference (and what that difference would be) between creating a table in the normal way with partitioning by range (datetime) with a script to drop partitions with datetimes greater than 4 months ago, AND creating a temporal data table with system-versioned tables - which appears to simplify the partitioning and pruning requirements, understanding that dropping a partition is much much less resource intensive than any delete, etc?

Next, for performance considerations, would a simple master-slave mariadb in-order or out-of-order (faster, I know) parallel replication setup, with the mail functions to be written in procedural code in the application layer (I don't know of any native Maria mail function) on the slave work fine for this type and frequency of writes? And would it help to add a delayed slave replication (or would that harm performance greatly?), or would a REDIS cache layer be necessary, too?

And lastly, the client likes to have three 1-month partitions in the DB at all times - so the last 100 days of data, more or less, but I have already told them that that size of partition cannot be cached, and that is one of their performance problems. So, I am going to try to convince them to have 100 daily partitions instead, or possibly even 800 3-hour partitions, so the current partition can be cached; I believe that 2400 1-hour partitions would be too many. Any comments / advise or wisdom on this choice? Should this choice be aligned with the shortest time period for which data is to be agglomerated and analysed (currently 3 hours, but that might conceivable change to hourly) ?

Thanks for any wise answers. The client wants to do this on a single server without replication. And that would probably work fine, too, with hourly backups... I'd like to give them something world-class that can write 100 + rows of 10 columns per second and 400 + rows per second of big table autoincrement + 1 BIGINT + datetime(6) as described above per second easily as well as do all the housekeeping and the mailing functions, backups, etc.

Info: Engine is InnoDB, sql_mode=ORACLE, binary logging enabled, innodb_file_per_table=ON and we should have 64 GB of RAM to put in InnoDB Buffer Pool, and I am trying to get the client to provide at least 128 GB or more of fast ECC RAM. Current is 32 GB only - which is another of their Oracle performance problems.

Answer 1

I suspect that the temporal feature in MariaDB is aimed at tracking UPDATEs more than for administrating huge datasets.

Here's how I would do your task using PARTITIONing:

• 90 (daily) partitions using BY RANGE(TO_DAYS(...))
• DROP PARTITION daily. (Actually, try to do it more often, just incase the drop fails some time.) DROP is almost instantaneous DELETEing 10M rows takes a nasty amount of time.
• REORGANIZE PARTITION future TO ...[tomorrow], future LESS THAN MAX... -- And do this shortly before midnight.
• If you need do not need summarized data less than a day old, then run summary queries daily to gather 'yesterday's data.
• If you need summary data faster, then consider running a task hourly.
• Or use INSERT INTO summary_table(s)... ON DUPLICATE KEY UPDATE ... (upsert) at the same time as you do the data collection.
• See https://mysql.rjweb.org/doc.php/staging_table for high rate of ingestion.
• More on partitioning: https://mysql.rjweb.org/doc.php/partitionmaint
• Minimize the number of indexes in the billion-row table; use whatever indexes are needed in the summary tables.
• I would build the summary tables around 1-hour granularity. That is the "datetime" in the summary tables would be rounded down to the hour. (This could be squeezed into a 3-byte MEDIUMINT, but it is a hassle.) I assume you know how to sum the sums and sum the counts and do avg = sum(sums)/sum(counts)
• More on Summary tables: https://mysql.rjweb.org/doc.php/summarytables
• While it is possible to rollup, say, hourly summaries into daily ones, it is rarely worth the effort.
• Do try to shrink the datatypes where practical.
• If you have long TEXTs, consider [de]compressing them in the client and switching to BLOB. This will shrink such by about 3x.
• 'nanosecond precision' -- not directly available in MariaDB; however, a suitable number or string could be used. BIGINT UNSIGNED with ns from some epoch start seems best. DATETUNE(6) takes 8 bytes, but has only microsecond resolution. TIMESTAMP(6) avoids DST issues, but could shift the meaning of "day".
• There will be a lot of activity on the 'latest' partition; hence daily instead of monthly partitions minimizes the size of that partition.
• Redis -- I am not a fan of putting a cache in front of a cache. (The buffer_pool)
• Primary-Replica for offloading queries -- Yes. Another thing -- If, for example you need temp tables for ingesting and summarizing, it is possible to not replicate them while replicating the results. Other than than, keep in mind that all the writes to the Primary are repeated in the Replica. ROW format helps.
• "delayed slave replication" -- I don't see any advantage. There will be a small delay due to the way replication works.
• "size of partition cannot be cached, and that is one of their performance problems" -- I don't understand the point. Please explain with numbers. (Or maybe daily partitions avoids the issue?)
• The clients should nearly always reach for the summary tables, not the Fact table(s). The summary tables will be much smaller than the Fact table; so they will usually be cached, and who cares about the Fact's caching.
• Let's discuss the indexes of the various tables. The PRIMARY KEY of the billion-row table may the most critical to discuss. It is necessarily clustered, unique, and contains the partition key. The order of the columns versus the actions to be performed is what makes a difference.
• "2400 1-hour partitions" -- too many. The overhead of accessing multiple partitions hurts.
• Hourly backups -- Use either LVM or backup the incoming data. Do not try for a logical dump of 1B rows!
• I use this Rule of Thumb: MariaDB can handle simple queries at a rate of 1000/sec. Faster than that may require careful techniques -- many of which I have alluded to above and/or provide in the links.
• 50GB for buffer_pool if using 64GB of RAM. 25GB/32GB might do just fine -- remember than I am recommending smaller partitions and client usage focus on summary tables.

(I did something similar a decade ago, using MySQL. 400GB of data, 90-day retention, hourly injection of lots of data, summarized the hour's data and added to 7 summary tables in about 8 minutes. I had 90 daily partitions plus 24 hourly partitions. The hourly ones were because I got new data only once an hour; this made for good caching in only 32GB RAM. The nightly combining of the 24 partitions into a new daily partition was slow, but acceptable.)

Re Comments

SQL Stored Procedure vs client code -- SQL is not good at low-level string manipulation, but it is good at orchestrating SQL. That is, use each approach for what it is good at; don't try to move everything into one side or the other. The optimizations you mentioned are probably not worth worrying about. If the client and server are "close" to each other (not on the other side of the country), CALL is more of a convenience than a performance issue.

"Business logic" could be in the client or stored procs. Business logic thinks in terms of meters, dollars, volumes; SQL thinks only in lower-level terms -- INT, FLOAT, DECIMAL. Code layering involves deciding the best place to switch from one concept to the other.

If you use something like my "staging table", that is a good time do do the normalizing. And I show how to avoid "burning" auto-inc ids. Note avoiding "burning" helps avoid unexpected overflow in SMALLINT, etc.

10M rows/day = 120/second which is somewhat leisurely. Will there be "bursts". 1000/sec is possible with MariaDB, especially if you use some of the techniques I mentioned.

I got lost in your explanation of the PK and the Partition key. Please show those parts of the CREATE TABLE. (This helps avoid ambiguity.) If you are at a billion rows, will INT (2 billion max), even INT UNSIGNED (4 billion), be safe?

"Sensors" -- https://mysql.rjweb.org/doc.php/mysql_sensor

TINYTEXT -- No. It has subtle disadvantages during querying. VARCHAR(...) is better, whether it is 191 or 255 or even 400.

DATETIME takes 5 bytes. DATETIME(3) takes 7; (6) takes 8. Daylight savings time can lead to duplicated times once a year. Consider TIMESTAMP` instead.

DATETIME without fractional seconds cannot be unique at a rate of 150-300/sec. Think of it as a rounded-off time. Without checking, I would not trust (3) or even (6) to be unique.

"300 rows with the exact same TIME?" -- Probably OK. Need more specifics.

2038 is still 15 years off. Imagine running your app on a computer from 2008. Hopefully, you would have upgraded the hardware, software, etc. multiple times since then.

FLOAT is 4 bytes. It sacrifices precision to get range. If it stores integral values, its range is -16M..+16M (24 bit's worth). So, Float is not adequate for milliseconds; it will overflow in less than a day. DOUBLE is 53 bits of precision but 8 bytes, so it won't help.

DATE is 3 bytes and has 9999 limit, not 2038. "Hour" can be stored in MEDIUMINT (3 bytes), but involves conversions in various places in the app.