20

I am writing a proof-of-concept app which is intended to take live clickstream data at the rate of around 1000 messages per second and write it to Amazon Redshift.

I am struggling to get anything like the performance some others claim (for example, here).

I am running a cluster with 2 x dw.hs1.xlarge nodes (+ leader), and the machine that is doing the load is an EC2 m1.xlarge instance on the same VPC as the Redshift cluster running 64 bit Ubuntu 12.04.1.

I am using Java 1.7 (openjdk-7-jdk from the Ubuntu repos) and the Postgresql 9.2-1002 driver (principally because it's the only one in Maven Central which makes my build easier!).

I've tried all the techniques shown here, except the last one.

I cannot use COPY FROM because we want to load data in "real time", so staging it via S3 or DynamoDB isn't really an option, and Redshift doesn't support COPY FROM stdin for some reason.

Here is an excerpt from my logs showing that individual rows are being inserted at the rate of around 15/second:

2013-05-10 15:05:06,937 [pool-1-thread-2] INFO  uk.co...redshift.DatabaseWriter - Beginning batch of 170
2013-05-10 15:05:18,707 [pool-1-thread-2] INFO  uk.co...redshift.DatabaseWriter - Done
2013-05-10 15:05:18,708 [pool-1-thread-2] INFO  uk.co...redshift.DatabaseWriter - Beginning batch of 712
2013-05-10 15:06:03,078 [pool-1-thread-2] INFO  uk.co...redshift.DatabaseWriter - Done
2013-05-10 15:06:03,078 [pool-1-thread-2] INFO  uk.co...redshift.DatabaseWriter - Beginning batch of 167
2013-05-10 15:06:14,381 [pool-1-thread-2] INFO  uk.co...redshift.DatabaseWriter - Done

What am I doing wrong? What other approaches could I take?

dty
  • 18,795
  • 6
  • 56
  • 82

4 Answers4

22

Redshift (aka ParAccel) is an analytic database. The goal is enable analytic queries to be answered quickly over very large volumes of data. To that end Redshift stores data in a columnar format. Each column is held separately and compressed against the previous values in the column. This compression tends to be very effective because a given column usually holds many repetitive and similar data.

This storage approach provides many benefits at query time because only the requested columns need to be read and the data to be read is very compressed. However, the cost of this is that inserts tend to be slower and require much more effort. Also inserts that are not perfectly ordered may result in poor query performance until the tables are VACUUM'ed.

So, by inserting a single row at a time you are completely working against the the way that Redshift works. The database is has to append your data to each column in succession and calculate the compression. It's a little bit (but not exactly) like adding a single value to large number of zip archives. Additionally, even after your data is inserted you still won't get optimal performance until you run VACUUM to reorganise the tables.

If you want to analyse your data in "real time" then, for all practical purposes, you should probably choose another database and/or approach. Off the top of my head here are 3:

  1. Accept a "small" batching window (5-15 minutes) and plan to run VACUUM at least daily.
  2. Choose an analytic database (more $) which copes with small inserts, e.g., Vertica.
  3. Experiment with "NoSQL" DBs that allow single path analysis, e.g., Acunu Cassandra.
Joe Harris
  • 13,671
  • 4
  • 47
  • 54
  • 2
    Thanks for your reply. I understand all of your points, but it doesn't really explain why 10000 x single row inserts should be so much slower than bulk loading a single 10000 row CSV from S3 - I mean, the compression analysis, etc. still has to be done. Bear in mind I'm not talking about 10000 transactions here. Even a single transaction with 10000 inserts runs slowly and Redshift should be able to minimise block writes in that scenario. – dty May 20 '13 at 19:08
  • And besides which, we're not talking about a few 10's of percentage points difference here either! We're talking about 15 rows/sec compared with the 100,000 rows/sec I've subsequently achieved with S3 bulk loads! – dty May 20 '13 at 19:08
  • 1
    That's the nature of this particular beast unfortunately. I wouldn't assume that 10k inserts wrapped in a transaction is processed in bulk, especially if you're saying it's no better. I suspect that with Redshift it's either bulk or row-by-row. Write the 10k to a CSV and the bulk load it to see the diff. – Joe Harris May 22 '13 at 15:26
  • 5
    As I mentioned - I've seen up to 100k rows/sec when loading from S3. I just find it hard to believe there's such a large difference. I mean - if you said I can do 100k rows/sec from a CSV/S3, but only 20k/sec via SQL INSERT statements, I'd be shocked at the difference. But 100k vs 15 just makes no logical sense whatsoever! – dty May 22 '13 at 16:31
  • BTW, since I answered this I've been looking at SAP's HANA One database which might actually be much better for your scenario. It's available on the AWS Marketplace for $1/hr over the instance cost. There are lots of things to consider in that case but I'd at least give it a try if you have some time. – Joe Harris May 22 '13 at 19:11
5

The reason single inserts are slow is the way Redshift handles commits. Redshift has a single queue for commit.

Say you insert row 1, then commit - it goes to the redshift commit queue to finish commit.

Next row , row 2, then commit - again goes to the commit queue. Say during this time if the commit of row 1 is not complete, row 2 waits for the commit of 1 to complete and then gets started to work on row 2 commit.

So if you batch your inserts, it does a single commit and is faster than single commits to the Redshift system.

You can get commit queue information via the issue Tip #9: Maintaining efficient data loads in the link below. https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-techniques-for-amazon-redshift/

zethw
  • 323
  • 1
  • 12
scorpio155
  • 51
  • 1
  • 1
2

We have been able to insert 1000 rows / sec in Redshift by batching several requests together in the same INSERT statement (in our case we had to batch ~200 value tuples in each INSERT). If you use an ORM layer like Hibernate, you can configure it for batching (eg see http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/batch.html)

xpapad
  • 4,376
  • 1
  • 24
  • 25
  • Hi @xpapad, can you share a link that shows how to insert records into the database with Hibernate and Redshift? I was trying to do this but the merge and persist methods not worked. The transaction ended ok, but no record was inserted. I was able to do this using a manual insert query but I would like to do this using the merge and persist methods. – GarouDan May 16 '18 at 17:56
  • 1
    Are you sure? this isn't working for me. Using the JDBC batch APIs still seems to have the performance of single inserts. Do you mean having a ton of INSERTS in a single _transaction_ helps (which we've observed)? – J. Dimeo Oct 11 '18 at 17:48
1

I've been able to achieve 2,400 inserts/second by batching writes into transactions of 75,000 records per transaction. Each record is small, as you might expect, being only about 300 bytes per record.

I'm querying a MariaDB installed on an EC2 instance and inserting the records into RedShift from the same EC2 instance that Maria is installed on.

UPDATE

I modified the way I was doing writes so that it loads the data from MariaDB in 5 parallel threads and writes to RedShift from each thread. That increased performance to 12,000+ writes/second.

So yeah, if you plan it correctly you can get great performance from RedShift writes.

Jonathan Leger
  • 73
  • 3
  • 9