Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are now having problems trouble to tackle the intensive traffic to our servers during peak hours; see this related question on tuning the DB. At the moment we are using a smaller cloud service for the server hosting. Currently we are looking at the possibility to move our server to AWS. The problems we are having are mainly related to our database server and therefore we would like to have AWS-instance which is significantly better than the one we are using now.

The stats for the cloud server where the database server is located are as follows:

  • 8 CORES
  • 16GB RAM
  • 100 GB, RAID-10 * SAS 15.000 RPM

Is there anything in particular you should think about when choosing what amazon instance to use for a PostgreSQL database server?

share|improve this question
1  
keep in mind that Virtual machines in general aren't designed for IO. If you're having problems it might be time to leave the cloud and get a hardware cluster. –  xenoterracide Nov 2 '12 at 13:15
    
BTW, what is powering that RAID setup? Is it a hardware RAID controller with power-protected cache in write-back mode? 'cos if it isn't, for your workload adding a write-through caching RAID controller will boost your performance like you won't believe. –  Craig Ringer Nov 2 '12 at 14:09
add comment

1 Answer 1

up vote 6 down vote accepted

Asking "What AWS instance to choose for write-heavy PostgreSQL database server" is somewhat like asking "which spoon should I use to cut up this loaf of bread".

The answer is: don't use a spoon, use a knife. Or in this case, real hardware like a dedicated server with a batch of good quality SSDs and a decent raid controller that has a battery-backed write-back cache. Or at least a VPS with very strong I/O performance guarantees.

If in doubt, benchmark.

That said, you can make AWS perform significantly better than the defaults by using provisioned I/O EBS volumes on EBS-optimized instances, improving performance while retaining data durability. Alternately, you can sacrifice durability in exchange for more performance by using a high I/O instance with the database on the instance store; in this case you will need a careful backup and replication scheme because all your data will be lost if your instance stops or crashes.

If you need more assistance, contact me directly.

share|improve this answer
    
Thanks for all the help Craig! We are seriously considering using a dedicated server instead. –  lorgartzor Nov 2 '12 at 16:15
    
@lorgartzor Glad to help. It'd be really useful if you'd respond to comments seeking clarification, though. You never did say what your RAID setup was, and that's very relevant to this and to your other question. –  Craig Ringer Nov 4 '12 at 2:31
    
@lorgartzor I have revised this answer based on new information. –  Craig Ringer Nov 20 '12 at 0:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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