Thread: Postgresql Hardware
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
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.
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/
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 > >
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.
> 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"
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.
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.
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...
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.
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
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.)
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