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.

  • Too many questions in one Post to reasonably answer. You should focus on one topic and related questions at a time per Post. Generally speaking, Temporal Tables is a completely different feature than Partitioning. Is the 3 months of data rolling?...if so, it rolls by what time unit, day?...month? Or if it's not rolling, then are you planning to collect 3 months of data, analyze it, then drop all 3 months simultaneously and start collecting the next 3 months at a time?
    – J.D.
    Jul 28 at 12:19
  • Understood. The data is being added continually, the most recent data (3 hours worth) is aggragated every 3 hours, every day (the most recent 24 hours of data) and every month (on the first of each month for the last full month), and one months' worth of data is purged once a month (the data purged is in the partition that is dropped, all of it being from 4 months ago. Partitioning is used to keep the data from becoming too big, and only the most recent 3 months of data are needed at all times.
    – Ed Nett
    Jul 28 at 17:52
  • @J.D. - Yeah, long question. But I enjoyed giving an equally long answer.
    – Rick James
    Jul 29 at 18:51

1 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.

  • Thanks for your response and your advice. It looks good. I meant to say microseconds not nanoseconds, so sorry for my error. The client doesn't want to have master -replica because it's two servers instead of one, so I probably can't do that.
    – Ed Nett
    Aug 4 at 9:08
  • I would have thought that Temporal Data Types with System Versioning would be ideal for this type of project, but I understand that's it's new and PARTITIONing by RANGE is a tried and true solution, so I like that. I would like to see Temporal Data Types with System Versioning also automate the number of partitions to be kept, so that the number can simply be specified in the CREATE TABLE statement! Like they do for the AUTO CREATEing of new partitions. But that would just be a periodic DROP PARTITION and REORGANIZE is more what is often needed ...
    – Ed Nett
    Aug 4 at 10:30
  • I have a couple of questions: 1) The client had an employee specify that as much SQL, PL/SQL (existing Oracle DB), as many functions, stored procedures, etc. as possible be moved out of the database and into the application layer (requiring scripts in C++, python, etc.). However I keep reading that newer versions of Maria DB are optimized for running stored procedures, jobs, packages, etc. What's your thinking on this? Will moving this procedural code into the business or application layer really help improve Maria DB's maximum throughtbut and performance enormously?
    – Ed Nett
    Aug 4 at 10:31
  • 2) This database will be writing to disk (Buffer Pooled, of course) 99 % of the time. When you say Maria DB can handle 1000 queries per second, does that include writes (almost all INT, and DATETIME, some VARCHAR (from 50 to 4000) but the VARCHAR are used less than once per second, normally, with the exception of the billion row table. There are 5 other tables with 100 to 300 million rows for 3 months.
    – Ed Nett
    Aug 4 at 10:31
  • The billion row table is adding about 10 million rows per day (and I'd like to give the client a solution which allows them to double that with Maria DB) and it is writing to disk a VARCHAR(400)which cannot be normalized (IP addresses, website URL's, hostnames, etc.), as well as three INTs. The ID column is not autoincrement - it is being provided. It has two Primary Keys the ID and the DATE (SYSDATE) (currently writing day-month-year), the ID is a UNIQUE INDEX, and the two other INT's are PLAIN INDEXes currently. And the table is currently PARTITIONed 3 monthly partitions with a fourth
    – Ed Nett
    Aug 4 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.