What ways to clean up innodb mysql tables would you recommend?

Question

MySQL

Initial data:
1) 2 servers, on each of which mysql is raised
2) on each of the servers there is an innodb type table
3) these tables are replicated (by queries) among themselves according to the master-master type (suddenly this is important, an auto-increment field on one of the servers even, odd on the other)
4) tables contain about 50 million records (about 128 bytes each) and every day up to 10-20 million records can fall into any of the tables (not once, but during the day)
5) clean tables needed daily, cleaning order: 10-20 million records
6) at the moment, the size of each database on the screw is 50 gigabytes (including indexes)
What options are there, except for the DELETE, OPTIMIZE TABLE option?

Answer 1

Another option, I don’t know if it suits you, partitions in 5.1 . For you, I think a variant with alter table truncate partition ... is possible, and the fact that local indexes should also help, there is no global index - there will be no tails from deleted data. Of the minuses, well, there are a lot of them even in the official documentation . And here a lot depends on how your data is organized, the simplest option is with a date and time, you can use them to beat the data and clean up obsolete data.

Answer 2

It's been discussed a hundred times already. Set a flag for deletion for deleted records, and really reduce it only when there is no load on the database.

Answer 3

>shitcode-style solution: use a sequence function instead of autoincrement, which limits the maximum number of id in the table, and loops the sequence of IDs, and use insert ignore to overwrite old ones with new ones.
yes, the solution is strongly in the style of shitcode, and in this situation it will not work, because there are nuances, for example, packages are divided according to some attribute, and in each specific attribute, the number of packages can be from one to several million. in addition, the deletion should not be carried out randomly, but strictly for a specified period of time (incoming data), i.e. Roughly speaking, the archive should be cleaned, deleting old unnecessary records.