Обсуждение: Basic Postgresql Performance Question

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), 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)

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

Any thoughts?
Thanks.

* Bill (bill@math.uchicago.edu) wrote:
> 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), though I was wondering if

Excellent plan.  If you're looking at using Debian stable I'd encourage
you to consider using the PostgreSQL back-port of 7.4.2 to Debian stable
on backports.org.

> 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)

I don't think so...  There's something called OpenMosix which does this
on independent processes but not for threaded programs (since it doesn't
make sense to split them across different nodes if they're accessing the
same memory- every memory access would have to be checked) like the
PostgreSQL server.

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

Personally, I certainly think so.  More registers, more memory possible
inside one application, other stuff...

    Stephen

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)   

I don't think so...  There's something called OpenMosix which does this
on independent processes but not for threaded programs (since it doesn't
make sense to split them across different nodes if they're accessing the
same memory- every memory access would have to be checked) like the
PostgreSQL server.
 

-- 
Kevin Barnard

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.

* Scott Marlowe (smarlowe@qwest.net) wrote:
> On Wed, 2004-06-16 at 13:15, Bill wrote:
> > 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.

This is truer on more traditional 64bit platforms than on amd64 which
has more differences than just the ability to handle 64bit size things.
amd64 also gives you access to more registers than were on the
register-starved i386 platforms which increases the speed for most
applications where it usually wouldn't when recompiled for 64bit.

    Stephen