0

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?

Improve this question
2
  • What's the point of that? I can't muster out words to describe how terribly pointless this thing you're trying to do is.
    – N.B.
    Commented Feb 17, 2014 at 17:17
  • The purpose is utilizing all disks I/O. Is it possible to do so? I have already sensed how terrible it is but I still want to try.
    – Petrie Wong
    Commented Feb 18, 2014 at 1:31

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • Using table partition seems a good idea to solve the problem. I will evaluate RAID approach and table partition approach. Thank you!
    – Petrie Wong
    Commented Feb 18, 2014 at 6:02
0

I don't believe this is possible under any DBMS. In general because it is a bad idea.

If you have performance constraints that require I/O from multiple disks then it would be best to invest the money in the hardware to do it properly on a hardware raid level.

Otherwise just use a single disk.

Improve this answer

Your Answer

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

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