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 ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Here is the hard limit for MySQL
This presents a daunting challenge for file handles depending on Storage Engine
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.
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.
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.
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).