PostgreSQL High Performance Setup
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.
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.
Suggestions:
- 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 ;)
- ++ 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.