SqlPartitioner

SqlPartitioner provides a PartitionsManager class to help maintain partitioned tables in MySQL. If you have a table that is partitioned based on a timestamp, you will likely need to regularly add new partitions into the future as well as remove older partitions to free up space. This gem will help.

SqlPartitioner works with MySQL partitioned tables that are partitioned by a timestamp column, expressed as an integer representing a Unix epoch timestamp in either seconds or micro-seconds.

You can use ActiveRecord or DataMapper.

Supported functionality:

• initializing partitioning on a table
• adding new partitions of a given size (expressed in months or days)
• removing partitions older than a given timestamp or number of days

You can run the above operations directly or pass a flag to only do a dry-run.

Does not yet support databases other than MySQL. Target table can only be partitioned by its timestamp column representing seconds or micro-seconds.

You'll need to require 'sql_partitioner'.

Here's an example for initializing a PartitionsManager instance, using DataMapper:

partition_manager = SqlPartitioner::PartitionsManager.new(
    :table_name        => 'my_partitioned_table', # target table for partitioning operations
    :time_unit         => :micro_seconds, # or :seconds, as appropriate for the table's `timestamp` column
    :lock_wait_timeout => 1, #(seconds)
    :adapter           => SqlPartitioner::DMAdapter.new(DataMapper.repository.adapter),
    :logger            => Logger.new(STDOUT)
)

If you are using ActiveRecord, you can instead supply the following for :adapter:

SqlPartitioner::ARAdapter.new(ActiveRecord::Base.connection)

Regarding the :lock_wait_timeout option: any partitioning statement must acquire a table lock on the partitioned table, and while it is waiting to acquire this lock, any subsequent queries on that table will be blocked and have to wait. It may take a long time to acquire a table lock if there were already long-running queries in progress. Therefore, setting a short timeout (e.g. 1 second) ensures the partitioning statement will timeout quickly, so any other SQL operations on that table will not be delayed. If the partitioning command times-out, it will have to be retried later. MySQL's default value for lock_wait_timeout is 1 year.

Here's an example for initializing partitioning on the table. It will create partitions of size 30 days, as needed, to cover 90 days into the future:

days_into_future = 90
partition_size = 30
partition_size_unit = :days
dry_run = false
partition_manager.initialize_partitioning_in_intervals(days_into_future, partition_size_unit, partition_size, dry_run)

Here's an example for appending partitions to cover time periods into the future. It will create partitions of size 30 days, as needed, to cover 180 days into the future:

days_into_future = 180
partition_size = 30
partition_size_unit = :days
dry_run = false
partition_manager.append_partition_intervals(partition_size_unit, partition_size, days_into_future, dry_run)

Here's an example for appending a single partition with the given name and "until" timestamp (using microseconds in this case):

partition_data = {'until_2014_11_01' => 1414870869000000}
dry_run = false
partition_manager.reorg_future_partition(partition_data, dry_run)

Here's an example for dropping partitions as needed to only cover 360 days of the past:

days_into_past = 360
dry_run = false
partition_manager.drop_partitions_older_than_in_days(days_into_past, dry_run)

Here's an example for dropping a single partition, until_2014_11_01, by name:

partition_names = ['until_2014_11_01']
dry_run = false
partition_manager.drop_partitions(partition_names, dry_run)

The above operations can be helpful when creating a rake task that can initialize partitioning for a given table, and gets called periodically to add and remove partitions as needed.

Tested with Ruby 1.8.7 and 2.1.2, and MySQL 5.5.

Pull requests welcome.

• Dominic Metzger
• Sumner McCarty
• Prakash Selvaraj
• Jim Slattery

MIT License, see LICENSE