PostgreSQL High Performance Setup

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).

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?

* 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

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 )

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.

* 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

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)

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.

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?

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)

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)?

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.

Hello,

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

I think I gave plenty of answers and suggest one of my comments as an answer.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.