Skip site navigation (1) Skip section navigation (2)

Re: splitting data into multiple tables

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Viji V Nair <viji(at)fedoraproject(dot)org>
Cc: nair rajiv <nair331(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, pgsql-performance(at)postgresql(dot)org
Subject: Re: splitting data into multiple tables
Date: 2010-01-26 17:41:06
Message-ID: 4B5F2932.7040007@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com  www.2ndQuadrant.com


In response to

Responses

pgsql-performance by date

Next:From: Richard NeillDate: 2010-01-26 17:41:32
Subject: Re: Should the optimiser convert a CASE into a WHERE if it can?
Previous:From: Matthew WakelingDate: 2010-01-26 17:23:06
Subject: Re: Should the optimiser convert a CASE into a WHERE if it can?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group