Re: cpu_tuple_cost

From: "Magnus Hagander" <mha(at)sollentuna(dot)net>
To: "Greg Stark" <gsstark(at)mit(dot)edu>, "David Brown" <time(at)bigpond(dot)net(dot)au>
Cc: "Gregory Stark" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: cpu_tuple_cost
Date: 2005-03-16 09:42:04
Message-ID: 6BCB9D8A16AC4241919521715F4D8BCE6C7095@algol.sollentuna.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> > >The "this day and age" argument isn't very convincing. Hard drive
> > >capacity growth has far outstripped hard drive seek time
> and bandwidth improvements.
> > >Random access has more penalty than ever.
> >
> > In point of fact, there haven't been noticeable seek time
> improvements
> > for years. Transfer rates, on the other hand, have gone
> through the roof.
>
> Er, yeah. I stated it wrong. The real ratio here is between
> seek time and throughput.
>
> Typical 7200RPM drives have average seek times are in the
> area of 10ms.
> Typical sustained transfer rates are in the range of 40Mb/s.
> Postgres reads 8kB blocks at a time.
>
> So 800kB/s for random access reads. And 40Mb/s for sequential
> reads. That's a factor of 49. I don't think anyone wants
> random_page_cost to be set to 50 though.
>
> For a high end 15k drive I see average seek times get as low
> as 3ms. And sustained transfer rates get as high as 100Mb/s.
> So about 2.7Mb/s for random access reads or about a
> random_page_cost of 37. Still pretty extreme.
>
> So what's going on with the empirically derived value of 4?
> Perhaps this is because even though Postgres is reading an
> entire table sequentially it's unlikely to be the only I/O
> consumer? The sequential reads would be interleaved
> occasionally by some other I/O forcing a seek to continue.

What about the cache memory on the disk? Even IDE disks have some 8Mb
cache today, which makes a lot of difference for fairly short scans.
Even if it's just read cache. That'll bring the speed of random access
down to a 1=1 relationship with sequential access, assuming all fits in
the cache.

//Magnus

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rosny 2005-03-16 11:15:48 Which one is faster: one way reading ="single pass reading"
Previous Message Stef 2005-03-16 07:59:39 Re: Slow loads when indexes added.