How to make a REALLY FAST db server?

On Mon, 10 Sep 2001, bpalmer wrote:

- Hardware: dual / quad Intel class

Fairly easy to obtain. If all you want is a dual, you can use
desktop-class motherboards from such makers as Asus, Abit, and
IWill. If you're going for speed, stick to the DDR or SDRAM
capable boards.

- Disk: SCSI Raid 1+0

To really eek out as much speed as possible here, you'll want 10k RPM
Ultra-160 Fibre Channel SCSI drives with a dedicated hardware raid
controller. If have more reads than writes, you may want to use Raid 5
instead.

Postgres won't let you separate indexes from the database they represent,
so you can't make separate raid clusters for indexes and data; no
optimization there. Maybe in the next version that implements
schemas? What you can do if you use multiple DB's in your app design,
is put different DB's on different raid clusters. That'll help parallel
execution times. If you do this, make sure template1 and template0 are
separated from the rest of the databases, this will allow fast responses
from the system tables and make sure no application database IO affects
them adversely.

- Ram: Not really sure here. Is there math somewhere for ram needs for
pgsql? I imagine is has something to do with # connections, db size,
etc.

No reason not to go 2GB. Ram is cheap these days, and you can always
increase shared buffers and caches to actually fill the server memory
up with as much quick-fetch info as possible.

All in all, if you're making a DB machine, do whatever you can to get
rid of hits caused by disk IO. Parallelize as much as possible between
your databases, and if you have a DB capable of separating indexes from
the mix, do that too. Don't run any other services on it, and make
sure it has a nice wide 100MBit or 1GBit pipe so it doesn't saturate when
servicing multiple hosts.

Hope that helps.

-- 
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Programmer              |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : hamster.lee.net                                              |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

I'm looking to make a DB server for a project I'm working on (using

pgsql)

and am wondering if people have suggestions?

Thoughts:

- Hardware: dual / quad Intel class

If you're going dual P3, go with Athlons, they have a *much* higher
bandwidth across the front side and memory buses, not to mention being
better processors to start with. If you're thinking a quad Xeon, you can
still consider a dual Athlon. I do run a quad Xeon (SuperMicro 8050
chassis/motherboard), and it's fast, but there are tradeoffs. Dual 1.2
GHz Athlons = 2.4 GHz total, 4x700 MHz Xeons = 2.8 GHz total. Cost
difference = ~$4,000 or more.

