6 ms·

Look like a worthy competitor to Elasticsearch for analytics: it has custom partitioning strategies, true parallel querying, and support for columnar table stor

Look like a worthy competitor to Elasticsearch for analytics: it has custom partitioning strategies, true parallel querying, and support for columnar table storage, and has much of Postgres' rich SQL implementation. The parallel loading looks good, but I'm concerned that the single master means it's a bottleneck for writes -- is this the case, or is there a way to distribute writes across segments without involving the master? The other concern is that this isn't just "horizontally partitioned Postgres". It's forked from a very old version (8.2) of Postgres, and so doesn't have things like hot standby, streaming replication, the JSON datatype, GIN indexes (does it have GiST?), arrays, etc. It looks like it's optimized for parallel workloads, not for general use. Anyone here with any experience with Greenplum who can perhaps speak about their work and the things that Greenplum is good at?

jacques_chester 11y ago
> The parallel loading looks good, but I'm concerned that the single master means it's a bottleneck for writes -- is this the case, or is there a way to distribute writes across segments without involving the master? Yes, this is what gpfdist[1] is for. Used properly, data can be bulk loaded in parallel across worker nodes without a master bottleneck. I've never used it, but our field engineers tell stories of heroism and victory that typically starred gpfdist as the magic sword. In terms of what it's good at: really big queries on really big data sets. Note in particular the integration with the Apache MADlib (incubating) machine learning toolkit[2], which was opensourced and donated to the ASF earlier this year. [1] http://gpdb.docs.pivotal.io/4330/utility_guide/admin_utilities/gpfdist.html http://gpdb.docs.pivotal.io/4330/utility_guide/admin_utiliti... [2] http://madlib.net/ http://madlib.net/
- lobster_johnson 11y ago
  Thanks, useful. I guess I was too subtle in my comment; what I mean is, this looks like geared towards long, periodic bulk loads and not granular OLTP/webapp-type workloads where lots of clients write small transactions. We're using ElasticSearch for analytics, and being able to write to any node is really nice. We stream events in real time, and only do bulk loads when we need to change the schema or reprocess the data. But ES has its share of problems, and somethings like Greenplum would be preferable, if it can handle the write pattern.
  - jacques_chester 11y ago
    I'm not sure, to be honest. Hopefully some of our greenplum specialists spot this post and hop in.
  - parasubvert 11y ago
    Gpfdist is really just a binary format over HTTP, so it does handle streaming use cases well. Spring XD implemented a Java reactive version of the GPFdist protocol, so you could use that to wire it up to your existing app pretty easily (or just copy the Sink code which is pretty straightforward). Here are some docs and synthetic benchmarks: http://docs.spring.io/spring-xd/docs/current/reference/html/#gpfdist http://docs.spring.io/spring-xd/docs/current/reference/html/...
  - mavam 11y ago
    Could you elaborate on the issues you have with ElasticSearch? Performance/Scaling? Usability?
    - threeseed 11y ago
      I've attempted to load huge amounts of data into ElasticSearch. It is a bit fiddly to disable indexing and the sheer write performance is poor against dedicated databases. It is a testament to how good ElasticSearch is that people do use it as a database.
      - lobster_johnson 11y ago
        ES performs well, but we don't use it as a primary data source, and I would advise heavily against using it as one. Things are getting better with the new transaction log, but it's still extremely prone to falling over and to getting consistency issues, and some of the built-in automation can bite you in the ass. Recently we had a node — fortunately an expendable testing box — run out of disk space. ES stopped working, which is fine, but when we restarted it, several of the shards were corrupt, and there was no way to get it working again without dropping the index and recreating it. In another instance, we started getting client downtime because ES had suddenly detected low disk space on one node (its "high watermark" setting is extremely conservative and not based on a statistical regression of when it's going to run out), and had started relocating shards. When it was done, one of the shards were stuck in "RELOCATING" but marked as done; only restarting ES fixed that issue. When we freed up some disk space, it gave us more downtime when it decided to move the shards back. In theory, ES was behaving correctly, but the automation behaviour that kicked in was unnecessary and not desirable in that use case. ES has lots of good parts, but it also has a surprising number of bad parts — ugly stuff you just won't find in Postgres, for example.
- alexatkeplar 11y ago
  Is there an existing library (like a Cascading Tap) for parallel loading Greenplum from Hadoop? I couldn't find one.
  - jacques_chester 11y ago
    You should be able to build a pipeline with Spring XD, it has an HDFS source and gpfdist sink out of the box. http://projects.spring.io/spring-xd/ http://projects.spring.io/spring-xd/
nl 11y ago
Greenplum is designed for datawarehousing. The analytics it is built for aren't the kind that ElasticSearch is typically used for. Greenplum/datawarehouse style analytical are things like: What is the average spend of female customers with 2 children who live in postcode AAAA or BBBB. Break it down by day of week and group by their marital status. Now for the top cohort of buyers on Mondays, give me a breakdown of the 3 most popular products and our profit margin on each one. This is often called "Business Analytics" (BI) You could make ElasticSearch do that of course, but it wouldn't be much fun. Given that context, the single master limitation turns out not be be a huge problem. Typically Greenplum is setup to load data from the OLAP-style online system, and the amount of data loaded is very predictable. Same with things like hot standby and replication. In the Datawarehouse world it isn't uncommon for there to be nightly periods where a batch dataload occurs and/or nightly reporting is done, and the availability of external interfaces to the DB during that time maybe restricted.
- lobster_johnson 11y ago
  Thanks, that makes a lot of sense. We have those types of queries, too, which is where ES definitely breaks down. We wouldn't mind switching those parts (the "BI") of the analytics into something with higher latency.
  - threeseed 11y ago
    One option there is to use Spark. You can then write SQL, Scala, Python, R to interact with ElasticSearch. I can't recall the performance but against Cassandra, HDFS, HBase, MongoDB etc. it is very fast.
- blumkvist 11y ago
  Hey, forgive my ignorance, I'm the guy who uses this stuff, not the guy who builds it. My understanding was that, typically, the DW is where the data gets ETLed from various places and lives in a normalized environment. Then from that big pool, departments get built columnar or OLAP databases for specific analyses they want to do, and I connect not to the DW but to the OLAP instance (we call it Datamart here). Was I living a lie? At first I thought that your master-segment system is the same, but then I read your comment. I looked into Greenplum a while back and wanted it because of Madlib, but my guys said "nope" and went with MS.

jacques_chester 11y ago

> The parallel loading looks good, but I'm concerned that the single master means it's a bottleneck for writes -- is this the case, or is there a way to distribute writes across segments without involving the master? Yes, this is what gpfdist[1] is for. Used properly, data can be bulk loaded in parallel across worker nodes without a master bottleneck. I've never used it, but our field engineers tell stories of heroism and victory that typically starred gpfdist as the magic sword. In terms of what it's good at: really big queries on really big data sets. Note in particular the integration with the Apache MADlib (incubating) machine learning toolkit[2], which was opensourced and donated to the ASF earlier this year. [1] http://gpdb.docs.pivotal.io/4330/utility_guide/admin_utilities/gpfdist.html http://gpdb.docs.pivotal.io/4330/utility_guide/admin_utiliti... [2] http://madlib.net/ http://madlib.net/

lobster_johnson 11y ago
Thanks, useful. I guess I was too subtle in my comment; what I mean is, this looks like geared towards long, periodic bulk loads and not granular OLTP/webapp-type workloads where lots of clients write small transactions. We're using ElasticSearch for analytics, and being able to write to any node is really nice. We stream events in real time, and only do bulk loads when we need to change the schema or reprocess the data. But ES has its share of problems, and somethings like Greenplum would be preferable, if it can handle the write pattern.
- jacques_chester 11y ago
  I'm not sure, to be honest. Hopefully some of our greenplum specialists spot this post and hop in.
- parasubvert 11y ago
  Gpfdist is really just a binary format over HTTP, so it does handle streaming use cases well. Spring XD implemented a Java reactive version of the GPFdist protocol, so you could use that to wire it up to your existing app pretty easily (or just copy the Sink code which is pretty straightforward). Here are some docs and synthetic benchmarks: http://docs.spring.io/spring-xd/docs/current/reference/html/#gpfdist http://docs.spring.io/spring-xd/docs/current/reference/html/...
- mavam 11y ago
  Could you elaborate on the issues you have with ElasticSearch? Performance/Scaling? Usability?
  - threeseed 11y ago
    I've attempted to load huge amounts of data into ElasticSearch. It is a bit fiddly to disable indexing and the sheer write performance is poor against dedicated databases. It is a testament to how good ElasticSearch is that people do use it as a database.
    - lobster_johnson 11y ago
      ES performs well, but we don't use it as a primary data source, and I would advise heavily against using it as one. Things are getting better with the new transaction log, but it's still extremely prone to falling over and to getting consistency issues, and some of the built-in automation can bite you in the ass. Recently we had a node — fortunately an expendable testing box — run out of disk space. ES stopped working, which is fine, but when we restarted it, several of the shards were corrupt, and there was no way to get it working again without dropping the index and recreating it. In another instance, we started getting client downtime because ES had suddenly detected low disk space on one node (its "high watermark" setting is extremely conservative and not based on a statistical regression of when it's going to run out), and had started relocating shards. When it was done, one of the shards were stuck in "RELOCATING" but marked as done; only restarting ES fixed that issue. When we freed up some disk space, it gave us more downtime when it decided to move the shards back. In theory, ES was behaving correctly, but the automation behaviour that kicked in was unnecessary and not desirable in that use case. ES has lots of good parts, but it also has a surprising number of bad parts — ugly stuff you just won't find in Postgres, for example.
alexatkeplar 11y ago
Is there an existing library (like a Cascading Tap) for parallel loading Greenplum from Hadoop? I couldn't find one.
- jacques_chester 11y ago
  You should be able to build a pipeline with Spring XD, it has an HDFS source and gpfdist sink out of the box. http://projects.spring.io/spring-xd/ http://projects.spring.io/spring-xd/

nl 11y ago

Greenplum is designed for datawarehousing. The analytics it is built for aren't the kind that ElasticSearch is typically used for. Greenplum/datawarehouse style analytical are things like: What is the average spend of female customers with 2 children who live in postcode AAAA or BBBB. Break it down by day of week and group by their marital status. Now for the top cohort of buyers on Mondays, give me a breakdown of the 3 most popular products and our profit margin on each one. This is often called "Business Analytics" (BI) You could make ElasticSearch do that of course, but it wouldn't be much fun. Given that context, the single master limitation turns out not be be a huge problem. Typically Greenplum is setup to load data from the OLAP-style online system, and the amount of data loaded is very predictable. Same with things like hot standby and replication. In the Datawarehouse world it isn't uncommon for there to be nightly periods where a batch dataload occurs and/or nightly reporting is done, and the availability of external interfaces to the DB during that time maybe restricted.

lobster_johnson 11y ago
Thanks, that makes a lot of sense. We have those types of queries, too, which is where ES definitely breaks down. We wouldn't mind switching those parts (the "BI") of the analytics into something with higher latency.
- threeseed 11y ago
  One option there is to use Spark. You can then write SQL, Scala, Python, R to interact with ElasticSearch. I can't recall the performance but against Cassandra, HDFS, HBase, MongoDB etc. it is very fast.
blumkvist 11y ago
Hey, forgive my ignorance, I'm the guy who uses this stuff, not the guy who builds it. My understanding was that, typically, the DW is where the data gets ETLed from various places and lives in a normalized environment. Then from that big pool, departments get built columnar or OLAP databases for specific analyses they want to do, and I connect not to the DW but to the OLAP instance (we call it Datamart here). Was I living a lie? At first I thought that your master-segment system is the same, but then I read your comment. I looked into Greenplum a while back and wanted it because of Madlib, but my guys said "nope" and went with MS.