문제 설명
AWS Redshift JDBC 삽입 성능 (AWS Redshift JDBC insert performance)
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?
참조 솔루션
방법 1:
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:
- Accept a "small" batching window (5‑15 minutes) and plan to run VACUUM at least daily.
- Choose an analytic database (more $) which copes with small inserts, e.g., Vertica.
- Experiment with "NoSQL" DBs that allow single path analysis, e.g., Acunu Cassandra.
방법 2:
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/
방법 3:
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)
방법 4:
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.
(by dty、Joe Harris、scorpio155、xpapad、Jonathan Leger)