Re: Basic Postgresql Performance Question

On Wed, 2004-06-16 at 13:15, Bill wrote:
> Hi,
> I currently have a mysql server running with a database of around 800
> gb.  The problem is that the server is old (500 MHz Pentium III with 512
> MB RAM) and I want to change this to a new server and convert the
> existing database to Postgresql on Debian (I assume that Postgresql
> offers better performance for complex read only queries on large
> databases),

Usually, but there are always queries that run faster or slower on a
given database due to differences in architecture and design.  For
instance PostgreSQL tends to be slow when doing max/min aggs, but faster
when doing things involving complex joins and unions.

>  though I was wondering if
>
> 1.  It is possible to have some sort of load-balancing through buying
> many computers without replication, i.e have one server which has the
> databases and then other servers which has no database but just exists
> to balance the memory and processor load? (I have heard this is possible
> with C-JDBC)It is difficult to have enough space to replicate a 600 gb
> database across all computers)

That depends.  Most databases are first I/O bound, then memory bound,
then CPU bound, in that order.  With an 800Gb database your main "cost"
is gonna be moving data off of the platters and into memory, then having
the memory to hold the working sets, then the CPU to mush it together.

Now, if you're reading only tiny portions at a time, but doing lots of
strange work on them, say weather forcasting, then you might be CPU
bound.  But without knowing what your usage patterns are like, we don't
know whether or not running on multiple boxes would help.  There are
replication systems, but there's no such thing as a free lunch.

> 2.  It is advantageous to buy AMD 64 rather than the Pentium IV?

Yes and no.  If having more than 2 gigs of ram is important, 64 bit
architecures run faster than 32 bit, where having over 2 gigs usually
results in a slow down due to the memory switching they use.