Hardware recommendation: which is best

It depends what you want to do with your database.

Do you have many reads (select) or a lot of writes (update,insert) ?
You should use a hardware raid controller with battery backup write cache
(write cache should be greater than 256 MB).

.. heavy duty production server ?

How much memory do you have ?

How big is your database, tables ... ?

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Phoenix Kiula
Gesendet: Dienstag, 11. September 2007 13:49
An: Postgres General
Betreff: [GENERAL] Hardware recommendation: which is best

Hello

We're trying to look for the most optimal config for a heavy duty
production server, and the following two are falling in the same price
range from our supplier:

Option 1:
2 x 300GB SCSI (10k rpm) with SAS and RAID 1

Option 2:
4 x 300GB SATA2 (7200 rpm, server grade) with RAID 10

I am not sure how the pricing comes so similar with such different
RAID options, but given the two above I think the second option will
be better for a high volume server where Postgres is the main
application? The only reason I ask is because of so many websites, and
threads on this list, that trump the advantages of SCSI. Many thanks
for any advice!

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 11/09/2007, Franz.Rasper@izb.de <Franz.Rasper@izb.de> wrote:

It depends what you want to do with your database.

Do you have many reads (select) or a lot of writes (update,insert) ?

This one will be a hugely INSERT thing, very low on UPDATEs. The
INSERTS will have many TEXT fields as they are free form data. So the
database will grow very fast. Size will grow pretty fast too.

You should use a hardware raid controller with battery backup write cache
(write cache should be greater than 256 MB).

I'll have a raid controller in both scenarios, but which RAID should
be better: RAID1 or RAID10?

How much memory do you have ?

4GB to begin with..

How big is your database, tables ... ?

Huge, as the two main tables will each have about ten TEXT columns
each. They will have about 15000 new entries every day, which is quite
a load, so I believe we will have to partition it at least by month
but even so it will grow at a huge pace.

While we are at it, would postgres be any different in performance
across a single-CPU Quad Core Xeon with a dual CPU dual-core AMD
Opteron? Or should the hard disk and RAM be the major considerations
as usually proposed?

Thanks

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

On 09/11/07 07:55, Phoenix Kiula wrote:

On 11/09/2007, Franz.Rasper@izb.de <Franz.Rasper@izb.de> wrote:

It depends what you want to do with your database.

Do you have many reads (select) or a lot of writes (update,insert) ?

This one will be a hugely INSERT thing, very low on UPDATEs. The
INSERTS will have many TEXT fields as they are free form data. So the
database will grow very fast. Size will grow pretty fast too.

15000 rows/day times 365 days = 5475000 rows.

How big are these rows? *That* is the crucial question.

You should use a hardware raid controller with battery backup write cache
(write cache should be greater than 256 MB).

I'll have a raid controller in both scenarios, but which RAID should
be better: RAID1 or RAID10?

The striping aspects of RAID10 makes sequential reads and writes and
large writes much faster.

The more spindles you have, the faster it is.

If you are *really* concerned about speed, 4 x 147GB 10K SCSI

How much memory do you have ?

4GB to begin with..

How big is your database, tables ... ?

Huge, as the two main tables will each have about ten TEXT columns
each. They will have about 15000 new entries every day, which is quite
a load, so I believe we will have to partition it at least by month
but even so it will grow at a huge pace.

15000 in an 8 hour window is 31.25 inserts/minute or ~2 seconds/insert.

If the records are 30MB each, then that could cause some stress on
the system in that 8 hour window.

If they are 3MB each, not a chance.

While we are at it, would postgres be any different in performance
across a single-CPU Quad Core Xeon with a dual CPU dual-core AMD
Opteron? Or should the hard disk and RAM be the major considerations
as usually proposed?

Opteron is the standard answer.

What is your backup/recovery strategy?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5pn1S9HxQb37XmcRAnl1AJ48p5CGBMma15yWt9FtD0bOXN/D7ACeNxxq
9EWbm10L/Zt/tB1xPly/Ex0=
=QPI1
-----END PGP SIGNATURE-----

This one will be a hugely INSERT thing, very low on UPDATEs. The
INSERTS will have many TEXT fields as they are free form data. So the
database will grow very fast. Size will grow pretty fast too.

You should use a hardware raid controller with battery backup write cache
(write cache should be greater than 256 MB).

Take a hardware raid controller with battery backup write cache (512 - 1024
MB).

I'll have a raid controller in both scenarios, but which RAID should
be better: RAID1 or RAID10?

