Re: Table Partitioning in Postgres:

On Wed, 2003-02-19 at 05:43, Jonathan Bartlett wrote:
> > Certainly. But the advantage will not be visible unless you put it on a disk
> > that is on separate IDE or SCSI channel. Now that you have a large database,
> > are you using more than one SCSI channel? Otherwise just putting on different
> > disks will not help as much.
>
> This is quite untrue.  Even going over a single channel, it is highly
> unlikely that a single disk or RAID set is going to saturate the channel,
> because of disk seek times.  The purpose of putting tables on different
> disks than the indexes is so that the disks don't have to keep seeking
> back-and-forth between table, index, table, index, table, index, etc.
> Likewise with transaction logs, if they have their own disk, the
> read/write heads can pretty much stay in the same place with transaaction
> log updates.  Using these benefits you are more likely to make full use of
> a single channel than otherwise.
>
> Jon
>


While your statement is correct I did want to clarify that IDE and SCSI
were lumped together and they should not be.  SCSI and IDE performance
expectations differ because their bus technologies are dramatically
different.  IDE has some serious performance issues with multiple disks
per channel.  A single disk can effectively tie up an IDE channel for
the duration of an outstanding I/O operation, unlike what you would
expect with SCSI.  As such, it is highly recommended to have as many
channels as disks where you expect concurrent disk I/O on said disks.

In the case of IDE, you should see significant boosts in performance by
following this tip.  Even when sharing a bus with something as simple as
a CDROM, it can significantly and negatively impact IDE bus performance;
reflective of both throughput and latency.  As such, I highly recommend
that you use a channel per disk where you expect high rates of
concurrent disk I/O.  As a rule of thumb, you should use a channel per
disk with IDE where throughput and latency are of concern.


Regards,

    Greg


>
> >
> >
> > > Even Postgresql has to be told to perform vaccum and analyze.
> > > If the OS had enough intelligence we could trust it to do a good job,
> > > but until then ...
> >
> > Partially true. Postgresql could have done vacuum at runtime at the cost od
> > performance. So developers delegated the task to admin.
> >
> > Looking for a solution in problem, the real benefits won't be visible unless
> > you put it on a different disk channel. Otherwise RAID is your best bait now
> > as OS can handle it intelligently and it enhances the IO bandwidth immensely.
> >
> > Other than that you can not do much with postgresql right now.
> >
> >  Shridhar
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting