While Partitioning large tables in Mariadb we want some partitions to be placed in another storage device with different speed and capacity. That is our requirement. For that we are trying to perform some testing in our local set up,where we have placed some partitions of a table(Actually partitions of the innodb tables files, the .ibd ones) outside of the data directory by means of alter table statements with data directory clause. But the problem is that whenever we alter the table after partitioning ,the partitioned .ibd files placed outside of datadir comes back to the datadir which is really unexpected. So can anyone please help in this context to clarify whether this is a issue with mariadb or with the concept itself described as above.


That's a problem with ALTER. File a bug report if you like.

I consider RAID striping to be better than manually placing pieces of the database across physical volumes.

What kind of ALTER are you doing? Adding an index is different than adding a column is different than fiddling with the partition structure. There may be a workaround.

  • Thanks for ur help. We have initiated a study on Raid Striping. Regarding the alter statement, we have found a workaround . We have to keep the partitioning clause with every alter statement we use after partitioning. This keeps the partitioning intact. – Avijit batabyal May 2 '17 at 10:22
  • Beware of benchmarks -- they tend not to test real-life. Hand-striping and RAID-striping will show benefit only if you have multiple concurrent connections. And the access patterns (which partitions and blocks) are critical to whether the striping matters. – Rick James May 2 '17 at 14:37
  • Hand striping is particularly useful if you want to isolate the IO impact of some databases from the others, though if you are considering that maybe you want different servers instead of different drives. – David Spillett Jul 12 '19 at 21:11
  • @DavidSpillett - We agree to disagree. – Rick James Jul 12 '19 at 21:41
  • @RickJames - perhaps. Usually RAID striping is preferable, but unless you are talking about "cloud level" shared infrastructure there are definitely jobs for which device level isolation is the right tool, in my experience. – David Spillett Jul 12 '19 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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