Paul,

plinehan wrote:

> I'm still trying to get my head around the idea that if an
> organisation has two departments, i.e. Accounts Payable and
> Accounts receivable - AP and AR, both of which have a
> humungous main table - say, called AP and AR, that if both
> departments are issuing queries simultaneously, why it would
> not be a better idea to separate the AP and AR tables onto
> separate disks - for a given hardware configuration on a
> middling server.
>
>

I never did such experiment, but I will try to explain what I think
about it. Of course the text below is a very simplistic way to expose
the idea, forgive-me FB developers to simplify your hard work so much :)

Taking those 2 tables (AP, AR) each one on a separated disk.

Let's suppose you will run a query that need to read 100MB/Data, and
that the throughput of a single disk is 100MB/s, the query would run in
one second, right ? If 2 user execute the same query against each of the
table, each one would get a response in 1s. If 2 users are executing the
same query on AR, then the time would multiply by 2, so the response
would take 2s, even if the disk of AP table is idle.

If you have a RAID0 (ok, I know RAID0 is bad, RAID10 is the key for
security), you will have 200MB/s of the combined disks throughput, so if
only one person is executing the query against AR, the throughput of
both disks would be combined and the query would be execute in 0,5s, if
2 users are executing the query one in AR and the other in AP, the
throughput would be divided, so each query would execute in 1s (as if
the table was on distinct disks). If 2 users run the same query on AR,
both disks would be used and each user could read 100MB/s, and the query
would run on 1s for each one.

So I think that the more you split data across disks, more idle disks
you would have on a given time, if you combine the same number of disks
in a RAID0 style, you will always use the maximum throughput of all
disks combined, no matter which table/index/temp space you are using.

I think the performance would be better in a 3 disk on RAID0 then on a
disk for temp space, a disk for data and a disk for index, or a disk for
OLAP information, a disk for OLTP information and a disk for historical
data, and the combinations like this.

My experience is on small databases 15GB or below, so generally I use
RAID5 or RAID10 depending on the configuration of the server. Perhaps
for a 1TB database my point above are completely non-sense, since I
don't have numbers to prove it, I can't assure it's the best approach,
but that is my feeling.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br