Splitting queries across servers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wow. Your needs really *are* demanding. You might need to look into a
high-end server to meet those kinds of needs - Sun has some *very*
expensive hardware that can handle over 1/2TB of RAM, for example, and
Cray makes supercomputers (even more *very* expensive) which can handle
somewhere around 32 TB of RAM...

You're sure you can't possibly put up with even a small amount of
swapping?

On Jan 26, 2005, at 11:00 AM, Max wrote:

Hello,

Our postgresql database is getting too big to be handled by one
server. We
need the database to be in RAM and cannot afford swapping. At the
moment,
we're using only 3GB or RAM, however our business growth is going to
drive
this number into the double digits zone, maybe triple digits.

What are our options ?

I am a little bit ignorant in this part of RDBM. Can we distribute our
tables across multiple servers (read server+postgres) and perform
distributed SQL queries ? If so, how does that work ? I am totally
resourceless!

Thank you in advance

Max.

---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB98Wx7aqtWrR9cZoRAtYNAJ0SHP+Y65TasCxvUxd9RjTXSJqyOgCdEi6X
VfX0V8TKg4JEPPy6eJyYPVM=
=zAYr
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

We configured a slony1 cluster with a master and slave (both 7.4.6), and
used the slave to serve read-only queries thereby offloading some of the
work from the master database.

This worked well for us.

You could also take a look at pg_pool to distribute your load - but I
haven't actually used it, so can't give any advice...

Hope that helps.

John Sidney-Woollett

Max wrote:

Wow. Your needs really *are* demanding. You might need to look into a
high-end server to meet those kinds of needs - Sun has some *very*
expensive hardware that can handle over 1/2TB of RAM, for example, and
Cray makes supercomputers (even more *very* expensive) which can handle
somewhere around 32 TB of RAM...

Well, that's too much money. We'd like to stay away from these options.

You're sure you can't possibly put up with even a small amount of
swapping?

Nop. Too many users. Xmas shopping brought us down for a few minutes, and
the rest of the time we were too slow.

We configured a slony1 cluster with a master and slave (both 7.4.6), and
used the slave to serve read-only queries thereby offloading some of the
work from the master database.

This worked well for us.

You could also take a look at pg_pool to distribute your load - but I
haven't actually used it, so can't give any advice...

thanks, I am going to look into this.

With a Quad Opteron (4 memory slots per CPU), you could put 64GB of RAM
onto a single machine using 4GB DIMMs in every slot.

The other option is to explore static memory storage. It's probably too
expensive to put your entire DB onto such a device but moving just the
WAL there would give you a pretty decent jump in write performance. (I
ran some tests way back simulating this by using a RAMDISK to store the
WAL files and got ~60% increase in an update-heavy mix.)

Max wrote:

Isn't putting the WAL in memory dangerous in case of a power failure?
I would think that RI would be compromised.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of William Yu
Sent: Thursday, January 27, 2005 12:41 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Splitting queries across servers

With a Quad Opteron (4 memory slots per CPU), you could put 64GB of RAM
onto a single machine using 4GB DIMMs in every slot.

The other option is to explore static memory storage. It's probably too
expensive to put your entire DB onto such a device but moving just the
WAL there would give you a pretty decent jump in write performance. (I
ran some tests way back simulating this by using a RAMDISK to store the
WAL files and got ~60% increase in an update-heavy mix.)

Very dangerous. I only did it as a test to guestimate what kind of
performance I might get if I got a static ram hard drive to put the WAL
onto.

Dann Corbit wrote:

With a Quad Opteron (4 memory slots per CPU), you could put 64GB of RAM
onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not quite as good as
this. You really got me thinking now. 64Gb.. hm that would solve many
issues. what do you think the cost would be for such a beast ? Any idea ?

Max

On Thu, 27 Jan 2005 20:17:29 -0800, Max <maxdl@adelphia.net> wrote:

