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

  • 1
    I'm confused -- One thing talks about 50K tables; another talks about a mere 30 partitions. – Rick James Nov 11 '19 at 2:46
  • All tables in a database (including all partitions) is at least one file in a single directory. This, alone, leads to OS-related slowdowns when you have 50K tables. – Rick James Nov 11 '19 at 2:47

Lots of tables is almost always a bad idea.

I have used "summary tables" in several applications, but never more than 10.

Sounds like an extra column(s) in your summary tables would let you consolidate hundreds of separate tables into a single table.

If you want to discuss further, please provide more specifics -- a few of the schemas, the query to do the summarizing, the condition(s) for deleting your summary tables, etc.

Re: partitioning:

Sounds like you considered daily partitions and a nightly DROP PARTITION and REORGANIZE PARTITION as an alternative. That is a very good solution. In replication, the DROP would be sent as a "statement" even in row-based-replication. It is not a "delete". And the DROP is very fast since most of the effort is in the OS to release delete the file. In this I discuss some of the nuances: http://mysql.rjweb.org/doc.php/partitionmaint . There would be a barely noticeably replication lag.

You say you would need to add a column? You don't have some kind of datetime / timestamp / id that would suffice?

It will take a significant amount of time to ALTER TABLE .. ADD PARTITIONING ..

If you really won't want to make the transition, then consider the techniques in this for large, replication-friendly, deletes: http://mysql.rjweb.org/doc.php/deletebig

| improve this answer | |
  • Thanks Rick. I just added some color to the situation. – Josh Nov 10 '19 at 21:33
  • @Josh - I added to my answer. – Rick James Nov 11 '19 at 2:48
  • Thank you! We will look into adding a date column and then partition on that. – Josh Nov 11 '19 at 14:11
  • Question about the partition direction. We would be running updates to the data that is partitioned that would move big chunks of data from one partition to the next. For example, a single update might change the date on 5 million rows which would then move the data from one partition to the next. Is that much movement across partitions bad ? As stated before this is in an environment with replication. – Josh Nov 11 '19 at 14:38
  • @Josh - UPDATEing 5M rows will take hours whether it is partitioned or not and whether replicating or not. Find another way to achieve the "business logic". What is common to the 5M rows? That may lead to a solution. – Rick James Nov 11 '19 at 18:29

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