Ask HN: How to handle 50GB of transaction data each day? (200GB during peak)
Currently we write away every single transaction to txt files and once every X minutes gather those and start inserting them into 2 Mysql shards. After that we run a bunch of group by queries to generate cross table reports. Currently our CMS is very limited by this, you can only view data relations in the predefined tables and we would like to have (a lot) more flexibility here.
Perhaps the biggest issue of all is that both I and the other developer do not have experience with handeling this much data. We both have worked on systems which handle a fair bit of data, but not nearly as much as this. We've been looking at MapReduce for MongoDB but already run into problems when we try to insert a weeks worth of data, which is about 350gb, because we only get so many IOPS at AWS. If we want more IOPS, it gets really expensive really fast. We're also not entirely sure yet if MongoDB and MapReduce is the way to go.
What I'm looking for is a few pointers to help us get headed in the right direction. Which databases should we be looking at? Should we be looking in an entirely different direction? Are there some (recent?) blog posts about this which can help us out?
79 comments
[ 3.6 ms ] story [ 141 ms ] threadMy advice is to step away from AWS (because of price as you noted). Bare metal servers are the best startup friend for large data in regards to performance and storage. This way you avoid virtualized CPU or distributed file systems that are more of a bottleneck than advantage.
Look for GorillaServers at https://www.gorillaservers.com/
You get 40Tb storage with 8~16 cores per server, along with 30Tb of bandwidth included for roughly 200 USD/month.
This should remove the IOPS limitation and provide enough working space to transform the data. Hope this helps.
In a shell (modified for speed and ease of use) get, insert, update data in a simple way, without all the fat from other mainstream (Java) solutions.
Have you looked at Google at all? Cloud Bigtable runs the whole of Google's Advertising Business and could scale per your requirements.
https://cloud.google.com/bigtable/docs/
But the full solution to their problems is to write the solution using google dataflow (they can test this for free by using the apache flink execution engine for dataflow).
If your data are event data, e.g. User activity, clicks, etc, these are non-volatile data which should preserve as-is and you want to enrich them later on for analysis.
You can store these flat files in S3 and use EMR (Hive, Spark) to process them and store it in Redshift. If your files are character delimited files, you can easily create a table definition with Hive/Spark and query it as if it is a RDBMS. You can process your files in EMR using spot instances and it can be as cheap as less than a dollar per hour.
* Use a Queue. RabbitMQ is quite good. Instead of writing to files, generate data/tasks on the queue and have them consumed by more than one client. The clients should handle inserting the data to the database. You can control the pipe by the number of clients you have consuming tasks, and/or by rate limiting them. Break those queue consuming clients to small pieces. Its ok to queue item B on the queue while processing item A.
* If you data is more fluid and changing all the time, and/or if it comes in JSON serializable format, consider switching to postgresql ^9.4, and use the JSONB columns to store this data. You can index/query those columns and performance wise its on par (or surpasses) MongoDB.
* Avoid AWS at this stage. like commented by someone here - bare metal is a better friend to you. You'll also know exactly how much you're paying each month. no surprises. I can't recommend Softlayer enough.
* Don't over complicate things. If you can think of a simple solution to something - its preferable than the complicated solution you might have had before.
* If you're going the queue route suggested above, you can pre-process the data while you get it in. If its going to be placed into buckets, do it then, if its normalised - do it then. The tasks on the queue should be atomic and idempotent. You can use something like memcached if you need your clients to communicate between eachother (like checking if a queue item is not already processed by another consumer and thus is locked).
Would it be worthwhile to switch from MongoDB to Postgres (when MySQL is being used alongside Mongo)? I'd have to do lots of testing with it to know for sure though, I guess.
I use Mongo for timeseries-like data, and have >1 billion documents in Mongo currently. Querying super old data is pretty slow (and not needed generally), but querying recent data (recent being the last month) is "fast enough".
Also switching from mysql to Postgres should see performance gains (though unlikely to be a bottle neck).
I'm not one of those mongo bashers. I've used mongo before for the same purpose you do - time series statistical data along side a postgres db for everything else. And it worked fine for me. However, at that time postgresql was pre 9.4. Today I would have kept it all in postgres and simply archived older data if needed. And to be fair, its not even a "problem" until you need to filter by one data source and sort by the other. We had to be creative when we got to those problems.
As for my choice of Postgres over mySql - that is for 2 reasons - first being that postgres is not owned by Oracle, and second that postgres is more mature, feature rich and reliable than mysql (i.e., better).
We just finished trying to set up some servers in softlayer and it was thee biggest pain in the ass we've had to deal with in a LONG time. Everything required a support ticket.
And that's if you're willing to overlook their reputation. Just Google "softlayer spam" and you'll see what I mean right away.
Glad that it worked for you. But wow... Never again.
But I agree with 'bare metal is your friend'.
In my previous job we processed 100s of millions of row updates daily on a table with much contention and ~200G size and used a single PostgreSQL server with (now somewhat obsoleted by modern PCIe SSDs) TMS RamSAN storage, i.e. Fibre-Channel based Flash. We had some performance bottlenecks due to many indexes, triggers etc. but overall, live query performance was very good.
If I was faced with a dataset of 50GB in a text file, being loaded into a database every day, I would normalize as much as possible to ensure that I'm not adding 50GB to a database everyday. What's the plan, to have a 20TB database in one year? No, instead I would focus on micro-optimizations at the database level.
I would inspect the datatypes you're storing your data in. If it's a value with a limited domain (an enum in programming parlance), make sure you determine the theoretical maximum amount of values it could be, and use the equivalent numeric value to serve as a surrogate key in a separate table. If I know a value is never going to exceed a few thousand distinct values, I could use a smallint, which only uses 2 bytes, verses recording a char/varchar which is going to probably burn at least 4-5 bytes every row, likely much more.
I believe ordering of columns matters in Postgres, so consult the documentation/Stackoverflow where swapping around column ordering can sometimes save several bytes per row.
You'll notice quickly that you may have repeating 'groups' of similar values across your surrogate keys. For instance, if you had a StateID and a CountryID in the same table, chances are good the combinations of StateID and CountryID are finite, and could themselves go into their own table, which has its own surrogate key, so instead of storing two surrogates on every row, you could use only one. (Incidentally, chances are even better that CountryID should just be a reference on the State table itself, but this is just to illustrate the point).
Now imagine you have sets of attributes that describe whole subsets of the information in your tables that are mutually exclusive. You could make two different tables with identical data structures. Then placing a row in one 'means' those other implicit value combinations. Thus you record information without actually storing it. The price here is on retrieval, you have to union the tables if you want to consider the whole set. That said, you may shave a large amount of storage. Views could be created to make this transparent to end users.
Don't use an auto-incrementing index on the table, instead store a value that is iterated separately, and 'take' ranges of values at a time that get cached by routines that mean to load the data.
Through this style of process, one can often turn a 1TB table into something a fraction of that size, at the cost of much harder to write queries. That said, there are other benefits, suddenly you can load values into the supporting tables first, then cache those values and load the main dataset without doing lookups into the additional related tables. The 'growth' of a database will slow considerably. Then you just preprocess the files, and 'COPY' them in, which should allow you load them on the order of minutes, not hours.
If you need to generate rich multi-dimension reports I recommend you create ETL pipeline into star-like sharded database (ala OLAP).
Dimensions normalization sometime dramatically reduce data volume, most of dimensions even can fit into RAM.
Actually 200Gb per day not so much in terms of throughput, you can manage it pretty well on PostgreSQL cluster (with help of pg_proxy). I think mySQL will also work OK.
Dedicated hardware will be cheaper then AWS RDS.
Realistically, this is what I would do (I work on something very similar but not really in adtech space):
1. Load data in text form (assuming it sits in S3) inside hadoop (EMR/Spark)
2. Generate reports you need based on your data and cache them in mysql RDS.
3. Serve the pre-generated reports to your user. You can get creative here and generate bucketed reports where user will fill its more "interactive". This approach will take you a long way and when you have time/money/people, maybe you can try getting fancier and better.
Getting fancy: If you truly want near-real time querying capabilities I would looks at apache kylin or linkedin pinot. But I would stay away from those for now.
Bigtable: As someone pointed out, bigtable is good solution (although I haven't used it) but since you are on AWS ecosystem, I would stick there.
If you just want reports (and are okay getting them in the matter of minutes), then you can continue storing them in flat files and using apache HIVE/PIG-equivalent software (or whatever equivalent is hot right now, im out of date on this class of software).
If you want a really good out-of-box solution for storage + data processing, google cloud products might be a really good bet.
* Don't use queues. Use logs, such as Apache Kafka for example. It is unlikely to lose any data, and in case of some failure, the log with transactions is still there for some time. Also Kafka guarantees order of messages, which might be important (or not).
* Understand what is the nature of data and what are the queries that are made later. This is crucial for properly modeling the storage system.
* Be careful with the noSQL cool-aid. If mature databases, such as postgreSQL can't handle the load, choose some NoSQL, but be careful. I would suggest HBase, but your mileage may vary.
* NoSQL DBs typically limits queries that you might issue, so the modelling part is very important.
* Don't index data that you don't need to query later.
* If your schema is relational, consider de-normalization steps. Sometimes it is better to replicate some data, than to keep relational schema and make huge joins across tables.
* Don't use MongoDB
I hope it helps!
I'm wondering about MongoDB. Have you tried it recently? It really improves with every release. We have a 20TB Mongo database and it's fast and problem free.
Also regarding the queues. In my personal experience a single RabbitMQ server with good hardware can easily handle 50GB a day.
We're stuck with it now, and I really wish we had used Postgres for biz data and Cassandra for the high volume non-relational data. They're just materialized views on-top of your event stream anyway.
Kafka = good. Postgres can do a TB; if you plan bigger than that consider Cassandra.
Also consider protobuf instead of json.gz.
JK it's too early :(
Investing in working with the hadoop ecosystem is a much better long term solution (if this is the case).
But the reason that the hadoop ecosystem is so good is because it's a growing ecosystem with a lot of momentum and a focus on processing large and growing amounts of data at scale and in a distributed/parallel fashion. If this is what you need the ecosystem is perfect.
Does the business really need exactly this? What is their actual goal? Are they aware of the effort and resources required to get this report?
(Hope that might be helpful! A bunch of us hang out on IRC at #cassandra if you're curious.)
AWS also has Kinesis, which is deliberately intended to be a sort of event drain. Under the hood it uses S3 and they publish an API and an agent that handles all the retry / checkpoint logic for you.
If you want to keep a lot of data for ad-hoc queries but only use a subset for regular queries then consider setting up separate dense compute and dense storage Redshift clusters as appropriate. As far as pricing goes there's a hell of a discount for going reserved once you've got things set up the way you want them.
Once you've got that running and the immediate fires are out you may want to look at Spark to pre-compute some of the reports (you can still push the computed data into Redshift tables to save changing the reporting too much), although I don't think spark as a service isn't really there in AWS right now, and running up an EMR cluster is seriously going to cost you.
Bigtable provides similar capabilities to Redshift in the google ecosystem, and looks okay although I wasn't blown away by it.
Right now the first 9(out of 12) chapters are finished and they're really insightful.
Most of the big data tools out there will work with data in this format -- BigQuery, Redshift, EMR. EMR can do batch processing against this data directly from s3 -- but may not be suitable for anything other than batch processing. BigQuery and/or Redshift are more targeted towards analytics workloads, but you could use them to saw the data into another system that you use for OLAP -- MySQL or Postgres probably.
BigQuery has a nice interface and it's a better hosted service than Redshift IMO. If you like that product, you can do streaming inserts in parallel to your gcs/s3 uploading process for more real-time access to the data. The web interface is not bad for casual exploration of terabytes of raw data. And the price isn't terrible either.
I've done some consulting in this space -- feel free to reach out if you'd like some free advice.
I couldn't find any email on your profile, do you mind sharing it?
Issue is, I don't know that all the tools support lz4 like they support gzip.
If the tool supports Hadoop's FileInputFormat or a stream of whatever underlying data (you can use the 4mc or lz4 command line tool) it will work.
You can get another 10% with gzip -7 vs the default I bet.
I don't find the compression/decompression the bottleneck in most of this work and even then an extra 10% isn't worth sacrificing the interoperability between all the systems that support .json.gz
Higher gzip levels I think only really affect the compression step -- decompression is the same cost. These are mostly write-once read many times sorts of datasets.
lz4 will put less load on your cpu allowing you to get more throughput from your processing tasks. But I agree most people don't reach these bottlenecks because most people use tools which are not optimized for performance like using jackson or jq instead of boon or rapidjson.
Look here for a reproducible test I ran on real data: https://news.ycombinator.com/item?id=10721645
Do you have some points where BQ is advantageous over Redshift?
Cost is probably cheaper as well, but I don't have apples to apples numbers to compare.
But the main point being, if you get your data in the cloud as .json.gz, you can use them all fairly easily. Maybe one solution fits 90% of your needs, and another one the other 10%. That's what I'm finding at least.
(I ingest, process, and warehouse 20TB of data a day)
20TB a day is a lot -- what is your stack, and what compression/file format are you using?
Personally, I've found the best results with bZip2. But, we use LZO and LZ4 and even Snappy for different things.
Spark is money, but we've forked it to make several important customization / enhancements. We have proprietary indexing, and we do all of our processing in-memory, of course.
Oh, and some of our data is sharded in Redshift. Redshift works well with GZip, so we do use it there. But, that particular part of our warehouse (or data lake, or whatever you call it these days) has much less data than we ingest since we reduce a lot.
Short answer: I think you're looking in the wrong direction, this problem isn't solved by a database but a full data processing system like Hadoop, Spark, Flink (my pick), or Google Cloud's dataflow. I don't know what kind of stack you guys are using (imo the solution to this problem is best made leveraging java) but I would say that you could benefit a lot from either using the hadoop ecosystem or using google cloud's ecosystem. Since you say that you are not experienced with that volume of data, I recommend you go with google cloud's ecosystem specifically look at google dataflow which supports autoscaling.
Long answer: To answer your question more directly, you have a bunch of data arriving that needs to be processed and stored every X minutes and needs to be available to be interactively analyzed or processed later in a report. This is a common task and is exactly why the hadoop ecosystem is so big right now.
The 'easy' way to solve this problem is by using google dataflow which is a stream processing abstraction over the google cloud that will let you set your X minute window (or more complex windowing) and automatically scale your compute servers (and pay only for what you use, not what you reserve). For interactive queries they offer google bigquery, a robust SQL based column database that lets you query your data in seconds and only charges you based on the columns you queried (if your data set is 1TB but the columns used in your query are only some short strings they might only charge you for querying 5GB). As a replacement for your mysql problems they also offer managed mysql instances and their own google bigtable which has many other useful features. Did I mention these services are integrated into an interactive ipython notebook style interface called Datalab and fully integrated with your dataflow code?
This is all might get a little expensive though (in terms of your cloud bill), the other solution is to do some harder work involving the hadoop ecosystem. The problem of processing data every X minutes is called windowing in stream processing. Your problems are solved by using Apache Flink, a relatively easy and fast stream processing system that makes it easy to set up clusters as you scale your data processing. Flink will help you with your report generation and make it easy to handle processing this streaming data in a fast, robust, and fault tolerant (that's a lot of buzz words) fashion.
Please take a look at the flink programming guide or the data-artisans training sessions on this topic. Note that the problem of doing SQL queries using flink is not solved (yet) this feature is planned to be released this year. However, flink will solve all your data processing problems in terms of the cross table reports and preprocessing for storage in a relational database or distributed filesystem.
For storing this data and making it available you need to use something fast but just as robust as mysql, the 'correct' solution at this time if you are not using all the columns of your table is using a columnar solution. From googles cloud you have bigquery, from the open source ecosystem you have drill, kudu, parquet, impala and many many more. You can also try using postgres or rethinkdb for a full relational solution or HDFS/QFS + ignite + flink from the hadoop ecosystem.
For the problem of interactively working with your data, try using Apache Zeppelin (free, scala required I think) or Databricks (paid but with lots of features, spark only i think). Or take the results of your query from flink or similar and interactively analyze those using jupyter/ipython(the solution I use).
The short answer is, dust off your old java textbooks. If you don't have a java dev on your team and aren't planning on hiring one, the google dataflow solution is way easier and cheaper in terms of engineering. If you help I d...
Tip: focus on how to backup and restore first, the rest will be easy!
* Logs are written to S3 (either ELB does this automatically, or you put them there)
* S3 can put a message into an SQS queue when a log file is added
* A "worker" (written in language of your choice running on EC2 or Lambda) pops the message off the queue, downloads the log, and "reduces" it into grouped counts. In this case a large log file would be "reduced" to lines where each line is [date, referrer domain, action, count] (e.g. [['2016-02-24', 'news.ycombinator.com', 'impression', 500], ['2016-02-24', 'news.ycombinator.com', 'conversion', 20], ...]
* The reduction can either be persisted in a db that can handle further analysis or you reduce further first.
We looked at just about every open source and commercial platform that we might use as a backend, and decided that none were appropriate, for scale, maturity, or fairness / scheduling. So we ended up building, from scratch, something that looks a bit like Google's Dremel / BigQuery, but runs on our own bare metal infrastructure. And then we put postgres on top of that using Foreign Data Wrappers so we could write standard SQL queries against it.
Some blog posts about the nuts and bolts you might find interesting:
https://www.kentik.com/postgresql-foreign-data-wrappers/
https://www.kentik.com/metrics-for-microservices/
If we were starting today, we might consider Apache Drill, although I haven't looked at the maturity and stability of that project recently.