With a Quad Opteron (4 memory slots per CPU), you could put 64GB of RAM
onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not quite as good as
this. You really got me thinking now. 64Gb.. hm that would solve many
issues. what do you think the cost would be for such a beast ? Any idea ?

Depending on the vendor and the storage subsystem, between $20k and
$50k, and if you start with less memory it will be much less. I can
attest to the fact that 4 x Opterons work VERY nicely. ;)

Max

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

Without memory, the prices are roughly:

4x846 = $5500
4x848 = $6500
4x850 = $8000

Memory costs would be:

16GB (1GB DIMMs) = $3000
32GB (2GB DIMMs) = $7500
64GB (4GB DIMMs) = $24000
128GB (8GB DIMMs) = $60000

The prices on the 4GB & 8GB DIMMs are bleeding edge of course. I see a
wide range of for them on Froogle. You will want your vendor of course
to pick memory is proper for the MB and pay whatever premium they'll
charge ontop of it. (Looking at the Tyan website, they only list 2GB
DIMMs as supported for their Quad Opteron MBs but whether that's because
those specs were done before >= 4GB DIMMs were available or an actual
hardware limitation, I dunno.)

Max wrote:

Suppose that you currently need 16 GB to cache everything now.
I would install (perhaps) 32 GB ram for the initial configuration.

The price of memory drops exponentially, and so waiting for the price to
drop will give a much lower expense for the cost of the RAM.

The reason to double the ram is the expense of upgrading in terms of
labor and downtime for the computer. That can be very significant. So
if we double the ram, that should give one or (hopefully) two years
safety margin.

If the database is expected to grow exponentially fast, then that is
another issue. In such a case, if it can be cost justified, put on the
largest memory volume that is possible given your financial limitations.

In any case, plan to completely replace everything (CPU, memory, disk)
every 5 years or less.

All the value is really in the data. Because hardware increases compute
power exponentially over time, that also means that the value of the
hardware you bought decreases exponentially over time.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of William Yu
Sent: Friday, January 28, 2005 11:36 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Splitting queries across servers

Without memory, the prices are roughly:

4x846 = $5500
4x848 = $6500
4x850 = $8000

Memory costs would be:

16GB (1GB DIMMs) = $3000
32GB (2GB DIMMs) = $7500
64GB (4GB DIMMs) = $24000
128GB (8GB DIMMs) = $60000

The prices on the 4GB & 8GB DIMMs are bleeding edge of course. I see a
wide range of for them on Froogle. You will want your vendor of course
to pick memory is proper for the MB and pay whatever premium they'll
charge ontop of it. (Looking at the Tyan website, they only list 2GB
DIMMs as supported for their Quad Opteron MBs but whether that's because

those specs were done before >= 4GB DIMMs were available or an actual
hardware limitation, I dunno.)

Max wrote:

With a Quad Opteron (4 memory slots per CPU), you could put 64GB of

RAM

onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not quite as good

as

this. You really got me thinking now. 64Gb.. hm that would solve many
issues. what do you think the cost would be for such a beast ? Any

idea ?

Max

---------------------------(end of

broadcast)---------------------------

TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mike Rylander
Sent: Friday, January 28, 2005 4:02 AM
To: Max; PgSql General
Subject: Re: [GENERAL] Splitting queries across servers

On Thu, 27 Jan 2005 20:17:29 -0800, Max <maxdl@adelphia.net> wrote:

With a Quad Opteron (4 memory slots per CPU), you could put

64GB of RAM

onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not quite as good as
this. You really got me thinking now. 64Gb.. hm that would solve many
issues. what do you think the cost would be for such a beast ?

Any idea ?

Depending on the vendor and the storage subsystem, between $20k and
$50k, and if you start with less memory it will be much less. I can
attest to the fact that 4 x Opterons work VERY nicely. ;)

Thanks.

That would be quite an investment. Interesting. I wonder if we should put
windows or linux on it, since postgresql 8.0 works on windows.

What OS are you running on your quad opteron ?

Anyone's run some postgres performance tests between two machines same
config different OS ?

