Thread: Postgresql Hardware

Postgresql Hardware

From
Psicopunk
Date:
Hi,

We are developing a web application that will work on Postgresql. My
doubt is about the hardware that I can use for postgresql.

What HW is more important to postgresql performance?
Assuming that the database will have some load, what hardware must i
buy?


Thanks
Best regards

Re: Postgresql Hardware

From
Vick Khera
Date:
On Fri, Sep 11, 2009 at 6:01 AM, Psicopunk <gil.nunes.resende@gmail.com> wrote:

> What HW is more important to postgresql performance?
> Assuming that the database will have some load, what hardware must i
> buy?


Generally, you put as much RAM as you can afford, and then buy the
fastest disks you can afford.

Any advice beyond that will depend on taking exact measurements of
your application load and finding the bottlenecks and then increasing
performance at those points.  Usually it is I/O that limits you, so
you can also optimize your application by adjusting your schema design
and your queries.

Re: Postgresql Hardware

From
Bill Moran
Date:
In response to Vick Khera <vivek@khera.org>:

> On Fri, Sep 11, 2009 at 6:01 AM, Psicopunk <gil.nunes.resende@gmail.com> wrote:
>
> > What HW is more important to postgresql performance?
> > Assuming that the database will have some load, what hardware must i
> > buy?
>
>
> Generally, you put as much RAM as you can afford, and then buy the
> fastest disks you can afford.

More specifically, for high loads, get fast SCSI or SAS disks, directly
attached, and arrange them in a RAID-10.

Whether or not such a setup is overkill (sometimes it is) depends on
how much load you're really generating.  But (as Vick stated) DB servers
are usually bottlenecked on how fast they can access the disks, not RAM
or CPU.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: Postgresql Hardware

From
Ben Chobot
Date:
How much reading? Writing? Concurrent transactions? How much data will
you have? These are some of the things you need to provide to get a
reasonable answer.

Psicopunk wrote:
> Hi,
>
> We are developing a web application that will work on Postgresql. My
> doubt is about the hardware that I can use for postgresql.
>
> What HW is more important to postgresql performance?
> Assuming that the database will have some load, what hardware must i
> buy?
>
>
> Thanks
> Best regards
>
>

Re: Postgresql Hardware

From
Psicopunk
Date:
On 11 Set, 17:59, vi...@khera.org (Vick Khera) wrote:
> On Fri, Sep 11, 2009 at 6:01 AM, Psicopunk <gil.nunes.rese...@gmail.com> wrote:
> > What HW is more important to postgresql performance?
> > Assuming that the database will have some load, what hardware must i
> > buy?
>
> Generally, you put as much RAM as you can afford, and then buy the
> fastest disks you can afford.
>
> Any advice beyond that will depend on taking exact measurements of
> your application load and finding the bottlenecks and then increasing
> performance at those points.  Usually it is I/O that limits you, so
> you can also optimize your application by adjusting your schema design
> and your queries.
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general



How can i take some measurements to understand what bottlenecks will
appear?

This application will be always writing to database. How can i measure
the load at the database?
Can i tune the database depending on the hardware that i will buy?

Thanks for your help.
Best regards.

Re: Postgresql Hardware

From
Alan McKay
Date:
> How can i take some measurements to understand what bottlenecks will
> appear?

For long-term / ongoing I'm very happy so far with a package called
munin.  Google it and join their mailing list for help setting it up.

But it takes snapshots at 5 minute intervals and this is not configurable.

For immediate-term and for doing load tests, I've been very happy with
sar/sadc from the "sysstat" package that you can get from yum.  And
also a package called ksar that you can google and download.

From our wiki :

---snip---

Before starting your load test, start this command on all boxes you
want to monitor

 /usr/lib/sa/sadc -d -I -F 2 /var/log/foo/bar

or on a 64 bit box

 /usr/lib64/sa/sadc -d -I -F 2 /var/log/foo/bar

It will log system stats at 2 second intervals, until you control-C it.

Then you can view the file in kSar.

   * go to "File" then "New Window"
   * from the new window go to "Data", "Local Command"
   * enter the command sar -A -f /var/log/foo/bar

That's it! Now you will have some pretty graphs to look at!

--
“Don't eat anything you've ever seen advertised on TV”
         - Michael Pollan, author of "In Defense of Food"

Re: Postgresql Hardware

From
Chris Barnes
Date:
 
 
Purchase solid equipment and fairly current machines.
We buy referbished system at a fraction of the cost of new.
 
For example;
IBM 3650 with 8 x 300g SAS drives and controller, 4 slot dual with the following specs. 16 gb memory.
model name      : Intel(R) Xeon(R) CPU           E5345  @ 2.33GHz
cache size      : 4096 KB
 
This will probably cost ~  between $5000 & $8000
 
Set the drives up raid 1 and 5 for os/logs and data and global hotspare. Some prefer raid 10 if you have lots of drives.
 
This should give you 300g for os/logs and 600g for data.
 
Chris

 
> From: gil.nunes.resende@gmail.com
> Subject: [GENERAL] Postgresql Hardware
> Date: Fri, 11 Sep 2009 03:01:29 -0700
> To: pgsql-general@postgresql.org
>
> Hi,
>
> We are developing a web application that will work on Postgresql. My
> doubt is about the hardware that I can use for postgresql.
>
> What HW is more important to postgresql performance?
> Assuming that the database will have some load, what hardware must i
> buy?
>
>
> Thanks
> Best regards
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Faster Hotmail access now on the new MSN homepage.

Re: Postgresql Hardware