RAID1 is ok (mirroring)
RAID10 is (maybe) better

Normally I would use SAS Disks (instead of SATA2). How about RAI10 with SAS
?

4GB to begin with..

It is ok. Is your OS linux ? RAM should be easily expandable to 8 or 12 GB.
Do you use an 64 Bit OS ?

While we are at it, would postgres be any different in performance
across a single-CPU Quad Core Xeon with a dual CPU dual-core AMD
Opteron? Or should the hard disk and RAM be the major considerations
as usually proposed?

Both are ok. The AMD is maybe cheaper, but hard disks, RAID controller, RAM
and a good database design/import scripts are more important.

Do you have an server for testing ?

Greetings,

-Franz

On Tue, 11 Sep 2007, Phoenix Kiula wrote:

I'll have a raid controller in both scenarios, but which RAID should
be better: RAID1 or RAID10?

The point people are trying to make to you is that the differences between
RAID controllers can be as big as that between RAID architectures in cases
like yours. Which controller you're using and how the cache is setup can
have a larger impact on INSERT performance than how many/what type of
disks are involved. If you've comparing a fast SAS controller to a slow
SATA2 one, than the SAS setup may very well run faster no matter how many
disks the SATA2 one has. Conversely, it's not unheard of to have a SAS
controller with such miserable operating system drivers that there is no
performance advantage to using faster SCSI disks instead of SATA2.

You're not going to get a particularly useful answer here without giving
some specifics about the two disk controllers you're comparing, how much
cache they have, and whether they include a battery backup.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

The point people are trying to make to you is that the differences between
RAID controllers can be as big as that between RAID architectures in cases
like yours. Which controller you're using and how the cache is setup can
have a larger impact on INSERT performance than how many/what type of
disks are involved. If you've comparing a fast SAS controller to a slow
SATA2 one, than the SAS setup may very well run faster no matter how many
disks the SATA2 one has. Conversely, it's not unheard of to have a SAS
controller with such miserable operating system drivers that there is no
performance advantage to using faster SCSI disks instead of SATA2.

You're not going to get a particularly useful answer here without giving
some specifics about the two disk controllers you're comparing, how much
cache they have, and whether they include a battery backup.

Exactly, thats the point.

Thanks Greg.

You're not going to get a particularly useful answer here without giving
some specifics about the two disk controllers you're comparing, how much
cache they have, and whether they include a battery backup.

Scenario 1, SATAII:

- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10: 3Ware Raid 9650SE: http://www.acnc.com/04_01_10.html

Scenario 2, SCSI:

- Server: IBM e326m 1U Rackmount server
- CPU: Double -- Opteron 275 is 2 x 2.2GHz, with 2 x 1MB L2 Cache
- RAM: 4Gb PC3200 ECC Registered
- Hard disk: 2 x 300GB SCSI 10K RPM
- Raid 1: LSI Logic

Would appreciate any tips. From these two, Scenario 1 looks marginally
better to me. I am requesting further information about cache and
battery powered backup, but would appreciate first-off thoughts based
on above info.

TIA!

Scenario 1, SATAII:

- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10: 3Ware Raid 9650SE: http://www.acnc.com/04_01_10.html

Scenario 2, SCSI:

- Server: IBM e326m 1U Rackmount server
- CPU: Double -- Opteron 275 is 2 x 2.2GHz, with 2 x 1MB L2 Cache
- RAM: 4Gb PC3200 ECC Registered
- Hard disk: 2 x 300GB SCSI 10K RPM
- Raid 1: LSI Logic

Dont know anything about the LSI Logic controller, but
if it have battery backup write cache (at least 256 MB)
I would go with the IBM System. If am not wrong the harddisk
are hot swapable and you will get probably a better support.
Ask if you can get one for testing (normally IBM does it).

Some Questions:
Can you get Opterons with an higher cpu clock frequency ?
( >= 2.6 GHz)
Can you get 15K RPM SCSI disks ?

Greetings,

-Franz

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

On 09/11/07 11:26, Phoenix Kiula wrote:

Thanks Greg.

You're not going to get a particularly useful answer here without giving
some specifics about the two disk controllers you're comparing, how much
cache they have, and whether they include a battery backup.

[snip]

Would appreciate any tips. From these two, Scenario 1 looks marginally
better to me. I am requesting further information about cache and
battery powered backup, but would appreciate first-off thoughts based
on above info.

