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.