Re: splitting data into multiple tables

[Viji V Nair <viji@xxxxxxxxxxxxxxxxx>]

On Tue, Jan 26, 2010 at 11:11 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote:

Viji V Nair wrote:

A 15k rpm SAS drive will give you a throughput of 12MB  and 120 IOPS. Now you can calculate the number of disks, specifically spindles, for getting your desired throughput and IOPs

I think you mean 120MB/s for that first part.  Regardless, presuming you can provision a database just based on IOPS rarely works.  It's nearly impossible to estimate what you really need anyway for a database app, given that much of real-world behavior depends on the cached in memory vs. uncached footprint of the data you're working with.  By the time you put a number of disks into an array, throw a controller card cache on top of it, then add the OS and PostgreSQL caches on top of those, you are so far disconnected from the underlying drive IOPS that speaking in those terms doesn't get you very far.  I struggle with this every time I talk with a SAN vendor.  Their fixation on IOPS without considering things like how sequential scans mixed into random I/O will get handled is really disconnected from how databases work in practice.  For example, I constantly end up needing to detune IOPS in favor of readahead to make "SELECT x,y,z FROM t" run at an acceptable speed on big tables.

Yes, you are right.

There are catches in the SAN controllers also. SAN vendors wont give that much information regarding their internal controller design. They will say they have 4 external 4G ports, you should also check how many internal ports they have and the how the controllers are operating,  in Active-Active or Active- Standby mode.


 

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@xxxxxxxxxxxxxxx  www.2ndQuadrant.com