pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPeformance Tuning Opterons/ Hard Disk Layout
23+ messages / 12 participants
[nested] [flat]
* Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 16:39 John Allgood <john@turbocorp.com>
0 siblings, 1 reply; 23+ messages in thread
From: John Allgood @ 2005-02-23 16:39 UTC (permalink / raw)
To: pgsql-performance
Hello All
I am setting up a hardware clustering solution. My hardware is Dual
Opteron 550 with 8GB ram. My external storage is a Kingston Fibre
channel Infostation. With 14 15000'k 36GB drives. The OS we are running
is Redhat ES 3.0. Clustering using Redhat Cluster Suite. Postgres
Version is Postgres 7.4.7. We will be setting up about 9 databases which
range in size from 100MB to 2.5GB on the config. The postgres
application is mostly read intensive. What would be the best way to
setup the hardrives on this server. Currently I have it partioned with 2
seperate raid 5 with 1 failover per raid. I have two database clusters
configured with a seperate postmaster running for each. Running two
postmasters seems like a pain but that is the only way I knew to
seperate the load. I am mostly concerned about disk IO and performance.
Is my current setup a good way to accomplish the best performance or
would it be better to use all the drives in one huge raid five with a
couple of failovers. I have looked around in the archives and found some
info but I would like to here about some of the configs other people are
running and how they have them setup.
Thanks
John Allgood - ESC
Systems Admin
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 18:03 Michael Adler <adler@pobox.com>
parent: John Allgood <john@turbocorp.com>
0 siblings, 1 reply; 23+ messages in thread
From: Michael Adler @ 2005-02-23 18:03 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance
On Wed, Feb 23, 2005 at 11:39:27AM -0500, John Allgood wrote:
> Hello All
>
> I am setting up a hardware clustering solution. My hardware is Dual
> Opteron 550 with 8GB ram. My external storage is a Kingston Fibre
> channel Infostation. With 14 15000'k 36GB drives. The OS we are running
> is Redhat ES 3.0. Clustering using Redhat Cluster Suite. Postgres
> Version is Postgres 7.4.7. We will be setting up about 9 databases which
> range in size from 100MB to 2.5GB on the config. The postgres
> application is mostly read intensive. What would be the best way to
> setup the hardrives on this server. Currently I have it partioned with 2
> seperate raid 5 with 1 failover per raid. I have two database clusters
> configured with a seperate postmaster running for each. Running two
> postmasters seems like a pain but that is the only way I knew to
> seperate the load. I am mostly concerned about disk IO and performance.
> Is my current setup a good way to accomplish the best performance or
> would it be better to use all the drives in one huge raid five with a
> couple of failovers. I have looked around in the archives and found some
> info but I would like to here about some of the configs other people are
> running and how they have them setup.
http://www.powerpostgresql.com/PerfList/
Consider a separate array for pg_xlog.
With tablespaces in 8.0, you can isolate much of the IO in a single
cluster.
-Mike Adler
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 18:26 Bruno Almeida do Lago <teolupus@gmail.com>
parent: Michael Adler <adler@pobox.com>
0 siblings, 4 replies; 23+ messages in thread
From: Bruno Almeida do Lago @ 2005-02-23 18:26 UTC (permalink / raw)
To: 'Michael Adler' <adler@pobox.com>; +Cc: pgsql-performance
Is there a real limit for max_connections? Here we've an Oracle server with
up to 1200 simultaneous conections over it!
"max_connections: exactly like previous versions, this needs to be set to
the actual number of simultaneous connections you expect to need. High
settings will require more shared memory (shared_buffers). As the
per-connection overhead, both from PostgreSQL and the host OS, can be quite
high, it's important to use connection pooling if you need to service a
large number of users. For example, 150 active connections on a medium-end
32-bit Linux server will consume significant system resources, and 600 is
about the limit."
C ya,
Bruno
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Michael Adler
Sent: Wednesday, February 23, 2005 3:04 PM
To: John Allgood
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Peformance Tuning Opterons/ Hard Disk Layout
On Wed, Feb 23, 2005 at 11:39:27AM -0500, John Allgood wrote:
> Hello All
>
> I am setting up a hardware clustering solution. My hardware is Dual
> Opteron 550 with 8GB ram. My external storage is a Kingston Fibre
> channel Infostation. With 14 15000'k 36GB drives. The OS we are running
> is Redhat ES 3.0. Clustering using Redhat Cluster Suite. Postgres
> Version is Postgres 7.4.7. We will be setting up about 9 databases which
> range in size from 100MB to 2.5GB on the config. The postgres
> application is mostly read intensive. What would be the best way to
> setup the hardrives on this server. Currently I have it partioned with 2
> seperate raid 5 with 1 failover per raid. I have two database clusters
> configured with a seperate postmaster running for each. Running two
> postmasters seems like a pain but that is the only way I knew to
> seperate the load. I am mostly concerned about disk IO and performance.
> Is my current setup a good way to accomplish the best performance or
> would it be better to use all the drives in one huge raid five with a
> couple of failovers. I have looked around in the archives and found some
> info but I would like to here about some of the configs other people are
> running and how they have them setup.
http://www.powerpostgresql.com/PerfList/
Consider a separate array for pg_xlog.
With tablespaces in 8.0, you can isolate much of the IO in a single
cluster.
-Mike Adler
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 18:35 Rod Taylor <pg@rbt.ca>
parent: Bruno Almeida do Lago <teolupus@gmail.com>
3 siblings, 1 reply; 23+ messages in thread
From: Rod Taylor @ 2005-02-23 18:35 UTC (permalink / raw)
To: Bruno Almeida do Lago <teolupus@gmail.com>; +Cc: Michael Adler <adler@pobox.com>; pgsql-performance
On Wed, 2005-02-23 at 15:26 -0300, Bruno Almeida do Lago wrote:
> Is there a real limit for max_connections? Here we've an Oracle server with
> up to 1200 simultaneous conections over it!
If you can reduce them by using something like pgpool between PostgreSQL
and the client, you'll save some headache. PostgreSQL did not perform as
well with a large number of idle connections and it does otherwise (last
time I tested was 7.4 though -- perhaps it's better now).
The kernel also starts to play a significant role with a high number of
connections. Some operating systems don't perform as well with a high
number of processes (process handling, scheduling, file handles, etc.).
I think you can do it without any technical issues, but you will
probably be happier with the result if you can hide idle connections
from the database machine.
--
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 18:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Almeida do Lago <teolupus@gmail.com>
3 siblings, 2 replies; 23+ messages in thread
From: Tom Lane @ 2005-02-23 18:37 UTC (permalink / raw)
To: Bruno Almeida do Lago <teolupus@gmail.com>; +Cc: 'Michael Adler' <adler@pobox.com>; pgsql-performance
"Bruno Almeida do Lago" <teolupus@gmail.com> writes:
> Is there a real limit for max_connections? Here we've an Oracle server with
> up to 1200 simultaneous conections over it!
[ shrug... ] If your machine has the beef to run 1200 simultaneous
queries, you can set max_connections to 1200.
The point of what you were quoting is that if you want to service
1200 concurrent users but you only expect maybe 100 simultaneously
active queries from them (and you have a database box that can only
service that many) then you want to put a connection pooler in
front of 100 backends, not try to start a backend for every user.
Oracle may handle this sort of thing differently, I dunno.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 18:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 23+ messages in thread
From: Tom Lane @ 2005-02-23 18:51 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Bruno Almeida do Lago <teolupus@gmail.com>; 'Michael Adler' <adler@pobox.com>; pgsql-performance
Rod Taylor <pg@rbt.ca> writes:
> The kernel also starts to play a significant role with a high number of
> connections. Some operating systems don't perform as well with a high
> number of processes (process handling, scheduling, file handles, etc.).
Right; the main problem with having lots more backends than you need is
that the idle ones still eat their share of RAM and open file handles.
A connection pooler uses relatively few resources per idle connection,
so it's a much better impedance match if you want to service lots of
connections that are mostly idle.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 19:15 John Allgood <john@turbocorp.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 23+ messages in thread
From: John Allgood @ 2005-02-23 19:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruno Almeida do Lago <teolupus@gmail.com>; 'Michael Adler' <adler@pobox.com>; pgsql-performance
I think maybe I didn't explain myself well enough. At most we will
service 200-250 connections across all the 9 databases mentioned. The
database we are building is for a trucking company. Each of the
databases represents a different division. With one master database that
everything is updated to. Most of the access to the database is by
simple queries. Most of the IO intensive stuff is done when revenue
reports are generated and when we have our month/year end processing.
All the trucking loads that are mark as delivered are transferred to our
master database during night time processing. All that will be handled
using custom scripts. Maybe I have given a better explanation of the
application. my biggest concern is how to partition the shared storage
for maximum performance. Is there a real benifit to having more that one
raid5 partition or am I wasting my time.
Thanks
John Allgood - ESC
Tom Lane wrote:
>"Bruno Almeida do Lago" <teolupus@gmail.com> writes:
>
>>Is there a real limit for max_connections? Here we've an Oracle server with
>>up to 1200 simultaneous conections over it!
>>
>
>[ shrug... ] If your machine has the beef to run 1200 simultaneous
>queries, you can set max_connections to 1200.
>
>The point of what you were quoting is that if you want to service
>1200 concurrent users but you only expect maybe 100 simultaneously
>active queries from them (and you have a database box that can only
>service that many) then you want to put a connection pooler in
>front of 100 backends, not try to start a backend for every user.
>
>Oracle may handle this sort of thing differently, I dunno.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 19:46 John Arbash Meinel <john@arbash-meinel.com>
parent: John Allgood <john@turbocorp.com>
1 sibling, 1 reply; 23+ messages in thread
From: John Arbash Meinel @ 2005-02-23 19:46 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruno Almeida do Lago <teolupus@gmail.com>; 'Michael Adler' <adler@pobox.com>; pgsql-performance
John Allgood wrote:
> I think maybe I didn't explain myself well enough. At most we will
> service 200-250 connections across all the 9 databases mentioned. The
> database we are building is for a trucking company. Each of the
> databases represents a different division. With one master database
> that everything is updated to. Most of the access to the database is
> by simple queries. Most of the IO intensive stuff is done when revenue
> reports are generated and when we have our month/year end processing.
> All the trucking loads that are mark as delivered are transferred to
> our master database during night time processing. All that will be
> handled using custom scripts. Maybe I have given a better explanation
> of the application. my biggest concern is how to partition the shared
> storage for maximum performance. Is there a real benifit to having
> more that one raid5 partition or am I wasting my time.
>
> Thanks
>
> John Allgood - ESC
If you read the general advice statements, it's actually better to not
use raid5, but to use raid10 (striping and mirroring). Simply because
raid5 writing is quite poor.
Also, if you have the disks, the next best improvement is to move
pg_xlog onto it's own set of disks. I think that gets as much as 25%
improvement by itself. pg_xlog is an append process, which must complete
before the actual data gets updated, so giving it it's own set of
spindles reduces seek time, and lets the log be written quickly.
I think there is even some benefit to making pg_xlog be a solid state
disk, as it doesn't have to be huge, but having high I/O rates can
remove it as a bottleneck. (I'm not positive how large pg_xlog gets, but
it is probably small compared with the total db size, and I think it can
be flushed periodically as transactions are completed.)
I'm not sure what you are considering "shared storage". Are you thinking
that all the machines will be mounting a remote drive for writing the
DB? They should all have their own local copy (multiple masters on the
same database is not supported).
I think it is possible to get better performance by having more raid
systems. But it is application dependent. If you know that you have 2
tables that are being updated often and independently, then having each
one on it's own raid would allow better concurrency.
But it sounds like in your app you get concurrency by having a bunch of
remote databases, which then do bulk updates on the master database. I
think if you are careful to have each of the remote dbs update the
master at a slightly different time, you could probably get very good
transaction rates.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 19:50 Michael Adler <adler@pobox.com>
parent: John Allgood <john@turbocorp.com>
1 sibling, 0 replies; 23+ messages in thread
From: Michael Adler @ 2005-02-23 19:50 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance
On Wed, Feb 23, 2005 at 02:15:52PM -0500, John Allgood wrote:
> using custom scripts. Maybe I have given a better explanation of the
> application. my biggest concern is how to partition the shared storage
> for maximum performance. Is there a real benifit to having more that one
> raid5 partition or am I wasting my time.
I think the simplest and most generic solution would be to put the OS
and pg_xlog on a RAID 1 pair and dedicate the rest of the drives to
RAID 5 or RAID 1+0 (striped set of mirrors) array.
Depending on the nature of your work, you may get better performance
by placing individual tables/indices on dedicated spindles for
parallel access.
-Mike Adler
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 20:05 John Allgood <john@turbocorp.com>
parent: John Arbash Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 23+ messages in thread
From: John Allgood @ 2005-02-23 20:05 UTC (permalink / raw)
To: John Arbash Meinel <john@arbash-meinel.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruno Almeida do Lago <teolupus@gmail.com>; 'Michael Adler' <adler@pobox.com>; pgsql-performance
This some good info. The type of attached storage is a Kingston 14 bay
Fibre Channel Infostation. I have 14 36GB 15,000 RPM drives. I think the
way it is being explained that I should build a mirror with two disk for
the pg_xlog and the striping and mirroring the rest and put all my
databases into one cluster. Also I might mention that I am running
clustering using Redhat Clustering Suite.
John Arbash Meinel wrote:
> John Allgood wrote:
>
>> I think maybe I didn't explain myself well enough. At most we will
>> service 200-250 connections across all the 9 databases mentioned. The
>> database we are building is for a trucking company. Each of the
>> databases represents a different division. With one master database
>> that everything is updated to. Most of the access to the database is
>> by simple queries. Most of the IO intensive stuff is done when revenue
>> reports are generated and when we have our month/year end processing.
>> All the trucking loads that are mark as delivered are transferred to
>> our master database during night time processing. All that will be
>> handled using custom scripts. Maybe I have given a better explanation
>> of the application. my biggest concern is how to partition the shared
>> storage for maximum performance. Is there a real benifit to having
>> more that one raid5 partition or am I wasting my time.
>>
>> Thanks
>>
>> John Allgood - ESC
>
> If you read the general advice statements, it's actually better to not
> use raid5, but to use raid10 (striping and mirroring). Simply because
> raid5 writing is quite poor.
>
> Also, if you have the disks, the next best improvement is to move
> pg_xlog onto it's own set of disks. I think that gets as much as 25%
> improvement by itself. pg_xlog is an append process, which must complete
> before the actual data gets updated, so giving it it's own set of
> spindles reduces seek time, and lets the log be written quickly.
> I think there is even some benefit to making pg_xlog be a solid state
> disk, as it doesn't have to be huge, but having high I/O rates can
> remove it as a bottleneck. (I'm not positive how large pg_xlog gets, but
> it is probably small compared with the total db size, and I think it can
> be flushed periodically as transactions are completed.)
>
> I'm not sure what you are considering "shared storage". Are you thinking
> that all the machines will be mounting a remote drive for writing the
> DB? They should all have their own local copy (multiple masters on the
> same database is not supported).
>
> I think it is possible to get better performance by having more raid
> systems. But it is application dependent. If you know that you have 2
> tables that are being updated often and independently, then having each
> one on it's own raid would allow better concurrency.
>
> But it sounds like in your app you get concurrency by having a bunch of
> remote databases, which then do bulk updates on the master database. I
> think if you are careful to have each of the remote dbs update the
> master at a slightly different time, you could probably get very good
> transaction rates.
>
> John
> =:->
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 20:24 John Arbash Meinel <john@arbash-meinel.com>
parent: John Allgood <john@turbocorp.com>
0 siblings, 1 reply; 23+ messages in thread
From: John Arbash Meinel @ 2005-02-23 20:24 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance
John Allgood wrote:
> This some good info. The type of attached storage is a Kingston 14 bay
> Fibre Channel Infostation. I have 14 36GB 15,000 RPM drives. I think
> the way it is being explained that I should build a mirror with two
> disk for the pg_xlog and the striping and mirroring the rest and put
> all my databases into one cluster. Also I might mention that I am
> running clustering using Redhat Clustering Suite.
So are these 14-disks supposed to be shared across all of your 9 databases?
It seems to me that you have a few architectural issues here.
First, you can't really have 2 masters writing to the same disk array.
I'm not sure if Redhat Clustering gets around this. But second is that
you can't run 2 postgres engines on the same database. Postgres doesn't
support a clustered setup. There are too many issues with concurancy and
keeping everyone in sync.
Since you seem to be okay with having a bunch of smaller localized
databases, which update a master database 1/day, I would think you would
want hardware to go something like this.
1 master server, at least dual opteron with access to lots of disks
(likely the whole 14 if you can get away with it). Put 2 as a RAID1 for
the OS, 4 as a RAID10 for pg_xlog, and then the other 8 as RAID10 for
the rest of the database.
8-9 other servers, these don't need to be as powerful, since they are
local domains. Probably a 4-disk RAID10 for the OS and pg_xlog is plenty
good, and whatever extra disks you can get for the local database.
The master database holds all information for all domains, but the other
databases only hold whatever is the local information. Every night your
script sequences through the domain databases one-by-one, updating the
master database, and synchronizing whatever data is necesary back to the
local domain. I would guess that this script could actually just
continually run, going to each local db in turn, but you may want
nighttime only updating depending on what kind of load they have.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 20:41 John Allgood <john@turbocorp.com>
parent: John Arbash Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 23+ messages in thread
From: John Allgood @ 2005-02-23 20:41 UTC (permalink / raw)
To: John Arbash Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
Here is a summary about the cluster suite from redhat. All 9 databases
will be on the primary server the secondary server I have is the
failover. They don't actually share the partitions at the same time.
When you have some type of failure the backup server takes over. Once
you setup the hardware and install the clustering software. You then
setup a service "ie postgres" and then you tell it what harddrive you
will be using. /dev/sde1 and the clustering software takes care of
starting and stopping the postgres database.
Cluster Manager
The Cluster Manager feature of Red Hat Cluster Suite provides an
application failover infrastructure that can be used by a wide range of
applications, including:
* Most custom and mainstream commercial applications
* File and print serving
* Databases and database applications
* Messaging applications
* Internet and open source application
With Cluster Manager, these applications can be deployed in high
availability configurations so that they are always operational—bringing
"scale-out" capabilities to enterprise Linux deployments.
For high-volume open source applications, such as NFS, Samba, and
Apache, Cluster Manager provides a complete ready-to-use failover
solution. For most other applications, customers can create custom
failover scripts using provided templates. Red Hat Professional Services
can provide custom Cluster Manager deployment services where required.
Features
* Support for up to eight nodes: Allows high availability to be
provided for multiple applications simultaneously.
* NFS/CIFS Failover: Supports highly available file serving in Unix
and Windows environments.
* Fully shared storage subsystem: All cluster members have access to
the same storage.
* Comprehensive Data Integrity guarantees: Uses the latest I/O
barrier technology, such as programmable power switches and
watchdog timers.
* SCSI and Fibre Channel support: Cluster Manager configurations can
be deployed using latest SCSI and Fibre Channel technology.
Multi-terabyte configurations can readily be made highly available.
* Service failover: Cluster Manager not only ensures hardware
shutdowns or failures are detected and recovered from
automatically, but also will monitor your applications to ensure
they are running correctly, and will restart them automatically if
they fail.
John Arbash Meinel wrote:
> John Allgood wrote:
>
>> This some good info. The type of attached storage is a Kingston 14 bay
>> Fibre Channel Infostation. I have 14 36GB 15,000 RPM drives. I think
>> the way it is being explained that I should build a mirror with two
>> disk for the pg_xlog and the striping and mirroring the rest and put
>> all my databases into one cluster. Also I might mention that I am
>> running clustering using Redhat Clustering Suite.
>
>
> So are these 14-disks supposed to be shared across all of your 9
> databases?
> It seems to me that you have a few architectural issues here.
>
> First, you can't really have 2 masters writing to the same disk array.
> I'm not sure if Redhat Clustering gets around this. But second is that
> you can't run 2 postgres engines on the same database. Postgres doesn't
> support a clustered setup. There are too many issues with concurancy and
> keeping everyone in sync.
>
> Since you seem to be okay with having a bunch of smaller localized
> databases, which update a master database 1/day, I would think you would
> want hardware to go something like this.
>
> 1 master server, at least dual opteron with access to lots of disks
> (likely the whole 14 if you can get away with it). Put 2 as a RAID1 for
> the OS, 4 as a RAID10 for pg_xlog, and then the other 8 as RAID10 for
> the rest of the database.
>
> 8-9 other servers, these don't need to be as powerful, since they are
> local domains. Probably a 4-disk RAID10 for the OS and pg_xlog is plenty
> good, and whatever extra disks you can get for the local database.
>
> The master database holds all information for all domains, but the other
> databases only hold whatever is the local information. Every night your
> script sequences through the domain databases one-by-one, updating the
> master database, and synchronizing whatever data is necesary back to the
> local domain. I would guess that this script could actually just
> continually run, going to each local db in turn, but you may want
> nighttime only updating depending on what kind of load they have.
>
> John
> =:->
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 21:01 John Arbash Meinel <john@arbash-meinel.com>
parent: John Allgood <john@turbocorp.com>
0 siblings, 0 replies; 23+ messages in thread
From: John Arbash Meinel @ 2005-02-23 21:01 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance
John Allgood wrote:
> Here is a summary about the cluster suite from redhat. All 9 databases
> will be on the primary server the secondary server I have is the
> failover. They don't actually share the partitions at the same time.
> When you have some type of failure the backup server takes over. Once
> you setup the hardware and install the clustering software. You then
> setup a service "ie postgres" and then you tell it what harddrive you
> will be using. /dev/sde1 and the clustering software takes care of
> starting and stopping the postgres database.
>
Okay, I misunderstood your hardware. So you actually only have 1
machine, with a second machine as a potential rollover. But all
transactions occur on the same hardware, even if is a separate
"database". I was thinking these were alternate machines.
So my first question is why are you partitioning into a separate
database, and then updating the master one at night. Since everything is
restricted to the same machine, why not just have everything performed
on the master?
However, sticking with your arrangement, it would seem that you might be
able to get some extra performance if each database is on it's own raid,
since you are fairly likely to have 2 transactions occuring at the same
time, that don't affect eachother (since you wouldn't have any foreign
keys, etc on 2 separate databases.)
But I think the basic OS RAID1, pg_xlog RAID10, database RAID10 is still
a good separation of disks. And probably would help you maximize your
throughput.
I can't say too much about how the Cluster failover stuff will work with
postgres. But as long as one is completely shutdown before the next is
started, and they are both running binary compatible versions of
postgres, it seems like it would be fine. Not much different from having
a second machine that is sitting turned off, which you turn on when the
first one fails.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-23 21:02 Josh Berkus <josh@agliodbs.com>
parent: Bruno Almeida do Lago <teolupus@gmail.com>
3 siblings, 1 reply; 23+ messages in thread
From: Josh Berkus @ 2005-02-23 21:02 UTC (permalink / raw)
To: Bruno Almeida do Lago <teolupus@gmail.com>; +Cc: pgsql-performance
Bruno,
> For example, 150 active connections on a medium-end
> 32-bit Linux server will consume significant system resources, and 600 is
> about the limit."
That, is, "is about the limit for a medium-end 32-bit Linux server". Sorry
if the implication didn't translate well. If you use beefier hardware, of
course, you can manage more connections; personally I've never needed more
than 1000, even on a web site that gets 100,000 d.u.v.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-24 03:24 Christopher Browne <cbbrowne@acm.org>
parent: Bruno Almeida do Lago <teolupus@gmail.com>
3 siblings, 0 replies; 23+ messages in thread
From: Christopher Browne @ 2005-02-24 03:24 UTC (permalink / raw)
To: pgsql-performance
teolupus@gmail.com ("Bruno Almeida do Lago") wrote:
> Is there a real limit for max_connections? Here we've an Oracle server with
> up to 1200 simultaneous conections over it!
>
> "max_connections: exactly like previous versions, this needs to be set to
> the actual number of simultaneous connections you expect to need. High
> settings will require more shared memory (shared_buffers). As the
> per-connection overhead, both from PostgreSQL and the host OS, can be quite
> high, it's important to use connection pooling if you need to service a
> large number of users. For example, 150 active connections on a medium-end
> 32-bit Linux server will consume significant system resources, and 600 is
> about the limit."
Right now, I have an Opteron box with:
a) A load average of about 0.1, possibly less ;-), and
b) 570 concurrent connections.
Having so connections is something of a "fool's errand," as it really
is ludicrously unnecessary, but I wouldn't be too afraid of having
1000 connections on that box, as long as they're being used for
relatively small transactions.
You can, of course, kill performance on any not-outrageously-large
system if a few of those users are doing big queries...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://cbbrowne.com/info/slony.html
I've had a perfectly wonderful evening. But this wasn't it.
-- Groucho Marx
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-24 08:28 Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 0 replies; 23+ messages in thread
From: Vig, Sandor (G/FI-2) @ 2005-02-24 08:28 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance
Hi,
RAID1 (mirroring) and RAID1+0 (striping and mirroring) seems to
be a good choice. (RAID 5 is for saving money, but it doesn't have a
good performance)
I suggest you to make a different array for:
- Operating system
- db logs
- each database
It is a little bit of "wasting" disk storage, but it has the best
performance.
Forget RAID 5. If your fibre channel card and the external storage exceeds
their throughput limits you should consider to implement +1 fibre channel
and/or +1 external storage unit. (If you had such a load)
But it is only the hardware. The database structure, and the application
logic is the other 50% of the performance...
Bye
Vig Sándor
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of John Allgood
Sent: Wednesday, February 23, 2005 9:42 PM
To: John Arbash Meinel
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Peformance Tuning Opterons/ Hard Disk Layout
Here is a summary about the cluster suite from redhat. All 9 databases
will be on the primary server the secondary server I have is the
failover. They don't actually share the partitions at the same time.
When you have some type of failure the backup server takes over. Once
you setup the hardware and install the clustering software. You then
setup a service "ie postgres" and then you tell it what harddrive you
will be using. /dev/sde1 and the clustering software takes care of
starting and stopping the postgres database.
Cluster Manager
The Cluster Manager feature of Red Hat Cluster Suite provides an
application failover infrastructure that can be used by a wide range of
applications, including:
* Most custom and mainstream commercial applications
* File and print serving
* Databases and database applications
* Messaging applications
* Internet and open source application
With Cluster Manager, these applications can be deployed in high
availability configurations so that they are always operational—bringing
"scale-out" capabilities to enterprise Linux deployments.
For high-volume open source applications, such as NFS, Samba, and
Apache, Cluster Manager provides a complete ready-to-use failover
solution. For most other applications, customers can create custom
failover scripts using provided templates. Red Hat Professional Services
can provide custom Cluster Manager deployment services where required.
Features
* Support for up to eight nodes: Allows high availability to be
provided for multiple applications simultaneously.
* NFS/CIFS Failover: Supports highly available file serving in Unix
and Windows environments.
* Fully shared storage subsystem: All cluster members have access to
the same storage.
* Comprehensive Data Integrity guarantees: Uses the latest I/O
barrier technology, such as programmable power switches and
watchdog timers.
* SCSI and Fibre Channel support: Cluster Manager configurations can
be deployed using latest SCSI and Fibre Channel technology.
Multi-terabyte configurations can readily be made highly available.
* Service failover: Cluster Manager not only ensures hardware
shutdowns or failures are detected and recovered from
automatically, but also will monitor your applications to ensure
they are running correctly, and will restart them automatically if
they fail.
John Arbash Meinel wrote:
> John Allgood wrote:
>
>> This some good info. The type of attached storage is a Kingston 14 bay
>> Fibre Channel Infostation. I have 14 36GB 15,000 RPM drives. I think
>> the way it is being explained that I should build a mirror with two
>> disk for the pg_xlog and the striping and mirroring the rest and put
>> all my databases into one cluster. Also I might mention that I am
>> running clustering using Redhat Clustering Suite.
>
>
> So are these 14-disks supposed to be shared across all of your 9
> databases?
> It seems to me that you have a few architectural issues here.
>
> First, you can't really have 2 masters writing to the same disk array.
> I'm not sure if Redhat Clustering gets around this. But second is that
> you can't run 2 postgres engines on the same database. Postgres doesn't
> support a clustered setup. There are too many issues with concurancy and
> keeping everyone in sync.
>
> Since you seem to be okay with having a bunch of smaller localized
> databases, which update a master database 1/day, I would think you would
> want hardware to go something like this.
>
> 1 master server, at least dual opteron with access to lots of disks
> (likely the whole 14 if you can get away with it). Put 2 as a RAID1 for
> the OS, 4 as a RAID10 for pg_xlog, and then the other 8 as RAID10 for
> the rest of the database.
>
> 8-9 other servers, these don't need to be as powerful, since they are
> local domains. Probably a 4-disk RAID10 for the OS and pg_xlog is plenty
> good, and whatever extra disks you can get for the local database.
>
> The master database holds all information for all domains, but the other
> databases only hold whatever is the local information. Every night your
> script sequences through the domain databases one-by-one, updating the
> master database, and synchronizing whatever data is necesary back to the
> local domain. I would guess that this script could actually just
> continually run, going to each local db in turn, but you may want
> nighttime only updating depending on what kind of load they have.
>
> John
> =:->
>
---------------------------(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
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-24 13:28 Bruno Almeida do Lago <teolupus@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 23+ messages in thread
From: Bruno Almeida do Lago @ 2005-02-24 13:28 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
No problems my friend :P
I thought that since the beginning and just sent the e-mail to confirm if
there was no software limitation.
Best Wishes,
Bruno Almeida do Lago
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Wednesday, February 23, 2005 6:02 PM
To: Bruno Almeida do Lago
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Peformance Tuning Opterons/ Hard Disk Layout
Bruno,
> For example, 150 active connections on a medium-end
> 32-bit Linux server will consume significant system resources, and 600 is
> about the limit."
That, is, "is about the limit for a medium-end 32-bit Linux server".
Sorry
if the implication didn't translate well. If you use beefier hardware, of
course, you can manage more connections; personally I've never needed more
than 1000, even on a web site that gets 100,000 d.u.v.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 23+ messages in thread
* Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-24 18:31 John Allgood <john@turbocorp.com>
0 siblings, 3 replies; 23+ messages in thread
From: John Allgood @ 2005-02-24 18:31 UTC (permalink / raw)
To: pgsql-performance; john@arbash-meinel.com
Hello Again
In the below statement you mention putting each database on its own raid
mirror.
"However, sticking with your arrangement, it would seem that you might be
able to get some extra performance if each database is on it's own raid,
since you are fairly likely to have 2 transactions occuring at the same
time, that don't affect eachother (since you wouldn't have any foreign
keys, etc on 2 separate databases.)"
That would take alot of disk drives to accomplish. I was thinking maybe
putting three or four databases on each raid and dividing the heaviest
used databases on each mirrored set. And for each of these sets have its
own mirror for pg_xlog. My question is what is the best way to setup
postgres databases on different disks. I have setup multiple postmasters
on this system as a test. The only problem was configuring each
databases "ie postgresql.conf, pg_hba.conf". Is there anyway in
postgres to have everything in one cluster and have it seperated onto
multiple drives. Here is a example of what is was thinking about.
MIRROR1 - Database Group 1
MIRROR2 - pg_xlog for database group 1
MIRROR3 - Database Group 2
MIRROR4 - pg_xlog for database group 2
MIRROR5 - Database Group 3
MIRROR6 - pg_xlog for database group 3
This will take about 12 disk drives. I have a 14 bay Storage Bay I can
use two of the drives for hotspare's.
Thanks
John Allgood - ESC
Systems Administrator
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-24 19:12 Joel Fradkin <jfradkin@wazagua.com>
parent: John Allgood <john@turbocorp.com>
2 siblings, 0 replies; 23+ messages in thread
From: Joel Fradkin @ 2005-02-24 19:12 UTC (permalink / raw)
To: 'John Allgood' <john@turbocorp.com>; pgsql-performance; john@arbash-meinel.com
I am no expert, but have been asking them a bunch and I think your missing a
key concept.
The data is best on several drives.
I could be completely off, but if I understood (I just finished doing the
same kind of thing minus several databases) you want your WAL on fast drives
in raid 1 and your data (as many drives as you can use) on raid 10 (can be
slower drives , but I saw you already have a bunch of 15k drives).
So you may get best performance just using one database rather then several
smaller ones on mirrored data drives. Keep in mind if you go with ES4 (I am
using AS4) and postgres 8 you can add spindles and move hard hit tables to
their own spindle.
Again I am no expert; just thought I would echo what I was informed.
I ended up using 2 15k drives in raid 1 for my WAL and 4 10k drives for my
data in raid 10. I ended up using links to these from the original install
of postgres on the raid 5, 4 15k drives inside the server itself. I believe
this gives me three separate raid arrays for my install with logs and such
on the raid 5, data on the raid 10 and wal on the raid 1. I am in the
testing and conversion phase and have found it very fast. I used a 4
processor Dell 6550, but think from what I have been told your computer
would have been a better choice (CPU wise). I am not using fibre but do have
a 14 drive powervault which I split to have the 15k's on one side and the
10k's on the other. So I am using both channels of the controller. I have
been told for me to get best performance I should add as many 10k drives to
my data array as I can (but this was all I had in my budget). I have room
for 3 more drives on that side of the powervault.
Best of luck on your project.
Joel
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-24 19:40 John Arbash Meinel <john@arbash-meinel.com>
parent: John Allgood <john@turbocorp.com>
2 siblings, 0 replies; 23+ messages in thread
From: John Arbash Meinel @ 2005-02-24 19:40 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance
John Allgood wrote:
> Hello Again
>
> In the below statement you mention putting each database on its own
> raid mirror.
>
> "However, sticking with your arrangement, it would seem that you might be
> able to get some extra performance if each database is on it's own raid,
> since you are fairly likely to have 2 transactions occuring at the same
> time, that don't affect eachother (since you wouldn't have any foreign
> keys, etc on 2 separate databases.)"
>
> That would take alot of disk drives to accomplish. I was thinking
> maybe putting three or four databases on each raid and dividing the
> heaviest used databases on each mirrored set. And for each of these
> sets have its own mirror for pg_xlog. My question is what is the best
> way to setup postgres databases on different disks. I have setup
> multiple postmasters on this system as a test. The only problem was
> configuring each databases "ie postgresql.conf, pg_hba.conf". Is
> there anyway in postgres to have everything in one cluster and have it
> seperated onto multiple drives. Here is a example of what is was
> thinking about.
>
I think this is something that you would have to try and see what works.
My first feeling is that 8-disks in RAID10 is better than 4 sets of RAID1.
> MIRROR1 - Database Group 1
> MIRROR2 - pg_xlog for database group 1
> MIRROR3 - Database Group 2
> MIRROR4 - pg_xlog for database group 2
> MIRROR5 - Database Group 3
> MIRROR6 - pg_xlog for database group 3
>
> This will take about 12 disk drives. I have a 14 bay Storage Bay I can
> use two of the drives for hotspare's.
>
I would have all of them in 1 database cluster, which means they are all
served by the same postgres daemon. Which I believe means that they all
use the same pg_xlog. That means you only need 1 raid for pg_xlog,
though I would make it a 4-drive RAID10. (RAID1 is redundant, but
actually slower on writes, you need the 0 to speed up reading/writing, I
could be wrong).
I believe you can still split each database onto it's own raid later on
if you find that you need to.
So this is my proposal 1:
OS RAID (sounds like this is not in the Storage Bay).
4-drives RAID10 pg_xlog
8-drives RAID10 database cluster
2-drives Hot spares / RAID1
If you feel like you want to partition your databases, you could also do
proposal 2:
4-drives RAID10 pg_xlog
4-drives RAID10 databases master + 1-4
4-drives RAID10 databases 5-9
2-drives hotspare / RAID1
If you think partitioning is better than striping, you could do proposal 3:
4-drives RAID10 pg_xlog
2-drives RAID1 master database
2-drives RAID1 databases 1,2,3
2-drives RAID1 databases 4,5
2-drives RAID1 databases 6,7
2-drives RAID1 databases 8,9
There are certainly a lot of potential arrangements here, and it's not
like I've tried a lot of them. pg_xlog seems like a big enough
bottleneck that it would be good to put it on it's own RAID10, to make
it as fast as possible.
It also depends a lot on whether you will be write heavy/read heavy,
etc. RAID5 works quite well for reading, very poor for writing. But if
the only reason to have the master database is to perform read heavy
queries, and all the writing is done at night in bulk fashion with
careful tuning to avoid saturation, then maybe you would want to put the
master database on a RAID5 so that you can get extra disk space.
You could do proposal 4:
4-drive RAID10 pg_xlog
4-drive RAID5 master db
2-drive RAID1 dbs 1-3
2-drive RAID1 dbs 4-6
2-drive RAID1 dbs 7-9
You might also do some testing and find that pg_xlog doesn't deserve
it's own 4 disks, and they would be better off in the bulk tables.
Unfortunately a lot of this would come down to performance testing on
your dataset, with a real data load. Which isn't very easy to do.
I personally like the simplicity of proposal 1.
John
=:->
>
> Thanks
>
> John Allgood - ESC
> Systems Administrator
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-25 11:19 Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
0 siblings, 0 replies; 23+ messages in thread
From: Vig, Sandor (G/FI-2) @ 2005-02-25 11:19 UTC (permalink / raw)
To: pgsql-performance
-huh-
A lot of High-Tech ideas. But there is another way:
Simply measure the current IO load (pro DB if you must),
make an estimation how it could change in the future
(max. 3 years) and make a worst case scenario.
Than you should make a new array each time the
worst case scenario hits the IO bottleneck of your
config. (I mean the random read/write bandwith of
a raid array) than make so many raid arrays you
need. It's just that simple. :-)))
You should/must redesign it in every 3 years, that's for sure.
Vig Sándor
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of John Arbash
Meinel
Sent: Thursday, February 24, 2005 8:41 PM
To: John Allgood
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Peformance Tuning Opterons/ Hard Disk Layout
John Allgood wrote:
> Hello Again
>
> In the below statement you mention putting each database on its own
> raid mirror.
>
> "However, sticking with your arrangement, it would seem that you might be
> able to get some extra performance if each database is on it's own raid,
> since you are fairly likely to have 2 transactions occuring at the same
> time, that don't affect eachother (since you wouldn't have any foreign
> keys, etc on 2 separate databases.)"
>
> That would take alot of disk drives to accomplish. I was thinking
> maybe putting three or four databases on each raid and dividing the
> heaviest used databases on each mirrored set. And for each of these
> sets have its own mirror for pg_xlog. My question is what is the best
> way to setup postgres databases on different disks. I have setup
> multiple postmasters on this system as a test. The only problem was
> configuring each databases "ie postgresql.conf, pg_hba.conf". Is
> there anyway in postgres to have everything in one cluster and have it
> seperated onto multiple drives. Here is a example of what is was
> thinking about.
>
I think this is something that you would have to try and see what works.
My first feeling is that 8-disks in RAID10 is better than 4 sets of RAID1.
> MIRROR1 - Database Group 1
> MIRROR2 - pg_xlog for database group 1
> MIRROR3 - Database Group 2
> MIRROR4 - pg_xlog for database group 2
> MIRROR5 - Database Group 3
> MIRROR6 - pg_xlog for database group 3
>
> This will take about 12 disk drives. I have a 14 bay Storage Bay I can
> use two of the drives for hotspare's.
>
I would have all of them in 1 database cluster, which means they are all
served by the same postgres daemon. Which I believe means that they all
use the same pg_xlog. That means you only need 1 raid for pg_xlog,
though I would make it a 4-drive RAID10. (RAID1 is redundant, but
actually slower on writes, you need the 0 to speed up reading/writing, I
could be wrong).
I believe you can still split each database onto it's own raid later on
if you find that you need to.
So this is my proposal 1:
OS RAID (sounds like this is not in the Storage Bay).
4-drives RAID10 pg_xlog
8-drives RAID10 database cluster
2-drives Hot spares / RAID1
If you feel like you want to partition your databases, you could also do
proposal 2:
4-drives RAID10 pg_xlog
4-drives RAID10 databases master + 1-4
4-drives RAID10 databases 5-9
2-drives hotspare / RAID1
If you think partitioning is better than striping, you could do proposal 3:
4-drives RAID10 pg_xlog
2-drives RAID1 master database
2-drives RAID1 databases 1,2,3
2-drives RAID1 databases 4,5
2-drives RAID1 databases 6,7
2-drives RAID1 databases 8,9
There are certainly a lot of potential arrangements here, and it's not
like I've tried a lot of them. pg_xlog seems like a big enough
bottleneck that it would be good to put it on it's own RAID10, to make
it as fast as possible.
It also depends a lot on whether you will be write heavy/read heavy,
etc. RAID5 works quite well for reading, very poor for writing. But if
the only reason to have the master database is to perform read heavy
queries, and all the writing is done at night in bulk fashion with
careful tuning to avoid saturation, then maybe you would want to put the
master database on a RAID5 so that you can get extra disk space.
You could do proposal 4:
4-drive RAID10 pg_xlog
4-drive RAID5 master db
2-drive RAID1 dbs 1-3
2-drive RAID1 dbs 4-6
2-drive RAID1 dbs 7-9
You might also do some testing and find that pg_xlog doesn't deserve
it's own 4 disks, and they would be better off in the bulk tables.
Unfortunately a lot of this would come down to performance testing on
your dataset, with a real data load. Which isn't very easy to do.
I personally like the simplicity of proposal 1.
John
=:->
>
> Thanks
>
> John Allgood - ESC
> Systems Administrator
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from any
computer.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-25 16:20 Jim C. Nasby <decibel@decibel.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Jim C. Nasby @ 2005-02-25 16:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruno Almeida do Lago <teolupus@gmail.com>; Michael Adler <adler@pobox.com>; pgsql-performance
On Wed, Feb 23, 2005 at 01:37:28PM -0500, Tom Lane wrote:
> "Bruno Almeida do Lago" <teolupus@gmail.com> writes:
> > Is there a real limit for max_connections? Here we've an Oracle server with
> > up to 1200 simultaneous conections over it!
>
> [ shrug... ] If your machine has the beef to run 1200 simultaneous
> queries, you can set max_connections to 1200.
>
> The point of what you were quoting is that if you want to service
> 1200 concurrent users but you only expect maybe 100 simultaneously
> active queries from them (and you have a database box that can only
> service that many) then you want to put a connection pooler in
> front of 100 backends, not try to start a backend for every user.
>
> Oracle may handle this sort of thing differently, I dunno.
>
> regards, tom lane
Oracle has some form of built-in connection pooling. I don't remember
the exact details of it off the top of my head, but I think it was a
'wedge' that clients would connect to as if it was the database, and the
wedge would then find an available database process to use.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Peformance Tuning Opterons/ Hard Disk Layout
@ 2005-02-28 08:10 Markus Schaber <schabios@logi-track.com>
parent: John Allgood <john@turbocorp.com>
2 siblings, 0 replies; 23+ messages in thread
From: Markus Schaber @ 2005-02-28 08:10 UTC (permalink / raw)
To: John Allgood <john@turbocorp.com>; +Cc: pgsql-performance; john@arbash-meinel.com
Hi, John,
John Allgood schrieb:
> My question is what is the best way to setup
> postgres databases on different disks. I have setup multiple postmasters
> on this system as a test. The only problem was configuring each
> databases "ie postgresql.conf, pg_hba.conf". Is there anyway in
> postgres to have everything in one cluster and have it seperated onto
> multiple drives.
Using PostgreSQL 8.0, the newly introduced "tablespaces" solve all this:
http://www.postgresql.org/docs/8.0/interactive/manage-ag-tablespaces.html
Using PostgreSQL 7.4, you can relatively easy create single databases on
different drives. However, separating out single tables or indices
involves some black symlink magic. See google and
http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2005-02-28 08:10 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-23 16:39 Peformance Tuning Opterons/ Hard Disk Layout John Allgood <john@turbocorp.com>
2005-02-23 18:03 ` Michael Adler <adler@pobox.com>
2005-02-23 18:26 ` Bruno Almeida do Lago <teolupus@gmail.com>
2005-02-23 18:35 ` Rod Taylor <pg@rbt.ca>
2005-02-23 18:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-23 18:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-23 19:15 ` John Allgood <john@turbocorp.com>
2005-02-23 19:46 ` John Arbash Meinel <john@arbash-meinel.com>
2005-02-23 20:05 ` John Allgood <john@turbocorp.com>
2005-02-23 20:24 ` John Arbash Meinel <john@arbash-meinel.com>
2005-02-23 20:41 ` John Allgood <john@turbocorp.com>
2005-02-23 21:01 ` John Arbash Meinel <john@arbash-meinel.com>
2005-02-23 19:50 ` Michael Adler <adler@pobox.com>
2005-02-25 16:20 ` Jim C. Nasby <decibel@decibel.org>
2005-02-23 21:02 ` Josh Berkus <josh@agliodbs.com>
2005-02-24 13:28 ` Bruno Almeida do Lago <teolupus@gmail.com>
2005-02-24 03:24 ` Christopher Browne <cbbrowne@acm.org>
2005-02-24 08:28 Re: Peformance Tuning Opterons/ Hard Disk Layout Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
2005-02-24 18:31 Peformance Tuning Opterons/ Hard Disk Layout John Allgood <john@turbocorp.com>
2005-02-24 19:12 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-24 19:40 ` John Arbash Meinel <john@arbash-meinel.com>
2005-02-28 08:10 ` Markus Schaber <schabios@logi-track.com>
2005-02-25 11:19 Re: Peformance Tuning Opterons/ Hard Disk Layout Vig, Sandor (G/FI-2) <Sandor.Vig@audi.hu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox