Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1012
  • Last Modified:

PostgreSQL High Performance Setup

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
gwheato200
Asked:
gwheato200
  • 8
  • 7
1 Solution
 
MrNedCommented:
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
 
gwheato200Author Commented:
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
 
MrNedCommented:
* 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gwheato200Author Commented:
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
 
gwheato200Author Commented:
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
 
MrNedCommented:
* 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
 
gwheato200Author Commented:
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
 
MrNedCommented:
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
 
gwheato200Author Commented:
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
 
MrNedCommented:
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
 
gwheato200Author Commented:
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
 
MrNedCommented:
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
 
gwheato200Author Commented:
Hello,

For this server, what would you suggest for the size of the partitions in this alternative partition plan?
0
 
MrNedCommented:
/ <- 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
 
MrNedCommented:
I think I gave plenty of answers and suggest one of my comments as an answer.
0
 
QlemoC++ DeveloperCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now