I am going to deploy database tables to multiple disks.
My database layout would be like following example:
There are 3 tables (Customer, Order, Product) and 4 disks (disk_A, B, C, D).
Customer: 5% on disk_A, 95% on disk_B
Order: 25% on disk_A, 50% in disk_C, 25% on disk_D
Product: 100% on disk_A
Q1: Dose any DBMS support such data layout?
I tried tablespace in following DBMS but they do not work.
1) PostgreSQL: It doesn't support tablespaces across multiple disks. Is it?
2) MySQL and Oracle: It supports multiple disk tablespaces. But I
cannot control how much data store on each disks. (Eg. 25% in A, 0% in
B, 50% in C, 25% in D). Is it?
Q2: Which DBMS support that?
Best Answer
To use multiple disks for IO, your best bet is to use RAID, preferably hardware RAID, giving both increased reliability and performance both.
Trying to do it by hand is horrifyingly bad; you're unlikely to get a good performance balance, that performance balance probably, won't survive table growth, and a single disk failure destroys your data - you're effectively suggesting RAID 0 (striping) with all the disadvantages, but without most of the benefits.
If you insist on the mindbogglingly crazy idea you're looking at, then MS SQL Server Enterprise Edition (and likely other products) has partitioned tables, and that would allow you to have one table with partitions on different filegroups, and then you could put the different files of the filegroups on different partitions. You'd control proportions by the choice/control of the data the partitioning is done on.
The budget crazy idea is multiple tables to store different percentages of the data (by hand partitioning), and then UNION them into a view.
Again, don't do this - use RAID*. Heck, even use software RAID if you have too low a budget for hardware RAID!
* RAID 1, 10, 5, 50, 6, and 60 are approved variants for databases. Do NOT use RAID 0.