Another determining factor is the usage that you expect. If you need
extremely low latency on infrequent queries, the higher-frequency Athlons
will likely be much better, as for a single query you're pitting a 700 MHz
Xeon on a 100 MHz bus against a 1.2 MHz Athlon with a 266 MHz bus.
However, for all-out, slam-the-machine work, the ServerWorks Xeon chips do
shine, as they have 4-way interleaved memory and a crossbar to individual
CPU buses. (I wasn't aware that they used the crossbar until recently.)

If you do go with a quad Xeon, the SuperMicro 8050 is a *superb*
platform to start with. Add RAM, CPU's, RAID card, drives, and video, and
you're off to the races. Triple, redundant power supplies connected to an
intelligent power distribution system, an unearthly number of fans (again,
all in redundant push/pull pairs), 64/66 PCI, and 10 hot-swap, SCA drive
bays across two channels. Tough to beat. It is a very large chassis
(makes two mid-towers look puny), the rack-mountable 8060 looks like it
should be equally sturdy and reliable. The only time ours ever gets
rebooted is for hardware upgrades.

can do multi proc support
- Disk: SCSI Raid 1+0
- Ram: Not really sure here. Is there math somewhere for ram needs for
pgsql? I imagine is has something to do with # connections, db size,
etc.

While 1+0 is fast, you can realistically use RAID 5 for redundancy,
saving disks, provided that you're not using fsync(), and have plenty of
RAM. Between the write-cache on the RAID controller and from the kernel,
even a large number of inserts/updates don't actually have to hit the disk
at all.

As for how much RAM to use, you want to have enough that your entire
$PGDATA directory is held in disk cache, a good chunk for shared buffers,
a good chunk for sorts, enough for the backends, and enough spare for
write-caching that you won't hit the disk. Just how much that is depends
on the size of your data, and what type of usage you're going to give the
machine.

steve

I'm looking to make a DB server for a project I'm working on (using pgsql)
and am wondering if people have suggestions?

Thoughts:

- Hardware: dual / quad Intel class

OK, but remember I/O is key for databases. The more spindles the
better. See my performance article on techdocs.

- OS: Prolly FreeBSD (FFS is your friend (what with syncs and all) and it
can do multi proc support

I would recommend soft updates be enabled.

- Disk: SCSI Raid 1+0

Not sure about that. Is that optimal for I/O?

- Ram: Not really sure here. Is there math somewhere for ram needs for
pgsql? I imagine is has something to do with # connections, db size,
etc.

Again, see article.
-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

- Hardware: dual / quad Intel class

Fairly easy to obtain. If all you want is a dual, you can use
desktop-class motherboards from such makers as Asus, Abit, and
IWill. If you're going for speed, stick to the DDR or SDRAM
capable boards.

I'm not sure how much a 2+ way system will help. Most of the db work
will be done in one long serialized processes and those can't be spread
out over multiple processors (with current day postgresql).

- Disk: SCSI Raid 1+0

To really eek out as much speed as possible here, you'll want 10k RPM
Ultra-160 Fibre Channel SCSI drives with a dedicated hardware raid
controller. If have more reads than writes, you may want to use Raid 5
instead.

Why 5? 1+0 is far better and faster. I was planning on doing a
hardware RAID controller (just need to find the one that FBSD likes the
best).

Postgres won't let you separate indexes from the database they represent,
so you can't make separate raid clusters for indexes and data; no
optimization there. Maybe in the next version that implements
schemas? What you can do if you use multiple DB's in your app design,
is put different DB's on different raid clusters. That'll help parallel
execution times. If you do this, make sure template1 and template0 are
separated from the rest of the databases, this will allow fast responses
from the system tables and make sure no application database IO affects
them adversely.

Interesting thoughts..

No reason not to go 2GB. Ram is cheap these days, and you can always
increase shared buffers and caches to actually fill the server memory
up with as much quick-fetch info as possible.

But then why not 4G? I would love some real numbers rather than 'a
lot'. With oracle, you can plug in some numbers and a real extimate
will be spit out. I've worked with DB servers w/ 14G of ram that were
killing that, so "get a lot" isn't really good enough.

All in all, if you're making a DB machine, do whatever you can to get
rid of hits caused by disk IO. Parallelize as much as possible between
your databases, and if you have a DB capable of separating indexes from

Regretfully, it's only one database and the data model won't really
let me split it up much.

the mix, do that too. Don't run any other services on it, and make
sure it has a nice wide 100MBit or 1GBit pipe so it doesn't saturate when
servicing multiple hosts.

- Brandon

----------------------------------------------------------------------------
b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5

- Hardware: dual / quad Intel class

OK, but remember I/O is key for databases. The more spindles the
better. See my performance article on techdocs.

- OS: Prolly FreeBSD (FFS is your friend (what with syncs and all) and it
can do multi proc support

I would recommend soft updates be enabled.

Good points.

- Disk: SCSI Raid 1+0

Not sure about that. Is that optimal for I/O?

From my experience it is. As long as you have a raid controler that
can do 2 level RAID abstraction. First you need mirrored pairs and
then you stripe over them. It costs a lot in disk, but is stupid fast
with the right raid controller. With some Suns / FC / EMC, we were
getting ~100M/s+ with that setup for our Oracle server.

- Ram: Not really sure here. Is there math somewhere for ram needs for
pgsql? I imagine is has something to do with # connections, db size,
etc.

Again, see article.

Thanks.

----------------------------------------------------------------------------
b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5

I am currently working on a patch to 7.2 that will allow data/indexes to
be in different locations. I am also looking at replacing the current
LOCATION code with a table driven (pg_locations) that will allow not
only the default data/indexes locations to be set but also each
table/index to have its own location. The first part is finished. I
will be making a patch tomorrow after some more testing. On my app
(about 400G of table data and 350G of index data) it really makes a
difference...

Jim

- Hardware: dual / quad Intel class

OK, but remember I/O is key for databases. The more spindles the
better. See my performance article on techdocs.

- OS: Prolly FreeBSD (FFS is your friend (what with syncs and all)

and it

can do multi proc support

I would recommend soft updates be enabled.

Good points.

- Disk: SCSI Raid 1+0

Not sure about that. Is that optimal for I/O?

From my experience it is. As long as you have a raid controler that

can do 2 level RAID abstraction. First you need mirrored pairs and
then you stripe over them. It costs a lot in disk, but is stupid

fast

with the right raid controller. With some Suns / FC / EMC, we were
getting ~100M/s+ with that setup for our Oracle server.

- Ram: Not really sure here. Is there math somewhere for ram

needs for

pgsql? I imagine is has something to do with # connections, db

size,

etc.

Again, see article.

Thanks.

----------------------------------------------------------------------------

b. palmer, bpalmer@crimelabs.net

pgp:crimelabs.net/bpalmer.pgp5

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

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

TIP 1: subscribe and unsubscribe commands go to

majordomo@postgresql.org

bpalmer <bpalmer@crimelabs.net> writes:

I'm looking to make a DB server for a project I'm working on (using pgsql)
and am wondering if people have suggestions?

Thoughts:

- Hardware: dual / quad Intel class

Athlon gives more bang for the buck - the dual Athlons are _really_
nice, and have proven stable as well.

- Disk: SCSI Raid 1+0

I'd probably go for a 3ware RAID instead... IDE disks are so much
cheaper nowadays than SCSI, and the premium isn't really justifiable
anymore.

- OS: Prolly FreeBSD (FFS is your friend (what with syncs and all) and it
can do multi proc support

I'd recommend Linux, which has more mature MP support and scales
better, but I'm obviously biased :). It's of course very important to
keep what you are familiar with - a good sysadmin makes a world of
difference no matter what you're using.

- Ram: Not really sure here. Is there math somewhere for ram needs for
pgsql? I imagine is has something to do with # connections, db size,
etc.

"More is better. RAM is cheap. Avoid RAMBUS".

--
Trond Eivind Glomsr�d
Red Hat, Inc.

- Hardware: dual / quad Intel class

The ultimate would be an IBM S/390 mainframe running some distribution of
Linux S/390. I/O bandwidth on mainframe DASD is incredible, the memory is
robust and fast, and the CPU is trememdous.

The price is also trememdous.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

I'd probably go for a 3ware RAID instead... IDE disks are so much
cheaper nowadays than SCSI, and the premium isn't really justifiable
anymore.

Having used IDE and SCSI disks, when I'm serious about performance, IDE
doesn't even enter my mind. Also, over on the XFS list, there are a few
people that have been using 3ware cards, and it sounds like there are
still some serious caveats/bugs to them. Myself, I much prefer a good
SCSI RAID card that's going to work, and going to work well.

(As an aside, one person was in a heated argument about how much cheaper
IDE was than SCSI. I got on pricewatch, found some prices, and would have
been able to put together a very fast SCSI system for the same price as
his IDE array.)

steve

I'm not sure how much a 2+ way system will help. Most of the db work
will be done in one long serialized processes and those can't be spread
out over multiple processors (with current day postgresql).

That's assuming that only one query will ever be executed at once. As a
new backend is spawned for each connection, extra CPU's are very helpful
if the database will see more than occasional use.

Also, even if there's only one query, dual-CPU machines are generally
much more responsive, especially under load, as one CPU can be handling
interrupts, kernel code, and other system processes while the other sits
there doing your task.

To really eek out as much speed as possible here, you'll want 10k RPM
Ultra-160 Fibre Channel SCSI drives with a dedicated hardware raid
controller. If have more reads than writes, you may want to use Raid 5
instead.

Why 5? 1+0 is far better and faster. I was planning on doing a
hardware RAID controller (just need to find the one that FBSD likes the
best).

If you have enough RAM, disk speed isn't terribly important, so RAID 5
gives you the redundancy without as many disks. Throw in an extra gig of
RAM for your disk cache, turn of fsync(), and you're likely to see a lot
bigger speed-up than any disk upgrade will give you. There are cases
where that isn't the case (such as updating every row in a multi-gigabyte
table), but unless you're involved in those specialized cases, it's not as
important.

So, why did I say that I don't use IDE for high-performance machines?
IDE has limitations. For example, say I wanted 10 drives in my array.
Finding a 5-channel IDE RAID controller is probably not as easy (and not
as cheap) as a dual-channel SCSI RAID controller. Also, SCSI buses are
much better about sharing bandwidth than IDE, as IDE doesn't have some of
the "nifty" features that SCSI does. And to go one further, hot-swappable
SCA bays are pretty common in server chassis. I simply plugged the RAID
controller into the SCA backplanes, and was done. Had I gone IDE, there
would have been additional cost in obtaining the hot-swap IDE bays.

As an aside, if you do go with a 3ware card, you might NOT want to use
RAID 5. The processors on the card are not up to the computing demands of
RAID 5, you might want to take a look at:

http://marc.theaimsgroup.com/?l=linux-xfs&amp;m=99970690219042&amp;w=2

No reason not to go 2GB. Ram is cheap these days, and you can always
increase shared buffers and caches to actually fill the server memory
up with as much quick-fetch info as possible.

But then why not 4G? I would love some real numbers rather than 'a
lot'. With oracle, you can plug in some numbers and a real extimate
will be spit out. I've worked with DB servers w/ 14G of ram that were
killing that, so "get a lot" isn't really good enough.

We run 1.5 gigs, and that's plenty for us. I increased the shared
buffers until it didn't help any more, then doubled it, I believe that it
came out to around 128 gigs. I did the same with sort memory, that came
out to around 64 megs. The machine right now uses about 860 megs of disk
cache, but took a few months to ge that high. It hasn't used swap at all.
If it ever hits swap, we'll add more. Luckily, with the 4-way interleaved
memory, it'll take up to 16 gigs, and with 16 slots, there's a lot of room
to add more. : )

steve

On Mon, 10 Sep 2001, Steve Wolfe wrote:

So, why did I say that I don't use IDE for high-performance machines?
IDE has limitations.

Mainly, the fact that IDE controllers require far more CPU involvement
than any SCSI controller, especially on a saturated bus. A good SCSI
controller can stay below 2% under almost any circumstance. A bad IDE one
can go above 20%. I don't think I should have to say any more. ^_^

Luckily, with the 4-way interleaved memory, it'll take up to 16 gigs,
and with 16 slots, there's a lot of room to add more. : )

And I was assuming a non server-class. Stupid me. Yeah, why 2GB? Hell,
I'd put in the 16GB, and let it start caching result sets in memory.
Even if Postgres doesn't allow such fine-grained control of various
memory-hogging caching schemes now, it may add them later for
optimization's sake. Memory is cheap. More *IS* the answer these days
when concerning RAM.

Besides, if you have a small database (5-10GB), you could serve it from
a RAM device. Can't get much faster than that. ^_^

-- 
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Programmer              |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : hamster.lee.net                                              |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

""Steve Wolfe"" <steve@iboats.com> wrote in message
news:002801c13a3f$15f34660$50824e40@iboats.com...

(As an aside, one person was in a heated argument about how much cheaper
IDE was than SCSI. I got on pricewatch, found some prices, and would have
been able to put together a very fast SCSI system for the same price as
his IDE array.)

That's nuts: SCSI disks cost a lot more than comparable IDE disks.

Marshall

"Shaun Thomas" <sthomas@townnews.com> wrote in message
news:Pine.LNX.4.33L2.0109101709380.1809-100000@hamster.lee.net...

On Mon, 10 Sep 2001, Steve Wolfe wrote:

So, why did I say that I don't use IDE for high-performance machines?
IDE has limitations.

Mainly, the fact that IDE controllers require far more CPU involvement
than any SCSI controller, especially on a saturated bus. A good SCSI
controller can stay below 2% under almost any circumstance. A bad IDE one
can go above 20%. I don't think I should have to say any more. ^_^

Your example is not very convincing. You want to compare a good SCSI
controller with a bad IDE one? That's not what one would call equitable.

Consider: let's get an antique 50 pin adaptec SCSI controller, and compare
it to a 3ware escalade running RAID 0 over 4 drives. The IDE one is faster!
It saturates the PCI bus! The SCSI controller pokes along at 10 MB/s.
Doesn't prove anything.

The business about CPU utilization did, in fact, use do be an issue, but
hasn't been since the introduction of UltraDMA IDE controllers, which
was a few years ago. (Admittedly, if you've been running WinNT 4 all
this time, you haven't been able to take advantage of it.)

Marshall

I second the RAM point. Not only is more better, you also need to configure
postgres to use it. You will need to take the average size of your
backends, the number of concurrent connections you expect and the amount of
RAM you want postgres to leave for the OS and all the other processes. This
will let you back into the proper buffer setting to best use the available
RAM without letting the postmaster go to swap space. Buffers are not the
only variable for memory. You need to allow space in ram for sort memory as
well.

As far as disks go. You cannot have too many spindles. But the number of
spindles you have available depends on which pieces of postgres get split
onto dedicated spindles.

A note on SMP. Postgres is not a threaded application. The best you can
hope for is that multiple connections get spread over multiple processors.
Each individual connection lives on a single CPU.

Tunning is somewhat of a black art to get the right balance. If you have to
make a choice, buy fewer processors, faster disks, and as much RAM as the
board will handle.

--
Randy Hall - Red Hat Certified Engineer - Ex-Great Bridge PostgreSQL Expert
Resume: http://members.home.net/rthall3

----- Original Message -----
From: "Trond Eivind Glomsr�d" <teg@redhat.com>
To: "bpalmer" <bpalmer@crimelabs.net>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, September 10, 2001 4:54 PM
Subject: Re: [GENERAL] How to make a REALLY FAST db server?

bpalmer <bpalmer@crimelabs.net> writes:

I'm looking to make a DB server for a project I'm working on (using

pgsql)

and am wondering if people have suggestions?

Thoughts:

- Hardware: dual / quad Intel class

Athlon gives more bang for the buck - the dual Athlons are _really_
nice, and have proven stable as well.

- Disk: SCSI Raid 1+0

I'd probably go for a 3ware RAID instead... IDE disks are so much
cheaper nowadays than SCSI, and the premium isn't really justifiable
anymore.

- OS: Prolly FreeBSD (FFS is your friend (what with syncs and all) and

it

As far as disks go. You cannot have too many spindles. But the number of
spindles you have available depends on which pieces of postgres get split
onto dedicated spindles.

But if you have enough RAM that the entire DB can be held in RAM, and
if the DB acess pattern is 90% read, 10% write (which is usually the
case) then do you really need to worry about disk performance?

It seems like for most applications, it is possible to hold all the
data in RAM, and RAM is cheap these days.

"Shaun Thomas" <sthomas@townnews.com> wrote in message
news:Pine.LNX.4.33L2.0109101410050.1809-100000@hamster.lee.net...

- Disk: SCSI Raid 1+0

To really eek out as much speed as possible here, you'll want 10k RPM
Ultra-160 Fibre Channel SCSI drives with a dedicated hardware raid
controller. If have more reads than writes, you may want to use Raid 5
instead.

15k RPM drives are available from Seagate and IBM. The Seagate drives
are slightly faster.

Cheers,

Colin

Indeed, there are applications where this is true. Postgres will try to
keep as much in the buffers as possible. You always have to take your
application into account when tuning the database. Your case where the
database may fit entirely in RAM is quite different from a server holding
many gigabytes of data and associated index files.

--
Randy Hall - Red Hat Certified Engineer - Ex-Great Bridge PostgreSQL Expert
Resume: http://members.home.net/rthall3

----- Original Message -----
From: "Dr. Evil" <drevil@sidereal.kz>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, September 11, 2001 12:12 AM
Subject: Re: [GENERAL] How to make a REALLY FAST db server?

As far as disks go. You cannot have too many spindles. But the number

of

spindles you have available depends on which pieces of postgres get

split

On Mon, 10 Sep 2001 20:39:18 +0000 (UTC), bpalmer@crimelabs.net ("B.
Palmer") wrote:

Not sure about that. Is that optimal for I/O?

From my experience it is. As long as you have a raid controler that
can do 2 level RAID abstraction. First you need mirrored pairs and
then you stripe over them. It costs a lot in disk, but is stupid fast
with the right raid controller. With some Suns / FC / EMC, we were
getting ~100M/s+ with that setup for our Oracle server.

Agreed. I've seen 2-3 times speed improvement over raid5.

On Mon, 10 Sep 2001 21:14:33 +0000 (UTC), teg@redhat.com (Trond Eivind
Glomsr�d) wrote:

- Hardware: dual / quad Intel class

Athlon gives more bang for the buck - the dual Athlons are _really_
nice, and have proven stable as well.

Agreed, single Athlons are much better than single Intels, but I still
think Intel win in the SMP game. As for stability, I think it's a tad
too early to make such a statement.

- Disk: SCSI Raid 1+0

I'd probably go for a 3ware RAID instead... IDE disks are so much
cheaper nowadays than SCSI, and the premium isn't really justifiable
anymore.

Aargh, No! SCSI is MUCH better under load and more reliable. I've yet
to see an enterprise server utilise IDE raid.

- OS: Prolly FreeBSD (FFS is your friend (what with syncs and all) and it
can do multi proc support

I'd recommend Linux, which has more mature MP support and scales
better, but I'm obviously biased :). It's of course very important to
keep what you are familiar with - a good sysadmin makes a world of
difference no matter what you're using.

I'd recommend Solaris, which has MUCH more mature MP support!
Linux still has a way to go (although it's getting there very
quickly).

If you do decide to use Linux, check out ReiserFS file system.