Completing the data lifecycle is often harder than originally expected: Deleting data can cost sometimes way more than inserting it in the first place. MySQL Partitions can offer a way out. We have an earlier post on the subject.
A sample table, and a problem statement
Let’s define a kind of log table, to which data is added with an auto_increment id value and some data.
This is our basic Python framework for experimentation, using the click framework, and a command setup-tables. This command will run a number of SQL statements to initialize our log table named data.
The log table has three columns: id, an auto_increment counter, and two columns d and e, each containing 64 characters of data. To get things started, we add an initial partition, containing all id-values below 10.000 and an initial row.
If we were to add data to this table in a loop, we would increment our id-counter, and with InnoDB being what it is, all new rows will be added at the end of the table: We remember from ALTER TABLE for UUID that the physical order of any InnoDB table is by primary key - our id-counter.
Now, if we were to expire old data, we would start to delete rows with the lowest id-values, so we would delete rows from the beginning of the table, or the left hand side of the B+-Tree. To keep the tree balanced, MySQL would have to execute balancing operations, which will be expensive, because rows are being shuffeled around in the tree.
New data is added to the right hand side of the B+-Tree, while old data is being deleted at the left hand side. To keep the tree balanced, data is reshuffled, which is an expensive operation.
Instead, we are defining partitions. In our case, we are using the simplest definition possible: A PARTITION BY RANGE on the primary key column. We are making bins of 10.000 rows each, because that is convenient for our demonstration here.
We will be using the Python multiprocessing module to have three processes, an inserter(), a partitioner() and a dropper(). All of them are endless loops.
The inserter will insert random new data rows into the table as fast as possible.
The partitioner will make sure that we always have a sufficient number of empty new partitions for the inserter to continue.
The dropper will limit the number of partitions with data by throwing the oldest partition away.
We will have small piece of code that starts our processes:
The Inserter is an endless loop that generates two random 64 character strings and inserts a new row into the database. Every 10 rows, we commit, every 1000 rows we print a message.
Without the other two threads, the inserter will generate 10.000 rows and then stop, because there is no MAXVALUE clause.
The Partitioner is an endless loop that runs an ANALYZE TABLE data command to refresh the statistics, and then queries INFORMATION_SCHEMA.PARTITIONS for the five partitions with the highest PARTITION_ORDINAL_POSITION.
If there are fewer than 5 partitions in total, we generate new partitions no matter what.
If there are not at least 5 partitions with no rows int them, we create new partitions.
If we did nothing, we wait for 1/10th of a second and then check again.
The new partition gets a range expression with a limit 10.000 values higher than the highest one found, and the partition name is derived from the limit by dividing by 10.000.
This will simply format and run an ALTER TABLE statement to add a new partition to the existing table.
And the checking loop:
This code is mostly a long SELECT on the INFORMATION_SCHEMA.PARTITIONS table, and then two quick checks to see if we need to make more partitions.
The Dropper structurally mirrors the Partitioner: We have a tiny function to create the actual ALTER TABLE data DROP PARTITION statement:
And we have an endless loop that basically runs a SELECT on INFORMATION_SCHEMA.PARTITIONS and checks the number of partitions that have a non-zero number of TABLE_ROWS. If it is too many, we drop the one with the lowest number (“the first one found”, using an appropriate sort order in our SQL).
A test run
In our test run, we see immediately after startup how the five spare partitions are being created.
Once we cross the threshold of p1, the number of empty partitions is no longer low enough and another one is being created:
This continues for a while, until we have a sufficient number of data partitions so that we begin dropping, too:
Now the system reaches a stable state and will add and drop partitions in sync with the Inserter.
From inside SQL we can see the number of rows in the table rise, and then suddenly drop by 10.000 as we drop a partition.