Thinking about creating a (mariadb 10.4) "scratch db" where we put summary tables , tables used for cache , etc. The number of tables could get pretty big (potentially 100K) and all of the tables will share the same 'write once upon table creation then only read from until dropped' use pattern ( No updates, no deletes ).
Do you have any advice on this. Some specific questions..
1) Is this an ok idea?
2) What types of tables would you recommend using... table size would vary from 1 to ~ 750K rows, two ints per row?
3) Are there any db specific settings that you would recommend... ie, file per table, buffers sizes, key/index settings?
4) Are there any specific linux settings that you would recommend... ie. open files?
5) When we ran some tests (50k tables) it seemed connection time was a bit slower. Are there connection options that may help with that?
6) Should we break this up across multiple databases if possible ? (don't really want to do that unless necessary)
The reason we are thinking about having so many tables is this.
We have a table right now that has around 3 billion rows. Every night we trim out about 100Million of them. The delete takes quite a while (about an hour) and usually causes replication lag (we run row based replication right now but are looking at moving to mixed) and results in other processes slowing down. There is a natural break in the data but the break breaks the dataset up into ~100K tables. If we did that then we could just issue a drop table (several tables, actually) instead of the delete from statement. We think this would speed up deletes and replication and could be more performant than the 3 billion row table.
Another alternative we have thought about is partitioning the table. In order to that we'd have to add some more data (new column to parition on) to the 3 billion existing rows and we didn't necessarily want that and also, I don't think this route would help with the row based replication issue (drop partition would end up sending deleted rows to replicas ).