Postgresql – Postgres Plus Cloud Database on AWS for write heavy social game

awscloudenterprisedbpostgresql

I'm one of four founders of a big social gaming iPhone app in Sweden. We now suffer from severe database server issues so I'm turning to you guys to get some help.
First of all, let me get this out there: I'm not the tech guy in charge of the servers but I assist with research.

We got about 8k querys/5 sec to our PostgreSQL DB and we're growing fast. The DB server is already behaving stressed out. It's a 8 core VPS with 16 gb ram.

We're looking into the possibilities of using a Postgres Plus Cloud Database solution from EnterpriseDB running on AWS. (http://www.enterprisedb.com/cloud-database)

1) Anyone have experience in this matter?

2) We've also been looking into the options to use a dedicated server but we need the possibilities to scale the server up to 20-30 times. Is this relatively easy with EnterpriseDB's solution?

3) If i got it all right: EnterpriseDB offer a cluster solution which theoretically would solve our problems. Am I right?

Best Answer

For high performance systems, I think virtualization usually causes a lot more problems than it solves. Save yourself the headache. Build a decent db server inhouse.

It is hard to say what your current problem is, and virtualization makes troubleshooting much harder. Is the load cpu bound? Is it I/O bound? Are you having concurrency issues? Something else?

Now, if your 8k queries every 5 sec are not very complex queries, then the immediate question is why are you having performance problems. It may just take some db tuning. I have seen some indications that you should be able to get several times that on good hardware if things are well tuned. Again virtualization is probably making things harder to fix rather than easier.

Finally, I would highly recommend looking into Postgres-XC. It allows you to do sharding for write-extensibility while managing concurrency and consistency a layer above. I would start tuning what you have and move from there though.

Related Solutions

AWS – Best Instance for Write-Heavy PostgreSQL Database Server

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.

AWS Database Services for Logging Records like log4j or log4perl

Since you're looking at storing and querying 135,000,000 * 30 * 10 records that likely wouldn't benefit from traditional RDBMS features, I think Hadoop would be the way to go.

My experience is exclusively with Microsoft Azure - if you're not attached to Amazon you might check it out. Either way, Hadoop is open source so the majority of operations and activity should be the same regardless of your platform provider. I'd go with an option that allows you to test the performance of different cluster sizes so you're only paying for what you really need (especially with the 10x increase as an unknown right now).

The following link, despite its Azure focus, gives you a good tutorial of how to query log4j records with Hive. Amazon EMR appears to have great documentation.

Key points for your situation: Create a table structure over your files:

    CREATE EXTERNAL TABLE log4jLogs
    (t1 string, t2 string, t3 string, t4 string, t5 string, t6 string, t7 string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ';'
    STORED AS TEXTFILE LOCATION 'yourdrive:///logs/';

With the table defined you can then query similar to SQL:

    SELECT t4 AS sev, COUNT(*) AS ErrorCount
    FROM log4jLogs
    WHERE t4 = '[ERROR]'
    AND INPUT__FILE__NAME LIKE '%.log' GROUP BY t4;

With the EXTERNAL definition you can have multiple files and the table will include them all, so just drop them in the appropriate directory. If you're keeping 30 days worth you may want to get fancier and separate days into different folder structures/partition your Hive table.

Best Answer

Related Solutions

AWS – Best Instance for Write-Heavy PostgreSQL Database Server

AWS Database Services for Logging Records like log4j or log4perl

Related Question