Sunday, December 30, 2012

Don't forget to check your partitions!

As it's the end of the year it might be time to check your partition definitions.

If you forget to add a new partition in time partitions with no MAXVALUE might start to throw errors:
mysql> create table nye (`event_id` int not null auto_increment,`edate` year(4) not null, description varchar(200), 
    -> primary key(`event_id`,`edate`)) 
    -> partition by range( edate ) (
    -> partition p2010 VALUES LESS THAN (2011), 
    -> partition p2011 VALUES LESS THAN (2012), 
    -> partition p2012 VALUES LESS THAN (2013) );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2010','twenty ten'); 
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2011','twenty eleven');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2012','twenty twelve');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2013','twenty thirdteen');
ERROR 1526 (HY000): Table has no partition for value 2013

And partitions with a MAXVALUE might start to contain data of multiple years, and that might not be how it was intended.

So check your partitioning if you partition by year. It might also be a good moment to think about archiving old data.

You could use the common_schema.sql_range_partitions view to create the next partition.

mysql> select sql_add_next_partition from common_schema.sql_range_partitions where table_name='nye';
+-------------------------------------------------------------------------------------+
| sql_add_next_partition                                                              |
+-------------------------------------------------------------------------------------+
| alter table `test`.`nye` add partition (partition `p_2014` values less than (2014)) |
+-------------------------------------------------------------------------------------+
1 row in set, 22 warnings (0.08 sec)

mysql> call eval("select sql_add_next_partition from common_schema.sql_range_partitions where table_name='nye'");
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2013','twenty thirdteen');
Query OK, 1 row affected (0.00 sec)

O, and your sharding definitions might also contain a reference to a year.

And with the right partition definitions it's just:

mysql> SELECT TIMEDIFF(NOW(),'2013-01-01 00:00:00') AS 'Countdown';
+-----------+
| Countdown |
+-----------+
| -27:19:07 |
+-----------+
1 row in set (0.00 sec)

4 comments:

  1. I would recommend that this be part of your automated monitoring system. Thinking about purging data is a great idea. But you shouldn't have to manually think about partitioning.

    PalominoDB has a free nagios script to monitor table partitions:

    https://github.com/palominodb/PalominoDB-Public-Code-Repository/tree/master/nagios/table_partitions

    There are also tools to create new table partitions automatically:
    https://github.com/palominodb/PalominoDB-Public-Code-Repository/blob/master/tools/data_mgmt/src/pdb-parted

    And I think that can delete partitions too, if you want to archive stuff.

    ReplyDelete
  2. Exactly when numerous had just been persuaded that these machines were the best innovations in the field of check getting the money for, the possibility of online check changing was presented. Does this sound silly and unthinkable? On a few focuses, it is. https://www.aaa1autotitleloans.com/chicago

    ReplyDelete

  3. The larger part of online items and master classes manage enormous item dispatches and building specialty distributing domains. Be that as it may, what occurs after you "have" the gathering and the general population appear? What happens the day after websitebackupbot the gathering? Do you truly trust all you have to do is "look after children" online business?

    ReplyDelete
  4. Positive site, where did u come up with the information on this posting? I'm pleased I discovered it though, ill be checking back soon to find out what additional posts you include. script cek mutasi

    ReplyDelete

Note: Only a member of this blog may post a comment.