Max

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of William Yu
Sent: Friday, January 28, 2005 11:36 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Splitting queries across servers

Without memory, the prices are roughly:

4x846 = $5500
4x848 = $6500
4x850 = $8000

Memory costs would be:

16GB (1GB DIMMs) = $3000
32GB (2GB DIMMs) = $7500
64GB (4GB DIMMs) = $24000
128GB (8GB DIMMs) = $60000

The prices on the 4GB & 8GB DIMMs are bleeding edge of course. I see a
wide range of for them on Froogle. You will want your vendor of course
to pick memory is proper for the MB and pay whatever premium they'll
charge ontop of it. (Looking at the Tyan website, they only list 2GB
DIMMs as supported for their Quad Opteron MBs but whether that's because
those specs were done before >= 4GB DIMMs were available or an actual
hardware limitation, I dunno.)

Thank you! That's very thorough. hm... we're talking 35K then. That could be
in my budget.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Dann Corbit
Sent: Friday, January 28, 2005 12:01 PM
To: William Yu; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Splitting queries across servers

Suppose that you currently need 16 GB to cache everything now.
I would install (perhaps) 32 GB ram for the initial configuration.

Good point. Adding memory as I need it.

The price of memory drops exponentially, and so waiting for the price to
drop will give a much lower expense for the cost of the RAM.

The reason to double the ram is the expense of upgrading in terms of
labor and downtime for the computer. That can be very significant. So
if we double the ram, that should give one or (hopefully) two years
safety margin.

Downtime is a big deal, however I am planning on using replication with
pgpool.

If the database is expected to grow exponentially fast, then that is
another issue. In such a case, if it can be cost justified, put on the
largest memory volume that is possible given your financial limitations.

We can't really forecast the growing curve. My bet is that we have a short
term (6 months) need of 32 GB, so I'll just double that and it should give
us visibility for about a year. I hope!

I just realized I never asked that question: What is the maximum size of a
postgresql DB. Can it be anything ?

Max

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mike Rylander
Sent: Friday, January 28, 2005 4:02 AM
To: Max; PgSql General
Subject: Re: [GENERAL] Splitting queries across servers

On Thu, 27 Jan 2005 20:17:29 -0800, Max <maxdl@adelphia.net> wrote:

With a Quad Opteron (4 memory slots per CPU), you could put

64GB of RAM

onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not quite

as good as

this. You really got me thinking now. 64Gb.. hm that would solve many
issues. what do you think the cost would be for such a beast ?

Any idea ?

Depending on the vendor and the storage subsystem, between $20k and
$50k, and if you start with less memory it will be much less. I can
attest to the fact that 4 x Opterons work VERY nicely. ;)

Thanks.

That would be quite an investment. Interesting. I wonder if we should put
windows or linux on it, since postgresql 8.0 works on windows.

What OS are you running on your quad opteron ?

Anyone's run some postgres performance tests between two machines same
config different OS ?

Max

I want to mention that this last question is not invitation to flame about
windows against linux ;)
Personal preferences set aside, I am looking at performance consideration.

thanks!

Max

On Sat, 29 Jan 2005 09:55:15 -0800, Max <maxdl@adelphia.net> wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mike Rylander
Sent: Friday, January 28, 2005 4:02 AM
To: Max; PgSql General
Subject: Re: [GENERAL] Splitting queries across servers

On Thu, 27 Jan 2005 20:17:29 -0800, Max <maxdl@adelphia.net> wrote:

With a Quad Opteron (4 memory slots per CPU), you could put

64GB of RAM

onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not quite as good as
this. You really got me thinking now. 64Gb.. hm that would solve many
issues. what do you think the cost would be for such a beast ?

Any idea ?

Depending on the vendor and the storage subsystem, between $20k and
$50k, and if you start with less memory it will be much less. I can
attest to the fact that 4 x Opterons work VERY nicely. ;)

Thanks.

That would be quite an investment. Interesting. I wonder if we should put
windows or linux on it, since postgresql 8.0 works on windows.

