Solved

PostgreSQL High Performance Setup

Posted on 2011-03-22
18
960 Views
Last Modified: 2013-12-15
Hello,

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.
0
Comment
Question by:gwheato200
  • 8
  • 7
18 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35194834
Some thoughts:
* Partitioning looks ok, personally I would give less to the OS and leave some spare for a quick addition if you need it, or specifically for your applications.
* Set the mount points to whatever you are comfortable with!
* Since the database is probably going to fit entirely in memory you don't really need to worry about read performance.
* Also consider how you are going to backup the cluster/databases and where the backups will be written to.
* noatime is good but doesn't give you a whole lot of improvement. Setting the read ahead to a decent value will give you a big boost (for me increasing 256 to 8192 was the sweet spot).
0
 

Author Comment

by:gwheato200
ID: 35194901
Hello,

Thank you very much for your quick response.  This comment is in response to the expert comment
Some thoughts:
* Partitioning looks ok, personally I would give less to the OS and leave some spare for a quick addition if you need it, or specifically for your applications.
* Set the mount points to whatever you are comfortable with!
* Since the database is probably going to fit entirely in memory you don't really need to worry about read performance.
* Also consider how you are going to backup the cluster/databases and where the backups will be written to.
* noatime is good but doesn't give you a whole lot of improvement. Setting the read ahead to a decent value will give you a big boost (for me increasing 256 to 8192 was the sweet spot).

Comments:
* How much would you suggest reducing the OS by and allocating to spare?
* I recognize there is a lot of preference involved in defining mount points.  However, I would like a recommendation.  I do not know what values to use as mount points and I would be more comfortable with a suggestion from an Experts Exchange expert - what values would you enter for the mount point field in the CentOS window that asks for mount point during this disk setup step?
* Please suggest a plan/strategy/approach for where to backup the databases and where the backups will be written to.
* Why won't noatime give a lot of improvement?
* How is the read ahead value set?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35194980
* Hard to say... 20gb for OS should be plenty, and you wouldnt expect it to grow much. Maybe also separate out /tmp if you want to prevent rogue apps/scripts from filling up disk and crashing the os
* OK, use something that makes it obvious what it's for - even if postgres is the only thing on the box now, it might change later or if someone else needs to look after it. One suggestion:

/opt/postgres/9.0 <- stuff for binary installation, version specific in case you want multiple versions
/opt/postgres/data <- main database data
/opt/postgres/pg_xlog <- pg_xlog subdirectory links here
/opt/postgres/backups <- if you need to store backups locally
/opt/postgres/ts_tables <- eg tablespace name if you want them

* The backup strategy you need to figure out yourself based on your requirements. You need to read ALL of this: http://www.postgresql.org/docs/9.0/static/backup.html. Sorry, but I believe the most important thing you absolutely have to get right with databases are the backups. If you have more specific questions after familiarising yourself with the options, I'm happy to answer. Some considerations: acceptable level of data loss, do you need entire cluster backups, individual database backups, point in time recoverability, need to cater for hard drive losses, server losses, tape losses, how long need to be kept... etc
* noatime = depends on your usage I guess, but I tested it and got about 5% improvement for mine.
* Use the blockdev command as root: http://linux.about.com/library/cmd/blcmdl8_blockdev.htm
0
 

Author Comment

by:gwheato200
ID: 35195031
Hello,

Thank you very much for your quick response.  This comment is in response to the expert comment:
* Hard to say... 20gb for OS should be plenty, and you wouldnt expect it to grow much. Maybe also separate out /tmp if you want to prevent rogue apps/scripts from filling up disk and crashing the os
* OK, use something that makes it obvious what it's for - even if postgres is the only thing on the box now, it might change later or if someone else needs to look after it. One suggestion:

/opt/postgres/9.0 <- stuff for binary installation, version specific in case you want multiple versions
/opt/postgres/data <- main database data
/opt/postgres/pg_xlog <- pg_xlog subdirectory links here
/opt/postgres/backups <- if you need to store backups locally
/opt/postgres/ts_tables <- eg tablespace name if you want them