From
Scott Marlowe
Date:
On Fri, Sep 11, 2009 at 12:35 PM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:
>
>
> Purchase solid equipment and fairly current machines.
> We buy referbished system at a fraction of the cost of new.
>
> For example;
> IBM 3650 with 8 x 300g SAS drives and controller, 4 slot dual with the
> following specs. 16 gb memory.
> model name      : Intel(R) Xeon(R) CPU           E5345  @ 2.33GHz
> cache size      : 4096 KB
>
> This will probably cost ~  between $5000 & $8000

I can get that new with a 5 year warranty for about $7000 from
Aberdeen.  That's with a $1,000 RAID controller thrown in the mix.
dual QC opties or xeons.

> Set the drives up raid 1 and 5 for os/logs and data and global
> hotspare. Some prefer raid 10 if you have lots of drives.

Never RAID-5.  never. A big single RAID-10 on a good battery backed
caching controller will stomp any combination with RAID-5 into the
ground.  On a machine with

Note that on an 8 drive machine, I make one big RAID-10 out of 6 for
everything, and have 2 hot spares for redundancy.    That gives 900G
total to play around with if you have 300G SAS drives.

For about $8000 more you can get a 16 drive machine with 146G drives
and same basic setup, which I would recommend over the 8 drive
machine.  With 2 hot spares, and 2 in a mirror for the OS/xlog you
still have 12 drives for a RAID-10 of nothing but the db, and that
makes a huge difference.

Re: Postgresql Hardware

From
Scott Marlowe
Date:
On Fri, Sep 11, 2009 at 3:53 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> For about $8000 more you can get a 16 drive machine with 146G drives
> and same basic setup, which I would recommend over the 8 drive
> machine.  With 2 hot spares, and 2 in a mirror for the OS/xlog you
> still have 12 drives for a RAID-10 of nothing but the db, and that
> makes a huge difference.

remove the more up there, just for $8000, you can get...

Re: Postgresql Hardware

From
Psicopunk
Date:
Hi,

Thanks for your help.

I will take some measurements and analyze this graphs.

Does anyone know where i can get a book about Postgresql Tuning and
hardware impact on performance?

Thanks
Best regards.



Re: Postgresql Hardware

From
Greg Smith
Date:
On Mon, 14 Sep 2009, Psicopunk wrote:

> I will take some measurements and analyze this graphs.

A snapshot of "vmstat 1" data from when something like your real app is
running is far more useful at figuring out where you should allocate your
hardware resources for than any theoretical planning can be.

> Does anyone know where i can get a book about Postgresql Tuning and
> hardware impact on performance?

There aren't any yet.  I've collected links to the best stuff out there
and written a few articles all at
http://wiki.postgresql.org/wiki/Performance_Optimization

And my personal page at http://www.westnet.com/~gsmith/content/postgresql/
has some articles you might find interesting.  The "Database Hardware
Benchmarking" presentation covers a lot of the relevant material if you
want to spend an hour listening to me drone on about it, there's a lot of
background I cover that you can't really get just from the slides.

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

Re: Postgresql Hardware

From
Scott Marlowe
Date:
On Mon, Sep 14, 2009 at 9:30 AM, Greg Smith <gsmith@gregsmith.com> wrote:
> On Mon, 14 Sep 2009, Psicopunk wrote:
>
>> I will take some measurements and analyze this graphs.
>
> A snapshot of "vmstat 1" data from when something like your real app is
> running is far more useful at figuring out where you should allocate your
> hardware resources for than any theoretical planning can be.
>
>> Does anyone know where i can get a book about Postgresql Tuning and
>> hardware impact on performance?
>
> There aren't any yet.  I've collected links to the best stuff out there and
> written a few articles all at
> http://wiki.postgresql.org/wiki/Performance_Optimization
>
> And my personal page at http://www.westnet.com/~gsmith/content/postgresql/
> has some articles you might find interesting.  The "Database Hardware
> Benchmarking" presentation covers a lot of the relevant material if you want
> to spend an hour listening to me drone on about it, there's a lot of
> background I cover that you can't really get just from the slides.

Thumbs up to Greg's articles on performance tuning, they were a BIG
help to me a few years back when I was in Chicago (and they still are,
I've just learned a lot from them so I don't have to go back and read
them all the time now.)

Re: Postgresql Hardware

From
Psicopunk
Date:
Thanks,Greg!

I will read your articles.

This is a great help to understand what HW and how to tune my
postgresql.

Best regards.




On 14 Set, 16:46, scott.marl...@gmail.com (Scott Marlowe) wrote:
> On Mon, Sep 14, 2009 at 9:30 AM, Greg Smith <gsm...@gregsmith.com> wrote:
> > On Mon, 14 Sep 2009, Psicopunk wrote:
>
> >> I will take some measurements and analyze this graphs.
>
> > A snapshot of "vmstat 1" data from when something like your real app is
> > running is far more useful at figuring out where you should allocate your
> > hardware resources for than any theoretical planning can be.
>
> >> Does anyone know where i can get a book about Postgresql Tuning and
> >> hardware impact on performance?
>
> > There aren't any yet.  I've collected links to the best stuff out there and
> > written a few articles all at
> >http://wiki.postgresql.org/wiki/Performance_Optimization
>
> > And my personal page athttp://www.westnet.com/~gsmith/content/postgresql/
> > has some articles you might find interesting.  The "Database Hardware
> > Benchmarking" presentation covers a lot of the relevant material if you want
> > to spend an hour listening to me drone on about it, there's a lot of
> > background I cover that you can't really get just from the slides.
>
> Thumbs up to Greg's articles on performance tuning, they were a BIG
> help to me a few years back when I was in Chicago (and they still are,
> I've just learned a lot from them so I don't have to go back and read
> them all the time now.)
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general