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.

1 Answer 1


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. May 2, 2017 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, 2017 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. Jul 12, 2019 at 21:11
  • @DavidSpillett - We agree to disagree.
    – Rick James
    Jul 12, 2019 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. Jul 12, 2019 at 22:20

Your Answer

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

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