6

I don't understand how partitioning helps me more or less than using a separate table to store older records.

I need to maintain a list of stock entries along with historical records about the stock.

So here I have the option that if some stock is discontinued, I can move that stock to another table that will be accessed only at the time of reports when a user wants to see old data.

Another option I have is partitioning the table, but as I don't know much about partitioning I am not sure if this will really help me.

Stock records will be accessed each time stock is removed from inventory. Once the remaining quantity reaches 0, the item needs to remain in the table for reporting purposes only. A new entry is created upon purchase of further stock.

I have gone through the dev.mysql documentation on Partitioning.

Questions

  • Can anyone shade some light on this ?
  • Is this the right way of doing this ?
  • Should we handle it differently ?
2
  • 1
    What data volumes are you talking about? Millions of rows? Billions?
    – Philᵀᴹ
    Sep 21, 2012 at 20:16
  • @Phil Its only that I need access to only some hundreds of the records which are active. Once the entry become inactive It will be needed only and only for reports and analysis, once in a month. And about volumes it will grow with time but active records will be limited to a few hundreds or thousand only.
    – Sanuj
    Sep 22, 2012 at 4:03

2 Answers 2

2

Partitioning is helpful with large tables and management purposes for your DBA. If you have 50GB of data you should start looking into partitioning just for perfomrance alone, as Kim Tripp stated.

However, as a DBA I find partitioning great for management of large tables and reporting data that comes from a OLTP source, something which I think you are describing. Partitioning will allow you to also create different indexes, different fill factors, and utilize indexed views on top of them to gather massive performance boosts for aggregation type functions utilizng "Indexed Views over Partitioned Tables".

In my recommendation, get familiar with partitioning. It can save you disasters. I once inhereted a table that was 6 billion rows and growing at 2-3GB a day with only 20GB free on the drive. I was able to create a new partition for new data, copy out all the old data to different databases on RAID 5, and update the code to refer to views; all without client downtime in a 24/7 shop. Partitioning is essential as data sets grow larger.

4
  • Thanks for your answer :) So I understand that partition will help me with the data storage for old records definitely, so should I make a partitioned table for inactive records and a table for active records as active records will not exceed the thousand mark or should I keep them in one partitioned table based on active inactive??
    – Sanuj
    Sep 22, 2012 at 4:08
  • 1
    Sanuj, I just saw your posting, pardon for not replying earlier. It depends, I suspect it will change overtime. Currently it sounds like having all of your inactive data in 1 partition and the active data in another partition will work just fine. Maybe later on you can change the partition scheme so the inactives are split into 1 years or months worth data per partition. It's flexible. Ultimately, you would move the inactive data to another database and refer to it with a view. Then set it to 'read only' to optimize read performance, and partition it in that database(s). Oct 15, 2012 at 22:13
  • Thanks for your further explanation, Now I get the concept of this :)
    – Sanuj
    Oct 19, 2012 at 3:28
  • Even though we are utilizing database functionality through partitioning. One has to be wary to who this partitioned table is exposed to. Sometimes it's just safer to create separate tables, instead of partitioning.
    – Robert Co
    Dec 31, 2013 at 17:18
1

I am not sure if database partitioning is the solution for you without more details. But partitioning alone solves some difficult issues with may relate. In general database partitioning achieves the same thing your doing now just in a more automated way. Think of a partitioned tables as nothing more than several tables with the same name. Each partition stores a subset of your data. When your ready to access it you use the same parent table name but include a filter in the where clause to limit the data. A common way to partition tables is by date. You could create a new partition for every month using the following example:

create table stuff (
  id int(10) not null auto_increment,
  received timestamp not null,
  primary key(id) )
engine=InnoDB
partition by range (unix_timestamp('received'))
(partition p1 values less than (unix_timestamp('2014-01-01 00:00:00')),
partition p2 values less than (unix_timestamp('2014-02-01 00:00:00')),
partition p3 values less than (unix_timestamp('2014-03-01 00:00:00')),
partition p4 values less than (unix_timestamp('2014-04-01 00:00:00')),
partition p5 values less than (unix_timestamp('2014-05-01 00:00:00')),
partition p6 values less than (unix_timestamp('2014-06-01 00:00:00')));

This allows you to query a single table in your report and just filter by the date. So if you want to see only stuff from Jan 2014 then include that range in your where clause. MySQL will only query that 1 partition rather than scanning the entire table. Using this method is great for archiving data, maximizing query performance, and also giving you the ability to prune your data quickly by dropping individual partitions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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