How (on average) large are the records you need to insert, and how
evenly spread across the 24 hour day do the inserts occur?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5saES9HxQb37XmcRAjZnAKCrPnIyuhuXm+InFpmn/bDiw/1xKgCg7xoC
pq4xdQ72BT4qFVqvy2g5RrM=
=EbRU
-----END PGP SIGNATURE-----

On 12/09/2007, Ron Johnson <ron.l.johnson@cox.net> wrote:

How (on average) large are the records you need to insert, and how
evenly spread across the 24 hour day do the inserts occur?

There will be around 15,000 inserts in a day. Each insert will have
several TEXT columns, so it is difficult to predict, but about 30,000
to 100,000 characters in each row. And yes, the inserts will be very
consistently timed every day.

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

On 09/11/07 12:02, Phoenix Kiula wrote:

On 12/09/2007, Ron Johnson <ron.l.johnson@cox.net> wrote:

How (on average) large are the records you need to insert, and how
evenly spread across the 24 hour day do the inserts occur?

There will be around 15,000 inserts in a day. Each insert will have
several TEXT columns, so it is difficult to predict, but about 30,000
to 100,000 characters in each row. And yes, the inserts will be very
consistently timed every day.

15000*100000 = 1.5GB.

1.5GB / (24*60) = ~1 binary MB.

Any computer that that can't write 1 megabyte per minute in a day
should have been retired in 1970.

So.....

Unless there's something that you aren't telling us, this should be
handleable by a Wal-Mart Special with an extra-large disk in it.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG5vxLS9HxQb37XmcRAoneAKCsw0W92jmySlGaRFpkZsvIJovTJwCgqPFg
j4Fp2uV5vf+OIVhnuJSaQxs=
=4u83
-----END PGP SIGNATURE-----

On 9/11/07, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

Thanks Greg.
Scenario 1, SATAII:

- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10: 3Ware Raid 9650SE: http://www.acnc.com/04_01_10.html

Good controller. Battery backed cache module?

Scenario 2, SCSI:

- Server: IBM e326m 1U Rackmount server
- CPU: Double -- Opteron 275 is 2 x 2.2GHz, with 2 x 1MB L2 Cache
- RAM: 4Gb PC3200 ECC Registered
- Hard disk: 2 x 300GB SCSI 10K RPM
- Raid 1: LSI Logic

LSI makes a lot of different RAID controllers.

Again, battery backed cache? and how much for each controller?

Generally LSI's have been a bit slower than escalades in the past, but
they're also quite stable and reliable, and their close in
performance.

On Wed, 12 Sep 2007, Phoenix Kiula wrote:

Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10: 3Ware Raid 9650SE: http://www.acnc.com/04_01_10.html

The typical 9650SE will normally come with 256MB of cache (the 2-port
version has 128MB, the 24-port on 512MB; you're probably getting one of
the middle ones which all have 256). This is a fast controller (sometimes
people complain about its RAID 5 which isn't an issue for you), it has
good drivers for most popular operating systems, and as long as you make
sure you're buying it from day one with the optional Battery Backup Unit
(BBU) so you can safely run it in write-back cache mode the performance of
this setup should be excellent for database use.

- Server: IBM e326m 1U Rackmount server
- CPU: Double -- Opteron 275 is 2 x 2.2GHz, with 2 x 1MB L2 Cache
- RAM: 4Gb PC3200 ECC Registered
- Hard disk: 2 x 300GB SCSI 10K RPM
- Raid 1: LSI Logic

As far as I can tell IBM model uses the LSI Logic LSI53C1030 Fusion-MPT
Ultra320 SCSI Controller, usually abbreviated as the LSI 1030:
http://www.lsi.com/files/docs/marketing_docs/storage_stand_prod/raid/lsi53c1030_pb.pdf

This is a very basic SCSI controller, not one of the LSI MegaRAID
controllers that are often recommended by people here. This particular
model appears to have no write cache as all, which means you'll get poor
performance with INSERTs under PostgreSQL. A quick search suggests it has
a general history of performance issues, possibly related to that; two
example reports are for Linux and FreeBSD are:

http://stateless.geek.nz/2005/02/24/lsi-1030-raid-status-on-linux/ (may
have to grab this one from the Google cache instead:
http://64.233.169.104/search?q=cache:eMkvpB66H9QJ:stateless.geek.nz/2005/02/24/lsi-1030-raid-status-on-linux/+lsi+1030+raid+controller&amp;hl=en&amp;ct=clnk&amp;cd=5&amp;gl=us
)
http://lists.freebsd.org/pipermail/freebsd-scsi/2005-January/001646.html