* The backup strategy you need to figure out yourself based on your requirements. You need to read ALL of this: http://www.postgresql.org/docs/9.0/static/backup.html. Sorry, but I believe the most important thing you absolutely have to get right with databases are the backups. If you have more specific questions after familiarising yourself with the options, I'm happy to answer. Some considerations: acceptable level of data loss, do you need entire cluster backups, individual database backups, point in time recoverability, need to cater for hard drive losses, server losses, tape losses, how long need to be kept... etc
* noatime = depends on your usage I guess, but I tested it and got about 5% improvement for mine.
* Use the blockdev command as root: http://linux.about.com/library/cmd/blcmdl8_blockdev.htm

Comments:
* If the OS partition only has 20 gb then, assuming the JBoss app is on the same partition, the space could quickly fill up with Apache logs, JBoss logs, attachments uploaded by web app users, etc.  Are you suggesting another partition for Apache and JBoss?
* Thank you for suggesting the /opt paths that are easy to follow!  What should I enter as the value in the field asking for a mount point for the pgdata partition?  What should I enter as the value in the field asking for the mount point for the xlog partition? (ref. the first field on this window )
0
 

Author Comment

by:gwheato200
ID: 35195037
If you are suggesting another partition for Apache and JBoss, what mount point value would you put in the field I referred to in the previous post.
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35195228
* I'm not familiar with JBoss and not much with Apache, but you could either increase the OS partition to something that fits all of them or create separate ones.. /apps, /opt/apps, /opt/apache, sometihng like that.
* In my suggestion, pgdata = /opt/postgres/data, pg_xlog = /opt/postgres/pg_xlog
0
 

Author Comment

by:gwheato200
ID: 35195542
Hello,

So you are suggesting significantly more partitions than what I had originally planned?  Is this correct?

If so, What is the reason for having separate partitions for each of the following (why not put them on the same partition as the OS)?
* binary installation
* local backups
* tablespace name
* apps (Apache, JBoss)
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35195597
You have to decide if the tradeoff is worthwhile - fewer mounts is easier to manage and gives more overall disk space, more mounts gives more isolation between apps so eg apache logs cant crash postgres etc. Depends how big your shop is, uptime priorities, maintenance priorities etc. If you want a minimal setup, I'd probably do:

/ <- OS
/tmp <- Temp
/var <- Misc apps and data
/opt/postgres/data <- postgres data and xlogs

If you already have other servers, probably best to follow whatever standard they are using.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:gwheato200
ID: 35195760
Hello,

I have a good amount of space, don't need to go with a minimal setup and uptime is a high priority (much higher than maintenance).  What would be a less minimal approach to the partitions?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35195910
OK, I can't comment on jboss or apache - you can decide that yourself, but how about:

/ <- OS
/tmp <- Temp
/var <- Misc apps and data
/opt/postgres/data <- postgres data
/opt/postgres/pg_xlog <- postgres pg_xlog
/opt/postgres/backups <- postgres backups (in addition, copy them off this server)
0
 

Author Comment

by:gwheato200
ID: 35196000
Hello,

Thanks very much for the follow up.

I have an alternative approach question:
What do you think about using an ioDrive for the pgdata partition (there are some amazing performance stats on it for this type of application)?
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35196050
I haven't used any SSD's before so can't comment. I'm sure they are faster in some cases, but in your case with a 20GB database it wouldn't be worth the extra cost.
0
 

Author Comment

by:gwheato200
ID: 35198811
Hello,

For this server, what would you suggest for the size of the partitions in this alternative partition plan?
0
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
ID: 35203398
/ <- 50gb
/tmp <- 50gb
/var <- 100gb
/opt/postgres/data <- 100gb (in case you need to make copies for testing etc)
/opt/postgres/pg_xlog <- 50gb (depends on peak activity, 50 is a LOT for this size db)
/opt/postgres/backups <- 100gb

That leaves about 100gb spare to allocate somewhere if you need it.
0
 
LVL 7

Expert Comment

by:MrNed
ID: 35742756
I think I gave plenty of answers and suggest one of my comments as an answer.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 35821794
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Server guidance IBM X3500 M4 1 21
linux apache localhost dev laptop 5 33
Problem to start Neon 20 53
Server HP DL380 G7 13 36
Little introduction about CP: CP is a command on linux that use to copy files and folder from one location to another location. Example usage of CP as follow: cp /myfoder /pathto/destination/folder/ cp abc.tar.gz /pathto/destination/folder/ab…
Linux users are sometimes dumbfounded by the severe lack of documentation on a topic. Sometimes, the documentation is copious, but other times, you end up with some obscure "it varies depending on your distribution" over and over when searching for …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now