How to clean and clear large tables in MySQL
From time to time our data team gets requests for advice on how to perform cleanup operations against large database tables. Typically, these originate in a ticket requesting information about how the disk is being used or why a specific table is performing poorly.
Somewhat less often, we are asked us to explain why a cleanup attempt has failed or why it has caused downtime for an application. Managing these types of operations with minimal or no downtime can be a challenge given the way a database like MySQL performs these tasks.
The most common form of table cleanup operation we are asked about is for the sessions table. Even though these are not really recommended practice(https://guides.rubyonrails.org/action_controller_overview.html#session) they are still quite common to see. Depending on your application workload and use cases these tables can grow very quickly in size; often including older records that are never going to be used again. Even though the data payload in each row is relatively small, it's not uncommon to find sessions tables that are 10 or 20 GB in size—often larger than the rest of the database combined.
The "Standard" Solution
While there is no automatic session cleanup built into a rails app it happens to be really easy to write a rake task that handles this cleanup for you. In fact, it's so easy that it's often overlooked until the table has reached the size where it is difficult to manage