Based on the well known strengths of the 3Ware controller vs. what appears
to be a very weak LSI controller, I would expect the Asus system above to
have massively better performance for your intended application than this
particular IBM one--even though it's possible the real-world performance
of the CPU/memory might be a little better on the Opteron box. The fact
that it will have 2X as many disks will just increase its lead. And now
you know why everyone wanted such specific information!

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On 12/09/2007, Greg Smith <gsmith@gregsmith.com> wrote:

On Wed, 12 Sep 2007, Phoenix Kiula wrote:

Scenario 1, SATAII:
- Server: Asus RS120-E4/PA4 Dedicated Server
- CPU: Single -- Intel Quad Core Xeon Processor x3210 Processor 2.13Ghz
- RAM: 4Gb DDR2 Memory 667Mhz
- Hard disk: 4 x Seagate ES SATAII HardDrive 7200RPM 250Gb (Total 500Gb)
- Raid 10: 3Ware Raid 9650SE: http://www.acnc.com/04_01_10.html

The typical 9650SE will normally come with 256MB of cache (the 2-port
version has 128MB, the 24-port on 512MB; you're probably getting one of
the middle ones which all have 256). This is a fast controller (sometimes
people complain about its RAID 5 which isn't an issue for you), it has
good drivers for most popular operating systems, and as long as you make
sure you're buying it from day one with the optional Battery Backup Unit
(BBU) so you can safely run it in write-back cache mode the performance of
this setup should be excellent for database use.

- Server: IBM e326m 1U Rackmount server
- CPU: Double -- Opteron 275 is 2 x 2.2GHz, with 2 x 1MB L2 Cache
- RAM: 4Gb PC3200 ECC Registered
- Hard disk: 2 x 300GB SCSI 10K RPM
- Raid 1: LSI Logic

As far as I can tell IBM model uses the LSI Logic LSI53C1030 Fusion-MPT
Ultra320 SCSI Controller, usually abbreviated as the LSI 1030:
http://www.lsi.com/files/docs/marketing_docs/storage_stand_prod/raid/lsi53c1030_pb.pdf

This is a very basic SCSI controller, not one of the LSI MegaRAID
controllers that are often recommended by people here. This particular
model appears to have no write cache as all, which means you'll get poor
performance with INSERTs under PostgreSQL. A quick search suggests it has
a general history of performance issues, possibly related to that; two
example reports are for Linux and FreeBSD are:

http://stateless.geek.nz/2005/02/24/lsi-1030-raid-status-on-linux/ (may
have to grab this one from the Google cache instead:
http://64.233.169.104/search?q=cache:eMkvpB66H9QJ:stateless.geek.nz/2005/02/24/lsi-1030-raid-status-on-linux/+lsi+1030+raid+controller&amp;hl=en&amp;ct=clnk&amp;cd=5&amp;gl=us
)
http://lists.freebsd.org/pipermail/freebsd-scsi/2005-January/001646.html

Based on the well known strengths of the 3Ware controller vs. what appears
to be a very weak LSI controller, I would expect the Asus system above to
have massively better performance for your intended application than this
particular IBM one--even though it's possible the real-world performance
of the CPU/memory might be a little better on the Opteron box. The fact
that it will have 2X as many disks will just increase its lead. And now
you know why everyone wanted such specific information!

Thank you for the detailed explanation Greg. Very, very useful!

Just to confirm -- why do you say "[Opteron] will have 2X as many
disks"? In the dual-Opteron setup above I have 2 hard disks with
RAID1, whereas in the single-Xeon quad-core setup I have 4 disks with
RAID 10.

On 9/12/07, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

Just to confirm -- why do you say "[Opteron] will have 2X as many
disks"? In the dual-Opteron setup above I have 2 hard disks with
RAID1, whereas in the single-Xeon quad-core setup I have 4 disks with
RAID 10.

He didn't say that. Read his comment again. He said
the opteron may have a lead in terms of CPU/RAM performance,
but that the ASUS will have double the disk which may more than
make up for that.

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

On Wed, 12 Sep 2007, Phoenix Kiula wrote:

Just to confirm -- why do you say "[Opteron] will have 2X as many
disks"? In the dual-Opteron setup above I have 2 hard disks with
RAID1, whereas in the single-Xeon quad-core setup I have 4 disks with
RAID 10.

What I was trying to suggest was that the 3Ware system with the Intel
Quad-Core processor would likely be faster than the Dual Dual-Core Opteron
system even with the same number of disks because of how much faster its
controller is, and the fact that the 3Ware+Intel system has twice as many
disks just increases how much faster I'd expect it to be.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD