Highly random PostgreSQL database host

Question

We're working with PostgreSQL very successfully on a moderately large project (approximately 12GB of data in our working set)

Currently we're on a 2GB RAM machine with 7200RPM disks. You can imagine the performance goes to hell quite quickly, even with clustered tables and proper indexes, optimised queries and design, etc. We spend most of our time waiting on I/O, for both read and write operations.

We're putting another 2GB of RAM in the box and a 10kRPM Velociraptor disk but those are just stopgap measures while we work out how to go on from here. The whole set is updated very often, so SSDs are out (too expensive too- this project is being run by two students with no money!), and I'm kind of interested to hear if anyone else has any suggestions for cheap (<£100/mo) server/VPS solutions which would involve 12-16GB of RAM and/or snappy hard disks. Or, even better, an alternative solution to the problem. Are there any hosts who specialise in database hosting?

This is kind of a 'oh god there has to be a better way' post, but the basic gist of this is- are there hosts or solutions available at this cost point, if not why not, and what are the cheap solutions to this sort of problem?

Answer 1

It's probably worth double checking that you've tuned the server appropriately for the hardware you're on. Settings to be concerned with include shared_buffers, effective_cache_size, your checkpoint_settings, wal_buffer size, and I'd also make sure your work_mem isnt set too high. There's a wiki page I helped to write which covers most of this stuff, it's a good place to start: http://wiki.postgresql.org/wiki/Tuning%5FYour%5FPostgreSQL%5FServer

The next question might be to look at the queries that your running to make sure the query plans aren't causing more i/o than necessary. Better queries and improved indexes might help here.

Oh another thought; you didn't explain what the underlying disks actually were (and/or what you plan to do with the new disks), but it might be worth setting up some type of raid system, or splitting your xlog files to a separate set of disks.

Personally I don't know of any database hosting for Postgres at that price point that would include deeper investigation of what's going on with your database; most of them would leave that to you.

Answer 2

Hosting is a no-brainer - check hetzner. I'm using them, my friends are using them, and we are really happy with the offering.

On the other hand - to give you some perspective - 12GB is definitely not "moderately large" dataset. I would have hard time classifying it even as medium.

This is not to talk down to you - there are a lot of very important databases that are small. And a lot of big ones that are not that crucial. It all depends on how important the data and/or operations on the data are.

As for tuning - check what xzilla said, and read about EXPLAIN ANALYZE command/output. This is the single most useful part of PostgreSQL.