-1

There has been much talk about the benefits of table partitioning in Relational DBMS.

I would like to know:

  • Are there any risks to table partitioning ?
  • What measures are there to take to minimize such risks ?
1

MySQL Perspective

Here is the hard limit for MySQL

  • MySQL 5.6: the maximum number of partitions is 8192.
  • Before MySQL 5.6, the maximum number of partitions is 1024.

This presents a daunting challenge for file handles depending on Storage Engine

InnoDB

There will be 8192 (1024 for older versions) file handles, that is one file handle for each .ibd file dedicated to the partition PER TABLE. The option innodb_open_files get autosized (default is 300 in older version). InnoDB will cache file handles rather decently.

MyISAM

There will be 16384 (2048 for older versions) file handles, that is one file handle for each .MYD file and one for each .MYI file dedicated to the partition PER TABLE. The option open_files_limit gets autosized and the maximum is 65535. This can present extreme challenges for those with several partitioned MyISAM tables.

The only thing you can do is raise the OS ulimit to accommodate. Only then can you raise open_files_limit beyond 65535.

CAVEAT

Each time you access one or more partitions, you have to remember to explicitly close the file handles using FLUSH TABLES table_name; to close open file handles you no longer need. This makes room for new file handles to be opened. You can identify which tables have open handles with SHOW OPEN TABLES;. When doing these things, it becomes a little more manageable to have a table with a vast number of file handles. You just have to be very meticulous and scrupulous about closing unused open file handles often, even if touching just one partition.

|improve this answer|||||
0

It's not that easy to answer. Just a few thought maybe helping you:

Partitioning is adding more complexity to your database layout. So e.g. depending on your DBMS you will might will need to maintain trigger functions and indexes. Also it might get complicated for other users to understand what you did there.

There is also an impact on I/O. On the first hand, you can split data up so single files might not get that big, but on the other hand having all data on one disc, you risc a hight fragmentation of data. Of course, if you can split up between often used data and not very often used data, you could move the partitions to different file systems (e.g. a fast one for often used data and a cheaper one for less often used data).

|improve this answer|||||

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