Thread: Advantages and disadvantages of more than one dbserver on one server

Advantages and disadvantages of more than one dbserver on one server

From
"Daniel Seichter"
Date:
Hello,
I want to discuss a little bit about the following situation:
I only have one server for my databases.I had to develope on this server and
also had to manage several databases, so I can expect problems if I had to
restart postgreSQL, if I changed the logging.....
What do you think about this solution:
For each database I want to let run one server, and the different servers a
located in
/usr/local/pgsql-733-1    data-dir:    /db/pgsql-1
/usr/local/pgsql-733-2    data.......................

Is this a good idea? What problems can happen? I compile postgreSQL only
with the --prefix flag. What things I should do?
Each db got his own user, like postgres1, postgres2 or pgsql7331,.......

I sometimes need to log everything, but this is maybe once or twice a week,
but I can't restart the server during the day.

Daniel



Re: Advantages and disadvantages of more than one dbserver on one server

From
"Daniel Seichter"
Date:
The result of compiling, testing and let postgreSQL crash:

Now I know, that I only have advantages if I will let run each database with
its own session of pgsql. I can stop/start/restart(/kill) one session and
the other works as long as the server is not shutting down or something like
the worst case,...
I can setting up different users with different rights to each postgresql
and also can limit the possibility that users can have access to something
they shoudl not get have access.
Today memory isn't expensive so with 512MB I can be shure, every user on
this server might have enough to work without any timeout or waiting times
greater then 2 seconds.

Thanks to all which helped.

Daniel

P.S.: I hope the new release of postgreSQL for netware will be available
soon, so the same I can do without compiling.
--
postgreSQL on Netware - the red elephant
http://postgresql.dseichter.org



Re: Advantages and disadvantages of more than one dbserver

From
Sam Barnett-Cormack
Date:
On Thu, 3 Jul 2003, Daniel Seichter wrote:

> Today memory isn't expensive so with 512MB I can be shure, every user on
> this server might have enough to work without any timeout or waiting times
> greater then 2 seconds.

If the database is at all large, you really want more memory than that.
I have a really quite large DB, but I am *really* struggling with 1GB

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Re: Advantages and disadvantages of more than one dbserver

From
"scott.marlowe"
Date:
On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:

> On Thu, 3 Jul 2003, Daniel Seichter wrote:
>
> > Today memory isn't expensive so with 512MB I can be shure, every user on
> > this server might have enough to work without any timeout or waiting times
> > greater then 2 seconds.
>
> If the database is at all large, you really want more memory than that.
> I have a really quite large DB, but I am *really* struggling with 1GB

Note that running Postgresql on it's own server is a good idea if it's
currently sharing with an application server.  While some systems (Zope,
PHPNuke stuff) can run in little or no memory, most app servers eat up a
lot of memory that postgresql could use for cacheing.

We went from 512 Meg to 1.5 Gig and the change was tremendous.  That box
ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and
about 120 Megs of buffer routinely.  I'd heartily recommend even a jump
from just 1 to 1.5 gig if you can make it.

Eventually we're planning on moving our postgresql database to a dedicated
server.  Basically we'll move all the other stuff off of the current all
in one box and leave the database with an older, slightly slow machine
all to itself.  Funny I'm thinking of dual PIII-750s as slow.



Re: Advantages and disadvantages of more than one dbserver

From
"Daniel Seichter"
Date:
Hello Scott,
> Note that running Postgresql on it's own server is a good idea if it's
> currently sharing with an application server.  While some systems (Zope,
> PHPNuke stuff) can run in little or no memory, most app servers eat up a
> lot of memory that postgresql could use for cacheing.
We only use this server as postgreSQL server, and no apache, no mail no php
or something like this works on it.
And if the main database grows up to some GB of capacity, we had to migrate
to an new server.

BTW, Dual 750's will be great for me *g*

Daniel



Re: Advantages and disadvantages of more than one dbserver

From
Sam Barnett-Cormack
Date:
On Thu, 3 Jul 2003, scott.marlowe wrote:

> On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:
>
> > On Thu, 3 Jul 2003, Daniel Seichter wrote:
> >
> > > Today memory isn't expensive so with 512MB I can be shure, every user on
> > > this server might have enough to work without any timeout or waiting times
> > > greater then 2 seconds.
> >
> > If the database is at all large, you really want more memory than that.
> > I have a really quite large DB, but I am *really* struggling with 1GB
>
> We went from 512 Meg to 1.5 Gig and the change was tremendous.  That box
> ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and
> about 120 Megs of buffer routinely.  I'd heartily recommend even a jump
> from just 1 to 1.5 gig if you can make it.

I want either a seperate (high-ish spec, high memory) box, or to not
have to use my workstation as a server :) But I don't think my employers
will put any more money into this project until it's fully finished.

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Re: Advantages and disadvantages of more than one dbserver

From
"scott.marlowe"
Date:
On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:

