I am setting up a server with the following specs:
* Qty 4 Processors (AMD Opterons with 12 cores each)
* 32 GB Memory
* Qty 8 HDD (15K SAS Dual Port)
* CentOS 5.5
* JBoss
* PostgreSQL

It is likely that at a later point I will separate the application from the database but for now they are going to be on the same machine. I have read that PostgreSQL performance benefits from:
* RAID 10
* Separate OS partition
* Separate xlog partition
* Separate pgdata partition

Since my single RAID 10 volume seems to have a total of 559808 MB available, this is the current partition plan:
* 337856 MB for OS
* 102400 MB for pgdata
* 51200 MB for xlog
* 68352 MB for swap

Here are some questions:
* How does my partition plan look?
* When installing CentOS, when I get to the disk setup step I need to define mount points - what should I enter for the pgdata partition? (ex. ref this example setup mount points /pgdata1 )
* What should I enter as the mount point for the xlog partition?
* For filesystem type, avoidance of corruption is more important than perfect performance so the plan is to use 'noatime' but leave 'data=ordered' for the partition mount options - what do you think?
* Any other considerations?

Note: It is likely that the total size of all databases in the pgdata partition will not grow beyond 20 GB in the next few years.

  • This is a pretty powerful server. How many queries per second are you trying to support? – AngerClown Mar 23 '11 at 15:35
  • Ok, lets get real. Database + app server in use should not really swap. Now, I understand "swap out stuff not used like parts of the kernel etc.", but 64gb swap space is ridiculous. There is NO Way the computer can make use of that in a sensible fashion with decent speed. Takes too long. Cut that down. Significant. VERY significant. Like to 8gb or so. Maybe 12 or 16. But there simply is no way to even remotely make use of that 64gb you currently assign.

  • Your server hopefully has a lot to do computational wise, because while not pathetic it is NOT a high performance database server. Bad news. REALLY bad news. One raid 10 for all things shared - not a good idea. But 6 discs is NOT high performance 15k or not. I have a smaller db server here that has 6 discs in a RAID 10 only for the data. Whatever you do, transactional wise, you WILL be limited by disc performance again unless you do OLAP. There is no way that disc subsystem can push ONE 12 core proessor, 4 of them is absolutely impossible. In most cases a single 4 core would overload the discs. Really, better do something on the computational side.


  • Add another SSD for the logs. This is super fast and has very fast response times. The database needs to write changes off to disc ASAP, and that is in some cases "written off and flushed".
  • Make sure you need what you buy. I know Java can be a resource hog, but in these dimensions? Do you REALLY need 48 cores? Does Centos handle that decently? Linux DID have issues with too many cores. Now, I know these times are mostly over, but 48 cores can be quite pushing. I really like powerfull servers, but when I normally work with databases their size is 4 digits upwars (1000+gb) and the disc subsystem has a minimum of 10, often more than 1000 discs to feed that monster with the IO budget needed. OR servers are for virtualization.

  • Possibly add more RAM. 32gb sound impressive, but for 48 cores that is a little on the low side for my liking. I prefer going with a minimum of 1-2 gigabyte PER CORE.

  • If you go AMD remember to split your modules between processors ;)
| improve this answer | |
  • PostgreSQL fsync() logs after writing it, so you're on spot about your comment, also the performance problems on Linux appeared over 48 cores so he still ok. – coredump Mar 24 '11 at 3:51
  • The database is expected to fit in RAM in the near future, so all your comments about there not being enough disks to keep the CPUs busy are off target. – Greg Smith Mar 25 '11 at 16:49
  • 1
    REALLY ignorant, greg. This totally assumes NO WRITES as WRITES are FLUSHED in th logs, as coredump so nicely pointed out. No caching possible, unless the devlopers of postgres sql were ignorant enough to invalidate the ACID requirements of a good database (which they did not). As such it is not only writes, it is writes with a LOT of disc cache flushes. On every insert or update. – TomTom Mar 25 '11 at 20:09
  • ++ what TomTom wrote.
  • IIRC the reason for separate partitions for the data/xlog/OS is to get them on separate sets of spindles-- I don't see how dropping them all on the same RAID set accomplishes that.
  • While PostgreSQL does scale pretty well to multiple cores, 48 seems to be overkill.
  • There's also the speed of the cores. From what I've seen: the higher the core count, the slower the individual cores are-- you may be better served by fewer, but faster, cores.

There is a book, PostgreSQL 9.0 High Performance that does a pretty good job of covering the ins and outs of high performance PostgreSQL.

| improve this answer | |
  • Seconding the book. – sciurus Mar 23 '11 at 14:06

Splitting a single large RAID10 volume into multiple partitions accomplishes nothing useful. The disk usage patterns of the OS, WAL, and database drives are different enough that putting them onto separate disks makes PostgreSQL faster. For example, the WAL is all sequential writes, so having a dedicated drive for that can help with a number of things. Separate partitions on the same big drive volume doesn't improve performance the same way.

Ultimately it doesn't really matter though, when your data set is so small relative to the amount of RAM on your server. You don't actually need a high performance disk setup at all to accomplish that, just fast CPUs and RAM.

The one thing you didn't mention is what RAID controller you're using and whether you got a battery to provide backup to the cache. That's much more important than the partitioning trivia. See Reliable Writes for links to more background here.

| improve this answer | |

Databases are commonly I/O bound. Without knowing anything about your particular application I'd drop 3 of the processors and look at getting a Fusion IO card (or maybe an SSD) for the pgdata partition.

I'd also setup the RAID a bit different. The usage pattern of the xlog (sequential) will typically be different than the pgdata (random) partition. For this reason I'd suggest putting them on separate physical devices.

| improve this answer | |
  • 1
    I couldn't agree more. When database people refer to separate drives, they really do mean separate drives. This may be different in a SAN where you're carving up massive numbers of disks to make LUNs, but with internal storage, you'll want to be careful about how you carve up those drives. I'd use a mirrored pair for the OS, a mirrored pair for the logs and RAID 10 for data. – Jeremiah Peschka Mar 23 '11 at 15:29

The standard performance answer is 'test and see'. So, if you can try a few different configurations under load and see which one is the best for your load with your data that would be the 'correct' configuration.

With a 20GB database, you can fit (almost) all of the DB in filesystem cache and / or Postgresql buffer cache. You may not even have that many physical IOs once the server gets warmed up.

Maybe a good place to start is to create a 2 disk RAID 1 mirror for the OS and use the other 6 disks in a RAID 10 array for pgdata + swap. Until you have data to back it up, I don't see any need to separate the xlogs and the pgdata. This setup will at least allow you to move the log to the mirror drive if you really need to.

The same goes for the mount options. noatime is almost always a good idea, but anything else I would leave alone until you need it.

One thing to watch out for in CentOS / RHEL is LVMs. This is probably worth another question, but I never use LVM and instead create plain ext3 partitions. (I really hope you meant hardware RAID for your disks and not LVM RAID)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.