Re: Hardware/OS recommendations for large databases (

Scott,

On 11/16/05 9:09 AM, "Scott Marlowe" <smarlowe(at)g2switchworks(dot)com> wrote:

> The biggest gain is going from 1 to 2 CPUs (real cpus, like the DC
> Opterons or genuine dual CPU mobo, not "hyperthreaded"). Part of the
> issue isn't just raw CPU processing power. The second CPU allows the
> machine to be more responsive because it doesn't have to context switch
> as much.
>
> While I've seen plenty of single CPU servers start to bog under load
> running one big query, the dual CPU machines always seem more than just
> twice as snappy under similar loads.
>
I agree, 2 CPUs are better than one in most cases.

The discussion was kicked off by the suggestion to get 8 dual core CPUs to
process a large database with postgres. Say your decision support query
takes 15 minutes to run with one CPU. Add another and it still takes 15
minutes. Add 15 and the same ...

OLTP is so different from Business intelligence and Decision Support that
very little of this thread¹s discussion is relevant IMO.

The job is to design a system that can process sequential scan as fast as
possible and uses all resources (CPUs, mem, disk channels) on each query.
Sequential scan is 100x more important than random seeks.

Here are the facts so far:
* Postgres can only use 1 CPU on each query
* Postgres I/O for sequential scan is CPU limited to 110-120 MB/s on the
fastest modern CPUs
* Postgres disk-based sort speed is 1/10 or more slower than commercial
databases and memory doesn¹t improve it (much)

These are the conclusions that follow about decision support / BI system
architecture for normal Postgres:
* I/O hardware with more than 110MB/s of read bandwidth is not useful
* More than 1 CPU is not useful
* More RAM than a nominal amount for small table caching is not useful

In other words, big SMP doesn¹t address the problem at all. By contrast,
having all CPUs on multiple machines, or even on a big SMP with lots of I/O
channels, solves all of the above issues.

Regards,

- Luke