To the best of my knowledge there is no 64-bit Windows for AMD64, but
I've not been forced to use Windows in 5 or 6 years...

What OS are you running on your quad opteron ?

We are using the x86-64 build of Gentoo Linux and compiling our own
copy of PG 8.0.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mike Rylander
Sent: Saturday, January 29, 2005 11:01 AM
To: Max
Cc: PgSql General
Subject: Re: [GENERAL] Splitting queries across servers

On Sat, 29 Jan 2005 09:55:15 -0800, Max <maxdl@adelphia.net> wrote:

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mike Rylander
Sent: Friday, January 28, 2005 4:02 AM
To: Max; PgSql General
Subject: Re: [GENERAL] Splitting queries across servers

On Thu, 27 Jan 2005 20:17:29 -0800, Max <maxdl@adelphia.net> wrote:

With a Quad Opteron (4 memory slots per CPU), you could put

64GB of RAM

onto a single machine using 4GB DIMMs in every slot.

We were talking about a similar solution today, but not

quite as good as

this. You really got me thinking now. 64Gb.. hm that would

solve many

issues. what do you think the cost would be for such a beast ?

Any idea ?

Depending on the vendor and the storage subsystem, between $20k and
$50k, and if you start with less memory it will be much less. I can
attest to the fact that 4 x Opterons work VERY nicely. ;)

Thanks.

That would be quite an investment. Interesting. I wonder if we

should put

windows or linux on it, since postgresql 8.0 works on windows.

To the best of my knowledge there is no 64-bit Windows for AMD64, but
I've not been forced to use Windows in 5 or 6 years...

Actually winXP and win server 2003 do support AMD64, it's intel 64 that's
not supported yet.

What OS are you running on your quad opteron ?

We are using the x86-64 build of Gentoo Linux and compiling our own
copy of PG 8.0.

Thanks for reminding me that 64bit translates to: recompile everything you
need!

I think this is exactly the choice of configuration we are going to make.
Someone just reminded me that windows and linux come down to the same
performance, but that the real overhead is on maintenance. It's true that
linux implies less downtime, and you can always upgrade. On top of that,
there's more granularity control on linux.

Thanks for the info.

Max

Max wrote:

That would be quite an investment. Interesting. I wonder if we should put
windows or linux on it, since postgresql 8.0 works on windows.

What OS are you running on your quad opteron ?

Well here's one thing that you can bank on. You can get 64-bit Linux now
while 64-bit Windows is still beta. (The word is that 64-bit Windows is
pretty stable except for the lack of drivers but I'd still feel queazy
about spending this much money to run a beta OS.)

And you absolutely want a 64-bit OS with that much memory. Even on our
development server with just 3GB of RAM, we see quite big jumps in
performance after moving from 32-bit Linux to 64-bit Linux. I'd hate to
think about 64GB of RAM being swapped in and out using PAE.

Thanks for reminding me that 64bit translates to: recompile everything
you
need!

I think this is exactly the choice of configuration we are going to make.
Someone just reminded me that windows and linux come down to the same
performance, but that the real overhead is on maintenance. It's true that
linux implies less downtime, and you can always upgrade. On top of that,
there's more granularity control on linux.

I won't say anything on linux vs windows performance (although I sure do
have an opinion), but simply keep in mind that postgres on window is young
and postgres on linux is vary mature ; therefore one is likely to have a
lot more performance refinements than the other.

Also Linux is not that harder to administer (especially gentoo). After
all, all you want is a DB server, you'll install few software. Not like if
you needed 50 different apps.

In your case recompiling everything is also a bonus because you'll be
sure that everything is 64-bit optimized down to the last driver.
I wonder how long your killer machine will take to compile the whole OS.
And Postgres too... could you post that ?

If you are concerned about security, you can compile everything with the
stack protector (Propolice SSP) enabled. It's extremely advisable. All the
other options like grsec et al are nice but require careful thinking,
propolice just needs to be activated. If you don't know what it is I'll
post docs.