> On Thu, 3 Jul 2003, scott.marlowe wrote:
>
> > On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:
> >
> > > On Thu, 3 Jul 2003, Daniel Seichter wrote:
> > >
> > > > Today memory isn't expensive so with 512MB I can be shure, every user on
> > > > this server might have enough to work without any timeout or waiting times
> > > > greater then 2 seconds.
> > >
> > > If the database is at all large, you really want more memory than that.
> > > I have a really quite large DB, but I am *really* struggling with 1GB
> >
> > We went from 512 Meg to 1.5 Gig and the change was tremendous.  That box
> > ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and
> > about 120 Megs of buffer routinely.  I'd heartily recommend even a jump
> > from just 1 to 1.5 gig if you can make it.
>
> I want either a seperate (high-ish spec, high memory) box, or to not
> have to use my workstation as a server :) But I don't think my employers
> will put any more money into this project until it's fully finished.

Well, always look at memory and drives.  If you can add memory to your
workstation is will be a much faster server.  Adding drives into a RAID1
1+0 or 5 set can provide a performance increase too.


Re: Advantages and disadvantages of more than one dbserver

From
Sam Barnett-Cormack
Date:
On Thu, 3 Jul 2003, scott.marlowe wrote:

> On Thu, 3 Jul 2003, Sam Barnett-Cormack wrote:
>
> > On Thu, 3 Jul 2003, scott.marlowe wrote:
> >
> > > We went from 512 Meg to 1.5 Gig and the change was tremendous.  That box
> > > ran Apache/Postgresql/auth_ldap/LDAP and now has 800 Meg of cache mem and
> > > about 120 Megs of buffer routinely.  I'd heartily recommend even a jump
> > > from just 1 to 1.5 gig if you can make it.
> >
> > I want either a seperate (high-ish spec, high memory) box, or to not
> > have to use my workstation as a server :) But I don't think my employers
> > will put any more money into this project until it's fully finished.
>
> Well, always look at memory and drives.  If you can add memory to your
> workstation is will be a much faster server.  Adding drives into a RAID1
> 1+0 or 5 set can provide a performance increase too.

We'll be looking at more performance boosts once the system is mature.
We already have the DB on a dedicated drive, on a dedicated controller.
Moving to a RAID 1 config with another 120GB drive would be good. The DB
on disk currently takes up about 35GB with only 1 quarters data, but
growth with longer timespans should be minimal as the DB is very well
normalised. RAID 5 not an option in the current box, as there are not
enough bays in the (crappy RM desktop) case, and we'd have to use
software RAID or buy a more epensive controller, and probably move to
SCSI, which would be expensive enough to justify buying a small
dedicated box instead, which would be better in many ways. When ones
workstation is also supposed to be a high-availability server, certain
things become difficult.

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Re: Advantages and disadvantages of more than one dbserver

From
"Daniel Seichter"
Date:
Hello Sam and Scott
> We'll be looking at more performance boosts once the system is mature.
> We already have the DB on a dedicated drive, on a dedicated controller.
> Moving to a RAID 1 config with another 120GB drive would be good. The DB
> on disk currently takes up about 35GB with only 1 quarters data, but
> growth with longer timespans should be minimal as the DB is very well
> normalised. RAID 5 not an option in the current box, as there are not
> enough bays in the (crappy RM desktop) case, and we'd have to use
> software RAID or buy a more epensive controller, and probably move to
> SCSI, which would be expensive enough to justify buying a small
> dedicated box instead, which would be better in many ways. When ones
> workstation is also supposed to be a high-availability server, certain
> things become difficult.
Is there any knowned rate in percent, how much faster a database will be if
I would put 1GB instead of 512MB into my server?
What RAID do you prefer? A 'simple' RAID1 should be enough or do you prefer
RAID5 or what else?

Daniel



Re: Advantages and disadvantages of more than one dbserver

From
"scott.marlowe"
Date:
On Fri, 4 Jul 2003, Daniel Seichter wrote:

> Hello Sam and Scott
> > We'll be looking at more performance boosts once the system is mature.
> > We already have the DB on a dedicated drive, on a dedicated controller.
> > Moving to a RAID 1 config with another 120GB drive would be good. The DB
> > on disk currently takes up about 35GB with only 1 quarters data, but
> > growth with longer timespans should be minimal as the DB is very well
> > normalised. RAID 5 not an option in the current box, as there are not
> > enough bays in the (crappy RM desktop) case, and we'd have to use
> > software RAID or buy a more epensive controller, and probably move to
> > SCSI, which would be expensive enough to justify buying a small
> > dedicated box instead, which would be better in many ways. When ones
> > workstation is also supposed to be a high-availability server, certain
> > things become difficult.
> Is there any knowned rate in percent, how much faster a database will be if
> I would put 1GB instead of 512MB into my server?
> What RAID do you prefer? A 'simple' RAID1 should be enough or do you prefer
> RAID5 or what else?

Well, it really depends on the size of your data set.  If you've got a 100
Meg database, then going from 512Meg to 1Gig is likely a waste.  As long
as your dataset is larger than how much your kernel can cache given the
current memory size, then you can see a performance gain from more memory.

RAID-1 versus RAID-5

Generally RAID-1 is faster, especially for a smaller number of concurrent
users.  As your number of concurrent processes rise, you can look at using
something like RAID 1+0 / 0+1 to increase the number of platters, or
RAID5.  While 1+0 / 0+1 will usually be faster, they will provide less
storage per disk used, whereas RAID5 will provide better economy (n-1
storage) but slightly lower write performance than many RAID1 or 1+0
setups.

RAID5 will, however, provide very good read performance under parallel
load, so for data warehouses or mostly read sites, RAID5 is a good choice.
For heavy transactional load look at RAID1 or 1+0.